December 27, 2009 at 6:19 pm
Hi All, I have 6 servers located at our different office branches and a main server at the Head Office. All the servers would be running the copy of the same database initially. All servers including the main server would have read/write operations. I need the data from all the branch servers to be propogated to the main server. And similarly modifications at the main server back to the branch servers. Now is there a way to restrict data from the Main server to branches so that only data related to a particular branch is propogated to that branch and not all branch data is propogated to each branch. (The tables are common for all branches). Also to add that I have a static IP for the Main server only and also considering connectivity may go down at times. What would be the best method in this scenario, Mirroring or Replication? And the configuration also?
Thanx in advance..
December 27, 2009 at 7:39 pm
You can't use mirroring and have read/write at both ends. Only replication will allow this.
I'm not sure if bidirectional transactional replication will work for multiple branches. It can definitely restrict what is published to which place, but I'm not sure about how it works with multiple modes. My guess is that you'd have a separate publication for each branch from the main instance, so essentially 6 * the number of tables for publications.
December 28, 2009 at 6:49 am
Agree with Steve, you can't use Mirroring as a solution for this scenario.
To filter the data for each branch, you got to have individual publication for each branch filtering only the data pertaining to that branch. You could use Transactional publication with updatable subscriptions.
You need to keep in mind that there might be conflicts if the the data is updated at the sunscriber (branch) in that case you need to go to the Publication properties and under updatable subscriptions choose the property for Conflict resolution policy. This step must be performed immediately after publication is created and before a subscriber is initialized.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 28, 2009 at 7:45 am
I guess you should be good with a single publication with all the tables.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 3, 2010 at 6:19 pm
Multiple publications could be a headache. Coz there are more than a 100 branches for our company and the new system is yet to be implemented at each place. Which would finally mean 100 plus publications at the Main server?? I heard that for transactional replication to be working the connection can never be down. Any ideas about Merge replication??
January 3, 2010 at 9:07 pm
sarithanr82 (1/3/2010)
I heard that for transactional replication to be working the connection can never be down.
Not sure this is completely true.
Any ideas about Merge replication??
It is more complicated than transactional replication. It has conflict resolution and even there you got to have individual publication for each branch.
Have you considered SSIS for this?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply