Migrate a multi-TB SQL Server database to Amazon RDS Custom for SQL Server using Amazon FSx for Windows File Server

This is the second part in a two-part series on how to migrate a multi-TB database to Amazon Relational Database Services (Amazon RDS) Custom for SQL Server.

RDS Custom for SQL Server is a managed database service that automates database setup, operation, backups, high availability, and scalability while providing access to both the database and the underlying operating system (OS). This access allows the database administrator to enable native functionality like SQL Common Language Runtime (CLR), configure OS settings, and install drivers when migrating legacy, custom, and packaged applications.

Common migration challenges

RDS Custom for SQL Server has a default data drive (D:) that allows for up to 16 TB of storage. For information on RDS Custom for SQL Server storage constraints and how to adjust storage, refer to Modifying the storage for an RDS Custom for SQL Server DB instance.

When migrating multi-TB databases to RDS Custom for SQL Server, you might encounter the following challenges:

How to migrate a multi-TB database when the database size plus the size of the backup files is bigger than 16 TB.
How to avoid storage overprovisioning when migrating multi-TB databases

These challenges were addressed using Amazon Simple Storage Service (Amazon S3) and Amazon Elastic Block Store (Amazon EBS) in Part 1 of this series. In this post, we present another solution using Amazon FSx for Windows File Server.

This solution is mainly designed for RDS Custom for SQL Server hosting a database instance version 2019. Starting with RDS Custom for SQL Server hosting a database instance version 2022, it is recommended to use Amazon S3. For more details, see Native backup and restore with Amazon S3 integration on Amazon RDS Custom for SQL Server 2022.

Solution overview

The following high-level architecture diagram illustrates the migration steps of a multi-TB database to RDS Custom for SQL Server using Amazon FSx File Gateway and FSx for Windows File Server as staging storage for RDS Custom for SQL Server.

FSx for Windows File Server is only used to host the database backup files and no database files. The database files cannot be hosted on FSx for Windows File Server because it will cause RDS Custom for SQL Server to go into an unsupported configuration state. To understand more about unsupported RDS Custom configurations, refer to RDS Custom support perimeter and unsupported configurations.

The high-level workflow includes the following steps:

Backup the on-premises SQL Server database directly to the FSx File Gateway file share.
Attach RDS Custom for SQL Server instance to an AWS Managed Active Directory (AD). You can also use a self-hosted Active Directory.
Restore backup files on RDS Custom for SQL Server.

Prerequisites

You should have the following prerequisites:

Background knowledge about SQL Server backup and restore.
Knowledge on how to set up, launch, and connect to RDS for Custom SQL Server instances.
FSx for Windows File Server and FSx File Gateway configured for this solution. For instructions, refer to Creating Your Gateway. Other options of data transfer like AWS DataSync or AWS Snow Family can be explored.
An SMB file share on the FSx File Gateway mounted on your on-premises server for backing up the database. For instructions, refer to Mount and use your file share.
AWS services such as Amazon Elastic Compute Cloud (Amazon EC2) and AWS Command Line Interface (AWS CLI).
An existing Active Directory. RDS Custom for SQL Server works in either an AWS managed directory or self-managed directory. Connectivity between your Active Directory and Amazon VPC should also be established. In this solution, RDS Custom uses AWS Managed Microsoft AD and the FSx for Windows File Server share is attached to the AWS managed directory.

Because this solution involves AWS resource setup and utilization, it will incur in costs on your account. Refer to AWS Pricing for more information. We strongly recommend that you set this up in a non-production instance and run end-to-end validations before you implement this solution in a production environment.

Back up your on-premises SQL Server database to the FSx File Gateway file share

We back up our very large on-premises database to the SMB file share on FSx File Gateway. To reduce the time to generate the backup, we back up to multiple backup files:

SampleTest_FullBackupCompressed01.bak
SampleTest_FullBackupCompressed02.bak
SampleTest_FullBackupCompressed03.bak
SampleTest_FullBackupCompressed04.bak

Attach RDS Custom for SQL Server to a domain

