SQL Server 2008 Replication - Fixed Overhead of Replication?

Question

We are using SQL 2008 Transactional replication to replicate data from our production database to our reporting database.

We recently created a new 'Global' database that we would like to have replicated to our various production databases around the world. The number of tables in this database is small (less than 30) and the maximum number of rows in any given table might be a couple thousand. We would not be adding tables and data to this Global database very often.

Our DBA said that the "fixed overhead of replication is disproportional to the amount of data that would be actually replicated". I know a little about transactional replication but not enough to refute this argument. Is there really a great deal of 'fixed overhead' with replication, such that if we're not replicating much data, there is still significant resource usage?

Answer 1

I'll throw an answer. The physical overhead of a new replication should not be too big, on an existing replicator. The more difficult part of a replication is the configuration and the monitoring.

You said that right now you have replication only to the reporting server, and you actually "...like to have replicated to our various production databases around the world", so you'll have subscribers all over the world for this new database.

This means you'll have to establish many physical connection between the home server and those new ones (firewall, permissions, SQL Server installation ..etc). You'll have new replications to monitor, new jobs to check, new logs to verify...etc.

In the case of a small database I'd go with a simpler case of copying it's data: just copy the backup over the wire and restore in the new places, use log shipping, anything that would not require cumbersome monitoring and configuration.