Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[TheBoy]

I can bluff my way around MS SQL server, but got a question for any experts/architects about replication.  Looking at Transactional Replication.

Got multiple SQL Servers that can communicate via slow WAN links which feasibly may not always work. Each SQL Server deals with SQL queries and updates from multiple, local clients. When a client creates a new record, it indexes and uses the seed, that set to autoincrement.

Need to have a single database that is replicated (entirely) around those SQL Servers.

OK, so I will set up a distributor, and each one as a publisher and subscriber, no issues with that.


My question(s): Conflicts.

What happens if 2 records are created at exactly the same time (or at a time when the WAN link isn't working) at different sites? Will they get same seed?

What happens if one client reads a value from a field, decrements it, and write is back, and another client at another site does similar before its replicated? Does it only decrement once? Twice?


Anyone KNOW the answers? Is transactional replication the best replication to use?

[cem_devecioglu]

reading and thinking..

[Jimbob]

If I was in work I can ask the DBA's unlikely to be in for a week or so.

Can mail them if you dont get the true answer here 

[cem_devecioglu]

the most critical question is what development environment is used.. if .net solution very very easy..

[cem_devecioglu]

you can use unique identifier data type which brings definitely a unique value even across the servers..

http://msdn.microsoft.com/en-us/library/ms187942.aspx

[cem_devecioglu]

for those multiple client server environments database defined autoincrement value or value from a fixed server/table keys/indexes are not suitable..will bring many problems..

[cem_devecioglu]

if transactions are critical (sure is)  the best way to use : microsoft distributed transaction coordinator..

and you can use 2 phase commit in every possible way..

replication must be used mostly for reporting purposes I think.. as I worked for a bank previously ,
we used mostly transaction coordinators..

Here is how it works :

1 - you define the remote servers to insert update delete for every server and log on user password parameters..

2 - you define the triggers for the related tables for inserts updates including the remoteserver.dbo.tablename..

3  - you start msdtc server service in every server ..

4 - from the application you can start inserts and watch what goes on.. (of course by using unique identifier) ..

5 - my advice will be also using the client user name and recording time extra fields..

6 - you can use dtcping utility to check also if things go wrong

[Taxi_Driver]

I havent a clue.....but and this maybe a daft suggestion, Microsoft might know  :-/

Dunno if that would cost you a few sheckles, depends on support contract I guess  :-/

[cem_devecioglu]

but as a note the application must be specifically designed for that kind of use..

trying replication also can work but will be more complex to manage and maintain..

by the way msdtc will not be insistent for continuous wan link..when its open it will start to send..

[cem_devecioglu]

I can bluff my way around MS SQL server, but got a question for any experts/architects about replication.  Looking at Transactional Replication.

Got multiple SQL Servers that can communicate via slow WAN links which feasibly may not always work. Each SQL Server deals with SQL queries and updates from multiple, local clients. When a client creates a new record, it indexes and uses the seed, that set to autoincrement.

Need to have a single database that is replicated (entirely) around those SQL Servers.

OK, so I will set up a distributor, and each one as a publisher and subscriber, no issues with that.


My question(s): Conflicts.

What happens if 2 records are created at exactly the same time (or at a time when the WAN link isn't working) at different sites? Will they get same seed?

What happens if one client reads a value from a field, decrements it, and write is back, and another client at another site does similar before its replicated? Does it only decrement once? Twice?

Anyone KNOW the answers? Is transactional replication the best replication to use?

the best and appropriate way is to avoid incrementing or decrementing common tables or numbers..

you can use two phase commit and many kinds of locking but these will cause real headaches..(done that and lived many problems)

ps: I hope I could explain the picture

[TheBoy]

thanks. Not going to modify the app too much (needs a rewrite, but thats a longer term goal), so looking for SQL Server to do the replication in background.

My orig Qs remain:
Using Transactional server-server replication, will I automatically avaoid the conflicts in 1st post?

[cem_devecioglu]

thanks. Not going to modify the app too much (needs a rewrite, but thats a longer term goal), so looking for SQL Server to do the replication in background.

My orig Qs remain:
Using Transactional server-server replication, will I automatically avaoid the conflicts in 1st post?

without seeing the actual application code and data types its hard to predict :-/

but if you add extra fields like server name or such  and include in primary key fields in addition to autoincrement values this may help me thinks..

[tunnie]

shouldn't the distributor prevent DB locks / simultaneous records being written?

[cem_devecioglu]

shouldn't the distributor prevent DB locks / simultaneous records being written?

imho easiest way is to set up the scenario and test.. :-/

the solutions may be applied after..

[TheBoy]

shouldn't the distributor prevent DB locks / simultaneous records being written?

WAN (possibly unreliable) links mean it has to use queued transactional replication.  So distributor may not always be available