Migrating to SQL Azure from on-premise SQL 1/4 — Pitfalls

We are currently moving our databases from an on-premise setup to the cloud using Azure SQL databases.

In this series we’ll discuss the process and pitfalls on getting a database into the cloud.

The requirements are as follows:

  • On-premise SQL Server instance (SQL Server Standard)
  • Azure Subscription

Our setup is a SQL Server 2016 Standard edition, which includes the feature for Transactional Replication as a publisher.

On-Premise features

An on-premise SQL server setup provides you with a lot of features, however, not all of them convert to Azure SQL that easily.

Data Types

Not all data types are supported in Azure, you might want to rebuild some tables with a convert script before moving to Azure.

SQL Jobs

The concept of jobs does not exist in Azure SQL, but there is Azure Automation that allows you to run scripts and even stored procedures on a schedule using Powershell.

Linked Servers

Linked servers cannot be configured in Azure SQL, I find this to be a pretty obvious constraint.

If you rely on external data, you’ll need to migrate this to Azure SQL as well. The external data source needs to be configured and can be queried, with limitations of course.

Replication

There is no possibility to set up replication, not as a publisher, anyway. Azure SQL databases can become subscribers, but the replication jobs need to run on the publisher, an on-premise machine.

There is an alternative, though.

Azure SQL Data Sync has its limitations, but it can certainly be used for one-way replication, be that ‘replication’ is not the correct term for this kind of setup. It is solely data synchronization, schema changes are not propagated. Your schema changes will need to be scripted manually.

My advice for supporting replication: either invest in a VM with SQL Server as a publisher or re-engineer your software solution so that this is handled in the application layer.


Resource considerations

An on-premise setup holds some additional benefits besides its rich feature set, namely resource usage.

If your SQL Server instance is installed on a beefy machine, performance will not likely be much of an issue. Although it does not scale freely, once you upgrade the machine it might take a while until you reach its raw limit again.

Also, an on-premise machine allows SQL Server to use whatever capacity is available, Azure SQL databases are throttled. Basically, instead of providing the process with all its available capacity, it denies and timeouts requests to resources. This is common in a cloud solution and can be partially overcome by throwing a bucket of cash against your Azure SQL database server by placing it in a higher performance tier or scaling out your Elastic Pool.

Another approach would be to reconsider your application’s data layer. Requesting data using paging and filtering can provide significant benefits to your database performance.

Elastic pool vs Dedicated pricing tier

As mentioned before, Azure SQL databases are throttled, once you’ve reached the limit of the database resource units (DTU’s), additional requests will be timed out. This avoids one rogue query to blow up a host machine in a Microsoft datacenter.

DTU, or database throughput unit, is measured using the CPU, Memory and Disk read/write utilization for your database.

With an elastic pool, you create a shared resource group for your databases. This can be much more cost efficient, especially when your database infrastructure requires multiple databases or multi-tenancy.

This is not always the case, if your database resource usage displays a typical pattern with peaks in performance every now and then, an Elastic Pool might be suited for you.

However, if your database usage requires large amounts of resources (DTU’s) for a longer period of time, import data jobs that run for several minutes, for example. It might be a better idea to have a dedicated resource tier for your database.

If you want to scale out your Elastic Pool, keep in mind that all databases within the pool are affected, briefly. But there will certainly be some kind of downtime during the scaling process. If you need to scale out a dedicated database tier, only that database is affected.


Migrating to Azure — Strategies

Depending on your application’s needs you might want to choose for a specific migration approach. If you cannot afford to have any downtime, or as less as possible, you may want to consider to choose for the most intense migration strategy.

Data Migration Assistant

Microsoft provides a proprietary tool to migrate your on-premise database to Azure.

I have no experience using this tool, but it looks promising.

Data Migration Using Replication

We’ve decided to opt for replication because our databases contain about 100GB of data. Using a tool seemed untrustworthy for this kind of load.

Downtime is allowed — strategy

When your application is permitted to be down for a large period of time, you might want to generate a back-up of your database and restore the database as-is. This provides a full restore and is the safest option, since no real migration takes place. Keep in mind that some data types and SQL features are not supported in Azure SQL (see above).

This is also a possible approach if you’re allowed for your application to be set to read-only mode for the duration of the migration.

The strategy will be as follows (downtime window indicated in bold):

  • Stop applications that target the database(IIS website, Desktop apps, other web apps)
  • Set database to read-only mode
  • Extract data (BACPAC)
  • Set database offline
  • OPTIONAL: Put database online as read-only
  • Restore database in Azure (BACPAC)
  • Alter connection strings for your applications
  • Start applications

Based on your database size, this might not be an option, our databases were about 100GB, so uploading a BACPAC to Azure would take too long and is too erratic to rely on. But for small databases, this could potentially be a compelling strategy.

Some downtime is allowed — strategy

In case it is acceptable for your application to be migrated in a maintenance-window (30mins — 1hour), you might favor the following strategy using data replication.

For starters, the on-premise database will be setup as a replication publisher using transactional replication. A first-time snapshot is taken, after that, each transaction is delivered on a trial and error basis. If a transaction can not be delivered to a subscriber, you have the possibility to act upon the error and retry. This is also the preferred approach for migrating large databases.

We picked this strategy because of the limited downtime and because we needed to convert some data type during the migration.

This is how you might want to migrate using the replication strategy (downtime window indicated in bold):

  • Generate scripts from source database (Tables, Views, Stored Procs)
  • Create empty database in Azure
  • Run create scripts from source database (here you will find the incompatible data types)
  • Setup source database as replication publisher using transactional replication
  • Setup target Azure database as publication subscriber
  • Let the replication take place
  • After replication is finished, stop applications that target source database
  • Verify no additional transactions are logged or delivered
  • Stop replication log agent and subscriber agent
  • Take source database offline
  • Re-seed the target database (this is a result of replication)
  • Run additional migration scripts against target database (specific to your database-setup)
  • Alter connection strings for your applications
  • Start applications
  • Verify

I will go into detail on this strategy in upcoming posts, for now, let us stick to the theory.

The additional migration scripts included the creation of new views, external tables, additional indexes and a re-indexing of the entire database. So our maintenance window was about 1–2 hours long.

Absolutely no downtime is allowed — strategy

In case your application is not allowed to have any downtime, you might want to consider a variant on top of the previous strategy.

In essence, this includes a load balancer that directs the traffic from the source database to the target database once the migration is completed.

This involves a lot of testing of the staging-system (target database), once the system is working, the migration is re-done and the traffic is directed from one machine (on-premise) to the other (Azure SQL Database).

Not to be pessimistic, but there will be downtime, eventually. It would be best to make the business aware of this. There are other strategies to allow for retries of writing data in a microservice setup (using CQRS, Event Sourcing ,and a Message Queue). But this is a whole other topic altogether.


I hope to have shed some light on how migrating to Azure can work for you and your business requirements. In the next post, I will explain how we did it, what choices we made and for what reasons.

Like what you read? Give Maarten Merken a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.