Amazon Lures Microsoft Customers with SQL Server RDS
May 29, 2012 / By Mohammed Mawla
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:
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)', '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
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
|1||ProductName||Microsoft SQL Server|
|3||Language||English (United States)|
|7||FileDescription||SQL Server Windows NT – 64 Bit|
|8||FileVersion||2009.0100.2789.00 ((KJ_SP1_QFE-CU).110928-1654 )|
|10||LegalCopyright||Microsoft Corp. All rights reserved.|
|11||LegalTrademarks||Microsoft SQL Server is a registered trademark of Microsoft Corporation.|
Only ONE CPU core
- Tempdb files location
|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
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
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 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.
Create login test with password = ‘P@ssw0rd’
Create User Test for login [Test]
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
|##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|
- 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
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
One comment on “Amazon Lures Microsoft Customers with SQL Server RDS”
Leave a Reply
You must be logged in to post a comment.