SQL Database Managed Instance

Migrating Your SQL Server Estate To Azure

/ Mike / Leave a comment

Many customers want to move their SQL Server databases into the Azure cloud. Whilst not discussed here, moving to a PaaS (Platform As A Service) database platform provides many benefits over you having to manage your on premise or IaaS (Infrastructure As A Service) SQL Server databases.

Whilst many want to, the actual process isn’t traditionally easy. There are many steps to go through, including identifying what you need to migrate, assessing compatibility with newer SQL versions, making any necessary changes, and that’s even before you attempt the migration itself! I’ve had several DBA roles in the past and the challenge is all too real.

Thankfully, there’s a handful of tools in the Azure space that can make this whole process a lot easier. In this blog I’ll touch on each of these – what to use and when, and will also provide a high level process that links them all together.

A lot of the information I’m going to talk about can be found in the excellent Azure Data Migration Guide, and is recommended reading for any migration to Azure. It also covers other sources such as MySQL and PostgreSQL as well as good old SQL Server.

migration process

Data Migration Process (Azure Data Migration Guide)

Discover

In this phase you are looking to identify and inventorise your SQL Server estate so that you know what you actually have running. The MAP toolkit is a great free tool to get started, and will give a breakdown of the SQL Server Editions and the features in use.

MAP Summary

Sample MAP Summary (Data Migration Guide)

 

Assess

Now we’ve documented our estate and know what we need to migrate. We need to assess these source databases and see if there are any breaking or deprecated features that would need changing before attempting to move to the latest version of SQL.

In our kit bag this time we have the Database Migration Assistant (DMA).  Newly updated (October 2018) to include Azure SQL Managed Instance as a target, you simply point DMA at your source database, choose “Assessment” and it will spit out any issues with SQL Server feature parity or compatibility that you need to be aware of.

DMA

Database Migration Assistant

Migrate

Okay, so we’ve ran our assessment using the DMA. We fixed or amended any potential blockers to moving our source database into our target SQL database. We’re now ready to move onto the final part of the migration, the actual move itself.

There are several methods of doing this (depending on your source and target), from the good old back up, copy and restore through to more advanced methods. You can also use the DMA itself to do a migration, however the DMA is more recommended for singleton migrations or for testing before the real thing.

For production scale migrations we recommend the use of the Azure Database Migration Service (DMS), which is a fully managed database migration service and allows us to not only migrate databases from SQL Server in bulk, but also let’s us do these migrations online, meaning that, following the restore to the target database, the DMS establishes a continuous sync between source and target. This feature means that you don’t have to do the cut off straight away if you’re not ready, and make that move further down the line whilst minimizing the operational impact.

dms-create-project3

Example DMS Migration Project

Summary

In this article I’ve barely skimmed the surface on some of the processes and tools required to migrate your database into Azure SQL. However, with the Database Migration Guide, coupled with the Database Migration Assistant and the Database Migration Service, you’ve now got an excellent kit bag to make that challenging migration just a little bit easier (and less stressful!).

Useful Reading/Links

Azure Data Migration Guide

MAP Toolkit

Database Migration Assistant

Database Migration Service

Choosing Your Database Migration Path (Whitepaper)

 

 

SQL Server Options In Azure

/ Mike / Leave a comment

The data platform options in Azure are vast, and they grow and change with each month. Choosing the right database platform for your workloads can be a challenge in itself. This post aims to give some clarity as to the options available in the SQL Server space.

Before diving in, it’s worth clarifying the underlying platform options to be discussed. SQL Server in Azure comes in both Infrastructure As a Service (IaaS) and Platform As a Service (PaaS) flavors, which each providing different benefits depending on your specific needs. This is best summarized in the graphic below:

sqliaasoptions

SQL Server IaaS & PaaS

Infrastructure As A Service Options

SQL Server In Azure VM

As simple as it sounds, you can run SQL Server “as is”, running the full engine on a Windows or  Linux virtual machine, and also in a Docker container. This option is exactly the same as the one you’d run on premise, with the full SQL Server feature set, including High Availability, Security and various other features depending on the edition you choose.

If you need full control over/access to the OS, need to run apps or agents alongside the database, and basically want to manage all aspects of your solution, then SQL Server IaaS is the right solution.

This SQL Server option can be accessed from the Azure marketplace, with many editions available depending on your needs.

Billing in this model comes in two flavors too, Bring Your Own Licence (BYOL) where you provide your own SQL licence and just pay for the underlying compute/storage, or Pay As You Go where you pay the SQL licence per minute the machine is running for.

Platform as a Service (PaaS) Options

Along with the full control of SQL Server IaaS, you also have several PaaS options. These provide much better total cost of ownership (TCO) as much of the daily administration is handled by the Azure platform, so things like backups, high availability, performance tuning and monitoring are all abstracted away, allowing you to just focus on building/using your application.

 

sqlpassoptions

SQL PaaS Options

SQL Database