In order for RDS Custom for SQL Server to be able to access an Amazon FSx file share, the RDS Custom EC2 instance must be connected to the Microsoft AD that you joined to your Amazon FSx file system. Follow these steps to join the RDS Custom for SQL Server to the domain:

Configure Amazon Route 53 to allow DNS routing

To configure Amazon Route 53:

Log into the Route 53 console and make sure you are in the correct Region
On the left pane, under Resolver, select Outbound endpoints, and click on Create outbound endpoint

In the “Create outbound endpoint” screen enter the following information:
1. Endpoint name – enter a name for your endpoint
2. VPC – select the VPC that hosts your RDS Custom
3. Security group for this endpoint – select the security group of the RDS Custom instance
4. Endpoint Type – select IPv4
5. Protocols for this endpoint – select Do53
6. IP address #1 and IP address #2 – select the Availability Zone and Subnet where your RDS Custom for SQL Server is hosted. Leave “Use and IPv4 address that is selected automatically” checked.
Choose Create outbound endpoint (it might take couple of minutes).
On the left pane, under Resolver, select Rules, and choose Create rule

In the “Create rule” screen enter the following information:
1. Name – enter a name your rule
2. Rule type – select Forward
3. Domain name – enter the name of your domain
4. VPCs that use this rule – select the VPC where your RDS Custom for SQL Server is hosted
5. Outbound endpoint – select the endpoint created in the prior step
6. Target IP addresses – enter the DNS IP addresses of your domain controller. Leave Port as 53 and Transmission Protocol as Do53
Choose Submit
Make sure your RDS Custom for SQL Server security group has the necessary rules to support Route 53 as well as any other services or applications. In our setup we added the following outbound rules to the VPC CIDR as destination:
- TCP/UDP 53 – DNS
- TCP/UDP 88 – Kerberos authentication
- TCP/UDP 389 – LDAP
- TCP 445 – SMB
- TCP 135 – Client CIDR – Replication RPC, EPM
- TCP 49152-65535 – Client CIDR RPC Replication, user & computer authentication, group policy, trusts

Remote desktop to instance and add instance to domain

After configuring Route 53 for DNS routing:

Remote desktop to your RDS Custom for SQL Server. For instructions, refer to Connecting to your RDS Custom DB instance using RDP.
Verify connectivity to your domain. Run nslookup <domain_name> to verify it points to both IPs of your domain controller.

Note: If nslookup does not resolve correctly, joining the domain will FAIL.
Add the EC2 instance to the domain by running the following PowerShell command: Add-Computer -DomainName example.com -Credential example.com\Admin -Restart -Force. This command will prompt you to use the domain credential password.
After the RDS Custom instance is added to the domain, a post check is recommended. Run following PowerShell command: Test-NetConnection -ComputerName ssm.<awsregion>.amazonaws.com -Port 443 -InformationLevel Detailed
If the command returns True, then DNS resolution was setup correctly. If it returns False, then the RDS Custom for SQL Server instance will go out or perimeter.

For instructions on how to join to a self-hosted Active directory, refer to use a self-hosted Active Directory with Amazon RDS Custom for SQL Server. If the RDS Custom instance needs to be persisted in the Active Directory, then you can persist the member join by following the steps in Automate the configuration of Amazon RDS Custom for SQL Server using AWS Systems Manager.

After you join RDS Custom for SQL Server to an Active Directory, you can map your Amazon FSx file share. However, the mapping of the file share is not necessary because the restores will work using the Universal Naming Convention (UNC) path of the Amazon FSx drive, as shown later. You can map a file share on an EC2 Windows instance by following step-by-step instructions using the Windows File Explorer or the command prompt. For detailed steps on how to map a file share, see Mapping a file share on an Amazon EC2 Windows instance.

In our solution, we used a PowerShell command similar to the following to map a file share on an EC2 Windows instance:
```
net use H: \\<FSx DNS name>\share /persistent:yes
```
Enter your Microsoft AD credentials for the Amazon FSx service account, as shown in the following example.

Restore backup files on RDS Custom for SQL Server

Before you restore the backup files, verify the backup files exist on the Amazon FSx file share. Additionally, verify the permissions of the SQL Service account to the file share. Restore the files with the following code:

