Amazon Web Services EC2 is a great IaaS Platform and many organizations use it to host SQL Server instances. Being IaaS platform is opens up easy migration scenarios from on-premises deployments and for many people its a great first step into cloud. However, hopefully soooner than later, many companies realize that they are ready to take next step – move their workloads and databases to PaaS cloud service. This is where Microsoft SQL Azure DB shines.
Microsoft Azure SQL Database (formerly SQL Azure, Windows Azure SQL Database) is a cloud-based database service from Microsoft offering data-storage capabilities. The aim is for users to just communicate with a T-SQL endpoint rather than managing database storage, files, and high availability. Azure SQL Database obviously has number of limitations like lack of support of cross database queries, SQL Broker, etc. meaning that not every database can be migrated to Microsoft Azure SQL DB without prerequisite work. However, for folks that are ready to migrate Microsoft Data Migration Assistant can be viable option for such migration from AWS EC2 IaaS.
For my tutorial I have SQL Server on Windows instance in EC2 running well known venerable AdventureWorks2016 database. Here it is amongst my other servers in EC2 console:
With AWS elastic IP assigned to that Windows machine I can easily connect to default SQL instance via my local SSMS:
Obviously previously I had to setup proper inbound rules for this machine both with AWS EC2 Security Groups and Windows Firewall.
Next step is to create Microsoft Azure DB target. This is well documented topic as well, officially described here – https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started-portal .
I created S3 tier database calling it SmallSQLTarget.
I also had to again go to security group for the server and open inbound ports for SQL Server traffic again (port 1433). Smart way to do it is to filter by client IP of my workstation where I run my SSMS and will run Data Migration Assistant tool
Now I can make sure that I can connect to my target Microsoft Azure SQL DB from my client machine via SSMS as well:
Now lets proceed with migration. For that you will need to download and install Microsoft Data Migration Assistant from – https://www.microsoft.com/en-us/download/details.aspx?id=53595 . Once you installed the tool lets create new project. I will start with assesment project to make sure I dont have any critical incompatibilities that preclude my migration to PaaS platform.
Tool will check for compatibility issues in my database as well as feature parity to see what needs to be done to migrate any SQL Server instance features to PaaS if anything at all.
We can then provide credentials to our AWS EC2 based instance and pick database after connecting to the source:
Now that we added source lets start assessment. DMA can have any version of SQL Server as assesment source up to 2016. As assesment done you will get a nice report where you can see all of the potential issues before you migrate. It should something look like this :
Now lets migrate our database as I found no migration blockers here.
Press on + button and create new migration project
Lets connect again to instance on EC2 and select DB to migrate
Next connect to Microsoft Azure SQL DB target by specifying server address and credentials and select target db, in my case SmallSQLAzure.
After that tool will do quick assesment and present you with schema objects you can migrate , highlighting\marking any objects that may have issues
After you pick objects you wish to migrate , tool will generate SQL DML script for these objects. You can then save that script for analysis and changes , as well as apply that script via SSMS on your own. Or you can go ahead and apply the script via tool as I done for this example by pressing Deploy Schema button.
After schema deployment finish we can proceed with data migration by pressing Migrate Data button.
Next, pick your tables to migrate and start data migration. Migration of data for smaller database like AdventureWorks takes few minutes. Everything transferred other than 2 temporal tables , but those are very different from regular database table. SQL Server 2016 introduced support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. There are other ways to migrate temporal tables to SQL Azure that I will post in my next blog posts.
Well, we are pretty much done. Next, check the data has migrated to target by doing few queries in SSMS:
For more on Microsoft Data Migration Assistant see – https://docs.microsoft.com/en-us/sql/dma/dma-overview, http://www.jamesserra.com/archive/2017/03/microsoft-database-migration-tools/, https://blogs.msdn.microsoft.com/datamigration/2016/08/26/data-migration-assistant-known-issues-v1-0/, https://blogs.msdn.microsoft.com/datamigration/2016/11/08/data-migration-assistant-how-to-run-from-command-line/
Hope this helps.