Blog

Amazon RDS – MS SQL features

Microsoft SQL server is a prominent relational database in the industry. AWS RDS supports multiple versions of MS SQL server. Below list of supported versions and editions. All these versions support point-in-time restores, and automated or manual backups. DB instances running SQL Server can be used inside a VPC. You can also use SSL to connect to a DB instance running SQL Server. Amazon RDS currently supports Multi-AZ deployments for SQL Server using SQL Server Mirroring as a high-availability, failover solution.

Supported Versions

AWS RDS makes available the majors versions of MS SQL server from 2008 onwards. The details of these versions are as below.

  • SQL Server 2017 RTM
  • SQL Server 2016 SP1
  • SQL Server 2014 SP2
  • SQL Server 2012 SP4
  • SQL Server 2008 R2 SP3

Below is an example of how to get the supported DB Engine versions using AWS API in a python SDK program.

import boto3

client = boto3.client('rds')

response = client.describe_db_engine_versions(
    DBParameterGroupFamily='',
    DefaultOnly=True,
    Engine='sqlserver-ee',
    EngineVersion='',
    ListSupportedCharacterSets=False, #True,
)

print(response)

On running the above program, we get the following output −

{
   "ResponseMetadata": {
      "RetryAttempts": 0,
      "HTTPStatusCode": 200,
      "RequestId": "186a9d70-7580-4207-8727-4d29aebb5213",
      "HTTPHeaders": {
         "x-amzn-requestid": "186a9d70-7580-4207-8727-4d29aebb5213",
         "date": "Fri, 14 Sep 2018 05:39:11 GMT",
         "content-length": "1066",
         "content-type": "text/xml"
      }
   },
   "u'DBEngineVersions'": [
      {
         "u'Engine'": "sqlserver-ee",
         "u'DBParameterGroupFamily'": "sqlserver-ee-14.0",
         "u'SupportsLogExportsToCloudwatchLogs'": false,
         "u'SupportsReadReplica'": true,
         "u'DBEngineDescription'": "MicrosoftSQLServerEnterpriseEdition",
         "u'EngineVersion'": "14.00.3035.2.v1",
         "u'DBEngineVersionDescription'": "SQL Server 2017 14.00.3035.2.v1",
         "u'ValidUpgradeTarget'": []
      }
   ]
}

Microsoft SQL Server Licensing

The software license for RDS DB instance is included in the pricing for using MS SQL server. The user does not need to bring in any license. Also the pricing includes software license, hardware resources and AWS RDS management features.

Following are the MS SQL server editions that are available in the MS SQL Server editions.

  • Enterprise
  • Standard
  • Web
  • Express

Unlike oracle, there is no additional licensing requirement for Multi A-Z deployment. Microsoft Server uses SQL server Database Mirroring for such deployment.

For instances terminated because of licensing issues, AWS maintains DB snapshots from which the DB can be restored, when the licensing issue is resolved.

Microsoft SQL Server Security

The database engine of MS SQL server uses a role based security.

The master user name used when creating a DB instance is a SQL Server Authentication login that is a member of the processadmin, public, and setupadmin fixed server roles.Any user who creates a database is assigned to the db_owner role for that database and has all database-level permissions except for those that are used for backups. Amazon RDS manages backups for the user.

Features not Supported in RDS

There are quite several features that are not supported by AWS RDS for MS SQL Server. Some of them are listed below. This is important for a scenario when the on-premise database is being taken to the cloud, availability of these features must be evaluated carefully.

  • Always On
  • Backing up to Microsoft Azure Blob Storage
  • Buffer pool extension
  • BULK INSERT and OPENROWSET(BULK…) features
  • Data Quality Services
  • Distributed Queries (i.e., Linked Servers)
  • Distribution Transaction Coordinator (MSDTC)
  • File tables
  • FILESTREAM support
  • Performance Data Collector
  • Policy-Based Management
  • SQL Server Audit
  • Server-level triggers
  • T-SQL endpoints (all operations using CREATE ENDPOINT are unavailable)

Leave a Reply