Amazon Lures Microsoft Customers with SQL Server RDS

A good part of any DBA experience involves designing for Scalability and High Availability. Whenever you start putting 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? Can I, if needed, be able to add hardware resources easily?

- How can I recover from hardware failures without losing data? How fast?

- Patching without downtime ?

and many more

For the above concerns, 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 aka 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)

The above options however require extensive planning , design and testing to meet the requirements beside it also posed the routine challenges of patching , hardware failures ,applications 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 make it easier to operate and scale databases without the hassle of administration tasks like scaling, high availability, backup, indexes maintenance, integrity check or patching.

Few platforms have emerged to allow businesses to meet their SLA and meet 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 MySQL only 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 though 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, 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 provided already 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 then you already have SQL server licence as in 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 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 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” runs so that they run in off-peak hours. I found that backup and maintenance can’t fall in 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 (took like 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 autherize access to the instance (compare that to configuring protocols and remote access to on-premise instance). I allowed IP ranges using option.

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

Interrogating the instance

After configuring authorization, I could use SQL server 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 verifies the following (remmember this is Express edition instance)

- Max number of user databases is 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;
      ROLLBACK TRANSACTION;
      RETURN;
   END

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

SELECT @sql = 'ALTER AUTHORIZATION ON DATABASE::'
       + QUOTENAME(@name) + ' TO [rdsa]';
      EXEC(@sql);

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

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

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

- Trigger [rds_drop_login_trigger] runs for DROP_LOGIN event to ensure no special logins are dropped

CREATE TRIGGER [rds_drop_login_trigger] ON ALL SERVER FOR DROP_LOGIN AS BEGIN
   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 = 'NT SERVICE\SQLSERVERAGENT'
    OR @login_name = 'RDSIMAGE\Administrator' BEGIN
      RAISERROR('Cannot drop special principal ''%s''', 16, 1, @login_name) WITH LOG;
      ROLLBACK TRANSACTION;
   END
END

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;
GO

Only ONE CPU core

- Tempdb files location

Exec tempdb..sp_helpfile;
GO

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%)
GO
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
GO
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.

-CheckDB
Successful

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

-Adding Login
Successful

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

-Logins and permissions

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

(
SELECT ','+r2.name
FROM
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'
and p.name=r.name
FOR XML PATH('')
),1,1,'') AS Permissions_user

FROM
sys.server_principals r
WHERE r.type IN('S','U','G')
order by 1

Monitoring

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

- There’s also 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 guranteed firing alert just for testing

After five minutes, the alert fired

Summary

With adding SQL server its RDS and .NET to AWS, Amazon is providing an alertative to SQL server and .NET user base other than SQL Azure.

SQL Azure have an edge though on some features and on the price as well 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 areas that should lead to better offerings on the price and features set

Enjoy