Last month, one of my acquaintances, who happens to work as a sysadmin at a small logistics company, got in touch with me in a panic. One of their on-premise SQL servers was nearing the end of its lifecycle, and the company had decided to migrate to Azure SQL. He thought it was going to be a routine task to complete over the weekend. Instead, the migration took five days, involved numerous failed restores, and a few collation-related dramas. He had already attempted three different migration “hacks” that supposedly required no third-party software and ended up with two test databases in disarray before he decided to ask for help.

Free / Manual Migration: Step-by-Step

If you are determined to use a free but legitimate method to migrate your database to a new server, here is what you would typically have to do:

1. Create a backup of the database that you wish to migrate. To do this, open SSMS, right-click the database, choose Tasks > Backup, and store the .bak file in the location of your choice.

2. Transfer the .bak file to the destination server (or blob storage, in the case of migrating to Azure).

3. Restore the database on the target server. Right-click Databases, choose Restore Database, and follow the wizard’s instructions. If you are restoring an MSSQL database to Azure SQL, you may want to use SqlPackage.exe (.bak file format is not supported by Azure). Alternatively, you can use the “Deploy Database to Microsoft Azure SQL Database” wizard in SSMS.

4. Resolve any collation or schema-related issues. This step can be particularly time-consuming, as you will have to make sure that all users, logins, and permissions are correctly restored on the target server.

5. Migrate data: you may either restore the database using the .bak/.bacpac file or use the Import/Export Wizard / bcp utility if you are using different versions of SQL Server. This step can take a lot of time, especially for large databases.

6. Point your applications to the new database instance. Make sure to update all connection strings and firewall settings.

Common Migration Pitfalls

I asked my friend what went wrong during the migration, and he had quite a list:

• MDF/NDF file issues: if the source database was faulty, any attempt to restore it on a new server would fail.

• Version mismatches: he initially tried to restore the backup in SQL Server 2012 after taking it from SQL Server 2019.

• Collation mismatches: the collation of the source and destination servers did not match, which resulted in many join-related errors.

• Orphaned users: some users were absent on the destination server, while others had different permissions.

• Absent objects: some functions, triggers, and foreign keys were lost during the migration.

• Cross-database querying: Azure SQL does not allow cross-database queries and has a limited set of stored procedures and functions compared to on-premises SQL Server.

• Large payloads: migrating large databases to Azure SQL often results in timeout errors.

• Tools’ shortcomings: the tools that he used did not have an option to skip corrupt pages during the restoration process.

• Time constraints: the weekend was not the best time to schedule a migration, especially since it involved databases with critical data.

After helping my friend recover two test databases, I decided to investigate the issue further. It seems that many database administrators have similar stories and are now opting for a commercial solution called Kernel Migration for SQL Server. One of the main advantages of this tool is that it can be used to migrate both healthy and corrupt databases – all with minimal downtime. Users can preview all database objects before migrate SQL server database and transfer data directly to Azure SQL, Amazon RDS, or a local instance of SQL Server. All in all, Kernel Migration for SQL Server can be a great alternative to manual migration for companies that want to avoid the risks involved in using third-party tools.

Learn More: https://www.nucleustechnologies.com/sql-migration/