Please advise for replicatd DB strategy.

  • Dear friends,

    though I use sql server 2012, this is the replication forum so I post here.

    I have 4 db with replication configuration for let's say 10 articles. Other tables in locations have exactly same schema but their data is specific to location like employee names so they are not replicated.

    I will use the simplest and most problem-free backup solution for each of them. First of all its clear that every backup will be full backup. Also I have a luxury to stop sql services in locations and copy database files.

    MS documents dives examples like "if merge replication is made in every 10, 20 30 days.. etc". but I use continious merge replication and data is replicated in about 60 seconds.

    my question is

    1. what do you suggest ?

    2. can I use this strategy:

    backup each location to their local server with an application like sqlbackupandftp

    in this option, I don't know howto restore them one by one and all of them together.

    I think, when I need to restore a database, it will be for one location because its almost impossible all 4 sites would have problem at the same time.

    when I restore publisher or subscriber, how does it effect other sites ?

    any comments or simple step by step sample licks will be appreciated.

    best regards.

  • I am not quite sure, but it sounds like you would want to use Merge replication with possible filters on the articles by "location" or location id. Without seeing the schema I can't say categorically. How would you take the master tables and filter each of them so that "data is specific to location"? If it is just a matter of filtering each table with a "where locationid = X", where x is a scalar (including a scalar function which will not change over time) you may be able to setup static filters. If not you would need to use join filters.

    Regarding backup and restore, you can't do this with replication in place. It has to be dropped. You can backup replicated databases but restoring them won't restore replication. To do that you need a more or less full sql backup including master, msdb, and distribution. I've never even attempted to do a restore on this basis, and I feel worth more risk and effort than it would be worth.

  • yeah, its merge replication. though I had difficulties about ID column, I made it. but if you make mass updates in database (which I need to) and meanwhile users continues to work then it causes a lots of pain. And what I did not understand is, why replication monitor screens so poor ? stopping them, starting over, error messages, everything.. its not user friendly.

    anyway, thanks for your comments.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply