Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



How to Migrate an On-Premise SQL Server Database into Microsoft Azure As-A-Service (PAAS)
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-07-27








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