Our website and sql database are hosted away from our offices and is updated via the web by customers and by us in our offices. However due to our slow internet connection we would like to bring a replication server into our office and have the two databases talk to each other when a change occurs.(Customers updating the public database and us update a local one).
I have seen sites mention circular replication is troublesome and may not work, is this the case or is it just a matter of correct configuration?
Any advice, tips etc would be appreciated.
Thanks
John
I guess u need a Transactional replication configured between the two server.
Two scenario u can have
1. Central publishers and many(as many as u wish) read only subscriber
2. Other case in which subscribers also get updated Immediate updating subscribers.
I guess u have a scenario where in both the server will get updated. u can go for transactional replication with immediate updating subscribers.
I see no issues in this and ofcourse correct configuration is must for anything u do with SQL Server.
|||
Also i have one more quick question..how frequent is update on the both the server?
if both server are frequently updated u should consider using merge replication instead of transactional replication with immediate updating subscription as immediate updating subscription is useful for only occasional changes on the subscribers.
|||
I posted this on a different thread, but I think it applys to you as well.
Here is a direct quote from BOL http://msdn2.microsoft.com/en-us/library/ms152570.aspx
The following types of replication and replication options allow you to make changes at a Subscriber and have those changes flow to the Publisher:
Merge replication
There are a large number of Subscribers.
Data is replicated to mobile users.
Replicated data is frequently updated at the Subscriber.
Data filtering is needed so that Subscribers receive different partitions of data.
Replication Configuration Helper (Id buy it!)
Let's face it - setting up SQL 2005 merge pull replication using HTTPS between numerous SQL Server 2005 Express clients and one or more Publishers is an overly complicated exercise.
It should be easy. Why is it so hard?
In a nutshell: The connection chain and security chain is long and when there's a problem the error messages just don't point you to the answer - You have to search for it;re-read the doco; talk to gurus; use logging tools;re-read the error message 100 times; etc. etc.
It should be possible to create a 'Replication Configuration Helper' that steps -step by step - through both the connection chain (App - to SQL Express - to local network components - to the TCP/IP network - to the web server - to the publishers/distributers) and through the security chain (App - to SQL Express authentication - local agents including certificate errors - to ISS server authentication - to Distributer authentication -to Publisher authentication to Article authentication).
Let's face it - the steps occur in sequence and each step can be tested and the appropriate error message raised - TOGETHER WITH INSTRUCTIONS ON HOW TO RESOLVE! (I mean - what's use is an error message when you have to cut and past the error message into a browser and search for ages before you get the instructions on how to resolve the problem?)
So c'mon - someone volenteer to write a 'Replication Configuration Helper'
