Press "Enter" to skip to content

Month: August 2023

The Trials and Tribulations of Migrating SSISDB

Published 2023-08-23 by Kevin Feasel

Andrea Allred packs up boxes and moves the database across town:

Recently I needed to move a dev instance from a physical server to a virtual one and needed to do SQL upgrades. We decided it was time to spin up a new dev instance and that required moving SSISDB. I will be honest, I was not prepared for all the things. I also did not have a password for the encryption so I had to force things to work. Because this was a dev server, I wasn’t as worried about what I would lose, I just wanted it to work. Here is what I did.

There’s a lot more here than “restore database from backup,” so click through to read the steps.

Comments closed

Delegating Permissions Management in SQL Server

Published 2023-08-23 by Kevin Feasel

Andreas Wolter has people for that:

Both roles can GRANT/DENY/REVOKE any permission within a database. Since that makes members of these roles super powerful, their membership should always be tightly controlled and audited. (also see: Security concept: Audit Trail).
Consequently, most customers just keep this power to the main Administrators-group.

However, there are cases where it is useful to enable a set of non-admin users—let’s call them LowPermission-Managers—to work in a more self-sufficient way and enable them to grant some permissions to other users without the need to involve “the Administrators”.
The emphasis is on “some permissions” asthis only makes sense for low privileges. Making the LowPermissionManagers members of db_owner or db_securityadmin -roles would defeat the purpose.

Read on for two techniques which allow you to do just that.

Comments closed

Text-to-Video with Azure Open AI and Semantic Kernel

Published 2023-08-22 by Kevin Feasel

Sabyasachi Samaddar continues a series on generating video from a series of text prompts:

Welcome back to the second part of our journey into the world of Azure and OpenAI! In the first part, we explored how to transform text into video using Azure’s powerful AI capabilities. This time, we’re taking a step further by orchestrating our application flow with Semantic Kernel.

Semantic Kernel is a powerful tool that allows us to understand and manipulate the meaning of text in a more nuanced way. By using Semantic Kernel, we can create more sophisticated workflows and generate more meaningful results from our text-to-video transformation process.

In this part of the series, we will focus on how Semantic Kernel can enhance our application and provide a smoother, more efficient workflow. We’ll dive deep into its features, explore its benefits, and show you how it can revolutionize your text-to-video transformation process.

Read on for an understanding of how Semantic Kernel fits in and what you can do with it.

Comments closed

Against Waiting for Microsoft Fabric

Published 2023-08-22 by Kevin Feasel

Paul Andrew follows Betteridge’s Law of Headlines:

But, lets prepare for it in terms of the technical capabilities we line up in our existing data architecture.

The hype curve around Microsoft Fabric since its announcement earlier in the year has been huge. The problem is, we now face some difficult questions in terms of our technology estate. Especially if we have designs and a project already in flight using other Azure Resources.

Read on for Paul’s thoughts on the matter and why you shouldn’t wait until Microsoft Fabric is officially out—use what is available in the meantime and then decide whether you want to make a transition. Paul leaves one thing in the margins that I would want to make clear: if this is your plan, avoid the dedicated SQL pool unless you absolutely need it or plan to stay on Synapse once Fabric is GA.

Comments closed

Decrypting Stored Procedures with SQL Compare

Published 2023-08-22 by Kevin Feasel

Steve Jones cracks the case:

I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

In a previous post I set up some procedures and then showed code to decrypt them. Here, I’ll use SQL Compare 15, which makes this easy.

Read on to see how it works.

Comments closed

Index Maintenance in Azure SQL DB with Elastic Jobs

Published 2023-08-22 by Kevin Feasel

Scott Klein continues a series on index maintenance in Azure SQL Database:

It’s finally here: the third and final blog post about Azure automation. The first blog covered how to automate Azure using Runbooks, the second blog post showed how to do it using Azure Functions, and this blog post will cover how to do it using Azure Elastic jobs.

To be fair, I titled this blog “Automating Azure with Elastic Jobs”, but Elastic Jobs isn’t part of Azure Automation, so please don’t get confused. The goal with this is to demonstrate how to automate some Azure database DBA tasks.

Read on for a brief primer on elastic jobs and how to use them.

Comments closed

DATEDIFF() and Month Boundaries

Published 2023-08-22 by Kevin Feasel

Deb Melkin fed the mogwai after midnight:

I was working on a query this week that reminded me of a fun quirk when working with dates and the DATEDIFF function in particular.

I have a process that takes a while to run. Because of all of the moving parts to keep track of, I have an audit table to track what I’m doing to collect basic info like when did it start, when did it end, etc. I created a simple report for myself to break things down so I can report back to the team. I threw together a simple SQL statement, using DATEDIFF to figure out the how long things took. Looking at the results, I saw some interesting results.

Read on for two queries, one which has a bit of a problem and one which strives to correct that problem.

Comments closed

Tenant Configuration in Microsoft Fabric

Published 2023-08-22 by Kevin Feasel

Marc Lelijveld collects some data:

It has been quiet for a few weeks due to summer break. But now, it’s time to ramp up again and continue posting about Fabric. This time, I’ll start with a question that many Fabric (and Power BI) users ask themselves: “What does my tenant configuration look like?”. Often, users find themselves eager to explore new features they’ve come across online. But somehow, they cannot get it to work, or the feature does not even show for them.

In this blog, I’ll elaborate on the challenges and scenarios in which questions like these come up, and what you can do as a Fabric / Power BI administrator to ease answering this question.

Read on for one of the most common scenarios.

Comments closed

Omitted Variables and Logistic Regression

Published 2023-08-21 by Kevin Feasel

John Mount misses a variable:

I would like to illustrate a way which omitted variables interfere in logistic regression inference (or coefficient estimation). These effects are different than what is seen in linear regression, and possibly different than some expectations or intuitions.

This is an interesting article and there’s a really good comment helping to explain this effect in epidemiology.

Comments closed