February 23, 2011 at 5:30 am
Hi,
We originally had database topology where a master sql server receives all (or at insert 99%) of inserts/updates and, using replication, it sent new/updated records to several child servers used only for "select" operations.
The short story is: the system using the database topology changed a lot and now the piece of the system using the child databases need to perform (many) insert/update operations so we have to do ugly things to move data from childs to master.
I wonder if there is an alternative sql topology that fit better our new needs. It seems that we should try to have a single and powerful sql server (now we have 1 master and 3 childs) and using mirroring to other servers just for fault scenarios.
Thanks!
February 23, 2011 at 6:15 am
The first thing that comes to mind is merge replication. Have you looked into that? It's hard to set up and administer, but I think it does what you need.
Other alternatives are to create your code such that it writes the data out to several servers at once. Not pretty, but doable. Another alternative is to look at a transaction management software such as BizTalk which would allow you to write once from your application, to BizTalk, which would then handle the hard process of writing out that transaction to multiple databases on different servers, all as part of a larger, extended transaction, that can be committed or rolled back as a unit.
Out of all the options that came to mind, I'd lean towards BizTalk. Other people might have better ideas though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2011 at 2:22 pm
Thanks Grant Fritchey!
February 23, 2011 at 2:29 pm
merge replication isn't hard to setup but there is an overhead to it
you are basically writing the records on 2 servers
you also have to be careful with identity column issue with merge replication
February 23, 2011 at 2:32 pm
another option would be to use transactional replication from the 3 children to a read only version of the master
February 23, 2011 at 2:51 pm
...and you could also use with transactional replication with updatable subscriptions OR IF your data is mutually exclusive you could use peer-to-peer replication. Both of which are far less overhead than merge as the previous poster mentioned.
If there is true conflict resolution needed, i.e. update at the publisher would need to win out over an update over the subscriber (or some variation) then you would have to go with merge.
It would be good to read up on the benefits of each type though.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply