One of the key goals of a Data Platform modernisation program is thinking about what to do with your SQL Servers. So here we are going to give some high-level tips to migrate from SQL Server to the cloud.
If you want to move to a more cloud-native approach, we can split the migration journey into an IaaS (Infrastructure as a service) or PaaS (Platform as a Service) approach.
Quite simply, IaaS is where you have SQL Server on Azure virtual machines and PaaS could be anything from elastic pools, Managed Instances or Azure SQL Database. This topic is geared to more of a PaaS design where Azure SQL Database is the truest form of a platform as a service database engine within the Microsoft world.
Why PaaS for SQL Server Workloads?
The below image shows the various forms of SQL Server that the modern-day data professional will encounter. This diagram shows you the administration and monetary cost of each type.
As you can see, PaaS when compared to SQL Server on physical machines has less administration overhead with a lower shared cost. Therefore, many companies are pursuing a cloud strategy and even though this image applies to SQL Server, it can be applied to other technology areas too.
The idea of less administration is more about the human element of work. There is no need to patch operating systems, upgrade SQL Server versions, configure Always on Availability Groups or backup configuration. This is done for you, leaving data professionals to focus on
the application and database performance from a coding perspective. Ultimately your end customer benefits from this.
The shared lower cost is about being more cost-efficient. Not only is this from a licensing perspective (where you can bring your own license), you also don’t have to worry about the costs of the underlying Data centre components where the database infrastructure is hosted such as power, electricity and cooling capabilities.
Being in the cloud makes you more agile to market changes. For example, if you know that you need to scale up or out your compute tiers for seasonal sales or an increase in demand, then it is very easy to do that within a cloud environment, not so much when working within an on-premises setup.
Data Migration Assistant (DMA) Tool
The first key task is analysing your on-premises workload to understand what sizing you need in the cloud. The DMA (Data Migration Assistant) tool is used to carry out that assessment and depending on the target, it can help with the migration of data. The tool can be downloaded from Microsoft here: https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15.
Important Note: you will also need to install .NET Core 3.1 SDK
This tool is crucial in understanding the work needed for a successful migration. Below are some best practices to follow:
- Assessments on production servers should be executed during non-peak hours.
- If you decide to use this tool to migrate data then always state a single central location that is accessible by both source and target servers. If this is not done then the tool may introduce some copy operations which may cause a substantial increase in migration times.
The main purpose of this tool is to analyse the workloads, the tool can also help with migrations, but we shall consider that as a separate task and out of scope for this section.
Once the DMA Tool is Installed
So once the tool has been installed, the first task is to run the sqlassesment.exe application via the command line, such as:
.\SqlAssessment.exe PerfDataCollection
–sqlConnectionStrings “Data Source=Server1;Initial Catalog=master;Integrated Security=True;” “Data Source=Server2;Initial Catalog=master;Integrated Security=True;”
—outputFolder C:\Output
This will then load up the datalink window to establish the connection to the relevant database.
We then need to take the above outputs (from C:\output) as inputs into the SKU recommender. Note you need to state the target platform type because we could use other types too. Here my target is Azure SQL Database.
.\SqlAssessment.exe GetSkuRecommendation
–outputFolder C:\Output
—targetPlatform AzureSqlDatabase
If you study the above output, it is very concise in its recommendations. It will start with basic things like the storage size and core requirements based on the CPU metrics captured. Also, note the I/O latency figures – it states 1.24 m/s latency was captured thus it recommends Business Critical tier for Azure SQL Database, which is the correct option here.
So, remember to look out for the SKU Recommendation which is the minimum cost-efficient SKU offering among all the performance eligible SKUs that could accommodate your workloads. Also study the Recommendation Reason, which is provided for each tier that is recommended.
Cost Saving Techniques When Migrating from SQL Server
Moving into Azure there are two common ways to help reduce costs, they are Azure Hybrid benefit and reservation policies.
Azure Hybrid Benefit
SQL Server customers with SA (Software Assurance) can leverage Azure Hybrid Benefit and pay base rate on either General Purpose or Business Critical SKU for managed instances or Azure SQL Database.
The following licensing types apply:
- Windows Server Datacentre Edition with Software Assurance.
- Windows Server Standard Edition with Software Assurance.
- SQL Server Enterprise Edition core licenses with Software Assurance.
- SQL Server Standard Edition core licenses with Software Assurance.
This can be activated during the build process of the Azure SQL Database. As you can see below, I build a generation 5 hardware 4 vCore Azure SQL Database which costs $706 without hybrid benefit but once enabled the cost reduces to $438.
Without hybrid benefit:
With hybrid benefit:
Reservation Policy
Further discounts are possible if you decide to leverage a reservation policy. This is a reservation of compute for either one or three years. This could lead to up to 70% savings when compared to a pay as you go model. When combined with Azure Hybrid Benefit, that is where the organisation will see the most cost gains.
This policy is best applied to workloads that are predictable and known to the business. The great thing about this is the vCore flexibility, this allows you to scale up or down within a performance tier and region without losing the reserved capacity benefit. If you combine reservation policy with Hybrid Benefits expect some significant cost savings. This can be done via purchase reservation within the Azure Portal.
High-Level Approaches for Migrating
Bacpac
If the target is Azure SQL Database (allowing for downtime) then BACPAC could be used. As you can see below, an assessment is required with relevant fixes, then the migration can take place. This is where you could use the DMA tool to do both tasks.
You can also build your own bacpac rather than rely on the tool. Another technique to migrate data to a shell Azure database would be to leverage the .NET SqlBulkCopyClass. Bacpac techniques are useful for smaller datasets where downtime is possible. If you decide Bacpac is the right method, then a tip is to scale up the tier of your target SQL Database as high as possible during the migration then once complete, scale it back down. This allows for a high performing loading exercise.
SSMS (SQL Server Management Studio)
A handy feature for database migration is built into SSMS. If you right-click on a database and select tasks you will see the option – “Deploy to Microsoft Azure SQL Database”.
This uses a Bacpac under the covers but abstracts it away from you. So, all you need to do is enter the target Azure SQL details as shown below.
Once the migration is complete, you will be able to see it within Azure.
Replication
If the business cannot afford downtime when moving to Azure SQL Database, then an advanced setup is leveraging transactional replication (database level) to the cloud. Then make an application switch, once replication changes have been applied to the new target.
Replication approach:
- Use transactional replication to replicate data from a SQL Server database to Azure SQL Database. Azure SQL Database is the subscriber within the replication topology.
- Let all replication changes replay to Azure.
- Redirect the client or middle-tier applications to connect to the migrated database copy.
- Decommission on-premises SQL Server instance.
Summary
As you can see from the article there are some compelling reasons to move your data stack to the cloud. If you now know that this is the strategy you would like to pursue then the key tasks are, to analyse your workload to understand what you can move and what service tiers you need. Then to carry out a migration exercise. Once done you should always conduct post-migration checks to confirm that the Service tier you selected is the most optimal one.
Migrate from SQL Server with Ballard Chalmers
As a Microsoft Gold Partner with a heritage in SQL Server and current expertise in Azure, we are uniquely placed to help you migrate from SQL Server to Azure SQL. Whether it is lift and shift, or modernisation with rearchitecting or rebuilding, our team has the experience. Get in touch to find out how we can help.
