kohera-logo-regular.svg

SQL Server Upgrade Advisor 2014

Wondering where to start with your dinosaur database servers that are still floating around somewhere in your state-of-the-art environment? Oh yes, everyone else may have forgotten about them, but these machines still exist. While everyone else is happily going about their business, you need to maintain the old servers. After all, you’re the DBA  and your job is to keep these machines alive.

You probably don’t even know what the databases are for and the original developers of the application may have already left or marked their solution file in Visual Studio with a warning: ‘Don’t touch, legacy application, no maintenance support’.

So basically you’re stuck maintaining an old SQL 2000, or if you’re lucky an SQL 2005 machine.

In the current age of virtualization it’s pretty easy to keep these systems alive. So I’d say it’s time to start an assessment for decommissioning the old machines. But where to start and what to do? If I get an assignment like this, I usually start by firing up the Upgrade Advisor from Microsoft. This tool allows you to get a quick overview of the problems or hurdles you need to overcome when you upgrade to SQL 2014.

But first, let me introduce you to the Upgrade Advisor and the potential pitfalls you may encounter installing it.

Installation

You can download the Upgrade Advisor here. It is part of the Feature Pack for SQL Server 2014. You’ll have to choose whether you want to download the 32-bit or 64-bit version. When doing an assessment on older machines, the 32-bit version can still be the way to go.

Here’s the first tricky part. Whereas previous versions of the Upgrade Advisor can only be installed via its MSI, this new version (UA 2014) needs to be installed together with the SQLDOM.msi. So before you can start installing, you need to download and install the SQLDOM.msi. I learned this the hard way.

You can choose whether you install the Upgrade Advisor on your local machine and connect remotely to the server. This is what I usually do if possible, because:

  • the reports are directly available on your local machine
  • should your dinosaur server become unstable and you would rather not touch it, you’re not forced to install something on the server itself
  • if your old server still runs on the previous .NET version (below 3.5) you won’t be able to install Upgrade Advisor 2014 on it

What are the prerequisites?

When you connect remotely via the UA make sure you:

  • are sysadmin on the SQL box.
  • are local admin on the SQL Server
  • have remote access to the registry (to check this, go to Services and check whether the Remote Registry is enabled. If disabled, you need to enable it before you can connect remotely to the registry)
  • make sure your firewall doesn’t block your ports. You can telnet to port 1433 and 1434 or you can execute a netstat -an on the server to see which ports SQL Server is listening to

Running the UA

When running the UA on a remote machine, I prefer to use the FQDN just to be safe. As a side note, although it’s not scientifically proven, you know when things are working (on default instances) when the MS SQL Server is automatically filled in (it’s just a feeling I have ;-))

The next step is choosing which options you want to check. If you mark Reporting Services for analysis, you’re obliged to run this on the machine itself. The wizard will give you a warning.

Next you can choose the databases you want to analyse. Note that when running Upgrade Advisor 2014, you can’t select the databases that run in compatibility mode 80. I can assure you, there are still a lot of them hanging around on SQL Server boxes.

To avoid this problem, first install an SQL 2012 or SQL 2008R2 Upgrade Advisor and then check those databases. Next you can raise the compatibility mode to 90 (SQL 2005), which lets you analyse the databases with the UA. Hit run, and wait.

The UA will start analysing things for you. It might take a while before the analysis is complete, but the result is a report that provides an overview of how to upgrade your databases.
More information about the Upgrade Advisor can be found here.

Start to analyse

And now it’s up to you. You should interpret the reports and decide what needs to be done before upgrading to SQL 2014. Some tasks can be done after migrating the databases, while others have to be done before you can even start on this.

As always, it’s best to first move to a development environment and test whether the application is performing as expected. You should also talk to the developers and ask for their support. Make sure you have their buy-in and talk about the advantages of moving to the newer version. It may be that the “old code” has to be refactored before the migration can be successfully completed.

You can find out if you can successfully finish your migration by using the Upgrade Advisor as a reference and guide throughout the process.

Have fun migrating and last but not least, have fun with SQL 2014.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...