Copy Database

This document describes the process of duplicating your database in Azure so that you can work on a non-production copy of the data.

You will need to login to the Azure portal and then go to your Database. Be sure you are on the database and not the database server. Look for the "Copy" button near the top and click it.

The page that will open allows you to make a copy of the database. There are some options you can change, such as the name of the new database, but leaving everything at their default values will be fine too. One thing you may want to change is the new database name. By default it just appends "Copy", but you may want to make that more obvious with something like "CopyForSpark".

One of the things you can change on this screen is the database tier (S2, S4, etc). By default it will use the same tier as the original database. If your database is already over provisioned a bit, you can shrink this down some to save a few bucks, but the more you reduce the database tier the longer the restore and scrubbing will take. So be smart.

Click the "Review + Create" button to review everything before you proceed.

The final review screen will show you the estimated cost, amongst other things. You should assume that this will take a few days (especially the first time). So you can take the total estimated cost and divide by 10. This will give you the rough cost for 3 days. Hopefully it won't take that long. As soon as we are done with the database we will delete it.

At this point you should see a "Deployment is in progress" screen. How long this takes depends on the size of your database and the service tier you chose. But it could easily take a few hours to process. Once it is done this will change to "Your deployment is complete". Click the "Go to resource" button that will appear.

Firewall

If you know your SQL Server firewall is configured to allow you to connect already, then you can skip this step. But by default, external IP addresses are not allowed to connect. So we need to fix that. From the Overview page of the database, click the link to the server.

From the server screen, you will need to scroll down the left-side menu until you get to the Security section. Click on the "Networking" link.

On this screen you need to make sure "Public network access" is set to "Selected networks". Then you need to add your IP address to the "Firewall rules" section if it is not already there. If you google for "whats my ip address" it is usually helpful enough to give you the answer without having to click on any sites.

If your IP is not listed add it and give it a meaningful name then click Save.

Connection String

In order to connect to the database, you will need to get a connection string from Azure. From the Overview page of the database, click the "Show database connection strings" link.

This will take you to another page where you can see the connection string. You want the "ADO.NET (SQL authentication)" string. You will need to replace the "User ID" and "Password" values with the same ones in your web.ConnectionString.config file from your Rock instance. Do NOT take the entire connection string from the config file otherwise you will be connected to the production database. You just want the user and password.

You'll use this connection string later in the Database Cleaning step.

Delete Database

Once you are all done with the process of scrubbing and exporting the database you will want to delete it so you don't continue to be charged for it. From the database Overview screen, click the "Delete" button and then confirm the process.

Remember to delete any new firewall rules you added and undo any other changes you made to the Firewall.