Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Please advise for replicatd DB strategy.


Please advise for replicatd DB strategy.

Author
Message
aykut canturk
aykut canturk
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 160
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.
Michael Oberhardt
Michael Oberhardt
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 93
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.
aykut canturk
aykut canturk
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 160
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search