What are the options for migrating MS SQL Server to AWS?
Let’s assume that you have a SQL Server workload on-premises and want to take those to the cloud.
You have two options for MS SQL Server migration to AWS.
Option 1: Self-Managed on Amazon EC2
- Installation of SQL Server on Amazon EC2 (Elastic Compute Cloud).
- Benefits: Provides a virtual machine where you can install MS SQL Server tailored to your specific business requirements. It also gives you complete control over every setting, just like when it’s installed on-premises.
Option 2: Managed Service on Amazon RDS
- Running SQL Server on Amazon RDS (Relational Database Service).
- Benefits: a fully managed service handling all maintenance, backups, and patching.
SQL Server on Amazon EC2
Using Amazon EC2 is like running a SQL Server Database on-premises.
You have full control over the operating system, database installation, and configuration.
You are responsible for administering the database, patching the operating system and the database, and tuning the operating system and database parameters.
Additionally, you manage security, backups, recovery, and configure high availability or replication across your instances in the same or different regions
With EC2, you can quickly provision and configure DB instances and storage, and you can scale your instances by changing the size of your instances or the amount of storage.
You can provision your databases in AWS Regions across the world to provide low latency to your end users worldwide.
Running your relational database on Amazon EC2 is the ideal scenario if you require a maximum level of control and configurability.
You can also use SQL Server services and features that are not available in Amazon RDS.
Amazon RDS for SQL Server
On the other hand, Amazon RDS is a fully managed service that makes it easier to set up, operate, and scale a relational database in the cloud.
RDS automates the installation, disk provisioning, and management, patching, minor and major version upgrades, backup and recovery of your SQL Server databases.
It also offers automated Multi-AZ (Availability Zone) synchronous replication, allowing you to set up a highly available and scalable environment fully managed by AWS.
It is offered with multiple database engines (PostgreSQL, Oracle, MySQL, MariaDB, SQL Server, etc.). This managed service has some guardrails in place.
For example, you don’t have:
- Administrator access to the instance running SQL.
- Access to the filesystem.
- The ability to Remote Desktop Protocol (RDP) to the server running SQL, etc.
Additionally, some features are not supported by SQL Server on AWS RDS.
Here, you can see the ever-shrinking list.
Choosing between Microsoft SQL Server solutions on AWS
For SQL Server Databases, both Amazon RDS and Amazon EC2 have advantages and certain limitations.
Using RDS lets you focus on more important tasks, rather than the day-to-day administration of SQL Server and the underlying software stack. Also, it can be more cost-effective than Amazon EC2.
Alternatively, Amazon EC2 gives you flexibility, more control, and choice.
In general, consider Amazon RDS first.
Based on your answers to the considerations preceding, Amazon RDS might be a better choice than Amazon EC2 if:
- You want to focus on high-level tasks, such as performance tuning and schema optimization, and outsource the following tasks to Amazon:
- management of security patches
- upgrades of minor SQL Server versions
- provisioning of the database
- management of backup and recovery
- storage management.
- You need a highly available database solution and want to take advantage of the push-button, synchronous Multi-AZ replication without having to manually set up and maintain database mirroring, AlwaysOn, availability groups, or failover clusters.
However, running SQL Server on AWS EC2 might be the better choice if the following is true:
- Full control needed: If you need complete control over your DB instances, including the operating system and the software stack.
- Host access required: If you need host access for specific tasks like SQL Server Integration Services (SSIS) packages or bulk insert operations.
- Third-party management: If you prefer using third-party solutions or leveraging your experienced database administrators for managing the databases, including tasks like backups, replication, and clustering.
- Exceeding limits: Your database size and performance requirements exceed the current maximums or other limits of Amazon RDS or need to use SQL Server features not currently supported by Amazon RDS.
Assessment and planning (best practices)
There are a lot of things that you need to correctly understand to make a successful SQL migration to AWS.
Inventory all SQL Server dependencies
One of the first steps would be to inventory all your SQL Server dependencies.
Make sure that you know exactly which web servers are interfacing with your SQL Server, know that maybe there is a reporting service server running somewhere.
Know who is interacting with your systems.
So, when it comes time for you to migrate to AWS, you will know exactly who’s going to be impacted and you can minimize the impact on these entities.
Identify SQL versions or edition features
Identify SQL versions or edition features currently used.
This may be an opportunity for you to reduce your licensing costs.
Understanding high availability and disaster recovery requirements, as well as authentication requirements (SQL vs. Windows authentication), will help you decide which route to go: RDS or EC2.
Know your licensing options
Perhaps you can bring your own SQL license, depending on the licensing agreement you have with Microsoft.
The options may vary, so understanding it thoroughly can save you a considerable amount of money.
SQL Server performance requirements
Considering performance requirements is important, especially if you want to show your stakeholders that the migration was a “Success”.
It means you should establish a baseline for your existing environment by obtaining performance reference points.
This way, after the migration, you can demonstrate improvements in performance by X and availability by Y.
Leverage your retention policy
If you can leverage your retention policy, then we highly recommend that you do.
For example, assume that you have 10 years of data in an SQL Server environment but your policy dictates you to keep only five years.
Perhaps you should archive those five years that you don’t need into something like Amazon S3, which is cloud object storage service that is secure, persistent and cost-effective.
If you do it before the migration that would reduce your migration time and afterwards that could considerably save some money on performance costs.
Acknowledge internal capabilities
When it comes to SQL migration time, the last thing would be to acknowledge your internal capabilities.
What does that mean?
Let’s assume that your team is very tenured, they have the expertise in-house but maybe they don’t have the time or expertise to execute a migration.
Hybrid Scenarios and Data Migration
Some AWS customers already have SQL Server running on-premises but want to use the AWS Cloud to enhance their architecture to provide a more highly available solution.
Other customers are looking to migrate workloads to AWS without incurring significant downtime.
AWS provides various services and an AWS SQL Migration tool to assist customers in these use cases.
Additionally, SQL Server offers several replication technologies that provide high availability and disaster recovery solutions.
These features differ depending on the SQL Server version and edition.
AWS storage solutions allow you to pay for only what you need.
You get the benefits without the upfront investment and hassle of setting up and maintaining an on-premises system.
Amazon Simple Storage Service (Amazon S3)
S3 gives you the ability to back up SQL Server Databases to a highly secure, available, durable, reliable storage solution.
Using Amazon S3, you can take advantage of the flexibility and pricing of cloud storage.
Many third-party backup solutions are designed to securely store SQL Server backups in Amazon S3.
Also, you can design and develop an SQL Server backup solution yourself by using AWS tools like the AWS CLI, AWS Tools for Windows PowerShell, or a wide variety of SDKs for .NET or Java, and the AWS Toolkit for Visual Studio.
AWS Storage Gateway
AWS Storage Gateway enables your existing on-premises to cloud backup applications to store primary backups on Amazon S3’s scalable, reliable, secure, and cost-effective storage service.
It is a service connecting an on-premises software appliance with cloud-based storage to provide seamless and secure integration between an organization’s on-premises IT environment and AWS’s storage infrastructure.
AWS Storage Gateway supports industry-standard storage protocols that work with your existing applications.
It provides low-latency performance by maintaining frequently accessed data on-premises while securely storing all your data encrypted in Amazon S3.
AWS Snowball Edge
Snowball is a service and a physical storage appliance that enables you to move petabytes of data to AWS.
It helps eliminate challenges that you can encounter with large-scale data transfers, including high network costs, long transfer times, and security concerns.
You can order a book or a Crock-Pot on Amazon Prime, knowing it will show up at your door two days later.
Similarly, you can order several AWS Snowball Edge appliances from your AWS Management Console.
Here’s how to use them:
- Once the appliance arrives, connect it to your local network.
- Download the Snowball Client (referred to as the “Client”) and run it to establish a connection with the device.
- Use the Client to choose the file directories you wish to transfer to the device.
- The Client will encrypt your files and transfer them to the device quickly.
- After completing the transfer, the device’s E Ink shipping label will automatically update, indicating it’s ready to be returned.
- You can monitor the job status through Amazon Simple Notification Service (SNS), text messages, or directly in the Console.
AWS Database Migration Service (AWS DMS)
You can begin a database migration with just a few clicks in the AWS Management Console.
It helps you migrate databases easily and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database.
Once the migration has started, AWS manages many of the complexities of the migration process like data type transformation, compression, and parallel transfer (for faster data transfer).
It also ensures any changes made to the source database during the migration are automatically updated in the target database.
Database Migration Service (DMS) is intended to support migrations to and from AWS-hosted databases, where both the source and destination engine are the same and heterogeneous data sources.
Native SQL Server migration methods
Amazon RDS also supports native migration methods.
These approaches extend your existing on-premises high-availability solution and provide a disaster recovery solution with AWS by leveraging native SQL Server features such as:
- Log shipping
- Database mirroring (Enterprise version)
- Always On availability groups (SQL Server 2014 or newer)
- Distributed availability groups (SQL Server 2016 or newer)
- Transaction replication
Conclusion
As you can see, there are several approaches available for migrating your SQL Server workload to AWS.
