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?

MDCCL
  • 8,530
  • 3
  • 32
  • 63
SQL_NoExpert
  • 1,107
  • 1
  • 21
  • 37

2 Answers2

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

Kin Shah
  • 62,545
  • 6
  • 124
  • 245
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.

Alen
  • 539
  • 3
  • 9