dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted October 22, 2018

SQL Server on Amazon Web Services (AWS)

By Anoop Kumar

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:

  1. Availability of up to 30 databases on each of MS SQL Server database instance
  2. 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
  3. Limitation on use of certain ports, which are reserved for Amazon RDS
  4. Maximum storage size for MS SQL Server database instance is 16 TB for General Purpose SSD storage
  5. Many server-level roles are not available like sysadmin, serveradmin, bulkadmin, dbcreator, securityadmin and diskadmin
  6. 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.

See all articles by Anoop Kumar



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM