0

I would like to know what are best practices for using SQL Server replication on a SQL Server instance that may have other application databases that may also use replication. That is, our product needs to play well with other users of the instance.

The product currently uses SQL Server replication to create a copy database used for reporting. It is always the sole user of the SQL Server instance. But we now need to document and test (regulatory requirements) how the product can share the instance.

I'm making the assumption here that we still need replication as we do not see another way to isolate reporting load from the application's database.

Has anybody done this successfully?

If we are using instance level replication:

  • Is there a way we can stop/start/modify replication for our application without affecting others?

  • Do setting differ greatly? That is, is it realistic to share instance level replication settings across applications?

Non-instance replication just looks hard, do I have the wrong view here?

Our customers use SQL Server 2008 R2 or SQL Server 2012.

Rob Smyth
  • 1,768
  • 11
  • 19
  • Out of curiosity, what regulatory requirement says that you can't multi-host databases in your environment? That is, if you're concerned that replication (or any other traffic) will be too impacting to other databases, why do you not have the option to put those other databases elsewhere? Seems arbitrary to me. – Ben Thul Dec 11 '16 at 18:55
  • The regulatory requirement is for documenting and testing, not technology. – Rob Smyth Dec 11 '16 at 20:33

1 Answers1

0

At an instance level, replication configures only one distributor. That is, regardless of how many databases you have configured for replication on an instance, they'll all share one distributor. You do have the option to make that distributor local (i.e. on the same instance) or remote. So, if you find that distribution is taking up considerable resources (or anticipate that that's going to be the case), configure remote distribution.

Whatever drive holds your databases' log files will need to have sufficient headroom in their throughput to handle the logreader agent. If you're concerned that your database's activity will be impacting to other databases, isolate.

As for other concerns, replication is a lot like your line of business application. That is, it needs to read data (from the publisher and distributor depending on which phase of replication you're talking about) and write data (from the distributor and subscriber again depending on which phase of replication you're talking about). Provision resources accordingly and you should be just fine.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Thx Ben, but the question is not about resourcing. It is about best practice / practicality of sharing instance level replication with other application databases. – Rob Smyth Dec 11 '16 at 20:35
  • If I were evaluating this, the above is what I'd consider. I'm assuming that any security concerns have been addressed through discussing whether to mulithost these databases in the first place. – Ben Thul Dec 12 '16 at 12:20