Migrating SQL Server to the Cloud – SQL Server Migration Process

by | Oct 22, 2015 | Blog

This is the fifth in a series of blog posts about SQL Server and the cloud (here you can read onetwothree & four). Congratulations if you’ve made it this far!

So far we’ve looked at the hosting options available, but how do you actually migrate your data into the cloud? Good question, keep reading to find out how…

Once you’ve built your shiny new cloud based SQL Server instance you need to get your database onto it. The degree of difficulty of this process is mainly determined by the size of the database you are transferring – obviously the larger the database the longer the migration process.

At the time of writing, AWS is the only vendor that is creating solutions for customers with HUGE databases. They recently announced the AWS Database Migration Service and AWS Snowball, which are two ways to migrate large data sets into the cloud. You can read more about that here.

But for “normal” sized databases here is an overview of the migration processes offered by the three main cloud vendors:

AWS has different methods for database migration depending on whether you are using AWS RDS for SQL Server or Amazon EC2/AMI:

1. AWS RDS for SQL Server – due to the fact that AWS RDS is a fully managed database there are several features disabled by default, such as RESTORE DATABASE and BULK INSERT. This means that the only way to migrate an existing database into the cloud is to follow a process documented by Amazon that makes use of the Generate and Publish Scripts Wizard to create a database creation script. There are also a number of other steps to follow, which I won’t go into here, but the whole process is fully documented on the AWS website.

2. Amazon EC2/AMI – this is much simpler as RESTORE DATABASE and BULK INSERT are available. A SQL Server backup file can be copied onto the VM via numerous methods, for example WinSCP, RDP, Dropbox, SFTP, etc.

Google offers two defined methods for transferring files between your office and the cloud:

1. gcloud tool – a command-line tool can be used to copy files onto your virtual machine instances as well as to download files.

2. Secure FTP – use of FileZilla or other SFTP utilities to move data to and from the cloud.

These methods can be used to move a SQL Server backup to the cloud ready for loading into the cloud based instance.

Azure offers many methods to transfer SQL Server databases to and from the cloud, as well they should as SQL Server is actually their software! The two easiest methods are as follows:

1. Deploy a SQL Server Database to a Microsoft Azure VM Wizard – if the target database is SQL Server 2012 or greater this wizard fully migrates the database from its existing location to the cloud instance. Easy!

2. Manually move database backup file – use of RDP, copy, SFTP, etc. to manually move the database backup onto the Azure VM and then load it onto the new SQL Server instance.

Next week we’ll take a look at the SLA’s of the cloud vendors and their reliability.