USE [master]
GO
RESTORE DATABASE [SampleTest] FROM 
DISK = N'Z:\SampleTest_FullBackupCompressed01.bak', 
DISK = N'Z:\SampleTest_FullBackupCompressed02.bak', 
DISK = N'Z:\SampleTest_FullBackupCompressed03.bak', 
DISK = N'Z:\SampleTest_FullBackupCompressed04.bak' 
WITH FILE = 1, 
          MOVE N'SampleTest_Data001' TO N'D:\rdsdbdata\DATA\SampleTest_Data001.mdf', 
          MOVE N'SampleTest_Data002' TO N'D:\rdsdbdata\DATA\SampleTest_Data002.ndf',  
          MOVE N'SampleTest_Data003' TO N'D:\rdsdbdata\DATA\SampleTest_Data003.ndf', 
          MOVE N'SampleTest_Data004' TO N'D:\rdsdbdata\DATA\SampleTest_Data004.ndf', 
          MOVE N'SampleTest_Data005' TO N'D:\rdsdbdata\DATA\SampleTest_Data005.ndf', 
          MOVE N'SampleTest_Data006' TO N'D:\rdsdbdata\DATA\SampleTest_Data006.ndf',  
          MOVE N'SampleTest_Data007' TO N'D:\rdsdbdata\DATA\SampleTest_Data007.ndf', 
          MOVE N'SampleTest_Data008' TO N'D:\rdsdbdata\DATA\SampleTest_Data008.ndf', 
          MOVE N'SampleTest_Data009' TO N'D:\rdsdbdata\DATA\SampleTest_Data009.ndf', 
          MOVE N'SampleTest_Data010' TO N'D:\rdsdbdata\DATA\SampleTest_Data010.ndf', 
          MOVE N'SampleTest_Data011' TO N'D:\rdsdbdata\DATA\SampleTest_Data011.ndf',  
          MOVE N'SampleTest_Data012' TO N'D:\rdsdbdata\DATA\SampleTest_Data012.ndf',  
          MOVE N'SampleTest_Data013' TO N'D:\rdsdbdata\DATA\SampleTest_Data013.ndf', 
          MOVE N'SampleTest_Data014' TO N'D:\rdsdbdata\DATA\SampleTest_Data014.ndf', 
          MOVE N'SampleTest_Data015' TO N'D:\rdsdbdata\DATA\SampleTest_Data015.ndf', 
          MOVE N'SampleTest_Data016' TO N'D:\rdsdbdata\DATA\SampleTest_Data016.ndf', 
          MOVE N'SampleTest_Log01' TO N'D:\rdsdbdata\DATA\SampleTest_Log01.ldf',  
          NOUNLOAD, STATS = 5
GO

When restoring (or backing up) from a mapped drive, you might get an error (for details, see Back up to a network share file).

If that is the case, instead of using the drive letter, specify the UNC as shown in the following code:

