Introduction
In this article we will discuss options to make Microsoft SQL Server (MS SQL Server) available on Amazon Web Service (AWS). AWS is the largest cloud service provider and lots of organizations have been leveraging AWS as their cloud service. Organizations are migrating on premise legacy applications to the cloud as well as building new business critical applications, using the latest technology suites in the cloud to support their businesses. In such cases, it is required for organizations to explore the options of availability of Microsoft SQL Server on AWS for the applications that have been using Microsoft SQL Server as relational database.
Microsoft SQL Server Availability on AWS
There are multiple ways to make Microsoft SQL Server available on AWS. Amazon provides Relational Database Service (RDS) and Amazon Elastic Compute Cloud (EC2) to run Microsoft SQL Server Databases on AWS. These two approaches have their own advantages to maintaining MS SQL Server databases on AWS.
Relational Database Service (RDS), is a manage service that is easy to set up, maintain and scale a Microsoft SQL Server Database on AWS. AWS RDS takes care of the end to end management of MS SQL Server from installation, disk provisioning, version upgrades, security patching to back up and recovery of MS SQL Server databases. Also, AWS RDS supports high availability of databases and scalability of the environment automatically.
Amazon Elastic Compute Cloud (EC2), is a service to provide computing capacity on AWS cloud. Amazon provides the capability to run MS SQL Server on Amazon EC2 like running an MS SQL Server on premise data center. In this scenario, organizations will have maximum control and flexibility to configure MS SQL Server with additional responsibilities to manage the deployed MS SQL Server and databases.
Microsoft SQL Server on Amazon RDS
Amazon RDS is a managed service and supports MS SQL Server availability on Amazon cloud. Amazon RDS supports certain features and options of MS SQL Server like Core database engine features, MS SQL Server Development Tools, MS SQL Server Management Tools, Change Tracking, Service Broker, Spatial and location features. Also, there are certain security features available with RDS like Transparent Data Encryption (TDE), Secure Socket Layer (SSL) connection, Encryption of storage at rest using Amazon Key Management Service to secure SQL Server databases and data.
Amazon RDS manages high availability of MS SQL Server and databases using multi-availability zone capability (Multi-AZ). This reduces the organization’s overhead to manually set up and maintain database mirroring, failover clusters, or Always On Availability Groups to achieve high availability.
Amazon RDS manages provisioning of the database, management of security patches, version upgrades of MS SQL Server and disk storage management.
Amazon RDS gives an opportunity to organizations to focus on high-level tasks, such as performance tuning and schema optimization and RDS manages backups and point-in-time recovery of the database in the event of a crash and failure.
There are certain limitations with Amazon RDS, which are important to explore before organizations choose Amazon RDS as an option to deploy MS SQL Server. Some of the key limitations are as follows:
- Availability of up to 30 databases on each of MS SQL Server database instance
- Amazon RDS doesn’t support other MS SQL Server services like SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Quality Services (DQS), or Master Data Services (MDS) on the same server as Amazon RDS MS SQL Server DB instance
- Limitation on use of certain ports, which are reserved for Amazon RDS
- Maximum storage size for MS SQL Server database instance is 16 TB for General Purpose SSD storage
- Many server-level roles are not available like sysadmin, serveradmin, bulkadmin, dbcreator, securityadmin and diskadmin
- Some of the following key features of MS SQL Server are not available with Amazon RDS:
- Always On
- Backing up to Microsoft Azure Blob Storage
- BULK INSERT and OPENROWSET(BULK…) features
- Database Log Shipping
- Distributed Queries (i.e., Linked Servers)
- Distribution Transaction Coordinator (MSDTC)
- File tables
- Maintenance Plans
- PolyBase
- R
- Replication
- Server-level triggers
- Service Broker endpoints
- Stretch database
You can refer to the AWS portal to get the comprehensive list of MS SQL Server and database limitations on Amazon RDS.
Microsoft SQL Server on Amazon EC2
Availability of MS SQL Server on Amazon EC2 gives full control over the environment. Organizations can have full control over the operating system on Amazon Machine Image, including MS SQL Server installation and configuration, which was not the case with Amazon RDS.
With Amazon EC2, organizations can achieve environment scalability and high availability by quickly provisioning and configuring database instances and storage, and scale database instances by changing the EC2 instances size or storage capacity. High performance can be achieved by provision MS SQL Server in AWS Regions across the world to provide low latency to geographical distributed end users. Multi-Availability Zone deployment will help in configuring the high availability.
Organizations are responsible for data replication and recovery across database instances in the all regions in the event of failure or database crash.
The biggest pro with an Amazon EC2 approach over Amazon RDS is organizations can use SQL Server services and features that are not available in Amazon RDS. On the other hand, the biggest con with using the Amazon EC2 approach is maintenance overhead. Organizations will be responsible for administering the MS SQL Server and databases, including backups and recovery, version upgrade, operating system patching, security management, and configuring high availability or replication.
Summary
Amazon Web Service provides multiple ways to deploy MS SQL Server on AWS. For MS SQL Server, both Amazon RDS and Amazon EC2 have advantages and limitations. Amazon RDS is easy to set up, manage and maintain. Using RDS, organizations have the opportunity to focus on more important tasks, rather than the day-to-day administration of MS SQL Server and the underlying databases. Alternatively, running MS SQL Server in Amazon EC2 gives more control, flexibility, and features to use with additional maintenance overhead.
