Sql-server – What type of SQL Server Replication Allows Modifications

replicationsql server

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?

Best Answer

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).
Related Question