Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Please advise for replicatd DB strategy. Expand / Collapse
Author
Message
Posted Sunday, November 10, 2013 10:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 23, 2014 11:22 AM
Points: 52, Visits: 140
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.
Post #1512973
Posted Wednesday, January 08, 2014 5:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:34 PM
Points: 19, Visits: 91
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.
Post #1529167
Posted Thursday, January 09, 2014 4:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 23, 2014 11:22 AM
Points: 52, Visits: 140
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.
Post #1529287
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse