1

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.

Improve this question
0

3 Answers 3

Reset to default
1

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.

Improve this answer
1
  • On top what @Max mentioned, you need to prepare a cleanup script for your test/dev environment, so you can do the setup again and again because during your setup, something can be wrong and you need to clean up any half-baked set up and re-start with your setup script. I know because I have been there and done that.
    – jyao
    Commented Oct 12, 2018 at 16:38
0

Log shipping may work as an alternative to transactional replication for this use case.

Improve this answer
-1

Assuming "Customer" database is the publisher and "Billing" the subscriber in a transactional push replication topology, your refresh steps (from prod) would look something like below:

  1. Script out all the permissions from both Customer and Billing DBs on the DEV instances.

  2. Since you want the replication configuration on DEV to mirror that in prod, Script out the replication from the publisher on prod by right-clicking the publication in question (in SSMS)-->generate Scripts--> select "To create or enable the components"-->Hit "Generate Script" button to either script out in a new SSMS window or save the script to file.

  3. Restore both DBs (with overwrite) using the corresponding backups from prod i.e. Billing on DEV would be refreshed from Billing on Prod and same for Customer.

  4. Remove the carried over prod users from the refreshed DBs in DEV followed by running the exported permissions in #1.

  5. This completes the refresh of the two databases. Now proceeding with reconfiguring replication between them.

  6. Run the publication creation script (from #2) on the publisher instance on DEV by carefully changing the server and account names to reflect the DEV environment since the script was extracted from prod. Make any other changes such as post-snapshot script path etc, if it exists.
    DO NOT run the last portion of the script which runs the "sp_addsubscription". Only snapshot and log reader agent should be created at this point.

  7. Now, manually initialize the subscription database by running the following on publisher in the publication DB. This would simply join the publisher and subscriber WITHOUT needing a full reinitialization thus saving a lot of time. Data would start replicating from this point on.

    USE [PubDB] GO EXEC sp_addsubscription @publication = , @subscriber = '', @destination_db = , @subscription_type = push, @sync_type = 'replication support only'

  8. Ensure that running the above code creates MSupd, MSins, MSdel stored procedures in the subscriber DB. This is important.

  9. Run the sp_addpushsubscription_agent (last part of the script from #2)

  10. Start the synchronization.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.