Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

    Unanswered: replication advice

    hi folks! long time no post!

    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.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    One way that i have done it in the past is to take a backup, propagate it, then start without snapshot. here is a link:

    Initializing a Transactional Subscription Without a Snapshot

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    thanks for the link - i'll look into this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •