Administering SQL Server Through Amazon’s RDS

Administering RDS

Amazon’s Relational Database Service (RDS) offers a low maintenance, flexible, and cost effective way to run application databases, such as Episerver, in the Amazon cloud. RDS allows you to run any supported version of Microsoft SQL Server and any edition of that version you like. With RDS, Amazon will pre-configure instance parameters; provide monitoring, user notifications, and automatic software patching; and perform backups as specified by the user. Along with all this, RDS also allows for easy and fast scaling of resources to meet application requirements.

These capabilities allow for streamlined deployment of database instances, reducing the user side maintenance required to keep them running correctly. However, the cost of all this streamlining and low maintenance is a reduction in the access and rights the user has to the administer the instance, but this is a small price to pay for the benefits provided by RDS, and Amazon has provided us with some ways to get around these limitations.

Administrative Access to RDS

Because RDS SQL Server instances are running on shared servers that are maintained by Amazon, users of RDS instances are not given SQL Server SA access to the instance or access to server running the instance. At first glance, not having this kind of access can appear to be a major obstacle to administration on RDS. However, this is not really the case, because Amazon has provided users of RDS with a number of tools and work arounds to address issues related to having less than full access.

We’ll look at some of the most common administration tasks that are impacted by using RDS and their work arounds below, but before we delve into what is different about SQL Server administration on RDS, let’s look at what is not impacted by RDS’s administration limitations. Some the tasks and tools I use daily that function the same on RDS instances as on any other instance include:

• Accessing the instance with SQL Server Management Studio
• Setup of new databases
• Setup of new SQL Server user accounts (Windows authentication is also supported)
• Assigning user rights to user databases
• Running Activity Monitor
• Running system stored procedures such as sp_who and sp_who2
• Finding and addressing blocking processes

Administration tasks that require SA access to the instance or access to the file system the instance is running on are impacted by using RDS. Let’s take a look at some of these tasks:

Manual SQL Server Backups and Restores

Because RDS users do not have access to the file system on the server hosting their instance, this means they cannot simply run manual database backups or restores to or from the default SQL Server backup location on the file system. To address this, Amazon has provided a way to setup an Amazon S3 bucket to store database backup files. Users are allowed to back up databases to this bucket or restore databases from native SQL Server backup files stored in the bucket. Amazon has provided some good documentation on how to do this, so I won’t go into detail on this process here, but I do want to point out a few things to keep in mind when you are doing SQL Server backups and restore on RDS:

• A backup file can’t be restored to the same DB instance that was used to create the backup file.
• The same backup file can’t be restored to a DB instance multiple times.
• You can't back up to, or restore from, an Amazon S3 bucket in a different region than your Amazon RDS DB instance.
• Amazon strongly recommends that you don't restore a backup file from one time zone to a different time zone.
• A database can’t be backed up during the maintenance window, or any time Amazon RDS is in the process of taking a snapshot of the database.

Bringing a Database Back Online

With RDS, we can take databases offline like we do with any other SQL Server instance. This can be down with an Alter Database statement or with SSMS GUI by right clicking on the database you want to take offline and selecting Task > Take Offline. In RDS, if we want to bring a database back online, we cannot do it in the normal manner; instead, we must use a stored procedure provided by Amazon. This stored procedure is named rds_set_database_online. The following statement can be used to run the rds_set_database_online stored procedure:

EXEC rdsadmin.dbo.rds_set_database_online <Name of database>

Changing the Database Recover Model

With RDS, Amazon manages the backups for instances and databases within an instance. For this reason, all databases in a RDS instance must have the same recover model settings, and users are not able to make changes to these settings. If backup retention is enabled for the instance, all the databases will be set to the Full recover model. If backup retention is not enabled for the instance, all the databases will be set to the Simple recover model. If a user makes changes to the recover model of a database, that change will be automatically revived by Amazon within 5 minutes.

Viewing SQL Server Error Logs

RDS SQL Server instances and SQL Server Agent error logs cannot be viewed using the normal SQL Server tools, because the user does not have the rights to use these tools. To view SQL Server and SQL Server Agent error logs with RDS, use the rds_read_error_log stored procedure.

The rds_read_error_log stored procedure has the following parameters:

• @index – The version of the log to retrieve. The default value is 0, which retrieves the current error log. Specify 1 to retrieve the previous log, specify 2 to retrieve the one before that, and so on.
• @type – The type of log to retrieve. Specify 1 to retrieve a SQL Server instance error log. Specify 2 to retrieve a SQL Server Agent log.

To view the current SQL Server error log using the rds_read_error_log stored procedure, use the following statement:

EXEC rdsadmin.dbo.rds_read_error_log @index = 0, @type = 1;

For more information about Amazon RDS for SQL Server, please visit Amazon’s RDS page. We hope this look at administering RDS has been helpful, but if you have any questions, or If you need any help setting up or working with Amazon RDS for SQL Server, please let us know.