Amazon Lures Microsoft Customers with SQL Server RDS

May 29, 2012 / By Mohammed Mawla

Tags: , , , ,

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;
      ROLLBACK TRANSACTION;
      RETURN;
   END

– 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),

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

– Triggers [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

Index Name Character_Value
1 ProductName Microsoft SQL Server
2 ProductVersion 10.50.2789.0
3 Language English (United States)
4 Platform NT x64
5 Comments SQL
6 CompanyName Microsoft Corporation
7 FileDescription SQL Server Windows NT – 64 Bit
8 FileVersion 2009.0100.2789.00 ((KJ_SP1_QFE-CU).110928-1654 )
9 InternalName SQLSERVR
10 LegalCopyright Microsoft Corp. All rights reserved.
11 LegalTrademarks Microsoft SQL Server is a registered trademark of Microsoft Corporation.
12 OriginalFilename SQLSERVR.EXE
13 PrivateBuild NULL
14 SpecialBuild NULL
15 WindowsVersion 6.1 (7601)
16 ProcessorCount 1
17 ProcessorActiveMask 1
18 ProcessorType NULL
19 PhysicalMemory 615 (644472832)
20 Product ID NULL

I used only ONE CPU core.

- Tempdb files location

Exec tempdb..sp_helpfile;
GO

name fileid Filename filegroup size maxsize growth usage
tempdev 1 D:\RDSDBDATA\DATA\tempdb.mdf PRIMARY 4672 KB Unlimited 10% data only
templog 2 D:\RDSDBDATA\DATA\templog.ldf NULL 768 KB Unlimited 10% 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%)
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
Name type_desc is_disabled create_date modify_date Permissions_user
##MS_PolicyEventProcessingLogin## SQL_LOGIN 1 2012-04-23 08:48:02.750 2012-04-23 08:48:02.767 NULL
##MS_PolicyTsqlExecutionLogin## SQL_LOGIN 1 2010-04-02 17:37:39.220 2012-04-23 08:48:02.780 NULL
BUILTIN\Users WINDOWS_GROUP 0 2012-04-06 21:51:15.263 2012-04-06 21:51:15.270 NULL
Moham SQL_LOGIN 0 2012-05-13 20:16:22.093 2012-05-13 20:16:22.233 setupadmin,processadmin
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 0 2012-04-06 21:51:15.203 2012-04-06 21:51:15.217 sysadmin
NT SERVICE\MSSQLSERVER WINDOWS_GROUP 0 2012-04-06 21:51:15.233 2012-04-06 21:51:15.250 sysadmin
rdsa SQL_LOGIN 1 2003-04-08 09:10:35.460 2012-05-13 20:15:47.907 sysadmin
RDSIMAGE\Administrator WINDOWS_LOGIN 0 2012-04-06 21:51:15.250 2012-04-06 21:51:15.260 sysadmin
test SQL_LOGIN 0 2012-05-13 22:25:33.230 2012-05-13 22:25:33.243 NULL

Monitoring

– 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

Summary

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.

Enjoy!

One Response to “Amazon Lures Microsoft Customers with SQL Server RDS”

  • Raul says:

    Hi,

    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

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>