The first of the pure PaaS offerings, (sometimes called Database As a Service (DBaaS)), SQL Database offers the power of the SQL Server engine, but without the management overhead that comes with maintaining a full SQL Server instance.

As a DBaaS, SQL Database brings with it many features, including dynamic provisioning and resizing, built in High Availability, automatic backups, point-in-time restore and active geo-replication. As Microsoft assumes much of the daily maintenance work, this frees you up to realize cost or operational benefits that you wouldn’t have experienced with your on-premise or hosted solution.

Unlike the IaaS option, where you choose the virtual machine that your instance will reside on (with associated cpus, ram etc), SQL Database instead comes in different tiers, from Basic through to Premium. Rather than choosing specific hardware items like RAM, SQL Database tiers are measured in Database Throughput Units (DTUs), with the underlying specifications abstracted away.

Read here for more details on how to choose the best tier for your application.

There is a small subset of features not available or not applicable in SQL Database compared to SQL Server. Please check here and here.

SQL Database Managed Instances (Preview)

Designed to minimize the challenges of migrating applications to a SQL Database environment without having to perform application rewrites, SQL Managed Instance is an extension to SQL Database that offers the full SQL Server programming surface and includes several native SQL Server features:

  • Native Backup and Restore
  • Cross Database queries and transactions
  • Security features – Transparent Data Encryption, SQL Audit, Always Encrypted and Dynamic Data Masking
  • SQL Agent, DBMail and Alerts
  • Change Data Capture, Service Broker, Transactional Replication and CLR
  • DMVs, XEvents and Query Store

On top of this, SQL Managed Instance offers full security and isolation, with SQL Managed Instance being behind their own virtual network (vnet) within Azure (this is now also available for SQL Database).

Currently in private preview, with public preview due shortly, SQL Managed Instance is a great way to get started moving data to the cloud that combines the benefits of both PaaS and IaaS SQL Server models without having to make changes to the affected application. Microsoft have also created the Azure Data Migration Service to make the migration as seamless as possible.

SQL Database Managed Instance also provides an added incentive to move to the cloud with the Azure Hybrid Cloud Benefit for SQL Server. This allows you to move your on-premise SQL Servers to Azure, and only pay for the compute and storage. See here for more details.

SQL Database Managed Instance is going to be a real game changer in this space in my opinion. My colleague James Serra has created an excellent deck that goes into more details here.

SQL Database Elastic Pools

Whilst not technically a different type of SQL Database offering, Elastic Pools provides the ability to manage multiple SQL Databases that have a variable and unpredictable workload. SQL Databases in an Elastic Pool are allocated elastic Database Throughput Units (eDTUs) that dynamically scale the databases within the pool to meet the required performance demands.

Elastic Pools are ideal for multi-tenancy environments where the workload can’t be predicted, but you don’t want to have to over provision for those “just in case” moments.

SQL Data Warehouse

SQL Data Warehouse, whilst is “relational” in that it has tables with relations between them, is a different concept to the options above. SQL Data Warehouse is what is known as an MPP (Massively Parallel Processing) solution, designed for heavy duty data processing and querying at scale, the likes you’d see in a data warehouse or similar analytics solution.

SQL Data Warehouse could easily be the subject of a full article all on its own. This platform provides a fully scalable analytics engine where the compute and storage are scaled independently, meaning you’re not fixed into certain configurations that are over specced, thus providing greater TCO benefits. With the underlying Polybase engine supporting it, SQL Data Warehouse is a must-have component in many modern data warehouse architectures within Azure.

Read here for more information about SQL Data Warehouse use cases and design patterns.

Honorable Mentions

This post is specifically around SQL Server options within Azure, however I wouldn’t be doing justice to the platform if I didn’t mention the other database options available, and will be covered in future posts:

  • Cosmos DB – A globally distributed, multi-mode database service that supports SQL, Graph, Cassandra, MongoDB and Table APIs.
  • Azure Database for MySQL – Exactly as it says on the tin. This is another PaaS offering that provisions a MySQL instance.
  • Azure Database for PostgreSQL – As above, this PaaS offering provides PostgreSQL functionality, all managed by the Azure platform.

Which Should I Choose?

There are many factors to consider when choosing a relational database service in Azure, including cost, control, workload type, governance requirements and many more. As a general rule, if you need to have full control over your environment, with a full feature set, then SQL Server 2017 as a virtual machine or container is the way to go. For applications born in the cloud that just need the database engine and don’t want or need the hassle of maintaining the database, then SQL Database or SQL Database Managed Instance are excellent options. Again, if you need that elasticity that comes with hosting a multi tenant or highly variable environment, then SQL Database Elastic Pools is the option for you.

The real game changer now is SQL Managed Instances. Offering a full SQL Server feature set, but with all the benefits of PaaS, this option is great for those looking for a seamless move from on-premise to the cloud.

Further Reading

SQL Database vs SQL Server

SQL Data Warehouse vs SQL Database

SQL Managed Instances