Share this
by Pythian Marketing on May 29, 2012 12:00:00 AM
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.
– 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!
No Comments Yet
Let us know what you think