Hello and welcome to another detailed article on MSSQL. Today I will be touching the subject matter of doing an in-line upgrade of a MSSQL Server from one version to another.
For today’s article, we will be upgrading from Microsoft SQL Server 2014 to SQL Server 2017 .
As with any upgrade, we should first have a pre-upgrade checklist with all the tasks that need to be done prior to the upgrade itself. This way we’ll be sure we haven’t missed anything and that the odds are nothing will go wrong (something always goes wrong).
So since we’re going to probably be using Windows Server 2012 R2 with our SQL 2014 installation, here is “the list”:
- Windows Updates
- .NET Framework 4.6.1
- KB2919355
- Free Space
- Data Migration Assistant
Now let’s see what each step does. The first step, of course, is to make sure you have all your Windows Updates. That will include SQL 2014 Service Packs also. It’s recommended to have Service Pack 3 installed. Then we need to make sure we have .NET 4.6.1 installed.
Again, if you ran Windows Updates, most likely the .NET Framework will be installed. Then we need to make sure KB2919355 is also installed. Now the next step should be a best practice overall, but always check that you have at least 100GB of free space on your partitions before doing any type of upgrade on your servers.
Now the last step is also the beginning of the whole upgrade process. Running the Data Migration Assistant will analyze our SQL installation and the databases and offer a best course of action to upgrade to 2017.
To download the Data Migration Assistant you can visit this link:
https://www.microsoft.com/en-us/download/details.aspx?id=53595
Now let’s pull up that SQL Server and start installing the DMA and getting an assessment of our environment. Please note that my installation is in a test lab and does not represent a production environment.
Upgrade SQL Server to New Version
Ok so let’s start upgrading!
I went through to the link provided above and have downloaded the Data Migration Assistant. Now let’s go through the installation steps and do our assessment.
Hit Next, then on the next screen accept the terms and hit Next.
On the next screens just hit Install and make sure you mark the Launch checkbox once the installer is finished.
Once the installer has finished with the whole process, we will be launched into this nice looking dashboard with a nice welcome message. You know the type of Microsoft heart-warming welcome messages that become the dreaded horrors of our nightmares since they always remind us of failed migrations, long hours at the office and so forth. Oh well, let’s move on.
Now let’s start by hitting the “+” button and choosing our migration scenario. Once you press that button, a new blade will appear with some options.
As you can see, I choose Assessment, put in any project name (mine is 2014to2017 since that’s what we’re trying to achieve), and I set the source and target server type as SQL Server since all we’re going to do is an in-line upgrade.
Now let’s hit the Create button.
Once we hit create, we are given three options to choose from. Check Compatibility Issues, which will be our only choice for this upgrade.
Leave New features’ recommendation unchecked, since we’re not interested in that at this time. The third option is applicable in other scenarios in older SQL versions and is also greyed out since it doesn’t apply to our scenario.
Once we click Next, we are brought to a screen where we need to connect to the server that we choose to upgrade. Since we are upgrading the server we are connected to, we will choose localhost as the server and make sure to check “Trust server certificate” so we won’t get some nasty errors that the name is not trusted in the domain and so forth.
Then we hit Connect and if the connection is successful, we will be given a list of all databases in that SQL instance. In my case I have a test database called vpm, which we will be using in the assessment.
Now we click Add and then we click on Start Assessment.
Now we’ll just wait for the assessment to finish.
Now that the assessment is finished and we can see that we have a green check sign next to our DB, we are free to upgrade to SQL Server 2017 knowing nothing will break and there’s full compatibility.
Now it’s time to start the upgrade process.
I have connected a SQL 2017 Standard ISO to my machine. Now we just run the regular setup and choose Upgrade from a previous version of SQL Server.
We can either put in our serial number or choose Evaluation, whatever fits our current need.
Next we make sure we are working on the correct instance.
On the next page, we have the option to select and deselect which features we want to upgrade.
On the next screens, we just click next until we get to the upgrade process, wait for that to finish and we’re done!
That’s it! Thank you for taking the time to read this detailed article on how to perform an upgrade of SQL Server the right way!
