Hello and welcome to another detailed article on MSSQL. Today I will touch upon the subject of the online update of the MSSQL server from one version to another.
In today’s article, we will be upgrading Microsoft SQL Server 2014 to SQL Server 2017.
As with any upgrade, we first need to have a pre-upgrade checklist with all the tasks that need to be done before the upgrade itself. That way, we can make sure we haven’t missed anything and most likely things will go wrong (something always goes wrong).
So, since we’re probably going to use Windows Server 2012 R2 with our SQL 2014 installation, here’s 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 Windows updates. This will also include SQL 2014 Service Packs. Service Pack 3 is recommended. Next, we need to make sure we have .NET 4.6.1 installed.
Again, if you’ve run Windows Updates, the .NET Framework will most likely be installed. Next, we need to make sure KB2919355 is also installed. The next step should be generally best practice, but always make sure you have at least 100 GB of free space on your partitions before performing any updates on your servers.
Now the last step is also the beginning of the whole update process. Running the Data Migration Assistant will analyze our SQL and database installation and suggest the best way to upgrade to 2017.
To download the Data Migration Assistant, follow this link:
https: //www.microsoft.com/en-us/download/details.asps? id = 53595
Now let’s get this SQL Server up and running and start installing the DMA and getting an assessment of our environment. Please note that my installation is in a test lab and is not a production environment.
Upgrade SQL Server to the new version
So let’s get started with the update!
I followed the link above and downloaded the Data Transfer Assistant. Now let’s go through the installation steps and make our assessment.
Click Next, then on the next screen, accept the terms and click Next.
On the following screens, just click Install and make sure you check the Run box after the installation is complete.
Once the installer has finished the whole process, we will move on to this beautiful toolbar with a nice welcome message. You know the type of welcome messages from Microsoft that become the horror of our nightmares as they always remind us of failed migrations, long hours in the office, etc. Well, let’s move on.
Now let’s start by clicking the “+” button and choosing a migration script. As soon as you press this button, a new blade will appear with some parameters.
As you can see, I select Assessment, enter any project name (mine is 2014-2017, as this is what we are trying to achieve), and I set the source and target server type to SQL Server, since all we are going to do is inline update.
Now let’s press the Create button.
Once we click “Create”, we will be presented with three options to choose from. Check out compatibility issues, which will be our only choice for this update.
Do not select the New Feature Recommendations checkbox as we are not interested in this at this time. The third option is applicable in other scenarios in older versions of SQL and is also greyed out as it does not apply to our scenario.
After we click Next , we will be taken to the screen where we need to connect to the server we have chosen to update. Since we are updating the server we are connected to, we will select localhost as the server and be sure to check “Trusted Server Certificate” to avoid getting nasty errors that the name is not trusted in the domain, and so on.
Then we click Connect and if a connection is established we will be presented with a list of all databases in that SQL instance. In my case, I have a test vpm database that we will be using when evaluating.
Now we click Add and then Start Assessment.
Now we will wait for the end of the exam.
Now that the assessment is complete and we see a green check mark next to our database, we can upgrade to SQL Server 2017 knowing nothing breaks and full compatibility.
It’s time to start the update process.
I have installed a SQL 2017 ISO image on my machine. Now we just run the normal installation and select Upgrade from a previous version of SQL Server.
We can enter a serial number or select “Score” depending on our current needs.
Then we make sure we are working on the correct instance.
On the next page, we have the option to select and deselect the features that we want to update.
On the next screens, we just click Next until we get to the update process, wait for it to complete, and we’re done!
This is it! Thank you for taking the time to read this detailed article on how to properly upgrade SQL Server!