2

In our environment we are using Log Shipping as DR. Our primary database is a clustered instance. My requirement is to set up a snapshot replication for reporting purposes.

My plan is as follows:

  1. Make the production database as the distributor and publisher.
  2. The subscriber will be a read only for reporting.

My question is:

Will there be any problem if the production database acts both as distributor and subscriber?

Improve this question
6
  • U mean will there be any problem if the production db acts both as Distributor and Subscribor as will there be any problem if the production db acts both as Distributor and Publisher ? It depends on how busy is the publisher publishing data e.g. amount of transactions on publisher. If its low volume then should be fine.
    – Kin Shah
    May 17 '18 at 15:58
  • This will be a busy server but I will be using snapshot replication where only once a day it replicates
    – SQL_NoExpert
    May 17 '18 at 15:59
  • How big is the database ?
    – Kin Shah
    May 17 '18 at 16:17
  • Currently 60gb but it will increase yearly around 30 gb
    – SQL_NoExpert
    May 17 '18 at 16:18
  • 1
    Then instead of going through all the hassel of replication why not backup/restore with compression or just delay the log restores and open the db for reporting ?
    – Kin Shah
    May 17 '18 at 16:19
1

It depends on your hardware. I've seen a table in the 70GB range take approximately 20 minutes to create a snapshot. Longer on older hardware. If anything else is being done, it can result in blocking.

If you write the snapshot files to the same drives as your database files then it will create I/O contention and can impact performance. And saturate your network bandwidth.

But the same can happen if you have a separate distributor as well.

Improve this answer
2
  • Thanks for the comment, I beleive using the same server for distributor and subscriber will release some load from the publication database.
    – SQL_NoExpert
    May 17 '18 at 18:16
  • I would then configure separate disks for the snapshot files and database files
    – Alen
    May 17 '18 at 18:28
2

Since your database is 100GB, you can use backup and restore method with compression (make sure Instant file Initialization is enabled).

Another approach will be to use logshipping with delaying the restoring of log files on the standby server and having the database in standby read mode ( WITH STANDBY).

The way you do is -

  • take your regular log backups on your primary (in your case every 15 mins)
  • keep the copy job as is to copy log files to network share.
  • Choose the option to delay the restoring of logs as per your threshold.
  • To speed up restoring the logs - change the setting of logshipping to norecovery
  • once done restoring the logs, change it back to standby.

If you want to automate the entire process, use dbatools - Log Shipping functions/cmdlets

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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