# Homogeneous database migration for SQL Server Homogeneous database migration AWS offers you the ability to run SQL Server databases in a cloud environment. For developers and database administrators, running SQL Server database in the AWS Cloud is very similar to running SQL Server database in a data center. This section describes options for migrating your SQL Server database from an on-premises environment or a data center to the AWS Cloud. AWS offers three options for running SQL Server on AWS, as described in the following table. **** | Option | Highlights | More information | | --- | --- | --- | | SQL Server on Amazon RDS | Managed service, provides easy provisioning and licensing, cost-effective, easy to set up, manage, and maintain. | [Amazon RDS for SQL Server](rds-sql.md) section | | SQL Server on Amazon RDS Custom | Managed service, but you retain administrative rights to the database and the underlying operating system. | [Amazon RDS Custom for SQL Server](rds-custom-sql.md) section | | SQL Server on Amazon EC2 | Self-managed, provides full control and flexibility. | [Amazon EC2 for SQL Server](ec2-sql.md) section | | SQL Server on VMware Cloud on AWS | Set up, scale, and operate your SQL Server workloads on VMware Cloud on AWS and integrate with Directory Service, Active Directory Connector, and Amazon S3. | [VMware Cloud on AWS for SQL Server](vmware-sql.md) section | **Notice** As of April 30, 2024, VMware Cloud on AWS is no longer resold by AWS or its channel partners. The service will continue to be available through Broadcom. We encourage you to reach out to your AWS representative for details. Your application requirements, database features, functionality, growth capacity, and overall architecture complexity will determine which option to choose. If you are migrating multiple SQL Server databases to AWS, some of them might be a great fit for Amazon RDS, whereas others might be better suited to run directly on Amazon EC2. You might have databases that are running on SQL Server Enterprise edition but are a good fit for SQL Server Standard edition. You might also want to modernize your SQL Server database running on Windows to run on a Linux operating system to save on cost and licenses. Many AWS customers run multiple SQL Server database workloads across Amazon RDS, Amazon EC2, and VMware Cloud on AWS. **Note** You can use Migration Hub Orchestrator to automate and orchestrate your SQL Server database migrations to Amazon EC2 or Amazon RDS by using native backup and restore. For more information, see the [AWS Migration Hub Orchestrator section](mho.md). # Amazon RDS for SQL Server Amazon RDS for SQL Server is a managed database service that simplifies the provisioning and management of SQL Server on AWS. Amazon RDS makes it easy to set up, operate, and scale SQL Server deployments in the cloud. With Amazon RDS, you can deploy multiple versions of SQL Server (2014, 2016, 2017, 2019, and 2022) and editions (including Express, Web, Standard and Enterprise) in minutes, with cost-efficient and resizable compute capacity. You can provision Amazon RDS for SQL Server DB instances with either General Purpose SSD or Provisioned IOPS SSD storage. (For details, see [Amazon RDS Storage Types](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html#Concepts.Storage) in the AWS documentation.) Provisioned IOPS SSD is designed to deliver fast, predictable, and consistent I/O performance, and is optimized for I/O-intensive, transactional (OLTP) database workloads. Amazon RDS frees you to focus on application development, because it manages time-consuming database administration tasks, including provisioning, backups, software patching, monitoring, and hardware scaling. Amazon RDS for SQL Server also offers Multi-AZ deployments and read replicas (for SQL Server Enterprise edition) to provide high availability, performance, scalability, and reliability for production workloads. ## When to choose Amazon RDS Amazon RDS for SQL Server is a migration option when: + You want to focus on your business and applications, and you want AWS to take care of undifferentiated heavy lifting tasks such as the provisioning of the database, management of backup and recovery tasks, management of security patches, minor SQL Server version upgrades, and storage management. + You need a highly available database solution, and you want to take advantage of the push-button, synchronous Multi-AZ replication offered by Amazon RDS, without having to manually set up and maintain database mirroring, failover clusters, or Always On availability groups. + You want to pay for the SQL Server license as part of the instance cost on an hourly basis instead of making a large, upfront investment. + Your database size and IOPS needs are supported by Amazon RDS for SQL Server. See [Amazon RDS DB Instance Storage](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html) in the AWS documentation for the current maximum limits. + You don’t want to manage backups or point-in-time recoveries of your database. + You want to focus on high-level tasks, such as performance tuning and schema optimization, instead of the daily administration of the database. + You want to scale the instance type up or down based on your workload patterns without being concerned about licensing complexities. After assessing your database and project requirements, if you decide to migrate to Amazon RDS for SQL Server, see the details provided in the following sections, and review the [migration best practices](best-practices.md) we discuss later in this guide. For currently supported SQL Server features, versions, and options, see [Amazon RDS for SQL Server features](https://aws.amazon.com/rds/sqlserver/features/) on the AWS website, [Choosing between Amazon EC2 and Amazon RDS](comparison.md) later in this guide, and [Microsoft SQL Server on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html) in the AWS documentation. If you’re moving to Amazon RDS Custom, make sure to review the [requirements and limitations for Amazon RDS Custom for SQL Server](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-reqs-limits-MS.html). ## High availability Amazon RDS provides high availability and failover support for databases that are deployed with the Multi-AZ option. When you provision your database with the Multi-AZ option, Amazon RDS automatically provisions and maintains a synchronous standby instance in a different Availability Zone. The primary database synchronously replicates the data to the standby instance. If problems occur, Amazon RDS automatically repairs the unhealthy instance and re-establishes synchronization. In case of infrastructure failure or Availability Zone disruption, Amazon RDS performs an automatic failover to the standby instance. Failover occurs only if the standby and primary databases are fully synchronized. Because the endpoint remains the same for the primary and standby instances, you can resume database operations as soon as the failover is complete, without performing a manual intervention. The failover time depends on the time it takes to complete the recovery process. Large transactions increase the failover time. The following diagram illustrates the Amazon RDS for SQL Server Multi-AZ deployment option. ![\[Amazon RDS for SQL Server in a Multi-AZ configuration\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/images/sql-migration-rds-ha.png) When you set up SQL Server in a Multi-AZ configuration, Amazon RDS automatically configures standby database instance using database mirroring or Always On availability groups, based on the version of SQL Server that you deploy. The specific SQL Server versions and editions are listed in the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerMultiAZ.html). In Multi-AZ deployments, operations such as instance scaling or system upgrades such as operating system (OS) patching are applied first on the standby instance, before the automatic failover of the primary instance, for enhanced availability. Because of failover optimization of SQL Server, certain workloads can generate greater I/O load on the standby instance than they do on the primary instance, particularly in database mirroring deployments. This functionality can result in higher IOPS on the standby instance. We recommend that you consider the maximum IOPS needs of both the primary and standby instances when you provision the storage type and IOPS of your Amazon RDS for SQL Server DB instance. You can also specify `MultiSubnetFailover=True`, if your client driver supports it, to significantly reduce the failover time. ### Limitations + The Multi-AZ option isn’t available for SQL Server Express and Web editions. It’s available only for SQL Server Standard and Enterprise editions. + You can't configure the standby DB instance to accept database read activity. + Cross-Region Multi-AZ isn’t supported. + In Amazon RDS you can issue a stop command to a standalone DB instance and keep the instance in a stopped state to avoid incurring compute charges. You can't stop an Amazon RDS for SQL Server DB instance in a Multi-AZ configuration. Instead, you can terminate the instance, take a final snapshot before termination, and recreate a new Amazon RDS instance from the snapshot when you need it. Or, you can remove the Multi-AZ configuration first and then stop the instance. After seven days, your stopped instance will restart so that any pending maintenance can be applied. For additional limitations, see [Microsoft SQL Server Multi-AZ deployment notes and recommendations](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerMultiAZ.html#USER_SQLServerMultiAZ.Recommendations) in the Amazon RDS documentation. ## Read replicas Read replicas provide scalability and load balancing. A SQL Server read replica is a physical copy of an Amazon RDS for SQL Server DB instance that is used for read-only purposes. Amazon RDS helps reduces the load on the primary DB instance by offloading read-only workloads to the read replica DB instance. Updates made to your primary DB instance are asynchronously copied to the read replica instance. When you request a read replica, Amazon RDS takes a snapshot of the source DB instance, and this snapshot becomes the read replica. There is no outage while creating and deleting a read replica. Amazon RDS for SQL Server upgrades the primary database immediately after upgrading the read replicas, regardless of the maintenance window. Every read replica comes with a separate endpoint that you use to connect to the read replica database. Amazon RDS for SQL Server makes it easy to create read replicas by configuring Always On availability groups, and maintaining secure network connections between a primary DB instance and its read replicas. You can set up a read replica in the same AWS Region as your primary database, or in another Region. You can create up to five read replicas for one source DB instance. **Note** Read replicas are available only with the following SQL Server versions and editions: SQL Server 2017 Enterprise edition 14.00.3049.1 or later SQL Server 2016 Enterprise edition 13.00.5216.0 or later SQL Server versions and editions that support database mirroring for Multi-AZ environments do not offer read replicas. The following diagram illustrates an Amazon RDS for SQL Server DB instance in a Multi-AZ environment with a read replica in another Availability Zone within the same AWS Region. Not all AWS Regions offer more than two Availability Zones, so you should [check the Region](https://aws.amazon.com/about-aws/global-infrastructure/regions_az/) you’re planning to use before adopting this strategy. ![\[Amazon RDS for SQL Server with a read replica in another Availability Zone in the same Region\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/images/sql-migration-rds-rr-same-region.png) A SQL Server read replica doesn't allow write operations. However, you can promote the read replica to make it writable. After you promote it, you cannot revert it back to a read replica. It will become a single, standalone DB instance that has no relationships with its original primary database instance. The data in the promoted read replica will match the data in the source DB instance up to the point when the request was made to promote it. The SQL Server DB engine version of the source DB instance and all of its read replicas will be the same. For efficient replication, we recommend the following: + Set up each read replica with the same compute and storage resources as the source DB instance. + You must enable automatic backups on the source DB instance by setting the backup retention period to a value other than 0 (zero). + The source DB instance must be deployed in a Multi-AZ environment with Always On availability groups. For SQL Server version support, editions, and limitations, see [Read replica limitations with SQL Server](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.ReadReplicas.html#SQLServer.ReadReplicas.Limitations) in the Amazon RDS documentation. For more information about using read replicas, see [Working with read replicas](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html) and [Working with SQL Server read replicas for Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.ReadReplicas.html) in the AWS documentation. For more information about data transfer pricing, see [Amazon RDS pricing](https://aws.amazon.com/rds/pricing/). ## Disaster recovery With Amazon RDS for SQL Server you can create a reliable, cross-Region disaster recovery (DR) strategy. The main reasons for creating a DR solution are business continuity and compliance: + An effective DR strategy helps you keep your systems up and running with minimal or no interruptions during a catastrophic event. A reliable and effective cross-Region DR strategy keeps your business in operation even if an entire Region goes offline. + A cross-Region DR solution helps you meet auditing and compliance requirements. Recovery point objective (RPO), recovery time objective (RTO), and cost are three key metrics to consider when developing your DR strategy. For other options for providing cross-Region replicas, see [AWS Marketplace](https://aws.amazon.com/marketplace/). For more information about these approaches, see [Cross-Region disaster recovery of Amazon RDS for SQL Server](https://aws.amazon.com/blogs/database/cross-region-disaster-recovery-of-amazon-rds-for-sql-server/) on the AWS Database blog. # Amazon RDS Custom for SQL Server If you’re unable to move to a fully managed service such as Amazon RDS because of customization requirements for third-party applications, you can migrate to Amazon RDS Custom for SQL Server. With Amazon RDS Custom, you can retain administrative rights to the database and its underlying operating system to enable dependent applications. ## When to choose Amazon RDS Custom for SQL Server Amazon RDS Custom for SQL Server is a good migration option when: + You have legacy, custom, and packaged applications that require access to the underlying operating system and database environment. + You need administrative user access to meet vendor-based application deployment requirements. + You need access to the underlying operating system to configure settings, install patches, and enable native features to meet the dependent application's requirements. + You want to access and customize the database environment (by applying custom database patches or modifying OS packages) to meet your database and application needs. ## How it works To use Amazon RDS Custom for SQL Server, review the [requirements](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-reqs-limits-MS.html#custom-reqs-limits.reqsMS) in the Amazon RDS Custom for SQL Server documentation. You must first set up your environment for Amazon RDS Custom for SQL Server, as explained in the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-setup-sqlserver.html). After the environment is set up, follow these steps, which are illustrated in the following diagram: 1. Create an Amazon RDS Custom for SQL Server DB instance from a SQL Server engine version offered by Amazon RDS Custom. Amazon RDS Custom for SQL Server currently supports SQL Server 2019 and SQL Server 2022 on Windows 2019 with the [supported DB instance classes](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-reqs-limits-MS.html#custom-reqs-limits.instancesMS) listed in the documentation. For more information, see [Creating an RDS Custom for SQL Server DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-creating-sqlserver.html#custom-creating-sqlserver.create). 1. Connect your application to the Amazon RDS Custom DB instance endpoint. For more information, see [Connecting to your RDS Custom DB instance using AWS Systems Manager](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-creating-sqlserver.html#custom-creating-sqlserver.ssm) and [Connecting to your RDS Custom DB instance using RDP](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-creating-sqlserver.html#custom-creating-sqlserver.rdp). 1. (Optional) Access the host to customize your software. 1. Monitor notifications and messages generated by Amazon RDS Custom automation. For more information about these steps, see the [Amazon RDS Custom documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-sqlserver.workflow.html). ![\[Amazon RDS Custom for SQL Server workflow\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/images/custom-rds-sql-server.png) Amazon RDS Custom is a managed database service that automates the setup, operation, and scaling of databases in the cloud while granting you access to the underlying operating system and database environment. In Amazon RDS Custom for SQL Server, you can install software to run custom applications and agents. Because you have privileged access to the host, you can modify file systems to support legacy applications. You can also apply custom database patches or modify OS packages on your Amazon RDS Custom DB instances. If you want to customize your instance, you can pause Amazon RDS Custom automation for up to 24 hours and then resume it when your customization work is complete. Pausing the automation prevents Amazon RDS automation from directly interfering with your customizations. When you resume automation, the [support perimeter](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-troubleshooting.html#custom-troubleshooting.support-perimeter) determines whether your customization of the database or operating system environment interferes with, or breaks, Amazon RDS Custom automation. Amazon RDS Custom supports your customization of the host and database environment as long as your changes don’t put the DB instance outside the support perimeter. The support perimeter checks are performed every 30 minutes by default, and also occur after events such as snapshot deletions or uninstalling the Amazon RDS Custom agent, which monitors the DB instance. The Amazon RDS Custom agent is a critical component for ensuring Amazon RDS Custom functionality. If you uninstall the agent, Amazon RDS Custom runs the support perimeter check after one minute and moves the DB instance outside the support perimeter. When you set up an Amazon RDS Custom for SQL Server DB instance, the software license is included. That is, you don’t need to purchase SQL Server licenses separately. For more information about licensing, see section 10.5 in [AWS service terms](https://aws.amazon.com/service-terms/). If you have an active AWS Premium Support account, you can contact AWS Premium Support for Amazon RDS Custom for SQL Server-specific issues. Amazon RDS Custom for SQL Server is supported in a limited selection of AWS Regions and with limited DB instance classes. For these and other limitations, see the [requirements and limitations](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-reqs-limits-MS.html) page in the Amazon RDS Custom for SQL Server documentation. If you have an on-premises SQL Server database, you can follow the process described in the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-migrating.html) to migrate it to Amazon RDS Custom for SQL Server by using native backup and restore utilities. For additional information, see the following resources: + [New – Amazon RDS Custom for SQL Server Is Generally Available](https://aws.amazon.com/blogs/aws/new-amazon-rds-custom-for-sql-server-is-generally-available/) (AWS News blog) + [Configure SQL Server replication between Amazon RDS Custom for SQL Server and Amazon RDS for SQL Server](https://aws.amazon.com/blogs/database/configure-sql-server-replication-between-amazon-rds-custom-for-sql-server-and-amazon-rds-for-sql-server/) (AWS Database blog) + [Automate on-premises or Amazon EC2 SQL Server to Amazon RDS for SQL Server migration using custom log shipping](https://aws.amazon.com/blogs/database/automate-on-premises-or-amazon-ec2-sql-server-to-amazon-rds-for-sql-server-migration-using-custom-log-shipping/) (AWS Database blog) + [Configure high availability with Always On Availability Groups on Amazon RDS Custom for SQL Server](https://aws.amazon.com/blogs/database/configure-high-availability-with-always-on-availability-groups-on-amazon-rds-custom-for-sql-server/) (AWS Database blog) + [Get started with Amazon RDS Custom for SQL Server using an CloudFormation template (Network setup)](https://aws.amazon.com/blogs/database/get-started-with-amazon-rds-custom-for-sql-server-using-an-aws-cloudformation-template-network-setup/) (AWS Database blog) + [Migrate on-premises SQL Server workloads to Amazon RDS Custom for SQL Server using distributed availability groups](https://aws.amazon.com/blogs/database/migrate-on-premises-sql-server-workloads-to-amazon-rds-custom-for-sql-server-using-distributed-availability-groups/) (AWS Database blog) + [Optimize your SQL Server costs by using bring your own media (BYOM) on Amazon RDS Custom for SQL Server](https://aws.amazon.com/blogs/database/optimize-your-sql-server-costs-by-using-bring-your-own-media-byom-on-amazon-rds-custom-for-sql-server/) (AWS Database blog) # Amazon EC2 for SQL Server Amazon EC2 supports a self-managed SQL Server database. That is, it gives you full control over the setup of the infrastructure and the database environment. Running the database on Amazon EC2 is very similar to running the database on your own server. You have full control of the database and operating system-level access, so you can use your choice of tools to manage the operating system, database software, patches, data replication, backup, and restoration. This migration option requires you to set up, configure, manage, and tune all the components, including EC2 instances, storage volumes, scalability, networking, and security, based on AWS architecture best practices. You are responsible for data replication and recovery across your instances in the same or different AWS Regions. ## When to choose Amazon EC2 Amazon EC2 is a good migration option for your SQL Server database when: + You need full control over the database and access to its underlying operating system, database installation, and configuration. + You want to administer your database, including backups and recovery, patching the operating system and the database, tuning the operating system and database parameters, managing security, and configuring high availability or replication. + You want to use features and options that aren’t currently supported by Amazon RDS. For details, see [Features not supported and features with limited support](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.FeatureNonSupport) in the Amazon RDS documentation. + You need a specific SQL Server version that isn’t supported by Amazon RDS. For a list of supported versions and editions, see [SQL Server versions on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.VersionSupport) in the Amazon RDS documentation. + Your database size and performance needs exceed the current Amazon RDS for SQL Server offerings. For details, see [Amazon RDS DB instance storage](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html) in the Amazon RDS documentation. + You want to avoid automatic software patches that might not be compliant with your applications. + You want to bring your own license instead of using the Amazon RDS for SQL Server license-included model. + You want to achieve higher IOPS and storage capacity than the current limits. For details, see [Amazon RDS DB instance storage](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html) in the Amazon RDS documentation. For a list of currently supported SQL Server features and versions on Amazon EC2, see [Choosing between Amazon EC2 and Amazon RDS](comparison.md) later in this guide. # High availability You can use any SQL Server-supported replication technology with your SQL Server database on Amazon EC2 to achieve high availability, data protection, and disaster recovery. Some of the common solutions are log shipping, database mirroring, Always On availability groups, and Always On Failover Cluster Instances. The following diagram shows how you can use SQL Server on Amazon EC2 across multiple Availability Zones within a single AWS Region. The primary database is a read-write database, and the secondary database is configured with log shipping, database mirroring, or Always On availability groups for high availability. All the transaction data from the primary database is transferred and can be applied to the secondary database asynchronously for log shipping, and asynchronously for Always On availability groups and mirroring. ![\[SQL Server on Amazon EC2 in a Multi-AZ configuration in one AWS Region\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/images/sql-migration-ec2.png) # Log shipping Log shipping lets you automatically send transaction log backups from a primary database instance to one or more secondary databases (also known as *warm standby*) on separate DB instances. Log shipping uses SQL Server Agent jobs to automate the process of backing up, copying, and applying the transaction log backups. Although log shipping is typically considered a disaster recovery feature, it can also provide high availability by allowing secondary DB instances to be promoted if the primary DB instance fails. If your RTO and RPO are flexible, or your databases aren’t considered highly mission-critical, consider using log shipping to provide better availability for your SQL Server databases. Log shipping increases the availability of databases by providing access to secondary databases to use as read-only copies of the primary database when needed. You can configure a lag delay (a longer delay time) during which you can recover accidentally changed data on the primary database before these changes are shipped to the secondary database. We recommend running the primary and secondary DB instances in separate Availability Zones, and deploying a monitor instance to track all the details of log shipping. Backup, copy, restore, and failure events for a log shipping group are available from the monitor instance. A log shipping configuration doesn’t automatically fail over from the primary server to the secondary server. However, any of the secondary databases can be brought online manually if the primary database becomes unavailable. Log shipping is often used as a disaster recovery solution but also can be used as a high availability solution, depending on your application requirements. Use log shipping when: + You have flexible RTO and RPO requirements. Log shipping provides an RPO of minutes, and an RTO of minutes to hours. + You do not need an automatic failover to the secondary database. + You want to read from the secondary database, but you don’t require readability during a restore operation. For more information about log shipping, see the [Microsoft SQL Server documentation](https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server). # Database mirroring Database mirroring takes a database that's on an EC2 instance and provides a complete or almost complete read-only copy (mirror) of it on a separate DB instance. Amazon RDS uses database mirroring to provide Multi-AZ support for Amazon RDS for SQL Server. This feature increases the availability and protection of databases, and provides a mechanism to keep databases available during upgrades. **Note** According to the [Microsoft documentation](https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server), database mirroring will be removed in a future version of SQL Server. You should plan to use Always On availability groups instead. In database mirroring, SQL servers can take one of three roles: + The principal server, which hosts the primary read/write version of the database. + The mirror server, which hosts a copy of the principal database. + An optional witness server. This server is available only in high-safety mode. It monitors the state of the database mirror and automates the failover from the primary database to the mirror database. A mirroring session is established between the principal and mirror servers. During mirroring, all database changes that are performed in the principal database are also performed on the mirror database. Database mirroring can be either a synchronous or an asynchronous operation. This is determined by two mirroring operating modes: high-safety mode and high-performance mode. + **High-safety mode:** This mode uses synchronous operations. In this mode, the database mirroring session synchronizes the insert, update, and delete operations from the principal database to the mirror database as quickly as possible. As soon as the database is synchronized, the transaction is committed in both the principal and the mirror databases. We recommend that you use this operating mode when the mirror databases are in the same or different Availability Zones, but hosted within the same AWS Region. + **High-performance mode:** This mode uses asynchronous operations. In this mode, the database mirroring session synchronizes the insert, update, and delete operations from the principal database to the mirror database, but there can be a lag between the time the principal database commits transactions and the time the mirror database commits transactions. We recommend that you use this mode when the mirror databases are in different AWS Regions. Use database mirroring when: + You have strict RTO and RPO requirements, and cannot have delays between the primary and secondary databases. Database mirroring provides an RPO of zero seconds (with synchronous commit) and an RTO of seconds to minutes. + You do not have a requirement to read from the secondary database. + You want to perform automatic failover when you have a witness server configured in synchronization mode. + You cannot use Always On availability groups, which is the preferred option. Limitations: + Only one-to-one failover is supported. You cannot have multiple database destinations sync with the primary database. For more information about mirroring, see the [Microsoft SQL Server documentation](https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server). # Always On availability groups SQL Server Always On availability groups provide high availability and disaster recovery solutions for SQL Server databases. An availability group consists of a set of user databases that fail over together. It includes a single set of primary read/write databases and multiple (one to eight) sets of related, secondary databases. You can make the secondary databases available to the application tier as read-only copies of the primary databases (SQL Server Enterprise edition only), to provide a scale-out architecture for read workloads. You can also use the secondary databases for backup operations. SQL Server Always On availability groups support both synchronous and asynchronous commit modes. In synchronous mode, the primary replica commits database transactions after the changes are committed or written to the log of the secondary replica. Using this mode, you can perform planned manual failover and automatic failover if the replicas are in sync. You can use synchronous commit mode between SQL Server instances within the same environment (for example, if all instances are on-premises or all instances are in AWS). In asynchronous commit mode, the primary replica commits database transactions without waiting for the secondary replica. You can use asynchronous commit mode between SQL Server instances that are in different environments (for example, if you have instances on premises and in AWS). You can use Always On availability groups for high availability or disaster recovery. Use this method when: + You have strict RTO and RPO requirements. Always On availability groups provide an RPO of seconds, and an RTO of seconds to minutes. + You want to manage and fail over a group of databases. Always On availability groups support 0-4 secondary replicas in synchronous commit mode for SQL Server 2019. + You want to use automatic failover in synchronous commit mode, and you don’t need a witness server. + You want to read from the secondary database. + You want to synchronize multiple database destinations with your primary database. Starting with SQL Server 2016 SP1, SQL Server Standard edition provides basic high availability for a single, non-readable secondary database and listener per availability group. It also supports a maximum of two nodes per availability group. # Always On Failover Cluster Instances SQL Server Always On Failover Cluster Instances (FCIs) use Windows Server Failover Clustering (WSFC) to provide high availability at the server instance level. An FCI is a single instance of SQL Server that is installed across WSFC nodes to provide high availability for the entire installation of SQL Server. If the underlying node experiences hardware, operating system, application, or service failures, everything inside the SQL Server instance is moved to another WSFC node. This includes system databases, SQL Server logins, SQL Server Agent jobs, and certificates. An FCI is generally preferable over an Always On availability group when: + You’re using SQL Server Standard edition instead of Enterprise edition. + You have a large number of small databases per instance. + You’re constantly modifying instance-level objects such as SQL Server Agent jobs, logins, and so on. There are four options for deploying FCIs on AWS: + Amazon EBS Multi-Attach with persistent reservations + Amazon FSx for Windows File Server + Amazon FSx for NetApp ONTAP + Solutions from AWS Partners ## Using Amazon EBS Multi-Attach with persistent reservations [Amazon EBS Multi-Attach with NVMe reservations](https://docs.aws.amazon.com/ebs/latest/userguide/nvme-reservations.html) supports the creation of SQL Server FCIs with Amazon EBS `io2` volumes as the shared storage on Windows Server failover clusters. This feature simplifies the failover cluster setup process by enabling you to build a failover cluster by using Amazon EBS `io2` volumes. These volumes can be attached only to instances that are in the same Availability Zone. To deploy Windows Server failover clusters by using Amazon EBS `io2` volumes, you must use the latest AWS NVMe drivers. Amazon EBS volumes and instance store volumes are exposed as NVMe block devices on [Nitro-based instances](https://docs.aws.amazon.com/AWSEC2/latest/WindowsGuide/instance-types.html#ec2-nitro-instances). You must have the [AWS NVMe driver](https://docs.aws.amazon.com/AWSEC2/latest/WindowsGuide/aws-nvme-drivers.html) installed with the [SCSI persistent reservation feature](https://docs.aws.amazon.com/AWSEC2/latest/WindowsGuide/aws-nvme-drivers.html#configure-scsi-persistent-reservations) configured when you use Amazon EBS `io2` volumes to form WSFC and SQL Server FCIs. For more information about this feature, see the AWS blog post [How to deploy a SQL Server failover cluster with Amazon EBS Multi-Attach on Windows Server](https://aws.amazon.com/blogs/modernizing-with-aws/how-to-deploy-a-sql-server-failover-cluster-with-amazon-ebs-multi-attach-on-windows-server/). ## Using Amazon FSx for Windows File Server [Amazon FSx for Windows File Server](https://docs.aws.amazon.com/fsx/latest/WindowsGuide/what-is.html) provides fully managed shared file storage. It automatically replicates the storage synchronously across two Availability Zones to provide high availability. Using FSx for Windows File Server for file storage helps simplify and optimize SQL Server high availability deployments on Amazon EC2. With Microsoft SQL Server, high availability is typically deployed across multiple database nodes in an WSFC, and each node has access to shared file storage. You can use FSx for Windows File Server as shared storage for SQL Server high availability deployments in two ways: as storage for active data files and as an SMB file share witness. For information about how you can reduce the complexity and cost of running SQL Server FCI deployments by using FSx for Windows File Server, see the blog post [Simplify your Microsoft SQL Server high availability deployments using Amazon FSx for Windows File Server](https://aws.amazon.com/blogs/storage/simplify-your-microsoft-sql-server-high-availability-deployments-using-amazon-fsx-for-windows-file-server/). The blog post also provides step-by-step instructions for deploying SQL Server FCIs by using an Amazon FSx Multi-AZ file system as the shared storage solution. For more information, see the [Amazon FSx for Windows File Server](https://docs.aws.amazon.com/fsx/latest/WindowsGuide/what-is.html) documentation. ## Using Amazon FSx for NetApp ONTAP Amazon FSx for NetApp ONTAP is a fully managed service that provides highly reliable, scalable, high-performing, and feature-rich file storage that's built on the NetApp ONTAP file system. FSx for ONTAP combines the familiar features, performance, capabilities, and API operations of NetApp file systems with the agility, scalability, and simplicity of a fully managed AWS service. FSx for ONTAP provides multi-protocol access to data over the NFS, SMB, and iSCSI protocols for Windows and Linux systems. You can build a highly available SQL Server Always On FCI architecture, as explained in detail in the blog post [SQL Server High Availability Deployments Using Amazon FSx for NetApp ONTAP](https://aws.amazon.com/blogs/modernizing-with-aws/sql-server-high-availability-amazon-fsx-for-netapp-ontap/). FSx for ONTAP can also provide a quick way to fail over your SQL Server environment to a different AWS Region in order to meet recovery time objective (RTO) and recovery point objective (RPO) requirements. For more information, see the blog post [Implementing HA and DR for SQL Server Always-On Failover Cluster Instance using FSx for ONTAP](https://aws.amazon.com/blogs/storage/implementing-ha-and-dr-for-sql-server-always-on-failover-cluster-instance-using-amazon-fsx-for-netapp-ontap/). You can also use AWS Launch Wizard to deploy SQL Server solutions on AWS, with support for Always On Availability Groups and single-node deployments. Launch Wizard supports the deployment for SQL Server Always on FCIs on Amazon EC2 with FSx for ONTAP as the shared storage. This service saves you time and effort by replacing a complex manual deployment process with a guided, console-based wizard that accelerates the migration of your on-premises SQL Server workloads that rely on shared storage. For more information about how Launch Wizard can help you provision and configure SQL Server FCIs in hours, see the blog post [Simplify SQL Server Always On deployments with AWS Launch Wizard and Amazon FSx](https://aws.amazon.com/blogs/storage/simplify-sql-server-always-on-deployments-with-the-aws-launch-wizard-and-amazon-fsx/). Launch Wizard also supports deployment for SQL Server Always On FCIs by using [Amazon FSx for Windows File Server](https://aws.amazon.com/fsx/windows/) as the shared storage solution. ## Using solutions from AWS Partners + [SIOS DataKeeper](https://us.sios.com/) provides high availability cluster failover support across AWS Regions and Availability Zones. SIOS DataKeeper is available in [AWS Marketplace](https://aws.amazon.com/marketplace/seller-profile?id=3c91e2f7-fc8d-4cce-a8aa-1e37abcb4408). + [DxEnterprise](https://dh2i.com/dxenterprise-high-availability/) from DH2i enables fully automatic failover of SQL Server Availability Groups in Kubernetes and unified instance failover for Windows and Linux. D2HI is available in [AWS Marketplace](https://aws.amazon.com/marketplace/seller-profile?id=4e97d4b7-3366-42fd-8be8-732d38c9e24b). # FSx for Windows File Server FSx for Windows File Server provides fully managed, highly reliable, and scalable file storage that is accessible by using the Server Message Block (SMB) protocol. It is built on Windows Server and delivers a wide range of administrative features such as user quotas, end-user file restore, and Microsoft Active Directory (AD) integration. It offers Single-AZ and Multi-AZ deployment options, fully managed backups, and encryption of data at rest and in transit. You can optimize cost and performance for your workloads with solid-state drives (SSD) and hard disk drives (HDD) storage options, and you can scale storage and change the throughput performance of your file system at any time. Amazon FSx file storage is accessible from Windows, Linux compute instances running on AWS, and on premises. Amazon FSx makes it easier to deploy shared Windows storage for high availability SQL Server deployments through its support for continuously available (CA) file shares and smaller file systems. This option is suitable for these use cases: + As shared storage used by SQL Server nodes in a WSFC instance. + As an SMB file share witness that can be used with any SQL Server cluster with WSFC. Amazon FSx provides fast performance with baseline throughput up to 2 GB/second per file system, hundreds of thousands of IOPS, and consistent sub-millisecond latencies. To provide the right performance for your SQL instances, you can choose a throughput level that is independent of your file system size. Higher levels of throughput capacity also come with higher levels of IOPS that the file server can serve to the SQL Server instances accessing it. The storage capacity determines not only how much data you can store, but also how many IOPS you can perform on the storage. Each gigabyte of storage provides 3 IOPS. You can provision each file system to be up to 64 TB in size. For information about configuring and using Amazon FSx to reduce the complexity and costs of your SQL Server high availability deployments, see [Simplify your Microsoft SQL Server high availability deployments using FSx for Windows File Server](https://aws.amazon.com/blogs/storage/simplify-your-microsoft-sql-server-high-availability-deployments-using-amazon-fsx-for-windows-file-server/) on the AWS Storage blog. To learn more about creating a new CA share, see the [FSx for Windows File Server documentation](https://docs.aws.amazon.com/fsx/latest/WindowsGuide/managing-file-shares.html#create-ca-share). # Disaster recovery Many organizations implement high availability for their SQL Server databases, but that isn’t sufficient for organizations that require true IT resilience. We recommend that you implement a disaster recovery solution to avoid data loss and downtime of mission-critical databases. Adopting a multi-Region disaster recovery architecture for your SQL Server deployments help you: + Achieve business continuity + Improve latency for your geographically distributed customer base + Satisfy your auditing and regulatory requirements Options for disaster recovery include [log shipping](ec2-log-shipping.md), [Always On availability groups](ec2-always-on.md), [Amazon EBS snapshots](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-copy-snapshot.html) that are stored in Amazon S3 and replicated across AWS Regions, [Always On Failover Cluster Instances (FCIs)](ec2-fci.md) combined with Always On availability groups, and distributed availability groups. ## Distributed availability groups An architecture with distributed availability groups is an optimal approach for multi-Region SQL Server deployment. A distributed availability group is a special type of availability group that spans two separate availability groups. You can think of it as an availability group of availability groups. The underlying availability groups are configured on two different WSFC clusters. Distributed availability groups are loosely coupled, which means that they don't require a single WSFC cluster and they’re maintained by SQL Server. Because the WSFC clusters are maintained individually and transmissions are primarily asynchronous between two availability groups, it's easier to configure disaster recovery at another site. The primary replicas in each availability group synchronize their own secondary replicas. Distributed availability groups support only manual failover at this time. To ensure that no data is lost, stop all transactions on the global primary databases (that is, on the databases of the primary availability group). Then set the distributed availability group to synchronous commit. # VMware Cloud on AWS for SQL Server **Notice** As of April 30, 2024, VMware Cloud on AWS is no longer resold by AWS or its channel partners. The service will continue to be available through Broadcom. We encourage you to reach out to your AWS representative for details. [VMware Cloud on AWS](https://aws.amazon.com/vmware/) is an integrated cloud offering jointly developed by AWS and VMware. SQL Server easily integrates with VMware Cloud on AWS. This migration option makes it possible for you to build on your existing investment in virtualization. You can access VMware Cloud on AWS on an hourly, on-demand basis or in subscription form. It includes the same core VMware technologies that you run in your data centers, including vSphere Hypervisor (ESXi), Virtual SAN (vSAN), and the NSX network virtualization platform, and is designed to provide an efficient, seamless experience for managing your SQL Server databases. You can scale the storage, compute and memory of your SQL Server databases on VMware Cloud on AWS within minutes. VMware Cloud on AWS runs directly on the physical hardware, but takes advantage of network and hardware features that were designed to support the AWS security-first infrastructure model. This means that the VMware virtualization stack runs on AWS infrastructure without having to use nested virtualization. VMware Cloud on AWS makes it is easy to set up, scale, and operate your SQL Server databases workloads on AWS. It provides high availability solutions, integrates with on-premises Active Directory, and provides access to AWS services like AWS Directory Service for Microsoft Active Directory and AD Connector, Amazon Route 53, Amazon CloudWatch, and Amazon S3. You can store your backups in Amazon S3, and modernize and simplify your disaster recovery process. ## When to choose VMware Cloud on AWS VMware Cloud on AWS is an option for your SQL Server database when: + Your SQL Server databases are already running in an on-premises data center in a vSphere virtualized environment. + You have a large number of databases and you need fast migration (for example, only a few hours) to the cloud for one of the following reasons, without requiring any additional work from the migration team: + Data center extension. You need on-demand capacity to run virtualized desktops, to publish applications, or to provide a development/testing environment. + Disaster recovery. You want to set up a new disaster recovery system or replace your existing system. + Cloud migration. You want to migrate your entire data center to the cloud, or refresh your infrastructure. If your SQL Server database requires more than 80K IOPS, you can use vSAN. For more information, see [In the Works – VMware Cloud on AWS](https://aws.amazon.com/blogs/aws/in-the-works-vmware-cloud-on-aws/) on the AWS News blog, and [Deploy Microsoft SQL Server on VMware Cloud on AWS](https://aws.amazon.com/solutionspace/solutions/sql-server-vmware-cloud-on-aws/) on the AWS website.