Sql-server – Copy SQL Server replication configuration from production to test environment

replicationsql serversql-server-2016

Where I'm currently working we have an SQL Server's instance for a billing database and another instance for customer database. Both databases keep some tables synchronized using replication mechanism. I'm trying to set up some automated way to get fresh backups from production into QA/DEV environments, but in order for our platform to be kind of a mirror of production environment we need this replication thing between customer and billing databases to work. I know very few about these kind of things, I'm only a developer by unfortunately no one else is willing to do it.

My question is, Is it possible to copy the replication setup in production to QA without having to go along all the process of setting up replication step by step in QA/DEV environments? Maybe generating some scripts of this setup from production and with some small changes enable it in QA/DEV environments?

Additional notes:

  1. We use an instance for QA/Dev customer databases.
  2. Another instance for QA/DEV billing databases.
  3. Both instances run in the same server.

SQL Server version:

Microsoft SQL Server 2016 (RTM-GDR) (KB3210111) – 13.0.1728.2 (X64)
Dec 13 2016 04:40:28
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

This is probably a dumb question, but I'd appreciate any help about it.

Thanks.

Best Answer

From the Microsoft Docs on Replication:

If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.

In the event of server failure or the need to set up a test environment, you can modify the script by changing server name references, and it can be used to help recreate your replication settings. In addition to scripting your current replication settings, you should script the enabling and disabling of replication. For information about scripting replication objects, see Scripting Replication.

You can script the replication objects, then modify the server names to suit the test/dev environment via SQL Server Management Studio:

  1. Connect to the Distributor, Publisher, or Subscriber in Management Studio, and then expand the server node.

  2. Right-click the Replication folder, and then click Generate Scripts.

  3. Specify options in the Generate SQL Script dialog box.

  4. Click Script to File.

  5. Enter a file name in the Script File Location dialog box, and then click Save. A status message is displayed.

  6. Click OK, and then click Close.

The resulting script can then be ran against the test/dev environment to automatically setup replication, just ensure you modify all server names as needed. You really don't want the test/dev environment to interfere with the production environment.

Related Question