Consideration—Backup to S3 Performance
When taking a backup to Blob storage, the limiting factor will typically be bandwidth on your Internet connection. However, if that isn’t the case, then striping a backup over multiple files will help drive higher throughput on the network due to multiple writer threads. RDS for SQL Server can also make use of multiple threads when performing the restore operation, which results in higher throughput for the restore operation.
Database Migration Service
The Amazon Database Migration Service (DMS) is a service that will automate a large portion of the process when moving your databases from on-premises to AWS (EC2 or RDS for SQL Server) services. There are several options when it comes to using the Amazon DMS. It’s possible to do the schema, schema and data (static or data change), or data only to an existing database in Amazon.
There are a few infrastructure prerequisites for a successful DMS migration, including network connectivity between the source and VPC that the destination systems are located in.
RDS for SQL Server has a very high degree of parity with on-premises SQL Server. One of the features supported is Transactional Replication. RDS for SQL Server can be configured as a subscriber using a push architecture.
The use of Transactional Replication for database migrations does rely on the database schema supporting the use of this configuration. More details on Transactional Replication usage restrictions can be found in the Microsoft documentation article, "Considerations for Publishing."
If Transactional Replication is a viable option for your system, then it can be used to provide a migration mechanism that minimizes downtime during the migration event. Post-migration operations needed to decommission the source and replication topology will be minimal. The diagram below shows an example of the architecture for using Transactional Replication to migrate a database to RDS for SQL Server. More information about how to configure push subscriber replication with RDS for SQL Server can be found on the AWS Database blog.
Transactional Replication to RDS for SQL Server—push subscription
Database Migration Assistant
The Microsoft Database Migration Assistant (DMA) has two project types—the analysis project, which helps you identify blockers to migration to selected targets, and a migration project. The migration project allows you to copy the database schema and data from the source system to the target server. In the case of RDS for SQL Server migrations, the DMA will only be of use in performing an analysis of the source system to identify behavioral changes. You won’t be able to migrate a database to RDS for SQL Server with DMA because the account used for the migration needs to be a member of the sysadmin group. Unfortunately, sysadmin is not a group supported in RDS for SQL Server; it uses a bespoke security configuration.
Amazon EC2 VM
The Amazon PaaS offering provides a wealth of capabilities. But there are still going to be reasons to migrate to an IaaS solution with on-premises SQL Server due to constraints such as third-party vendor applications that only support specific versions of SQL Server.
There are several routes to moving on-premises databases to IaaS. You can use both DMA and DMS to perform a migration to AWS EC2 VMs. If you’re using SQL Server 2012 or newer, you can utilize Availability Groups to replicate the data. This migration model also helps with the application configuration, as the listener can be used to ease the application migration from a connectivity perspective.
Backup and Restore
You have likely been using backup and restore as a method for moving databases between SQL Servers for an exceptionally long time. It’s one of the most versatile options that can help with moving some of the largest databases by making use of full, differential, and transaction log backup options.
There are two main routes for using backup and restore to move SQL Server databases between servers. The first is to perform it manually via scripted methods such as T-SQL or PowerShell with the dbatools module. The second is to make use of log shipping within SQL Server, which allows for the priming of the target environment and can drastically reduce downtime for migration activities.
The option you choose to use will depend on the connectivity between on-premises systems and AWS. If the two environments are connected via either site-to-site VPN or Direct Connect, then both options are open, irrespective of whether you have extended your Active Directory (AD) domain into EC2 VMs. If the two environments are distinct, then you are restricted to the first option and have to manually perform the migration activities via Amazon S3 storage. It’s possible to automate these activities, but it will involve additional services and configuration to make work smoothly.
Backup and restore a SQL Server database to EC2 VM
Native Backup and Restore
When using native backup and restore methods for migrating a SQL Server database to an EC2 VM, the best route is via Amazon S3 storage. The use of S3 storage allows for both connected and disconnected environment configurations. It’s highly recommended that you make use of the dbatools PowerShell module, which gives you a wider range of options when scripting, and subsequently automating, the process.
Using Amazon S3 storage as part of this process means there’s no need to allocate temporary storage to your SQL Servers or stand up dedicated infrastructure to handle the backups. S3 storage can be created and destroyed with negligible impact to the target environment.
For connected hybrid environments in which the network and AD have been extended to an Amazon Virtual Private Cloud (VPC), having connectivity to both the source and destination makes this process a lot easier. S3 storage would take the place of a network share that you would use. At this point, the process is the same you would use for any on-premises migration between servers. You can even utilize PowerShell—if the version of SQL Server at the source supports backup to URL, then use Backup-DbaDatabase from the source to the mapped drive and Restore-DbaDatabase from the same S3 bucket mapped to the EC2 machine.
In disconnected environments, where the Amazon portion is distinct from the on-premises environment, the method is the same. However, the implementation differs, as you will need to connect to the target systems via a public route such as RDP to perform the restore operations. It isn’t advisable to expose the SQL Server systems to the Internet via public IP addresses on EC2 VMs.
Transaction Log Shipping
If you have a connected hybrid environment, then transaction log shipping is a viable option for migrating databases to EC2 VMs. Log shipping is a tried-and-tested technology for creating copies of databases; it can leverage many of the new features, such as backup encryption introduced with SQL Server 2014.
There are three options to configure log shipping regarding storage for backups:
Provision the required storage on the SQL Servers that will hold the transient backup files.
Stand up a dedicated file server within the environment that can be accessed by both source and target servers.
Make use of AWS Storage Gateway for files to use S3 as a storage area for transferring files between on-premises and the cloud.
Whether migrating to a single EC2 VM or to a multi-replica Always On Availability Groups configuration, log shipping can add a lot of value to the process. Once the storage is provisioned for the backup files, then it’s simply a case of using the native log shipping feature in SQL Server to configure the primary and secondary servers.
SQL Server log shipping’s ability to ship to multiple secondary systems to prime an Availability Groups configuration can really simplify the deployment. It means that all replicas will be at the same state so that when the primary replica is brought online, then the secondary replicas are simply added to the Availability Group.
Now you need to determine the timing for the shutdown of the application, wait for the final logs to be shipped, and then configure the target system. Once that’s done, the application(s) can be reconfigured to work with the new servers and begin processing again.
No matter which platform you plan to migrate to, the fundamental process is the same. You want to move databases from one place to another as effectively as possible with minimal input. DMS is often is the first port of call for those looking to migrate to the AWS platform, no matter what your target is. The ability to manage the projects in the service and leverage the online migration option means that it’s one of the most effective options when looking to combine it with automation routines.
If you’re not able to leverage DMS, there are still many other ways to migrate SQL Server systems to AWS.
Although this section of the guide has focused on the migration technology options, it’s still important to note that data testing and validation should be undertaken during the final migration. The tests and checks that you defined to ensure that the migration is successful need to be completed as part of the actual migration task, too. Data testing and validation will ensure that risks to the migration process are minimized.