Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SunnyDee
Yak Posting Veteran
79 Posts |
 Posted - 2010-01-23 : 18:07:31
|
| Hello:We have a scenario where we have server to server updates. I am thinking that a peer to peer transactional strategy is the best, but I have a few questions. DB1 has a high amount of transactions. DB2 has a low amount of transactions. Currently SSIS is set up to overwrite from DB1 to DB2. Clearly this causes problems in DB2, as it forces the users to enter the data twice.If we implement transactional replication, is it possible to create rules, i.e. certain tables with bidirectional replication and others with only one way replication. Is there any flexibility with checking for the last modified date of a row in a certain table?If it doesn't offer this type of flexibility, is there something else I should be considering? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-24 : 06:10:18
|
| Not my strong suit, so I may be way off beam, but this may give you some pointersYou can build Custom Conflict Detection and Resolution that will decide what to do in the event of a collision.If the servers are permanently connected you can replicate "immediately", that will reduce the number of collisions, obviously.If the servers are offline MERGE replication will take care of merging records where a row has been updated on only one of the servers; ROWGUIDCOL is used to match rows between servers. Hence should not be as "blunt an instrument" as overwriting all the data from DB1 to DB2, as you currently do. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-24 : 10:45:43
|
| if both servers will be updating their copies of the same tables, peer to peer is the way to go.seed your identity columns in a way that there won't be conflicts -- say you have 2 servers, seecd one odd, the other even.there is no built-in conflict resolution with peer-to-peer in sql 2005. last one to update wins. concurrent updates can also generate errors that will cause the distributor agent to fail.while BOL recommends not using identity fields, it is in fact one of the best methods to ensure there are no conflicts -- provided you've properly seeded your identity columns. i've seen where folks will add a column with the server name or id too.all in all, if you're already comfortable with transactional replication, then you'll find peer to peer no big deal.have a look at this as well: http://technet.microsoft.com/en-us/library/ms151196(SQL.90).aspx |
|
|
|
SunnyDee
Yak Posting Veteran
79 Posts |
Posted - 2010-01-24 : 14:25:15
|
| Thanks, I'll take a look at the recommended link. |
|
|
|
|
|
|
|
|
Replication Strategies