Migrating an On-Premise SQL Database to Azure – Is it right for my organisation?

“Should we migrate our databases into Azure?” is a question many Database Administrators? (DBAs), IT Managers and IT Directors have contemplated during the last decade and more than likely right now!  The decision to migrate is not straightforward, so a company should not blindly move their estate without considering the cost, effort and time required for a successful cloud relocation.

However, cloud migration is fully justified if the solution saves infrastructure and administration costs, improves system performance, scalability, security, or provides superior high availability and disaster recovery options.

A migration decision requires analysing the different solutions that Azure offers, and to weigh-up their pros and cons.  So, a plan is essential to assess whether to migrate or keep your databases on-premise.

The primary reasons for database migration are as follows:

Cost savings

On-premise hardware (i.e. data centers and servers) is costly and requires trained IT personnel to ensure the infrastructure is set up correctly, maintained and also fixed whenever downtime issues occur.

Cloud-based technology reduces the amount of guesswork required for infrastructure costs as Microsoft only charges for the services that the company uses, as opposed to buying hardware that may never be fully utilised.

Microsoft provides a pricing calculator that configures and calculates the cost for your proposed environment.  As an example, if your company needs ten Azure SQL Databases and two Virtual Machines, the pricing calculator estimates the setup costs per month and will reveal if the cloud solution is more cost-effective than your on-premise solution!

Scalability

A great feature of Azure Databases is the ability to scale up or down on storage and performance requirements with a minimal amount of downtime.  This means organisations do not need to purchase additional hardware to cater for spikes in demand or running out of disk space.

Disaster Recovery (DR) / High Availability

Azure has several data centres scattered across the globe, meaning whenever a situation occurs, databases can automatically failover to other regions with no downtime.  Also, database backups are provided by Microsoft, so Point-In-Time or Full database recovery are easily catered for.

Compliance

Nowadays the security of clients’ data is of paramount importance, especially with the enforcement of GDPR rules.  Azure meets strict compliance standards in their database framework, so companies considering database migration will automatically inherit the security to meet regulatory standards.

Technology

Azure has the latest cutting-edge technologies implemented into its framework, so database upgrades, tuning, architecture, machine learning and security are heavily invested in by Microsoft.

Time

If Azure is managing the backups, failover, security, upgrades, infrastructure and various other facets of database management, this frees up time for the IT team to focus on the goals of the business.  This means having more development time on projects, generating extra revenue and attracting more customers.

Hybrid Migration

Migrating to Azure is not an ‘all or nothing’ solution.  As an example, if the company Security Manager advised that all databases except the HR database can move to the cloud, then that is not a problem.  The HR database can stay on-premise and the other databases can be migrated strategically over time.

There are other factors to determine when not to move to Azure which could be:

Cost

Depending on the nature of the database it might not be cost-effective to migrate.  A database with very high I/O intensive workloads may need an expensive Premium-level service tier, which could be more costly than an on-premise solution.

Compatibility

There are some features in an on-premise SQL Database that are not available in Azure SQL Database, so unless they can be fixed or worked around, it will block the migration.  The business also needs to consider if current applications and reporting tools will still work in Azure.

Time

The company may not have the time or resources to complete a migration.  Other more pressing company objectives may put the migration on-hold or even cancelled.

Security

The nature of the database data might be so sensitive that moving to the cloud would violate the company’s security policy.

The above factors need consideration to aid the decision to migrate, so at Simpsons Associates, we use the following flowchart to help decide.

Step 1:  Identify Migration Benefits

This step is the main driver behind a migration decision which was discussed in the blog’s introduction, and all the factors need consideration before making the final decision to migrate.

After weighing up the pros and cons, the biggest question an IT Director can ask is:

“Will migrating to Azure, using a pay-as-you-use system be more cost-effective than using traditional on-premise infrastructure?”

