Top 3 Migration Strategies When Moving SQL On-prem to Azure SQL Database or SQL Managed Instance

Digital transformation shifts triggered by remote work and the pandemic have made clouding computing an absolute must for enterprises globally. While accessibility and cost-efficiency are expected, those who are migrating are looking at it futuristically from a scalability and resilience point of view. The global public cloud infrastructure market is expected to grow 35% to $120 billion in 2021 and the shift to the cloud will affect more than $1.3 trillion in IT spending by 2022.

The kind of capabilities the cloud has and the edge it gives to users irrespective of their business needs, geographies, and magnitude makes migration to the cloud a smart move. As we continue to work remotely yet collaboratively, it comes as no surprise that there is significant acceleration in cloud adoption and Microsoft Azure continues to climb the popularity ladder.

Leading companies worldwide are now eagerly leveraging Azure SQL to address bigger challenges and meet sustainability goals. PepsiCo recently announced its plans to shift its infrastructure, data estate, and ERP to Azure in a bid to move its SAP landscapes into the cloud. 

If you have decided to migrate to Azure, you may want to consider moving your SQL Server database to an Azure SQL Database or SQL Managed Instance. There would be security concerns and infrastructure differences that may stand in the way of a smooth migration. This may leave you a bit perplexed, but the truth is there are ways and means to move your existing databases to Azure. We reveal our 3 best strategies to do so. Read on.

1. Data migration assistant or Data migration service (DMA/DMS)

A tool or a specifically created service to help you with migration can be leveraged to assess feature parity and database compatibility issues that may arise while moving your server with the available deployment options. It will create a detailed report identifying compatibility issues and suggesting possible fixes as per different compatibility levels. Interestingly, DMA/DMS comes with online and offline options wherein offline allows you to migrate just the schema or the schema and data in one go. In contrast, the online option allows you to continuously synchronize the on-premises database and the Azure SQL database even before or after you move the initial schema/data. The offline method is ideal for testing/POC for smaller databases or those applications that can survive the long downtime during data transfer. On the other hand, the online option gives the Azure SQL Database plenty of time to decide when to cut over and start pointing your application to the Azure SQL Database.

2. Replication

When you add an Azure SQL Database as a subscriber to a transactional replication, you can migrate the data as well as objects from your on-premises SQL Server to Azure SQL Database. However, this replication can be initiated only from a snapshot since you do not have the option of restoring backups. The synching pretty much starts off the way it does in case of online migration for DMS, wherein it begins well in advance prior to the scheduled migration, and it’s up to you to decide when to cut over and re-point the applications to the Azure SQL Database once everything you need is synchronized. Tables can be replicated only with a primary key, so in case you have several tables you will have to figure out more innovative ways to move them.

3. SQL Scripts

Those working with a SQL Server may have right-clicked database > Tasks > Generate SQL Scripts command earlier but may have never realized that when you click on ‘Advanced’, you even get to control everything that is provided by the script. To make the script compatible with Azure SQL Database, you can even change the Engine Type from Stand-Alone or Azure SQL Database and specify the type of data you wish to script. So you can choose what to script – just schema, or schema and data both. This is nothing but your TSQL version of DACPAC (a single file that migrates only the schema) or BACPAC (a single file that encapsulates both schema and data and hence moves both). You must know that data scripting works well for smaller databases than bigger ones and the script that is created resembles individual insert commands per row.

Things to remember

While the migration process remains the same whether you are migrating to Azure SQL Database or Azure SQL Managed Instance, it is important to remember that while migrating databases protected by Transparent Data Encryption or TDE to a Managed Instance with the help of native restore option, the corresponding certificate from the on-premises needs to be moved before restoring the database. You must monitor applications once the migration is complete to track issues as well as improvements.

The Managed Instance Advantage

Azure SQL Database and SQL Managed Instance share a common code base with the latest stable version of SQL Server. Most of the features – be it language features, database features, or security features, are the same for both. Azure SQL Database came with many new features such as Dynamic Data Masking, Always Encrypted, Row-Level Security, Query Store, etc., to the SQL Server, adding to the on-premises functionality. But being built on a database scoped configuration model, it had certain limitations too. This is where Managed Instance came into the picture to remove those limitations and bridge the gap between Azure SQL Database and on-premises SQL Server, which is built on an instance scoped configuration model. Managed Instances let you lift-and-shift-on-premises SQL Servers and applications to Azure with minimal changes or alterations to allow you access to scalability and cloud availability without assuming on-prem responsibilities.

Migrate with MiCORE

Migrating to Azure SQL Database or Azure SQL Managed Instance needs a bit of planning and a whole lot of guidance. After all, it’s an important decision and has much to offer when executed correctly. Precisely why, we suggest you migrate with MiCORE. We have the experience, the expertise, and an energetic team of experts ready to help you down to the tiniest detail. We will figure out a plan based on the number of servers and databases, databases’ size, the acceptable business downtime during migration, and your business-specific needs. 

At MiCORE, we offer database managed services and cloud solutions with migration, 24/7 support, and monitoring for a seamless transition. Allow us to assess your business environment, and we will help you choose the best course of action for your SQL on-prem migration. Call us today.