Dev:"But it's new and shinny! Let's upgrade!" DBA:"Over my..." - Preparing to upgrade your SQL Server

simple talk - Database Administration - Preparing to Upgrade your SQL Server

It isn't a problem to use deprecated TSQL features until it comes to the time  to move the database to a server with a newer version of SQL Server, because The Upgrade Adviser tool will tell you what needs to be changed. An alternative is to flush out the use of archaic features via scripting  during the development process so there are no surprises later. Jonathan Allen shows how.

The job of preparing to upgrade SQL Server is one that DBAs look forward to but also dread in equal measure; on the one hand there is the delighted anticipation of new shiny things to work with, on the other hand the dread of knowing what to do if everything breaks or making sure that everything is ready for the changes? The problem is that your databases may be using features that don’t work in the version of SQL Server you’re upgrading to. If so, then they’ve got to be altered to get everything to work. Only if you make sure that all the databases will work on the new version of SQL Server can you look forward to a stress-free upgrade.

SQL Server makes things as easy as possible for you with various tools and features that let you know what if anything will cause problems and therefore stop working if you upgrade, and they will also warn you what features are likely to be problems in a version or two. There is the upgrade advisor tool, a dedicated application that will analyse all the components of SQL Server that you have installed and produce reports for you to review to guide you through an upgrade. This isn’t what we are going to look at, but I wholly recommend that you take a look at the Upgrade Advisor for the SQL Server version you intend to install, even if you are not intending to upgrade yet, and review what might be a problem and what information you will get out of it. Just so that you are one step ahead when the upgrade *does* get started. The SQL Server Upgrade Advisor can be found here:

...

Speaking of upgrading SQL Server servers, this looks like some great into for those looking to upgrade now or in the near future.

 

Related Past Post XRef:
"Microsoft SQL Server 2014 CTP1 Product Guide" - One new SQL Server, 12 PDF's...