Amazon has offered their Relational Database Service (RDS) for some time with a few different RDBMS choices. Recently SQL Server was added to the platform list, with SQL Server 2008 R2 available as an option, you can get a database in the cloud, without having to worry about any management of the host OS, no patching, and Amazon will even handle your backups.

This short piece will look at the setup of a new database instance on Amazon's RDS, connecting to it from your local copy of Management Studio (SSMS) and a quick look at running Red Gate Software's SQL Compare against the RDS instance.

Getting Started

The first thing you need to do is create an Amazon Web Services (AWS) account. When you first connect to the AWS site, you can sign it, or create a new account, just as you would on any web service.

Once you sign in, you will see all of the AWS offerings. The S3 Storage and EC2 compute clouds are the most well known, but Amazon has a lot of offerings you can use. The RDS tab is selected below.

Creating a new instance is fairly simple. If you click the "Launch DB Instance" button, you will get the pop up window shown below:

You can see there are a variety of offerings, and I expect SQL Server 2012 here at some point, but for now you can get a SQL Server 2008 R2 instance. If you select this, you will then be asked for some information about the instance you want to create.

We've filled in some information here, but the choices are to ask Amazon to provide the SQL Server license, or use your own license. That's great if you have some agreement with Microsoft for volume licensing. There's only one version for now, and the instance class is based on the Amazon sizing for virtual machines. You pay more for larger machines, but they handle a larger load. These sizes rance from a single virtual core with moderate I/O to a

You can also specify whether you want distributed deployment of your instance. This gives you a replica in the same, or another Amazon location, in case of issues with the machine. You can have Amazon apply minor patches as well automatically, and you select the storage, the instance names, and the sa password. Since you don't manage the underlying host OS, you don't get to use Windows users or accounts.

Once you complete this screen, you move on to more options for connections and security. You can change from the default port if you wish, pick the zone around the world you would like your machine to run in, and select security. Amazon has a few options for security, and you can read about them in detail on the Amazon site.

Next are your database backups. You can choose a number of options from the screen below, including no backups, which isn't recommended. You don't get the same options with SQL Server, and you don't make the backups yourself, but this is designed to be a database service, not a full instance. For the type of applications that I'd be running in the cloud, this is probably sufficient.

You can choose the backup window, and the retention period. You can set a particular window, or choose no window. The same options occur for maintenance operations as well. I assume that the duration means that the backup (or maintenance) can occur anytime during your window. Once you finish, you get a confirmation screen with your options:

If you launch the instance, it will first get created. This takes about 5-10 minutes, and during which you have a note on your console that shows all your instances and their statuses.

Once the instance is available, it will show with a green icon that indicates it's ready for use. If you select the instance, you will get the various configuration options in the lower panel, including the endpoint you need to connect to from SSMS. This is shown below:

From here, you connect with SSMS, just as you would to any other instance. Use the endpoint above, and enter your credentials from the instance setup.

You connect and for the most part a database in the RDS cloud looks like a database on your local server. SSMS just sees it as a normal SQL Server instance, with all the SQL Server 2008 R2 features that you expect. Or almost all the features. Anything that depends on the local file system (trace to a file, Windows logins, etc.) is disabled. This is really designed to be a set of databases, and you should code against them as just databases.

We can create a database, just like any other database, as shown above. In this case, I'll create the AdventureWorks database as an empty shell. Notice that there alread is an "rsadmin" database, provided by Amazon for their own container of some data. You ought to leave this alone.

Loading Data

Once the database is created, how do we populate it? We can't upload or run the AdventureWorks setup from the console since we don't have RDP access to the VM. Red Gate has a solution to make this easy, and it's the same way that you might want to deploy your local application to your RDS instance.

We can use SQL Compare to pull the schema from our local copy of AdventureWorks and move it to our RDS instance. In this case we can use the RDS endpoint in the SQL Compare setup. The source for comparison is the copy of AdventureWorks installed as a local instance.

Once the connection is made, SQL Compare will show you the list of differences between the two databases.

You can review the deployment script at this point, which lists all the DDL for the objects. If you run it, then all of the DDL will be executed on the remote instance.

For an intial deployment I would just let SQL Compare run. For a production deployment, I'd want to run a script that I had already deployed to the QA server, which I would save from SQL Compare, and then the let tool deploy to QA.

Once the deployment is complete, you have the schema in your RDS database, but what about the data? From the menu in SQL Compare, you can launch SQL Data Compare, which will move the rows from one database to the other.

Data Compare is a great product, and one that I love using for small disaster recovery issues, like restoring a table that has been altered or deleted accidently, but it works great for deployment purposes. You can copy data wholesale, or just data for certain objects. If you add lookup or reference tables to a database, this is a great way to ensure the data is pre-populated in your new system during deployment of the objects.

In this case, we can run the comparison for data just like the comparison for schemas. We see in the screenshot below that our new database has no rows. The screen below shows the number of rows in the source only in the middle, the rows that are different (in this case none), and the rows in the target (zero rows for all tables).

We can get a script for the deployment, as we did with SQL Compare.

The deployment takes a few minutes, and once it's done, you can access the AdventureWorks data and schema in your RDS instance just as with any other instance.

Using RDS

Once you have a database, what do you do with it? Just as you might use a database for backing an application on a local server, you can do it here.

I don't know if I'd use a thick client, but you certainly could. Perhaps you have some application on a phone or local machine that needs to connect to a database and doesn't need high security (no identity/financial/medical info), RDS would work great. If you need a web server, Amazon offers those, and you could use their web services to build a front end that connects to the RDS backend. There are some firewall and security limits, so if you have a known user base, you can limit access to the service.

The service is "pay for what you use" in terms of computing, storage, and bandwidth. That can be a little disconcerting for people that are used to investing in hardware and then having unlimited use. However in many cases you may find that RDS results in lower costs since you are not paying for the service if it's not being used. A great way to get started with an application, or even deploying an application whose usage you are unsure of. USing your own DNS, you can even provide an abstraction layer if you may move your database at a later point in time.

RDS isn't for everyone, but there are plenty of places where it might work well, and provide a lower cost method of deploying an application. Amazon has made it easy to use and the addition of SQL Server means that you can use a platform that you are familiar with, which will behave the same as the development instances you have in your office.