Search This Blog

Wednesday, August 5, 2015

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:

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

The Appendix the most interesting information lives here for the DBA

No comments:

Post a Comment