This post was part of a series talking about How to create a good architecture for SQL Databases:
| How to versioning SQL databases architecture using GitHub and Visual Studio |
|---|
| How to organize SQL legacy Databases |
| How to debug SQL Databases |
| How to migrate SQL Databases to a new version server |
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)
sometimes I would say it's the worst strategy!