Author Archives: John Sterrett

Previous SQL Server Updates

Hello everyone, this is John your Austin SQL Server Consultant here and today I am going to answer a question that comes up often so I wanted to blog about it for everyone. The question of the day is where can I download the previous SQL Server Updates?

The History towards Updates

Back in the day when we were young but not a kid anymore there were service packs and cumulative updates. We could download these separately and all of the updates were easy to find. Now today, if you click on a KB article to download an update you get pointed to the latest update as shown below.

Current SQL Server KB articles point to only the latest update.

How far is My SQL Server on Updates?

This is also another great question. My favorite place to find all the history of updates toward SQL Server is the SQL Server Build List Blog. You can cross-reference this towards your version by running the following query below.

You can use SELECT @@VERSION to get your current version number.
You can use SELECT @@VERSION to get your current version number.

I fully get exactly why Microsoft is trying to point everyone to the latest update. Normally, it makes perfect sense but let’s take a look at today Jan 9th, 2020. I am planning to update SQL Server 2017 to CU17. Its been out for two months. Today CU18 is released and if I wasn’t careful I would have downloaded a different update than expected.

SQL Server Blog List is a great resource for finding a list of all SQL Server Updates
SQL Server Blog List is a great resource for finding a list of all SQL Server Updates

Getting a previous SQL Server Update

So, on to the solution. It’s actually an easy one but also one that is easy to overlook as well. Let’s go back to the new standard update page for SQL Server updates.

That is right, the Microsoft Update Catalog is your best friend to find all your updates for Microsoft products including SQL Server. You can search for the product you want. For example, in this case, I am looking for SQL Server 2017 and can see all the previous updates for SQL Server.

All SQL Server Updates Can be Found in the Microsoft Update Catalog.
All the SQL Server Updates can be found in the Microsoft Update Catalog

If you enjoyed this tip and found it helped make sure to join our newsletter so you can enjoy more free tips, tricks, and video demos.

Free SQL Server Training Videos

Hello everyone! This is your SQL Server Consultant in Austin, TX and due to some posts on twitter about SQL PASS recordings costing $999 I wanted to share some of my favorite places to find free SQL Server training videos. I hope this helps make your data fast, secure and highly available in 2020 and beyond!

Where Is the Good Stuff Give Me Some More

Speaking on Migrating to Azure SQL Database at Ignite 2017
Attending conferences is nice but free recorded sessions are priceless!

Those who know me know I love music. I especially love the underground non-mainstream content. Therefore, my first recommendation is UserGroup.TV. As of December 27th, there are 127 videos tagged as SQL Saturday alone. Shawn goes around to almost every Tech conference he can find and brings his rig and records sessions for the community.

Are you in love with the new pop singles? Wish you could hear them before they hit the radio? If you like your tech like your music than Microsoft Ignite is for you. Every year Microsoft puts on a conference called, Ignite. This conference is usually where Microsoft will break its cutting edge tech. My favorite thing about the conference is that the content is available online for free. Midway through the page, you can search through the massive collection of free recorded sessions.

Next up, is the consistent greatest hits. Almost every session is a banger! This reminds me of my favorite Microsoft Data Platform conference. This is SQLBits and yes, their video content is also available for FREE.

Finally, Here is a collection of some great videos on performance tuning. Make SQL Server Go Faster, SQL Server Performance Improvements with SQL 2019. Another one is 7 Reasons your SQL Server Code is Slow! You can also find more great videos at Procure SQL Youtube Channel.

5 Game Changers with SQL Server 2019

Microsoft made SQL Server 2019 Generally Available this week we want to share some videos and code examples of our favorite new features. Most of these will make your code go faster without any code changes!

We have been testing SQL Server 2019 for months and hope you enjoy these features as much as we do!

Azure Backup for SQL Server VMs

I am not sure why but sometimes I am glutting for punishment. Maybe its why I try every backup and restore solution I can get my hands on? While Microsoft has done an amazing job at building the best relational database engine Azure Backup for SQL Server Virtual Machines has some architecture problems. In this post, I will showcase things you need to focus on, problems, and workarounds for your initial run with an Azure Backup for SQL Server VMs.

