Why not use AWS RDS to create SQL Server Development

The Microsoft SQL Server development machine is something that I’m using daily. The operations I perform are much different from the things that I did on the production server. The production environment should be stable, performant, and secure. On the contrary, the development environment should be flexible and easy to adjust to development needs.

Since we are using Amazon RDS to power some of our production servers, the RDS based SQL Server is in most cases the way we chose. It contains a single database, well-configured disaster recovery, and scales to the application needs.

RDS SQL Server issues

The RDS version of the Microsoft SQL Server got some limitations. They don’t bite us on production, but we have a hard time with them during development.

The first is the maximum number of databases. Since we don’t need a powerful instance, the limit is 30 databases. Working on multiple applications, we hit this limit quickly. It is larger for larger instances, but this comes with the price.

The second is the inability to restore the same database twice. I’m often working on several copies of the same database. When trying to restore the same database twice, I got this error:

Database [db_name] cannot be restored because there is already an existing database with the same familyguid on the instance.

You can export the database as a BACPAC file, but this is not the way I like. It fails on large databases and requires additional work.

There are more limitations, but the two above are the most important to me.

EC2 to the rescue

The simple EC2 instance with the SQL Server running on it is the simple solution to the above issues. Of course, you have to maintain such a server. Of course, disaster recovery requires more work from you. But this is a development server. It would be best to build it from scratch when needed – without limits to the database number and issues with multiple databases restored from the same backup.

There is one more advantage. You can turn the instance off out of work hours. You can even schedule it to start in the morning and turn off after work. Isn’t it nice to have a smaller AWS bill?