Migrate an On-Premise Production SQL Server Database into Microsoft Cloud PASS Environment
Source: A single, on-premise, SQL Server 2014 Database w/ size = 25gb
Destination: Microsoft Azure Cloud SQL Database AS-A-SERVICE (also called PAAS - Platform AS-A-SERVICE)
One of the biggest challenges with moving to The Cloud is actually moving to The Cloud. Yes, very true! Production applications often require very little outage time and production databases are often GB's in size. Right there is the real challenge.
Our project was to migrate a 25GB On-Premise Production SQL Server 2014 Database into the Microsoft Azure SQL Database (PAAS) environment within a 1-2 hour window. First, a little background on PAAS. The Microsoft Azure PAAS environment is an as-a-service environment where you manage the database only. Microsoft takes care of the rest. PAAS has easily scale-able tiering in place which is basically a performance + price thing.
With PAAS, the higher tier you chose, the greater performance you can achieve from your databases. All this is measured in DTU's - Database Transaction Units (DTUs) which describes the relative capacity of the performance level of Basic, Standard, and Premium databases. For example, a Premium P11 database with 1750 DTUs provides 350x more DTU compute power than a Basic database with 5 DTUs.
The below technical blog is a summary of the steps we decided on for this migration project.
Step 1: Take a Full SQL Server Backup of the Source DB (25GB)
Total Time: 7 mins
Step 2: (OPTIONAL) Restore the backup db, to the same server, with a NEW db name ex. <SourceDBName>_Copy
*Optional because you can work from your source db. We were able to work against a copy.
Total Time: 7 mins
Step 3: Using SQL Server 2014 Management Studio, execute a Export Data-tier Application task.
- You may need to resolve any issues with Azure SQL Database Compatibility check like Cross-Database calls or [DB].[dbo] type calls &
re-start this task again.
- It is a good idea to work with the latest download of SQL Server Management Studio. Download Here
Total Time: 2.5 hrs
Image 1: SQL Server 2014 Management Studio
Image 2: SQL Server 2014 Management Studio
Image 3: SQL Server 2014 Management Studio
Image 4: Output File (.bacpac file) (contains all schema and all data)
Image 5: Resource Monitor - Used to monitor output file creation progress
Image 6: SQL Server 2014 Management Studio
Output File final size: 3.8GB
Step 4: Copy the Output file (.bacpac) to Microsoft Azure Blob Storage using the Microsoft Azure Storage Command Line Tool - AzCopy
Image 7: Windows Server 2012 Search
Image 8: AzCopy Tool
Sample Command: AzCopy /Source:C:\myfolder /Dest:https://myaccount.blob.core.windows.net/mycontainer /DestKey:key /Pattern:"abc.txt"
Image 9: AzCopy Tool
Total Time: 1 min and 48 secs. That is a 3.8GB file copied to Microsoft Cloud in under 2 minutes.
Step 5: Within Microsoft Azure Portal, navigate to SQL Servers, select a SQL Server, then select Import Database. This will import the
database into the as-a-service platform (PAAS)
Image 10: Microsoft Azure Portal
Image 11: Microsoft Azure Portal
Image 12: Microsoft Azure Portal
Total Time: Avg. 25 minutes (variances seen here with import performance over a few tests)
Note: At this point the On-Premise SQL Server Database 2014 is now fully migrated into Microsoft Azure PAAS Environment. You should next adjust the Pricing/Performance Tier for your SQL Database. P11 is the highest tier and cost the most! We chose P11 only to get the quickest possible import speed.
That's it !
Resources:
Microsoft Azure
SQL Database
SQL Server database migration to SQL Database in the cloud
Transfer data with the AzCopy Command-Line Utility
|
|
|
|
|
|