By Zeeshan Ashgar
From startups and decades-old enterprises to small businesses and mega-corporations, cloud computing has captured the attention—and investments—of leaders across the business landscape.
And while the reasons for this are no secret—global access, flexible pricing, zero maintenance—the process of migrating data to the cloud remains a bit of a mystery for many.
Why?
Well, primarily it is because different organizations utilize different data sources, whether it be a flat file, MS Access, Oracle, or MS SQL Server.
Are you preparing to migrate data from an on-premises MS SQL server to single instance Azure SQL?
OZ has got your back.
But first, before jumping into the database migration process, it is essential to understand the SQL offerings provided by the Azure cloud platform.
Azure Database Offering
The Microsoft Azure SQL family has a rich set of database offerings, each suitable for a different type of workload and pricing tier.
There are many reasons to migrate to the cloud, including…
- Capital vs operation expenditure. Enterprises and businesses bear only the operation costs of running their required workloads on the cloud. Simultaneously, a cloud provider benefits from an economy of scale and can pass those savings on to consumers.
- Short release cycle. Cloud providers make it extremely easy for end users to create any required workload from a web interface near-instantaneously. This feature of quickly creating, testing, and releasing resources in production is a hallmark of any cloud provider.
- Scale for urgent/immediate capacity needs. Cloud has built-in scalability—horizontally or vertically. Behind the scenes, monitoring systems track different metrics, and when a current set of resources can no longer handle the workload automatically it will scale out/up. Similarly, when demand slows, the system automatically scales in/down.
- Hardware refresh from outdated systems to cutting-edge data centers. Cloud providers have state-of-the-art data centers and, whenever any hardware update is required, they will handle it while ensuring the configured workloads function properly with zero downtime.
- Global reach. With help of cloud power, web applications, databases, and more can be easily accessible via the internet.
Considerations
Key factors to consider when you’re evaluating migration options:
- The number of both database servers and the databases in each server
- The sizes of the databases
- The amount of downtime a business can reasonably accept during the migration process
Deployment Models
Application workload and usage pattern are the main factors in deciding which Azure SQL deployment model should be used.
The models:
- A single database represents a fully managed database with an SLA of 99.95% that is suitable for most modern cloud applications and microservices
- An elastic pool is a collection of single databases with a shared set of resources—for example, CPU or memory—which is suitable for combining databases in a pool with predictable usage patterns that can effectively share the same set of resources. SLA for elastic pools is 99.995% and each database could be up to 100TB.
- Managed Instance is designed for customers seeking to shift many apps from an on-premises environment or IaaS, self-built, or ISV-provided environment to a fully managed PaaS cloud environment with as low a migration effort as possible and complete isolation of customer instance with native VNet support. SLA for this type of offering is 99.995%.
SQL on VM is suitable when full control over the database server with nearly 100% SLA is required.
It allows you to move on-premises databases without any changes and supports database sizes up to 256TB.
Purchasing Models
To choose between vCore, database transaction unit (DTU), or serverless purchasing models consider:
- The vCore model allows you to pick the number of vCores for Azure SQL Database—making it the simplest choice when translating from on-premises SQL Server.
In the vCore-based purchasing model, costs will depend upon…
- Service tier
- Hardware configuration
- Compute resources (vCores and memory)
- Reserved database storage
- Actual backup storage
- The DTU model abstracts the underlying compute, memory, and I/O resources to provide a blended DTU.
- The serverless model is suitable for workloads that require automatic scaling and compute resources billed per second of usage. Though the serverless tier automatically pauses databases during inactivity, storage is still billed. When activity resumes, so does the database.
Service Tiers
Service tiers are designed for different types of applications and include…
- The general purpose and standard service tier offers a balanced budget-oriented option, suitable for delivering applications in the middle and lower tiers. Redundancy is built into the storage layer to help recover from failures. It’s designed for most database workloads.
- The business critical and premium service tier is for high-tier applications that require high transaction rates, low-latency I/O, and a high level of resiliency. Secondary replicas are available for failover and to offload read workloads.
- The hyperscale service tier is for databases that have growing data volumes and need to automatically scale up to 100 TB in database size. It’s designed for very large databases.
Migration Tiers
- Azure Migrate: Azure Migrate is a Microsoft service that helps an enterprise assess how on-premises workloads will perform and how much they will cost to host. It helps in planning the migration process.
- Data Migration Assistant: This desktop tool from Microsoft provides seamless assessments of SQL Server and single-database migrations to Azure SQL Database (both schema and data).
It can accommodate downtime during the data migration process and reports compatibility issues as well as SQL server feature parity. It also tracks the status of migration per database object, including the number of rows migrated. Its processes should be performed in off hours. (Supported sources: SQL Server (2005 to 2019) on-premises or Azure VM; AWS EC2 & AWS RDS; GCP Compute SQL Server VM.) - Azure Database Migration Service: This Azure service can migrate SQL Server databases to Azure SQL Database through the Azure portal or automatically through PowerShell. To ensure connectivity with the source DB server it uses Azure VPN. Designed for large migrations in terms of the number of databases or size of the database, you can migrate single databases or at scale. It can run in both online and offline modes, with minimal downtime. Migrations at scale can be automated via PowerShell. The time to complete migration depends on database size and the number of objects in the database. It requires the source database to be set as read-only. (Supported sources: SQL Server (2005 to 2019) on-premises or Azure VM; AWS EC2 & AWS RDS; GCP Compute SQL Server VM.)
Migrate SQL Database Using Azure Migrate
Prerequisites:
Install DMA (Data migration assistant V5.6) tool.
- Download the sample AdventureWorksLT2017.bak file and restore it on the local on-prem database server.
- Create Azure Migrate project.
- Create a Database in Azure.
Login to your Azure portal and search “Azure Migrate.”
Click “Azure Migrate” and choose “Databases (only)” for migration.
Click on the filtered pipeline.
Enter basic information on the “Create project” blade and click “Create.”
Click “SQL databases” and choose “Create SQL database.”
Add basic information and click “Review + create.”
Once all the prerequisites are completed, run the DMA tool.
Click “New” and add a new assessment.
Add basic information and click “Create.”
Follow the wizard and click “Next.” Check both options—”Compatibility” and “Feature check” should be performed in the assessment.
Connect with your local DB server.
Once a connection is established, select your database.
Click “Start Assessment.”
Depending upon the size of the database, once the assessment is done a report will appear. See, for example, the below SQL Server feature parity report:
See, for example, the below compatibility issues report:
If there is no compatibility issue, it is time to migrate.
(Note in our example here the feature parity check flags one issue because we are using SQL Express edition which by default enables trace flag 8017 at a global level and is not supported in SQL Azure.)
Azure Migrate Assessment
Return to DMA.
Click “Upload to Azure Migrate.”
Connect to Azure.
Select “Subscription” and the Azure Migrate project from the dropdown.
Click “Upload.”
It will take five to ten minutes to upload the assessment.
Once uploaded, go to the Azure Migrate portal and click “Database.”
Open the “Assessed databases” report.
The database is ready for migration.
Create “Migration” from DMA.
Add basic information and click “Create.”
After connecting to the source server select the desired database and click “Next.”
Connect to the target server—”SQL Azure” in our case.
Select the required items to migrate—here everything is to migrate.
Click “Generate SQL Script.”
As demonstrated below, the script is generated successfully and ready for Schema deployment.
Once the script is generated click “Deploy Schema.”
Click “Migrate data” after schema generation has been successfully completed.
The DMA tool will display the row count and status “Ready to move.”
Click “Start Data Migration.”
Once the migration is completed, the DMA should report zero warnings and failures.
The migration has been completed successfully.
Post-Migration
In the post-migration stage, it is important to perform a very basic sanity test, which is a schema comparison between the source and destination database.
Compare the schema of the source on-premises database with the cloud-based destination database.
Here, one change has been found—related to an extended property only—which has no impact on migration.
Happy migration—and, remember, regardless of where you are on your journey, OZ will help you bring to bear the power of the cloud to transform your organization, save money, increase scalability, gain competitive advantage, and improve business efficiency and operations. Find out more about our cloud services and assessment offerings here.
Links
https://docs.microsoft.com/en-us/sql/dma/dma-migrateonpremsqltosqldb?view=sqlserver-ver16
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql