As a digital agency, we understand the value of process improvement and the benefit it provides to our team as well as clients. The value, oftentimes, doesn't come without pain, as improvement implies a challenge or breakdown in process has occurred.
Over the last couple years, C2 has adopted Amazon Web Services (AWS) to help our clients move to the cloud and make on-premise servers a thing of the past. One particular AWS service, the Relational Database Service (RDS), provides a managed relational database for SQL databases, along with five other database engines, without the need to manage a host server environment. We learned it can also present some unique challenges when working in multiple environments.
We interact with databases on a daily basis. A common request is to restore (or replace) a development database with one from a staging or production database to ensure that new code will work with the latest data. This is something that our DevOps team does so often that we could almost do it with our eyes closed. The process of creating a backup of the source database and restoring it over top of the existing destination database only requires a few simple steps within SQL Management Studio. This simple process, one we took for granted, posed a rather large headache once we moved to RDS for MSSQL. Because RDS manages hardware provisioning, database setup, patching, and backups, we were presented with three major challenges that prevented us from using our standard process flow.
The first challenge in RDS is not being able to replace (or overwrite) any existing databases. The second is not being able to backup an existing database and restore it with a new name. This is because a unique ID that’s associated with each RDS database prevents the creation of a new database from a backup. The final obstacle is not being able to drop (delete) the existing database for recreation. When you drop a database in RDS, RDS doesn’t delete the physical database files and will throw an error since a file with the same name already exists.
What we found was, though we couldn’t delete or overwrite the physical database files, we were able to rename them. We also found that if you create a .bacpak export of your database, the unique ID no longer prevents you from creating a new database, just so long as there are no other databases with the same name. With this information, we can create a new process that successfully refreshes an existing database in a matter of four simple steps.
Though we’ll be completely automating this for our internal use, here are the manual steps to refresh a MSSQL AWS RDS database from one to another on the same RDS instance:
Note: Before performing any of these steps against your production infrastructure, fully vet the process utilizing test databases.
1. Export both your source and destination databases.
As with any process that involves databases, it’s best practice to backup all databases you’ll be interacting with in case an error occurs. Using Microsoft SQL Management Studio, you can create a .bacpak export of your database by right-clicking on the database and selecting “Export Data-tier Application” from within the “Tasks” menu.
2. Rename the physical files of your destination database.
To ensure you can create a new database with the same name as your destination database, you’ll need to make sure to rename the physical files before dropping the database to avoid naming conflicts. The following script should be run against the existing database that you’d like to replace:
<p> CODE: https://gist.github.com/thec2group-blog/6ba5e3852db9c05a12be054e3ee4de77.js</p>
If you are unsure of the path for your physical files, use the following script to query them:
<p> CODE: https://gist.github.com/thec2group-blog/667c14ed15b00f82306d5e00bd283687.js</p>
3. Drop your existing (destination) database that you’d like to overwrite.
Now that the physical filenames should no longer be an issue, use the following script to drop your existing database.
Note: Once you perform this step, your database will be unavailable until you complete the import outlined in the next step.
<p> CODE: https://gist.github.com/thec2group-blog/51179939685c46d09dcfbaa912f61071.js</p>
4. Import your source database with the desired name of your destination database.
Now that the existing database has been dropped, you can import your source database while being sure to give it the desired name of the original destination database. To import your database using SQL Management Studio, right-click on your “Databases” folder and select “Import Data-Tier Application” using the .bacpak file from Step 1.
As with any process, change is inevitable. How you confront those changes determines how successful you and your processes will be. It’s true that moving our database infrastructure to RDS presented us with some hurdles that required time to resolve. But since RDS is a fully managed service provided by AWS, we’ve been freed from the burdens and hassles of the daily or weekly maintenance time required to keep a dedicated Windows server and database patched and running smoothly.