Note the question should be asked from not just a financial perspective but also from an employee resource standpoint (i.e. will my IT employees be more efficient using a cloud solution).

If there are more negatives than positives, then the decision is simple: don’t migrate for now.  The proposition can always be revisited in six months if the business position changes (i.e. extra revenue, relaxation of security protocols, DR requirements etc).

Step 2:  Identify Database Compatibility Issues

If the migration has been approved, then the technical staff (most likely the DBAs) need to discover if the target on-premise databases have any Azure SQL Database compatibility issues.  Compatibility issues occur when features exist in an on-premise database but are not available in an Azure SQL Database (i.e. linked servers, trace flags or FILESTREAMS)

The best tool to check compatibility issues is Microsoft Data Migration Assistant (DMA) If the compatibility issues cannot be resolved then a decision needs to be made to cancel migrating to an Azure SQL Database or use a different option such as migrating to a SQL Instance on an Azure Virtual Machine or even using an Azure SQL Managed Instance.

Step 3:  Identify the Azure Service Model

Identifying the correct service model affects the overall pricing, service tier, storage and performance of the database.  The database will need deploying as an individual Azure SQL Database, a database within an Elastic Pool, as an Azure SQL Managed Instance or even a database within an Azure Virtual Machine.

Step 4:  Identify the Azure Service Tier

Choosing the correct service tier affects the performance of the database so choose wisely!  A too-high service level results in unnecessary costs but a too-low level service level will degrade performance.

Microsoft provides an advisory tool on Azure Service Tiers based on CPU, IOPS and LOG utilization of your current on-premise database.

Even if the eventual service tier selected is not quite right, Azure allows the database to be scaled up or down to get the appropriate level of power required for your system.

Step 5:  Identify High Availability / Disaster Recovery Options

High Availability relates to providing business continuity if a server hosting an Azure SQL database fails.  Disaster Recovery refers to having a business continuity in the event of a whole data centre failing.

Azure provides different levels of protection from DR events ranging from high availability groups to active geo-replication across data centres located across the globe.  A decision on the level of DR protection relates to the business RPO (Recovery Point Objective – timeframe for how much data loss is acceptable i.e. 15 minutes) and RTO (Recovery Time Objective – timeframe for how long a system can have an outage i.e. 1 hour).

Step 6:  Identify Migration Tool

The next step is to migrate the schema and data from the on-premise database into the Azure SQL Database and to establish if database downtime is acceptable (i.e. choose an online or offline migration strategy).

There are several tools available to migrate a database that includes:

• SQL Server Management Studio (SSMS)
• Database Migration Assistant (DMA)
• SQL Server Data Tools (SSDT)
• SQLPackage.exe
• SQL Azure Migration Wizard
• Azure Database Migration Services
• Transactional Replication

For an offline migration that allows downtime, the wizards within the DMA or SSMS are very intuitive and easy to follow.  For an online migration, Transaction Replication allows data to be, ‘pushed’ from the on-premise database to the Azure database without any downtime.

Step 7: Migrate the Database

It is prudent to migrate a test copy of the live database before migrating the actual live system to ensure everything works as expecting.  The following areas should be examined.

• Ensure there are no migration errors reported.
• Record length of time to migrate schema and database (this equates to downtime).
• Check any external applications and users can connect to the Azure database (VPN, firewalls, SSMS etc).
• Check data can be read from and written to the Azure database as expected (via the application and direct SQL connections or roles).
• Check performance levels are acceptable on the Azure database (i.e. run some reports and check timings).

Once these areas are ratified, then the live database can be migrated.

Migrating an on-premise SQL Server database into the cloud is a big decision for any business, and we hope this blog gives you an idea of the processes involved. As a Microsoft Gold Partner, Simpson Associates have the necessary skills and experience to help. Please do not hesitate to contact them if you are looking to start your migration journey.

Blog Author

David Mills, Senior BI Consultant, Simpson Associates

Don’t forget to join us on Linkedin