Amazon Lures Microsoft Customers with SQL Server RDS

Posted in: Technical Track

A good part of any DBA experience involves designing for Scalability and High Availability. Whenever you start putting together a blueprint for a production instance, some questions must be answered:

  • Will the hardware resources be able to support any anticipated growth of data and user base during the lifetime of the hardware? If needed, can I add hardware resources easily?
  • How can I recover from hardware failures without losing data? How fast?
  • Will patching be without downtime ?

And the list goes on.

RDMS and 3rd party tools vendors came with technologies to overcome these challenges and meet the business needs, including:

  • Running a virtual instance on top of multiple nodes a.k.a. Clustering;
  • Shipping logs to a warm secondary server (Log shipping, Data Guard, Database mirroring, etc.);
  • Shipping database operations across the wire (SQL replication, Streams, etc.).

However, the above options require extensive planning, designing, and testing to meet the requirements. They also came with the routine challenges of patching, hardware failures, application configuration, etc.

Database in the cloud

With the burst of cloud computing, cloud providers tried to overcome the challenges of creating and maintaining infrastructure and to make it easier to operate and scale databases without the hassle of administration tasks like scaling, high availability, backup, index maintenance, integrity check, or patching.

A few platforms have emerged to allow businesses to meet their SLA and other performance objects as well:

– SQL Azure
Amazon Relational Database Service (RDS)
Google Cloud SQL

They are all promising similar features to overcome on-promise challenge of on-premises infrastructure including

– Automated Backups
– Scale On-Demand and across multiple geographic regions.
– No physical administration required
– Monitoring and Metrics
– Automatic Software Patching

Until very recently, SQL Azure was the only solution offering SQL Server on cloud, Google Cloud SQL offering only MySQL, and Amazon offering MySQL and Oracle.

Not anymore! The online retail and cloud solution giant is now offering SQL server as part of its RDS service and also added ASP.NET support for AWS Elastic Beanstalk. Although SQL Azure has an edge with features like Data Sync and bussines analytics, Amazon may look attractive for businesses that have multi-platform applications to unify under one vendor’s umbrella.Amazon excels in offering a free usage tier for application developers that includes 750 hours per month on a micro EC2 instance that is loaded up with SQL Server 2008 R2 Express Edition and has 20GB of database storage, 20GB of backup storage, and 10 million I/O requests per month.

SQL Azure has a 90-day free trial that offers almost the same features as Amazon free tier.

Amazon RDS for SQL Server

Amazon provides RDS for SQL server under two licence tiers:

1- License included

You don’t need a licence for your instance, but it’s already provided by Amazon. The hourly rate starts at $0.035 per hour for the Micro DB instance with SQL server express (I’m using that now), and this includes software, hardware, and Amazon RDS management.

This model only applies to Express, Web, and Standard Editions!

2- Bring your licence

Here, if you have a SQL server licence already, you already have a SQL Server licence, as in the case with Microsoft Volume Licensing or Software Assurance.

I signed up for SQL RDS free-tier; you have to have no paid Amazon RDS usage since Jan 1, 2012 in order to be eligible for that offer.

After that, you can use the AWS management console to launch and manage your instance. The setup is truly simple and asks for few parameters to fill. You have no control on the location of database files, instance collation, instance name, or admin accounts.

Here’re some screenshots of interest

– Initial RDS management before creating an instance:

Creating the instance is a matter of a few clicks with few configurations. Here you choose your RDS engine; there are entries for SQL STD , WEB, and ENT editions too.

Some parameters are filled, like allocated storage, instance identifier, and master username/password. I have no control on that “master username” permissions or collation.

We can also configure when backup and “maintenance” run so that they do so in off-peak hours. I found that backup and maintenance can’t fall in the same window, as in next screenshot. Although not recommended, I can run indexes maintenance, backup, and Checkdb all at the same time and the engine won’t complain (performance will degrade for sure).

After finishing the configuration, the instance was allocated (it took about 10 minutes for me). This is how the instance looks like in management console:

Now the instance is up and running. But before connecting to the instance , we’ll need to authorize access to the instance (compare that to configuring protocols and remote access to on-premise instance). I allowed IP ranges using Classless Inter-Domain Routing option.

A great feature is the ability to restore point-in-time to another instance. This is a great help in case something goes wrong with the data, such as a human error.

Interrogating the instance

After configuring the authorization, I could use SQL Server’s managment studio to connect to the instance as any normal SQL server instance. I created a database normally using following command

Create Database Moham:

I expanded every node of SQL server objects and found that some DDL triggers exist.

[rds_create_database_trigger] runs everytime a database is created and does the following. (Remember that this is an Express edition instance.)

– Verifies that is is within max number of user databases (30),

SELECT @count = COUNT(database_id) FROM sys.databases
   WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'rdsadmin');

   IF @count > 30 BEGIN
      RAISERROR('Database creation would exceed quota of 30', 15, 1) WITH LOG;

– Alters ownership of database to login [RDSA],
– Creates a user inside the database and links it to the current login,
– Assigns Db_Owner role to the above created role,
– Grants connect to login [RDSA] (RSDA is already a SYSADMIN anyway),

       + QUOTENAME(@name) + ' TO [rdsa]';

