In a previous article, we talked about Microsoft SQL server end of life status, the difference with servers that have reached an end of support status, and how to migrate them to a new version. As it turns out, there were quite a few people interested in what we had to say! We received several questions about SQL server migration, which we will answer in this article.  

We will look at the difference between in-place migrations and clean installations, what to do when your SQL Server version is no longer supported by third-party software, if you should use compatibility mode, and if you should handle a migration yourself. Read on to find out more! 

Should I change my SQL Server version with an in-place migration or a clean installation? 

An in-place migration installs the new version of SQL Server alongside the old version on the same machine. The advantage is that you do not have to invest in new hardware, but keep in mind that servers generally last around five years. The disadvantage is that you could also transfer hardware-based problems from the past, like disks formatted with the wrong number of bytes per cluster. 

When performing a clean installation, you can take full advantage of all new hardware features. For example, you can set a new standard when choosing disks and directories for storing data, logs, backups, and tempdb files. It also prevents interference from old issues on your server.  

Unless you want to risk extended downtime by switching hardware, clean installations require a second machine. However, organisations will generally have already anticipated buying a second machine in their IT budgets because of the aforementioned five-year lifespan of servers. 

Our database experts recommend a clean installation whenever possible. In our experience, in-place migrations are the exception to the rule. 

What if my new SQL Server version is not supported by my third-party software? 

Start by checking if there is a new version of the third-party software that is compatible. If there is no update available, check the documentation to see if the software supports compatibility mode. If that does not work, consider setting up a virtual machine with an older version of SQL to host the non-compatible database.  

If the software has nearly reached out of support status, you can choose to still migrate the database to a newer version and test if everything still works. Although your vendor will no longer support the software, you will be able to get rid of the old server. 

Should I use compatibility mode for my SQL Server? 

Compatibility features certainly have their uses, but should be used sparingly. Database veterans will remember when the ANSI SQL *= and =* syntax was replaced with LEFT OUTER JOIN and RIGHT OUTER JOIN respectively, leading to loads of syntax errors and malfunctioning applications. The compatibility mode helped alleviate many of those issues. It can also function as a quick fix when in-house applications no longer function after migration to a new version of SQL Server. 

However, our experts stress that compatibility mode should not be a permanent solution. During their audits, they have noticed that most databases unwittingly run in compatibility mode. This is because when you back up a database on an older version of SQL Server and restore it on a newer version, that database will initially run using the compatibility mode of the old server.  

For example: if you migrate a SQL Server 2016 database which is running in SQL Server 2012 compatibility mode to a new SQL Server 2019 machine, that machine will still run the 2012 compatibility mode by default. This means that your brand-new machine is still missing out on tons of features, improvements, and bug fixes! 

To avoid this, you need to manually modify the version after a restore. You can do so through the GUI or by using a T-SQL script. Should you encounter problems after changing the compatibility mode, you can still modify this option. The effect will be immediate, and there will be no downtime for your database or server. 

Should I migrate my SQL server database myself? 

It is certainly possible, but using a technical partner will save you significant amounts of time and effort. Database migration is not an easy process to start with, and becomes much more difficult as the size and complexity of your data infrastructure increases.  

An experienced partner can help structure the migration process by setting up an inventory of all databases, the persons responsible, any specifics that require special attention, and so forth. They can also provide a project manager to check the progress and keep everything on track. 

Perhaps most importantly, a partner will know how to handle security during migration, since this is the point where your sensitive or business-critical data is at its most vulnerable. If they are experienced, they will also have developed in-house migration tools that can automate the entire process. This avoids mistakes caused by manual inputs, drastically reduces time spent, and therefore lowers the project cost. 

At Monin, our employees have decades of experience in database migrations. We have the added benefit of being part of the Cronos Group, which means access to thousands of professionals with expertise. If there’s an error with one of your third-party database tools, chances are that we can put you in touch with the right person for the job. 

While we love to answer frequently asked questions to help people out in general, every project still has its own challenges and requirements. Do you still have some specific questions, or do you need a technical partner for an assessment? Contact us, and we’ll be happy to make your next project a success!