1

I have an application that uses a database for each instance of the application, one instance per client. Each client wants a database for reporting as we do not allow reporting from the production system, we use a simple script to backup and restore the database nightly for each client.

Now each client wants to modify their reporting copy of the database. They want to add tables and views to help with reporting. What type of replication would allow me to keep the data in sync from the publisher to the subscriber while also allowing the subscriber to be modified with new tables, views and sprocs? Also which type of replication would best fit this scenario of a reporting database?

Improve this question

2 Answers 2

Reset to default
2

Is it possible to take their suggestions and incorporate them into your main database; or are the additions very client specific? You could ask them for scripts to run post restore.

If you go down the replication road then see Chad's answer.

Some warnings with transactional replication though are that if your subscriber fails, you might fill your publisher disk and take down production - happens all the time when people aren't monitoring closely.

You will also need to put significant effort into automating it, because experience shows many application schema upgrades will fail for one reason or another until you remove replication beforehand and set it back up again afterwards.

The reasons we haven't recommended the other kinds of replication:

  • Merge is more often used for combining data from handheld devices using distinct IDs or GUIDs to keep everything separate.
  • Snapshot will be dumping out all the tables each night like a BCP then reapplying them. Very IO heavy. But it doesn't have an impact during the day (and some very rare apps are sensitive to the small load transactional replication puts on things).
Improve this answer
2
  • 1
    Yes, currently I am taking their scripts and applying them in the restore job. My thinking was that replication would be a set it and forget it type of feature but it doesn't sound like that from what you are saying. I think I might just keep the setup I have now it is relatively simple and problem free.
    – Lukasz
    Commented May 11, 2016 at 12:16
  • 1
    Definitely not "Set and forget". Replication is very finicky.
    – Chad Mattox
    Commented May 11, 2016 at 15:52
3

I would suggest Transactional Replication. This will be more of a "Real-Time" copy of the data than the one refresh per day that you have now. You can add new tables into the database without affecting the replicated tables.

Improve this answer

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.