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

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

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

How To Read The SQL Server Error Log

Reading the SQL Server Error Log is important when troubleshooting many issues for SQL Server. Some example issues would be errors related to Always On Availability Groups, long IO occurrence messages, and login failures.

Unfortunately, the SQL Server Error Log can be very full of information, making specific things hard to find, especially if you’re just visually scrolling the Error Log. Even if you’re recycling the Error Log each day and keeping 30 or more days of error log, on a busy system, the error log can still be quite full, even for a single day.

Continue reading

How to Build a SQL Server Inventory Using PowerShell

You’re a data professional and you’ve been given the keys to a new SQL Server environment. You know you need to build a SQL Server inventory so you know what is in your environment, but how do you get that information?

One of the things I have talked about in other posts is how to create a SQL Server inventory. I’ve discussed using the MAP Toolkit and building your own inventory database using T-SQL. Today, we’ll see another way to generate a list of SQL Servers in your environment.

Continue reading

How To Use Powershell to Configure Multiple Databases for Log Shipping

I recently needed to configure log shipping for multiple databases at once as part of a migration project. I turned to PowerShell to do this.

But before we get to that part, this post assumes that you’ve done the upfront work to create shares for the backups to write to and for the backups to be copied to. This will involve providing the right permissions for the SQL Server service accounts involved in Log Shipping. If you are not familiar with this, that’s perfectly fine. Check out this article in MS Docs first.

Continue reading

Top 3 Most Popular Posts of 2020

So, I realize it’s now almost April of 2021 when I’m posting this. Normally this sort of post would come out in January for the previous year. However, I have a reason for this being late and I’ll actually post about that separately.

Showing My Blog Some Love

In mid-2020 I decided to give my blog more attention. there were a number of reasons for this. First, I’d already spent a fair amount of money over the years on hosting and related things so I didn’t want to waste that. Second, I really wanted to try to start giving back to the SQL Server community. I have been the recipient of a lot of learning and help through a number of regular, and occasional, bloggers. I have also purchased and mostly read my fair share of SQL Server books, which was another way I had been helped by the community. Third, I wanted a place to record my own troubleshooting and learning so when I needed to do something again, I would have a record of what to do. By the way, I have seen this repeatedly listed as a reason that someone blogs.

Continue reading

My Availability Group Database Isn't Synchronizing

I currently manage 5 AlwaysOn Availability Groups. Two are on SQL Server 2014 and overdue for an upgrade, while three of them are on SQL Server 2017.  From time to time I have run into a couple of different situations that I needed to troubleshoot and I want to tell you where to look and what to check on in these scenarios. I can’t possibly tell you about everything that could go wrong, but I can tell you about my experience with AlwaysOn Availability Groups and let you decide if that experience helps you or not.

Continue reading

Measuring Your DBA Skills

Over the last 9 weeks I took you on a journey of skills and career topics related to being a SQL Server DBA. We looked at the Production DBA. We saw skills and career topics from the beginning to mid-career to Senior DBA. Then we looked at the Development DBA and their skills and career development needs. Finally there was a wrap up post.

To make it easier for everyone to get to these posts, I decided to bring them all together on a single page.

Continue reading

How Do I Measure My DBA Skills Part 9

Production and Development DBA Skills and Things We Didn’t Touch On

We’ve covered a lot of ground for Production and Development DBAs in terms of the skills and applicable career topics. We’ve mentioned necessary skills with T-SQL, backup/restore, automation, PowerShell, troubleshooting, SSIS, SSRS, patching, Query Store, HA/DR, leadership and emotional intelligence. That’s quite a list! However, looking back at the series, there are still a number of things that weren’t mentioned.

Continue reading