Migrating legacy SQL Server databases to Microsoft Azure is a common task for organizations looking to take advantage of the benefits of cloud computing. There are several ways to migrate your SQL Server databases to Azure, including Azure Database Migration Service (DMS) or the Azure Site Recovery service.

Business Scenario:

If you’re running on legacy SQL Server versions (2008R2\2012) with large size of databases (>1 TB) and want to migrate & upgrade to the latest versions of SQL Server on Azure with minimal application downtime, then follow this blog.

Business Challenges:

Below are some of the challenges you must be facing –

• SQL Server versions are already outdated, so there is no support from Microsoft regarding Service Packs\patches\Bug fixes.
• Lift & shift is not possible for migration to the cloud because of outdated software versions.
• If your business is 24*7, you can’t have a long change window during migration.
• Since these databases are large, the traditional backup and restore method is not recommended.

Solution Strategy:

To overcome the above challenges, we can use SQL Server Log shipping for this migration.

• SQL Server Log shipping automatically sends transaction log backups from a primary database server to one or more secondary databases on a separate secondary server.
• Apply transaction log backups to each of the secondary databases.
• Establish the connectivity between On-prem and Azure Server.
• Create a shared folder on the On-premises server to dump the scheduled log backups.
• Enable Log shipping for the huge size databases between on-premises and Azure.
• Kept the schedule of backups, copy, and restore every 15 mins.
• Initial Database backup and restore would take time because of the database size.
• Transfer other required SQL Server Agent Jobs and Logins to the Azure server.
• At the time of the cutover, ensure all the pending backup files are restored on the Azure server.
• Once the restore is complete, stop the connection on on-prem for a few minutes, take the final backup on on-prem, and restore it on Azure (execute jobs manually currently).
• We must match the data on both servers with database size and row count to avoid data loss and break-log shipping.
• Recover the databases on the Azure server and update the connection string with the Azure server.
• Perform post-migration tasks.

Outcome & Benefits:

• Improved security, access to latest SQL Server features and support from Microsoft
• Reduced downtime.
• Low cost of migration as we are not using any licensed software and hosting cost on Azure.
• Improved the database performance.

Mastanvali Shaik

Solution Architect – Managed Services