A Simple Way of Automating and Scheduling SQL Server Database Replication between Different Servers

image courtesy: https://www.spiria.com/site/assets/files/2398/2016-sql-server-logo.-large.jpg

Microsoft SQL Server provides SQL Server Replication features that we can utilise to set a database as Publisher and another one is Subscriber, so we can replicate the database with customisations in the scripts. However, sometimes the requirements may not be that complex so that SQL Server Replication could be a little overkill.

For example, recently one of my clients want to start a POC project to build a data warehouse. They have concerns regarding the production databases that they are relying on to keep the business running. So, before we can extract the data from the databases to build the data warehouse, we need to replicate the databases so that all the development works will be conducted on these replicas.

In this case, it is not necessary to include any customisations in the replicating processes, which is just simply mirroring the database. In this article, I’ll introduce a very easy way of automatically replicating SQL Server Databases between different servers.

Create SQL Servers in Azure

Let’s firstly create two SQL Servers for this experiment. It is highly recommended to use Azure, or otherwise, you’ll need to prepare two different machines, get proper SQL Server licenses and install the SQL Server software which could take an entire day.

If you are new to Azure, you’ll get $200 credits (in one month) when you register Azure as a new user.

Go to your Azure subscription, create a resource group first, which will be the container of the SQL Servers. Then, go to the marketplace and search "sql server", choose the SQL Server 2017 on Windows Server 2016 as shown in the screenshot.

In the dropdown manual, choose the Standard Edition to have all the standard features. Then, click "Start with a pre-set configuration" to save time.

Here we can choose Dev/Test environment and D-Series VM (both smallest) to save costs, especially if you have already spent all the free credits. Then, click "Create VM"

Make sure you selected the resource group that you created for this. Then, click next.

Fill in administrator credentials.

The disk is not important for this experiment, so we can skip. However, the virtual network is very important. Make sure you create a new virtual network and subnet, and more importantly, the other SQL Server that will be created later on must be in the same virtual network and subnet, if you don’t want to create some accessibility issues for this experiment. Also, open the RDP port 3389 for later on convenience.

Do not change the default port number and create an SQL Authentication. After that, we have done the configurations. Click create button to create the resources in Azure.

While waiting for the resource being deployed, we can create the other SQL Server. I’ll call it SQL-Server-Test.

Prepare the "Production" SQL Server

After the first SQL Server has been deployed, go to the resource group and find the Windows VM for SQL-Server-Prod. Because we have opened the RDP port for the machine, so we can remote control this VM using its public IP address.

Please also record the private IP address, which will be used later for the testing machine to connect to this product machine

Download a Sample DB

Let’s install a sample database from Microsoft. On the production machine, download the database Backup: https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak

In SSMS, login to the DB instance using the default Windows admin account.

Restore the Sample DB

Right-click "Databases" and select "Restore Database".

In the popup window, click the browse button on the right of "Device" radio button -> Add -> Browse the bak file that you’ve just downloaded -> OK -> OK -> OK. It may take about 1 minute to restore the whole database.

Prepare Backup Path

Next, we need to create a folder that the backup files will be put into. Also, we need to share this folder in the network so that the Test machine can access the backup file and restore it.

I created the directory C:backup. Then, share the fold to the same admin account at the other machine.

Note that it is important to add MSSQLSERVER user to the sharing. Otherwise, SQL Server might not be able to write backup files into this folder after the sharing. Just simply type the MSSQLSERVER username and click Add button, then make sure to grant Read/Write permission to it.

On the Test machine, we will be able to access the backup directory now.

Creating Backup on SQL-Server-Production

Since the SQL Server Agent is disabled by default, we need to remote control the Prod machine again to enable it. Right-click on SQL-Server-Agent -> Start. Then, in the pop-up confirmation window click Yes.

Wait until SQL Server Agent is started, go back to the testing machine. You will need to reconnect the production SQL Server to see the SQL Agent enabled.

Right-click Jobs -> New Job....

In the New Job window, input a name and go to Steps tab.

In Steps tab, click New button.

In the New Job Step window, input step name and the script as follows:

USE AdventureWorks2017
GO

DECLARE @filename varchar(255)
DECLARE @date datetime

SELECT @date=getdate()
SELECT @filename='C:backupAdventureWorks2017-' + CAST(DATEPART(yyyy, @date) as varchar) + '-' + CAST(DATEPART(mm, @date) as varchar) + '-' +  CAST(DATEPART(dd, @date) as varchar) + '.bak'

BACKUP DATABASE AdventureWorks2017 TO DISK=@filename WITH INIT
GO

After that, go to theAdvanced tab, configure the success and failure behaviours. Here, you need to consider your situations, such as how large your database is and etc.

Click OK to confirm apply the Job Step, then go to Schedule tab and click New button. You may configure the schedule based on your requirements. In this example, I’ll let the backup happens every day at 1:00 AM. Then, click the OK button to confirm the schedule.

You may also want to create alerts or notifications such as sending emails when the job fails. In this experiment, we’ll skip this step because it is not always needed.

Now, rather than wait until tomorrow at 1 AM, I would like to test the job. We can right-click the job we created and select "Start Job at Step…" to test the job. Since we only have 1 step in this job, it will directly start and run the backup step.

After a while, the job is success and you can also find the backup file at the backup directory.

Restoring the Backup on SQL-Server-Test

First of all, let’s check the accessibility of the backup file from the test machine by access SQL-Server-Prodbackup<backup-file>.

Another crucial step is to mount the remote backup folder to a local drive. This is because usually the SQL Server Windows Service is run as a service account which does not have permission to use network/remote resources, so the remote resource is not visible to it.

Simply right-click on the backup folder and select "Map network drive…".

Let’s mount it to Z: drive on the test machine.

Then, we need to let SQL Server identify the network drive using the xp_cmdshell command. By default, this command is not enabled, so we need to enable it. Open a new query sheet and run the following script to enable it.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

Then, define the network drive using xp_cmdshell. You can use any Windows user that can access this network drive. Note that you only need to run this command once, so the password will not be retained anywhere in plain format.

EXEC XP_CMDSHELL 'net use Z: SQL-Server-Prodbackup <password> /USER:<username>'

From the result panel, you should see the output saying it was successful.

We can also test whether it works or not, by the following script.

EXEC XP_CMDSHELL 'dir Z:'

If it works, you should see the file list including the backup file.

After that, we just need to create another SQL Server Agent Job with a step for restoring the database from the identified network drive. The script of the step is as follows:

DECLARE @filename varchar(255)
DECLARE @date datetime

SELECT @date=getdate()
SELECT @filename='Z:AdventureWorks2017-' + CAST(DATEPART(yyyy, @date) as varchar) + '-' + CAST(DATEPART(mm, @date) as varchar) + '-' +  CAST(DATEPART(dd, @date) as varchar) + '.bak'

RESTORE DATABASE AdventureWorks2017
FROM DISK=@filename
WITH REPLACE

GO

Then, repeat what we have done before in the production machine, right-click the agent job and select "Start Job at Step" to test the job. Wait for a while until the restoring finished, right-click "Databases" to refresh the database list, you will see the restored database!

Join Medium with my referral link – Christopher Tao

If you feel my articles are helpful, please consider joining Medium Membership to support me and thousands of other writers! (Click the link above)