What’s Azure Backup for SQL Server Virtual Machines (VMs)?

If you take a look at Azure Backup they added functionality for backing up SQL Server databases inside an Azure VM. This seems like a really cool feature. Let’s use the same technology we use to backup our VM’s to also backup our databases. You know the whole one-stop-shop for your disaster recovery needs. Comes with built-in monitoring and it also eliminates the struggle some people have with setting up certificates, encryptions, purging old backups in blob storage, backups and restores from blob storage. It is really nice to also have a similar experience as restoring Azure SQL Databases as well.

Unfortunately, the product doesn’t work as expected at this point in time. I would expect any database backup tool to be able and backup the system databases by default without any customization. Therefore, Last night I setup my first Azure Backup for SQL Server Virtual Machines in the Backup Vault and this morning you can see my results below.

Azure Backup for SQL Server VM’s gets 0/3 system databases backed up by default

Now we will dig into concerns and initial problems with Azure Backup for SQL Server Virtual Machines (VMs).

Automatically Backup New Databases

Having the ability to backup new databases automatically is taken for granted. So much, that I noticed that Azure Backup for SQL Server VM’s will not automatically backup new databases for you. That’s right. Make sure you remember to go in and detect and select your new database every time you add a database or you will not be able to recover.

Azure Backup for SQL Server VM’s has an interesting feature called Autoprotect. This should automatically backup all your databases for you. Unfortunately, this does not work. Yes, I double-checked by enabling autoprotect for a VM and I added a new database. The database didn’t get backed up so I had to manually add the database.

Simple Recovery Problems

Looking into the failures for my system database backups I noticed something interesting in the log for the master database. It looks like you will get errors with the only SQL Server backup policy created by default. The reason is the policy includes transactional log backups and as you know its impossible to take a transactional log backup if your database utilizes the simple recovery model. Now, most backup tools know how to roll with databases in simple and full recovery.

Looks like Azure Backup for SQL Server VM’s is not one of these tools that easily allow you to mix databases utilizing both simple and full recovery models.

Yup.. Simple recovery model is no bueno..
Yup, simple recovery model is no bueno..

So, how do we get around this? It is not too hard. Just create a new backup policy that does not include transactional log backups and assign it to your databases that utilize the simple recovery model.

Transactional Log Backup Problems

So, what happens when you try to take a transactional log backup of a database that doesn’t have a full backup? It fails. This is by design. If you try to take a log backup in this scenario with T-SQL it will fail as well. That said, several 3rd Party open source backup solutions like my recommended one can gracefully handle this for you. It can take a full backup instead of the log backup. I have grown to expect this behavior.

Here is what you will see in the logs of Azure Backup for SQL Server VM’s.

Log backups without a full backup fail. You have to force a full backup
You have to force a full backup or wait until the scheduled full backup occurs. Yuck!

So, the workaround here is simple. You can force a backup. This will start the process of allowing your schedule log backups to work as designed. You could also wait until the scheduled full backup runs but know this means you will not have point in time recovery until that full backup runs. There should be an option to perform a full backup instead of a transactional log backup if a full backup does not exist. This would prevent the transactional log scheduled backups from failing.

Things to Know!

Azure Backup for SQL Server VM’s pricing goes off of storage as well as instances of SQL. By default, compression is not used for the SQL Server Backups. You will most likely want to make sure you enable this to save some money.

Azure Backup for SQL Server VMs has a good amount of documentation for troubleshooting common problems.

There are many documented limitations that we didn’t cover in this blog post. Some shocking ones to me are SQL Server Failover Cluster Instances and don’t configure backup for more than 50 databases in one go

My First Post.

Being an IT professional with a focus on the legal industry I am glad to say that I have a place on the web.  With Justin Siebert and Ted Cromwell pleading WordPress I am joining the dark side and leaving the Microsoft stack for this blog.  I am primarly a ASP.NET developer and a SQL Server guru. I look forward to communicating with several IT professionals through this site.