SELECT @sql = 'USE ' + QUOTENAME(@name)
       + '; CREATE USER ' + QUOTENAME(@login)
       + ' FOR LOGIN ' + QUOTENAME(@login);

      SELECT @sql = 'USE ' + QUOTENAME(@name)
       + '; EXEC sp_addrolemember ''db_owner'', '
       + QUOTENAME(@login, '''');

      SELECT @sql = 'USE ' + QUOTENAME(@name)
       + '; GRANT CONNECT TO ' + QUOTENAME(@login);
      EXEC(@sql) AS LOGIN = 'rdsa';

– Triggers [rds_drop_login_trigger] runs for DROP_LOGIN event to ensure no special logins are dropped.

   DECLARE @login_name SYSNAME;

   SELECT @login_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');

   IF @login_name = '##MS_PolicyEventProcessingLogin##'
    OR @login_name = '##MS_PolicyTsqlExecutionLogin##'
    OR @login_name = 'NT AUTHORITY\SYSTEM'
    OR @login_name = 'NT SERVICE\MSSQLSERVER'
    OR @login_name = 'RDSIMAGE\Administrator' BEGIN
      RAISERROR('Cannot drop special principal ''%s''', 16, 1, @login_name) WITH LOG;

I ran some T-SQL queries to extract info about the instance.

– Instance Version

select @@VERSION

Microsoft SQL Server 2008 R2 (SP1) – 10.50.2789.0 (X64) Sep 28 2011 17:10:21 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

– Instance info
Exec Xp_msver;

1ProductNameMicrosoft SQL Server
3LanguageEnglish (United States)
4PlatformNT x64
6CompanyNameMicrosoft Corporation
7FileDescriptionSQL Server Windows NT – 64 Bit
8FileVersion2009.0100.2789.00 ((KJ_SP1_QFE-CU).110928-1654 )
10LegalCopyrightMicrosoft Corp. All rights reserved.
11LegalTrademarksMicrosoft SQL Server is a registered trademark of Microsoft Corporation.
15WindowsVersion6.1 (7601)
19PhysicalMemory615 (644472832)
20Product IDNULL

I used only ONE CPU core.

– Tempdb files location

Exec tempdb..sp_helpfile;

tempdev1D:\RDSDBDATA\DATA\tempdb.mdfPRIMARY4672 KBUnlimited10%data only
templog2D:\RDSDBDATA\DATA\templog.ldfNULL768 KBUnlimited10%log only

Microsoft recommendations for Tempdb are to create multiple datafiles if the instance has multiple cores. Also, it’s recommended to set Autogrowth in Mbytes and not in %.

Creating one additional Tempdb file
No permissions

USE [master] GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′, FILENAME = N’D:\RDSDBDATA\DATA\tempdev2.ndf’ , SIZE = 5120KB , FILEGROWTH = 10%)
Msg 5011, Level 14, State 2, Line 1
User does not have permission to alter database ‘tempdb’. The database does not exist, or the database is not in a state that allows access checks.

-Setting system parameters using Sp_configure

No permissions

Exec Sp_configure ‘xp_cmdshell’ ,1
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.

-Backup database

No permissions

Backup database moham to disk = ‘D:\moham.bak’

Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database ‘moham’.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


DBCC CHECKDB (‘moham’) with no_infomsgs
Command(s) completed successfully.

-Adding Login

Create login test with password = ‘P@ssw0rd’
Use Moham
Create User Test for login [Test] GO
EXEC sp_addrolemember ‘db_owner’ , ‘Test’

-Logins and permissions

SELECT AS [Name] ,r.type_desc,r.is_disabled,r.create_date , r.modify_date,

sys.server_principals r2
INNER JOIN sys.server_role_members m ON r2.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON
p.principal_id = m.member_principal_id
WHERE r2.type = 'R'
),1,1,'') AS Permissions_user

sys.server_principals r
WHERE r.type IN('S','U','G')
order by 1
##MS_PolicyEventProcessingLogin##SQL_LOGIN12012-04-23 08:48:02.7502012-04-23 08:48:02.767NULL
##MS_PolicyTsqlExecutionLogin##SQL_LOGIN12010-04-02 17:37:39.2202012-04-23 08:48:02.780NULL
BUILTIN\UsersWINDOWS_GROUP02012-04-06 21:51:15.2632012-04-06 21:51:15.270NULL
MohamSQL_LOGIN02012-05-13 20:16:22.0932012-05-13 20:16:22.233setupadmin,processadmin
NT AUTHORITY\SYSTEMWINDOWS_LOGIN02012-04-06 21:51:15.2032012-04-06 21:51:15.217sysadmin
NT SERVICE\MSSQLSERVERWINDOWS_GROUP02012-04-06 21:51:15.2332012-04-06 21:51:15.250sysadmin
rdsaSQL_LOGIN12003-04-08 09:10:35.4602012-05-13 20:15:47.907sysadmin
RDSIMAGE\AdministratorWINDOWS_LOGIN02012-04-06 21:51:15.2502012-04-06 21:51:15.260sysadmin
testSQL_LOGIN02012-05-13 22:25:33.2302012-05-13 22:25:33.243NULL


– AWS console provides a monitoring tab that displays instance and server activity:

– There are also an alerts functionality to setup alerts for the above monitored metrics in case any of them crossed a value. I created a test alarm to page if CPU usage crossed 1% for more than 5 minutes, a guaranteed firing alert just for testing.

After five minutes, the alert fired


By adding SQL Server, its RDS, and .NET to AWS, Amazon is providing an alternative to the SQL Server and .NET user base other than SQL Azure. However, SQL Azure has an edge on some features and on the price, while Amazon RDS excels over Azure on some features like point-in-time recovery and maximum size of database.

We shall see more competition in this area, which should lead to better offerings on the price and features set.


Interested in working with Mohammed? Schedule a tech call.

1 Comment. Leave new


Amazing document, but is there any way to create a sysadmin user in RDS instance of SQLSERVER? or add the role sysadmin to any user?


Leave a Reply

Your email address will not be published. Required fields are marked *