USE [master]
GO
RESTORE DATABASE [SampleTest] FROM 
DISK = N'\\<FSx DSN name>\share\Backup\SampleTest_FullBackupCompressed01.bak', 
DISK = N'\\<FSx DSN name>\share\Backup\SampleTest_FullBackupCompressed02.bak', 
DISK = N'\\<FSx DSN name>\share\Backup\SampleTest_FullBackupCompressed03.bak', 
DISK = N'\\<FSx DSN name>\share\Backup\SampleTest_FullBackupCompressed04.bak' 
WITH FILE = 1, 
          MOVE N'SampleTest_Data001' TO N'D:\rdsdbdata\DATA\SampleTest_Data001.mdf', 
          MOVE N'SampleTest_Data002' TO N'D:\rdsdbdata\DATA\SampleTest_Data002.ndf',  
          MOVE N'SampleTest_Data003' TO N'D:\rdsdbdata\DATA\SampleTest_Data003.ndf', 
          MOVE N'SampleTest_Data004' TO N'D:\rdsdbdata\DATA\SampleTest_Data004.ndf', 
          MOVE N'SampleTest_Data005' TO N'D:\rdsdbdata\DATA\SampleTest_Data005.ndf', 
          MOVE N'SampleTest_Data006' TO N'D:\rdsdbdata\DATA\SampleTest_Data006.ndf',  
          MOVE N'SampleTest_Data007' TO N'D:\rdsdbdata\DATA\SampleTest_Data007.ndf', 
          MOVE N'SampleTest_Data008' TO N'D:\rdsdbdata\DATA\SampleTest_Data008.ndf', 
          MOVE N'SampleTest_Data009' TO N'D:\rdsdbdata\DATA\SampleTest_Data009.ndf', 
          MOVE N'SampleTest_Data010' TO N'D:\rdsdbdata\DATA\SampleTest_Data010.ndf', 
          MOVE N'SampleTest_Data011' TO N'D:\rdsdbdata\DATA\SampleTest_Data011.ndf',  
          MOVE N'SampleTest_Data012' TO N'D:\rdsdbdata\DATA\SampleTest_Data012.ndf',  
          MOVE N'SampleTest_Data013' TO N'D:\rdsdbdata\DATA\SampleTest_Data013.ndf', 
          MOVE N'SampleTest_Data014' TO N'D:\rdsdbdata\DATA\SampleTest_Data014.ndf', 
          MOVE N'SampleTest_Data015' TO N'D:\rdsdbdata\DATA\SampleTest_Data015.ndf', 
          MOVE N'SampleTest_Data016' TO N'D:\rdsdbdata\DATA\SampleTest_Data016.ndf', 
          MOVE N'SampleTest_Log01' TO N'D:\rdsdbdata\DATA\SampleTest_Log01.ldf',  
          NOUNLOAD, STATS = 5
GO

Furthermore, using advanced backup options (BLOCKSIZE, MAXTRANSFERSIZE, BUFFERCOUNT) can considerably boost backup and restoration throughput.

Clean up

After the database is restored, if you don’t intend to use the FSX File Gateway and FSX for Windows File Server, make sure to delete them to reduce cost. Complete the following steps:

On the Amazon FSx console, choose the name of the file system you created for this exercise.
On the Actions menu, choose Delete file system.
Decide whether you want to create a final backup. If you do, provide a name for the final backup.
Any automatic backups are also deleted.
You can create new file systems from backups. We recommend that you create a final backup as a best practice. If you find that you don’t need it after a certain period of time, you can delete this and other manually created backups.
For File system ID, enter the ID of the file system that you want to delete.
Choose Delete file system.
The file system is now being deleted, and its status changes to DELETING. When the file system has been deleted, it no longer appears in the dashboard.
Now you can delete any manually created backups for your file system.
In the navigation pane, choose Backups.
Select any backups that have the same file system ID as the file system that you deleted, and choose Delete backup.
Leave the check box selected for the ID of the backup you selected, and choose Delete backups.

Your Amazon FSx file system and related automatic backups are now deleted. For more information, refer to Deleting Your Gateway by Using the AWS Storage Gateway Console and Removing Associated Resources.

If the RDS Custom instance doesn’t need to be a member of the Active Directory, then you can remove it following the steps in Remove-ADComputer.

Summary

In this post, we demonstrated how to successfully migrate a database when the total size of the database and backup exceeds 16 TB by using FSX for Windows File Server. This method also lets you avoid overprovisioning storage for RDS Custom for SQL Server while transferring very large databases, thereby reducing costs.

If you have any questions or comments, leave them in the comments section. To learn more about RDS Custom for SQL Server, see Working with Amazon RDS Custom.

About the Authors

Jose Amado-Blanco is a Sr. Consultant on Database Migration with over 25 years of experience working with AWS Professional Services. He helps customers on their journey to migrate and modernize their database solutions from on premises to AWS.

Priya Nair is a Sr. Database Consultant at AWS. She has over 18 years of experience working with different database technologies. She works as a database migration specialist to help Amazon customers move their on-premises database environments to AWS Cloud database solutions.

Suprith Krishnappa C is a Database Consultant with the Professional Services team at Amazon Web Services. He works with enterprise customers, offering technical support and designing customer solutions on database projects, as well as assisting them in migrating and modernizing their existing databases to the AWS Cloud.

AWS Database Blog