DEV Community

Leonardo Gasparini Romão
Leonardo Gasparini Romão

Posted on • Updated on

How to migrate SQL Databases to a new version

This post was part of a series talking about How to create a good architecture for SQL Databases:

It's common when we have legacy databases, to maintain not only the database as all infrastructure that supports it, like OS version or MSSQL version. But with time, new features are including in new versions of MSSQL, and these features can simplify some internal jobs that can be hard to do.

An example that I have, was when I working with a BulkInsert with different sources of CSV files that have different encodings like UTF-8, UTF-16, ASCII, etc...And my problem was to know that my version of MSSQL doesn't support UTF-8, and I didn't have the tools to upgrade the version. So I will show a good strategy to make these changes without problems. Other features are created with the years like:

  • Accepting JSON besides XML
  • Support to more encodings
  • Improve their Query Intelligence
  • Native BigData or BI software integration like Spark or PowerBI

So, we have some items that we need to be careful about when we make a migration...

1- Always have a support machine

It's impossible to change SQL version in the production server, impossible maybe it is not the best word but is impracticable to do this, so creating a new machine to put this new Data is the best option, besides, we can make tests before changing the machines.

2- Check all app that uses your Databases

If you need to migrate a database that is used to many applications, remember to copy not only data and Schema but:

  • Logs
  • Users (Username and Password)
  • Permissions
  • Jobs and Procedures
  • Folders
  • Files generated by procedures

This need to be precisely done, probably, you will have some issues in the first weeks after the migration.

3- Try to use SQL copy database wizard

Probably, Microsoft has to know what is the better strategy to migrate the database, so the SQL Management Studio does have a wizard to Copy the database to another server. This is interesting because we don't have to worry about some features that we can be forgotten when we make a migration. Microsoft has links to help with this, either by migrating to Azure or by another SQL server.

Migrate database it's not a trivial job, to make this we have to take proper precautions.

Useful links:
https://docs.microsoft.com/pt-br/sql/dma/dma-overview?view=sql-server-ver15
https://datamigration.microsoft.com/scenario/sql-to-azuresqldb?step=1
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15
https://stackoverflow.com/questions/31483452/store-nosql-data-on-sql-server

Top comments (1)

Collapse
 
crestiancik profile image
Crestiancik

sometimes I would say it's the worst strategy!