Relational Database Service (RDS) with SQL Server

Using Relational Database Service (RDS) with SQL Server on Amazon Web Services (AWS)

Are you thinking of using AWS RDS? If the answer is yes or maybe, then some research is required to gain an understanding of the constraints of the environment.

First, be prepared to learn a new vocabulary, as there are many acronyms. Some you will want to learn from a database perspective are

EC2	Elastic Compute Cloud
AZ	Amazon Availability Zones
S3	Simple storage service
EBS	Elastic Block Storage
VPC	Virtual Private Cloud
IAM	Identity Access Management

Here are some constraints to consider:

There is a limit of 30 databases per instance

Importing data with blobs requires all of them to be able to fit into memory, as writing to the temp directory is prevented. SQL Server will often write to the temp directory if there is not enough memory available to hold the blob during an import

Small tables can be done through the import/export wizard

Larger tables and blob data should be performed through the BCP utility

Resizing an instance up or down in CPU and memory may cause a 2 second up to a five minute delay while it fails over if you are using a Mutli-AZ

Changing the storage type will cause the DBA to have to rebuild the databases, i.e. recreate the database and then  import the data again

Point in time recovery, will create a new instance and not over write the current one. The DBA will need to rename the existing and the new one to swap places, or extract the data and import it back into the existing database.

Taking a snapshot freezes database I/O, so be alert as this may cause transactions and connections to momentarily hang up to 10 seconds

Terminating an instance, make a final backup if you have any doubt you might need to get the data back at a later date

Amazon provides very detailed documentation

Licensing everyone’s favorite area:

http://aws.amazon.com/windows/resources/licensemobility/sql/

Beware that there are minimums with respect to how many license you will need to have, if you’re going with the bring your own license model.

Migrating to RDS for SQL Server

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

The Appendix the most interesting information lives here for the DBA

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.html

To help or not to

Helping others even when it looks like it is not your problem, that is the main idea behind this post. I was recently request to provide assistance to an application team and business partner. I had reviewed the request and the logs of the database and even had the platform team look into the servers involved in the error. I could not find any errors being logged. I studied the email some more, and noticed an database error code listed in a screen shot in very tiny print. So researching this it was a no data found error. I asked the application team if they checked the SQL being executed but they had no idea.

We had a conference call and screen sharing and I asked to see the work flow in the Informatica tool. It showed a script being executed, so I asked what was passed in and what databases it was executed in, and we looked at a stored procedure. The application team was drilling deeper into the issue and found a flag in the workflow where it was still showing that it was running. This was false, so they changed it and viola.

They were able to start the workflow and process the flat files. I did not do anything but ask appropriate questions. Everyone was afraid to look at anything since it was such an old interface of at least 15 year or so. I merely wanted to see what parameters and execution of statements were being called.  The bottom line is don't be afraid to try and help others even if it does not appear to be a problem with your area of responsibility, sometimes you can make a difference and ask the questions that leads others to solve their problem.

Good luck on your troubleshooting and problem solving adventures!