For this months T-SQL Tuesday contribution I want to share some of my thoughts about SQL Server upgrades. Including ones for SQL Server 2019 and SQL Server 2022 (which is due to be released in near future).
This months T-SQL Tuesday is hosted by Steve Jones. Steve has invited us to discuss our thoughts about SQL Server upgrades.
You can find out more about the invite by clicking on this link about the T-SQL Tuesday 147 invitation or on the image below.
How I use to look at SQL Server upgrades
In the past I use to be filled with dread when it came to SQL Server upgrades. Mostly due to what was required. For instance, in one role I had to wait for some time for a new server to be available.
Sometimes additional configuration had to be done on the servers as well. It took time and effort to discover the best configurations for SQL Server.
One other major gripe I use to have with upgrades was one that I know others shared in the past. Which is the time it took to migrate databases from the old server to the new one.
For example, if you were using Log Shipping as a migration strategy you would have to make sure that you could do the final backups and restores as fast possible. Then there were the potential compatibility issues.
How I view SQL Server upgrades now
Luckily, these days a lot of the optimum settings for SQL Server are setup by default. In addition, there are also plenty of good resources to help you. For example, the SQL Server diagnostic queries provided by Glenn Berry.
Luckily, there are a lot of improvements to SQL Server these days. If you are using Log Shipping as a migration mechanism the RTO can be a lot faster due to optimizations done over the years.
In addition, there are now other options for migrating to newer versions of SQL Server such as SQL Server 2019 and SQL Server 2022. For example, Always-On Availability Groups.
To give an example of how much things have improved, some time ago I once did a migration for a company where the previous migration had taken days. We tested compatibility issues beforehand using a version of Data Migration Assistant and had performed other tests in advance as well to check applications would work afterwards.
Leading up to the migration we kept the new databases as up-to-date as possible. By using a restore strategy before switching over to the new databases.
It was all done within a few hours, and the majority of that time was testing that the applications worked when the databases were online. When I informed one of the managers their response was simply three words. “Is that it?”
Migrate gracefully
Whilst we are talking about Data Migration Assistant, just a reminder that it is good practice to upgrade your SQL Server database versions gracefully. Avoid jumping ahead by too many SQL Server versions at once.
For example, say you are running SQL Server 2008 R2 on an Azure virtual machine and you intend to migrate to SQL Server 2022. Avoid going straight from SQL Server 2008 R2 to SQL Server 2022. Look at advice either online or in the Data Migration Assistant about which version(s) of SQL Server to upgrade to in-between. To avoid any issues.
Which leads me to the next topic.
And then there’s moving to the cloud
Moving to the cloud can mean many things. However, one popular option is to migrate SQL Server databases that are on-premises to a cloud-based offering. Such as Azure SQL Database or Azure SQL Managed Instance.
I am not going to dive deeply into these services or go through the differences between IaaS and PaaS in this post. I covered some of things relating to this in past posts. Such as one post I did in 2020 which was an update about SQL Server related services in Azure.
What I will say is that I really like the advances in cloud-based offerings for various reasons. Including the flexibility that they have to offer.
Plus, there are lots of resources to help with cloud migrations as well. Like the Azure Database Migration Service.
If you are looking to use SQL Server related services in the cloud I do have one piece of advice for you. Which is to study for the DP-300 exam to get to know your options better.
Final word
I hope my thoughts about SQL Server upgrades have been of interest to some of you. Basically, I think they can be a lot easier now then they were in the past thanks to advances in SQL Server.
Like I mentioned earlier, I also like cloud-based offerings. With so many companies moving to the cloud I recommend entertaining the option to migrate to a cloud-based offering as well.
Of course, if you have any questions or comments about this post feel free to reach out to me.
[…] in my last T-SQL Tuesday post I mentioned the SQL Server diagnostic queries provided by Glenn Berry. Which reminded me to do […]