April 30, 2007 at 10:32 am
I am helping a small company and am seeking experiential advice.
This small company wishes to replicate their database, with each publisher/subscriber on a separate server in a different city. They are networking together via a "business package" from a cable company. Their broadband width is "acceptable".
They desire a "merged" replication for their database (aka, both ways). Their reason is because despite all network tweaking, they are unable to achieve satisfactory throughput from one city connecting to a server in another (aka, using one primary database).
Here's a brick wall: The database is the repository for a purchased software product, so NO flexibility. And there are too many issues to truly replicate them (tons of views, inserts not specifying columns, etc). Hence I've suggested they nix the idea altogether.
Has anyone encountered some similar situation? Here's a small-to-medium company, using propriety software that stores its data in SQL Server 2000, and they are claiming not enough network speed. It has been a long time since I've found this to be nothing shy of someone who hasn't set up their network well, but I'm not a network guru. I'm only a lowly DBA/developer with almost 30 years experience, but a non-network-guru nonetheless.
Does anyone have experience with something like this and wouldn't mind sharing their wisdom?
May 2, 2007 at 4:32 pm
The SQL2005 replication options are
Snapshot
Transactional
Merged
Peer-to-Peer [new]
or browse this for more details
http://msdn2.microsoft.com/en-us/library/ms151198.aspx
I suggest you have ONE publication for cope with ALL the branches [you can zone by geography to avoid n-1 connections from each on n branches!]. Sounds like merge is the way to go if each genuinely can post changes [but most businesses have a hierarchical trickle-down].
Realise that each table MUST have a Primary Key (PK) [except for Snapshot which is NOT right for your requirements/topology]. Quite a number of 3rd-party apps lack such essentials!
The SQL Standard edition [+above] can pub and sub, but Express can only be a sub
Suggest you earmark some time to define the business case thoroughly and review the technical state of current design. Which tables need to be published? 1-way hierarchical or up+down ? do you have PKs? what about audit tables? how to separate the identity values?
Recognise that overnight batch processes may be heavy on work-in-progress tables. If these are replicated, the results of every interim change may have to be propagated to all other nodes (ie not written to be repl-friendly). Better to sideline WIP to another db [Recovery=SIMPLE] and put FINAL contents into mainstream db.
Then experiment with topologies and measure latency between branches. You can have lotsa fun with virtual servers here !
You need someone with real-world repl experience to assist - eg we did LFB [starting with SQL v6.5 !] with 120 fire stations that had to operate autonomously yet communicate with central HQ for F29 forms etc [a bothway flow]. Complex!
HTH
May 2, 2007 at 4:56 pm
After digging into this it became obvious that you almost have to design replication as an application consideration, it's that integral in the database.
Thank you for your response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply