Last month, one of my acquaintances, working as a sysadmin at a small logistics company, got in touch with me in a panic. One of their on-premise SQL servers was approaching 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, a lot of failed restores, and a few collation-related dramas. He had already attempted three different migration tricks that supposedly required no third-party software and ended up with two test databases in disarray before deciding to ask my advice.

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 have to do:

1. Take backup of the database that you want to migrate. Simply open SSMS, right-click the database, go to Tasks > Backup, and save the .bak file in the desired location.

2. Transfer the .bak file to the destination server (or blob storage, in 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. In case of migrating an MSSQL database to Azure SQL, you may want to use SqlPackage.exe (.bak file format is not supported by Azure) or “Deploy Database to Microsoft Azure SQL Database” wizard in SSMS.

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

5. Migrate data: you may either restore the database using the .bak/.bacpac file or use Import/Export Wizard / bcp utility (in case of 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 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 led to many join-related errors.

• Orphaned users: some users were missing on the destination server, while some 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 restoration.

• Time constraints: the weekend was not the best time to schedule a migration, especially as 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. The main advantage of this tool is that it can be used to both migrate healthy and corrupt databases, 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. In all, Kernel Migration for SQL Server can be a great alternative to manual migration for companies that want to avoid the risks that come with using third-party tools.

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