Database-Administration

Fixing Missing SQL Agent Jobs After a Migration

I’ve been doing migrations fairly continuously for the past 18 months. PowerShell has been my primary mechanism for many parts of the process, including copying jobs from the source SQL Server to the target. That has worked almost without incident each time. However, recently, an app team noticed that there were SQL Server Agent jobs missing on their new 2022 SQL Servers.  Because the first couple of missing jobs also existed on their Development environment, they were able to recreate those jobs in production. They naturally expressed concern that other jobs may be missing.

Continue reading

How to Check SQL Server Security Part 3

In the previous two parts we’ve reviewed investigating logins to SQL Server, which operate at the top server level. We’ve also reviewed looking at logins at the database level. Today, I’ll be showcasing another couple of ways to view SQL Server security information.

Open source provides a lot of fantastic scripts and software. One of the more popular ones is the First Responder Kit. In that collection of scripts is sp_Blitz. This script is great because it provides a lot of information. The script results can also be problematic, because it provides so much information. On my little SQL Server I use for testing scripts and writing demos, sp_Blitz returned 68 rows across 8 priority levels or categories of issues. Again, wonderful if you want to see all the things! But, what if you’re trying to focus on a particular type of issue, like security? What do you do then?

Continue reading

How to Check SQL Server Security Part 2

In a previous post we started reviewing SQL Server security at the server level by looking at Logins. Is that enough though? It definitely isn’t! We need to look at the users at the database level as well. So, let’s do that.

Again, the internet is awash with scripts and options so let me give you a place to start.

Kenneth Fisher’s sp_DBPermissions

From the comments in the stored procedure, “This stored procedure returns 3 data sets. The first data set is the list of database principals, the second is role membership, and the third is object and database level permissions.”

Continue reading

Speaking at the PASS Data Community Summit

My favorite thing that happened this year was that I was a speaker at the PASS Data Community Summit in November. I presented an online live stream at the world’s largest SQL Server conference, and I did it while in-person at the conference in Seattle! Speaking at the Summit has been a bucket list type item for me for many years.

There is a sense of deep humility in being chosen to present at this conference. The SQL Server community is vast and full of really smart people. Because of this, being chosen to speak is a big honor. I had put in a lot of effort over the last year or so trying to give back to the community that has helped me so much over the years. The Summit has a wide range of topics and technical levels. There is something for everyone who works with SQL Server.

Continue reading

SQL Server Indexing Basics - 7 Lessons Learned

SQL Server indexing basics are critical to query and server performance. Resources, like CPU and disk, are affected by the indexes that you have, or the ones you’re missing.

In the StackOverflow2013 database we’re going to look at Badges and users. Specifically, I want to start by seeing what badges a user has and when that user received them. Some badges, because of the type of badge it is, can be awarded more than once.

Continue reading

6 Reasons to Learn PowerShell

SQL Server professionals may be reluctant to start using PowerShell.

After all, you already know T-SQL well and can do a lot with that to manage SQL Server.

I’ve been using PowerShell to manage SQL Server for the last 5 years and it has revolutionized how I interact with SQL Server. For example, I recently needed to update a SQL Agent job across the entire production environment. That’s almost 100 SQL Servers. I have a Central Management Server with folders for each product in my employer’s portfolio. Then there are folders for Dev, Staging and Prod. I could have manually clicked into the first product family then the prod folder and right clicked that to make a connection to all the SQL Servers in that prod folder. Then I copy paste my script in there to run it against those prod servers I’ve connected to.

Continue reading

SQL Server 2019 CU 16 Introduces Problems for Log Shipping

As a data professional managing SQL Servers, you need to be thinking about Windows and SQL Server patching.

You want to stay up to date, but you also have to be careful because sometimes staying fully up to date comes with the risk that a new patch might break a feature you are using. This is possibly the case for SQL Server 2019 CU 16. If you’re using Log Shipping on a database that has TDE enabled and compression is enabled on those backups, then you need to take heed.

Continue reading

One Key Mistake When Installing SQL Server

There’s one sure-fire way to set yourself and your applications up for failure when using SQL Server. I’ve seen all kinds of people do this.

It will cost you performance and create maintenance headaches down the road.

Let me explain what it is and why it causes trouble.

Hit next, next, next on the installer, accepting all the defaults, and leave it that way.

Racing through the installer accepting defaults will:

Continue reading

3 ways to verify that a backup has taken place

As data professionals responsible for SQL Server, it is drilled into our heads that we need to take backups. But, how do we know we actually have backups available to us when we need them? How can we verify that a backup has been taken? Some types of auditing that an employer has to undergo might require proof of backups. How will you provide it?

Here are 3 ways I can think of off the top of my head.

Continue reading

SQL Server 2012 End of Support Migration Plan

Today is end of support for SQL Server 2012. May it rest in peace.

Migrating a SQL Server can be a lot of work. There are so many things to think about. It’s a pain.

Live view, upgrading SQL Server

Let me offer you a template of steps to get you started on your planning.

Here’s a quick overview of my process:

• Run Database Migration Assistant against the source SQL Server to get a report of incompatibilities and breaking changes in relation to my target version and edition.

Continue reading