Looking for a bit of advice on a transactional replication setup we have. Also allow me to state that I have no exp with replication
Our operations team runs a system with something like 20 sql servers, about 50 different databases. On each server though, one database is replicated to all of them - it holds reference data that all other databases need access to. This db is about 10 gb. It's pretty much all numerical data - ints, and whatnot. hardly any strings.
During normal operation, small changes to this reference data are sometimes made, and replication does a good job of propagating the changes to subscribers.
Every year or so though, we need to import a big pile of new data into this reference db and replicate it out to all the subscribers. Also there's a lot that gets updated, it's not just a simple import. The amount of data that gets imported/updated is about 5gb, again all numerical. What ops is finding is that this massive import and update breaks replication. Plus it is VERY slow. It runs in a few minutes with replication off, but it can take HOURS with replication on. I don't have detail on the errors they are seeing though - can run them down if they would be useful.
Anyway, my question is, is it reasonable to be propagating 5-10gb via transactional replication? Is that too much for it to handle? Seems like a small amount to me but I don't have exp with replication per se - more just with the t-sql aspect of sql server.
What I was thinking would be good would be something like this:
1. import/update new data to a single db that is NOT replicated, call it DB_NEW
2. restore this new db on all 20 servers with a new name, like DB_NEW
3. then, during a maintenance window, do this:
4. turn off replication
5. rename DB on each of the 20 servers to DB_OLD
6. rename DB_NEW on each of the 20 servers to DB
7. turn replication back on again
This seems like a good way to go because most of the long running steps can be run without needing downtime (step 1,2). What I am afraid of is that the "turn off replication" and "turn it back on again" are not as simple as they ought to be - that's the part that's opaque to me.