Prashanth Jayaram

How to setup SQL Server database replication for a reporting server

August 7, 2018 by

In this article, we’ll discuss the purpose of database replication and show how you can implement Replication using ApexSQL Log, a SQL Server transaction log reader.

Replication

So what is SQL Server database replication? Well, essentially, it’s a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between those databases to help maintain consistency of the data. Using replication, you can distribute data to different locations and even to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, or even the internet.

Replication disadvantages

SQL Server replication as its devotees but it also can be challenging to set up and configure. There can be issues with replicating across different versions of SQL Server and other “pain points”. If you are looking for an out-of-the-box solution, that can be set up quickly but yet easily customized, SQL Server replication might not be the best choice.

An alternative

In this article, we are going to propose an alternative technology, ApexSQL Log

ApexSQL Log is a 3rd party SQL Server transaction-log reader, designed to read online transaction logs; detached transaction logs; transaction log backups for auditing, recovery, production load simulations and in our case replication. We’ll be using ApexSQL Log as our replication agent/technology

Use case

For our use case, we are going to do one-way replication from a production “Publisher” database to a reporting “Subscriber” database. Our goal will be to offload reporting queries to another server, to reduce load on production and enhance reporting performance (via less general workload, re-indexing for selects, potentially closer to clients etc)

Technical requirements

For this use case we can afford latency of up to 15 minutes between production and reporting systems

Replication task overview

Once we have initially set up our subscriber environment, we’ll be creating our replication task, automating and scheduling it

Here are the steps to follow:

  1. Using ApexSQL Log, create a redo.sql script; the redo.sql file is a collection of replay transactions
  2. Save it as a batch file. In this case, it is replication.bat.
  3. Schedule a job to run the batch file on an a scheduled interval to create a SQL script and execute the script on the Subscriber

Replication task

Let’s begin …

  1. Start ApexSQL Log
  2. Click New
  3. In the Select Database, enter the SQL Server instance and select the source database
  4. Click Next

  5. In the Select data sources step, by default, the online transaction log will be selected.
  6. Click Next

    Next, Select output, select Undo/Redo, this will create redo SQL script

  7. In Filter setup, select Continuous auditing. This will ensure that reading the transaction log is seamless as it will remember the LSN of the last transaction enduring no gaps of missing data nor any duplication of overlapping/redundant data.

  8. In the Batch script, enable Overwrite existing files and click Copy, to copy the script, and click Cancel

  9. Now, paste the content in the batch file, Replication.bat.

    Automation

    Here is the view of our batch script

    Although we could do this all in Windows Shell we will create a small wrapper PowerShell script to run our batch file and perform a few other simple tasks

    Prepare the PowerShell script involves the following steps:

    1. Load the SQL Server module
    2. Build filename in the format <path>\redo_yyyyMMdd-HHmmss followed by sql extension
    3. Execute the batch file which in turn invokes the ApexSQL Log executable.
    4. Run the SQL file on the subscriber database using Invoke_SQLCMD
  10. Save the PowerShell script as a ApexAutomation.ps1

Scheduling the job

Now that we’ve built our automation layer, it is time to schedule it

To create a job in SQL Server Agent, follow the below steps:

  • Navigate Jobs under SQL Server Agent. Right-click Jobs and select New. Type in the job name, Replication

  • Next, Select the Steps, click New and type in the step name, Replication
  • Select PowerShell
  • At the Command, type in the path(G:\DBA\Replication\ApexAutomation.ps1) of the PowerShell script

  • Now, go to Schedules and click New, Select Frequency “Daily” and ”Occurs every 15 minutes”

  • The SQL Job is created successfully.
  • On the source database, perform few DML operation and run the job

Review

You can test this on WorldWideImporters but running some sample inserts and updates. Then manually executing the batch script and comparing the data in the two tables with a tool like ApexSQL Data Diff and verifying that they are the same

Wrapping Up

This article is an effort to show how easy it is to set up Replication using ApexSQL Log. It’s also an effective way to transfer data from a source to a destination. If you’re working on busy OLTP systems with heavy workload, then you can run the job as frequently as every 5 minutes or less.

If you are considering replication, but don’t have a version/edition of SQL Server that supports it or would prefer an easier, out-of-the-box solution with no coding, except for a couple lines of PowerShell, you should consider ApexSQL Log for replication, as well as continuous auditing, forensic auditing, database recovery and even to simulate production loads in testing.

Table of contents

SQL Server replication: Overview of components and topography
SQL Replication: Basic setup and configuration
How to Add/Drop articles from existing publications in SQL Server
How to do a quick estimated compare of data in two large SQL Server databases to see if they are equal
SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database backup
How to setup a custom SQL Server transaction replication model with a Central Subscriber and Multiple Publisher databases
How to setup custom SQL Server transactional replication with a central publisher and multiple subscriber databases
How to set up a DDL and DML SQL Server database transactional replication solution
How to setup cross-platform transactional SQL Server replication for database reporting on Linux
SQL Server database migrations with zero data loss and zero downtime
Using transactional data replication to replay and test production loads on a staging server
How to setup SQL Server database replication for a reporting server
SQL Server transactional replication: How to reinitialize a subscription using a “Replication support only” –TBA
SQL Server Replication Monitoring and setting alerts using PowerShell –TBA

Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

Replication, Transaction log

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

1,564 Views