Omega Owners Forum
Chat Area => General Discussion Area => Topic started by: TheBoy on 16 July 2009, 18:37:25
-
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?
-
reading and thinking..
-
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 :y
-
the most critical question is what development environment is used.. if .net solution very very easy..
-
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
-
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..
-
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
-
I havent a clue.....but and this maybe a daft suggestion, Microsoft might know :-/ :D
Dunno if that would cost you a few sheckles, depends on support contract I guess :-/
-
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..
-
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 :-[
-
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?
-
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..
-
shouldn't the distributor prevent DB locks / simultaneous records being written?
-
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..
-
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 ;)
-
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..
Yeah. 2 Windows servers being built as we speak :(
-
here are some related info :
question :
From what I've read, SQL2000 shouldn't lock tables during the initial snapshot for transactional replication, I even checked the option :
"Do not lock tables during snapshot generation. All Subscribers must be servers running SQL Server version 7.0 or later."
However, running the initial snapshot last night DID lock tables needed by other processes. Am I missing something, or is Microsoft lying ?
Help ! I can't be limited to running snapshots on the weekend.
answer :
After replication begins, the Snapshot Agent places shared locks on the publication tables. The locks prevent changes until a record indicating the start of the snapshot is entered in the log file. After the transaction is received, the shared locks are released and data modifications at the database can continue. The duration for holding the locks is very brief (a few seconds) even if a large amount of data is being copied.
Have you got any columns enabled with IDENTITY property?
Articlehttp://www.sql-server-performance.com/snapshot_replication_tuning.asp for your information.
ps: sql 2005 have different and more complex algorithms.. so better than 2000..
and some more related discussions
http://www.sqlservercentral.com/Forums/Topic709082-146-1.aspx
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=93&threadid=88797&enterthread=y
http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.replication/2004-06/0581.html
-
Not too bothered about initial snapshot - that can be done out of hours with the application offline if needs be, as it will only be a very rare occurance.
Its the ongoing replication in normal day to day use that is the issue.
I've been all over the internet looking for the answers to my original question, when I find an answer, it seems to contradict itself.
-
Not too bothered about initial snapshot - that can be done out of hours with the application offline if needs be, as it will only be a very rare occurance.
Its the ongoing replication in normal day to day use that is the issue.
I've been all over the internet looking for the answers to my original question, when I find an answer, it seems to contradict itself.
:-/
if your application is for stock control (I guess) and if you use the central server for checking momentary stock conditions I think wont give healthy results over a wan anyway .. :-/
instead if leased lines are used ,so continuous data flow can be achieved may change the picture seriously..
-
Not too bothered about initial snapshot - that can be done out of hours with the application offline if needs be, as it will only be a very rare occurance.
Its the ongoing replication in normal day to day use that is the issue.
I've been all over the internet looking for the answers to my original question, when I find an answer, it seems to contradict itself.
:-/
if your application is for stock control (I guess) and if you use the central server for checking momentary stock conditions I think wont give healthy results over a wan anyway .. :-/
instead if leased lines are used ,so continuous data flow can be achieved may change the picture seriously..
Close, its for Point of Sale (which does include stock control)
Its for my brothers 2 retail outlets. Currently, each one has its own distinct, seperate database, which has worked well for years, but obviously to drive efficiencies, combining into 1 large database makes sense. For performance reasons, each site needs a local, updatable copy of the database.
Current 2 shops are connected via a high performance, low latency link, but as he expands, such links may not be possible (fast links are outrageously expensive in the UK), so need to build in the fact that the links may not be great now.
Its an old application, and at this stage I don't want to make massive changes to it. It will get rewritten, but not this year. These combining of database needs to be in place ideally in next 4 weeks (including all coding changes in the app), so really tight timescales.
I understand the issues regarding any stock reporting may not be 100% live - thats not an issue.
What I need to know is how I can replicate this database across multiple (currently 2) sites, and if SQL will reuse identity seeds at the 2 different sites if each site creates a new record at exactly the same time. And also if both outlets sell an item at exactly the same time, what happens to the stock level.
-
"For performance reasons, each site needs a local, updatable copy of the database"
Long time ago on a application I had a succesfully working software which was written as a client/server style (exe file from vb6) of which was connecting synchronously from 9600 baud leased lines over 900 kms.. and could be considered fast (few seconds waiting for a remote insert)
imho if you are going to redesign the software making a central single database server will solve
most of your problems and simplfy the case..and for back up that can be replicated also for urgent situations..
consider the situation that your brother will spread around the country with many shops..
"These combining of database needs to be in place ideally in next 4 weeks (including all coding changes in the app), so really tight timescales."
another method that you can effectively use is directly modifing the insert and updates and locks and
doing it on multiple servers from exe itself..(which will be more quick I think because you need only
another ado connection defined in code) so there will be no need for replication or other external support
"SQL will reuse identity seeds at the 2 different sites if each site creates a new record at exactly
the same time. And also if both outlets sell an item at exactly the same time,
what happens to the stock level"
if you dont use locking schemes the results will be unpredictable and random things will happen..
another solution for that problem is using a single identity table only on one server which will be commonly used..
but still requires some code changes..
-
Sorry not a help but I really like Advantage Database Server been using from V3 to V9
-
"For performance reasons, each site needs a local, updatable copy of the database"
Long time ago on a application I had a succesfully working software which was written as a client/server style (exe file from vb6) of which was connecting synchronously from 9600 baud leased lines over 900 kms.. and could be considered fast (few seconds waiting for a remote insert)
imho if you are going to redesign the software making a central single database server will solve
most of your problems and simplfy the case..and for back up that can be replicated also for urgent situations..
consider the situation that your brother will spread around the country with many shops..
"These combining of database needs to be in place ideally in next 4 weeks (including all coding changes in the app), so really tight timescales."
another method that you can effectively use is directly modifing the insert and updates and locks and
doing it on multiple servers from exe itself..(which will be more quick I think because you need only
another ado connection defined in code) so there will be no need for replication or other external support
"SQL will reuse identity seeds at the 2 different sites if each site creates a new record at exactly
the same time. And also if both outlets sell an item at exactly the same time,
what happens to the stock level"
if you dont use locking schemes the results will be unpredictable and random things will happen..
another solution for that problem is using a single identity table only on one server which will be commonly used..
but still requires some code changes..
I cannot rely on the WAN link being there, so cannot do simultaneous writes (SQL's transactional replication does offer instantaneous updates itself, which would solve all my problems, but is reliant on the 'master' database being permenently available, which for a WAN link, I cannot guarantee).
Transactional replication with queued updates is no good either - tested it, certain data types cannot be updated on anywhere other than the master db, again, no good for WAN links.
And to top off my day, one of my brother's SQL servers has rather spectacularly gone bang. Here's hoping once the hardware issue is resolved, the data is in tact :o
-
Sorry not a help but I really like Advantage Database Server been using from V3 to V9
Sorry, this isn't a job for toy databases ;)
What replication options does it offer now?
-
LOL - one of Microsoft's own samples for the type of setup I'm after looks interesting. Using peer-peer replication. All looks perfect until to read the bit that only one database server should be in use at any one particular time. What bloody use is that :o
-
Sorry not a help but I really like Advantage Database Server been using from V3 to V9
Sorry, this isn't a job for toy databases ;)
What replication options does it offer now?
TOY >:( I am sure I have seen performance figures for ADS being 138 times faster ;D
There is replication, but we are not using it yet as we need to move to the data dictionary instead of normal access, but we are looking into it for our biggest customers.
Lets just say that we are running one of the biggest factories (possibly biggest) of its type in the UK on it.
-
Sorry not a help but I really like Advantage Database Server been using from V3 to V9
Sorry, this isn't a job for toy databases ;)
What replication options does it offer now?
http://www.sybase.com/products/databasemanagement/advantagedatabaseserver/replication
http://www.sybase.com/files/Data_Sheets/advantage_replication_ds.pdf
-
Sorry not a help but I really like Advantage Database Server been using from V3 to V9
Sorry, this isn't a job for toy databases ;)
What replication options does it offer now?
TOY >:( I am sure I have seen performance figures for ADS being 138 times faster ;D
There is replication, but we are not using it yet as we need to move to the data dictionary instead of normal access, but we are looking into it for our biggest customers.
Lets just say that we are running one of the biggest factories (possibly biggest) of its type in the UK on it.
My own 20yr old database engine is faster than anything off the shelf... ...for specific queries. Any vendor can find a set of queries that will run optimised on their product and unoptimised on a competitors. Don't believe the hype ;). Oracle's Oracle and Microsoft's SQL Server are deemed as the fastest RDBMS systems for midrange servers for a reason.
But my current issue, perfomance isn't my primary concern, its reliably distributing the database across multiple sites, and allowing full updates at those sites.
-
Sorry not a help but I really like Advantage Database Server been using from V3 to V9
Sorry, this isn't a job for toy databases ;)
What replication options does it offer now?
http://www.sybase.com/products/databasemanagement/advantagedatabaseserver/replication
http://www.sybase.com/files/Data_Sheets/advantage_replication_ds.pdf
Thanks for info.
They provide absolutely zero info in that datasheet, but seems its some kind of transactional replication with queued updates in SQL server terms. Which is even less flexible than MS SQL server
-
I've found this for sql 2008 peer to peer
"Although peer-to-peer replication enables scaling out of read operations, write performance for the topology is like that for a single node. This is because ultimately all inserts, updates, and deletes are propagated to all nodes. Replication recognizes when a change has been applied to a given node and prevents changes from cycling through the nodes more than one time. We strongly recommend that write operations for each row be performed at only node, for the following reasons:
If a row is modified at more than one node, it can cause a conflict or even a lost update when the row is propagated to other nodes.
There is always some latency involved when changes are replicated. For applications that require the latest change to be seen immediately, dynamically load balancing the application across multiple nodes can be problematic.
Peer-to-peer replication in SQL Server 2008 introduces the option to enable conflict detection across a peer-to-peer topology. This option helps prevent the issues that are caused from undetected conflicts, including inconsistent application behavior and lost updates. By enabling this option, by default a conflicting change is treated as a critical error that causes the failure of the Distribution Agent. In the event of a conflict, the topology remains in an inconsistent state until the conflict is resolved manually and the data is made consistent across the topology. For more information, see Conflict Detection in Peer-to-Peer Replication"
schemas are interesting
http://207.46.16.252/en-us/magazine/2006.07.insidemsft.aspx
some close examples
http://www.databasejournal.com/features/mssql/article.php/3814591/Peer-to-Peer-Replication-in-SQL-Server-2008--Configure-a-two-node-topology.htm
from last link :
"Although conflict detection is introduced in Peer-to-Peer replication in SQL Server 2008, the conflict resolution is simply based on the peer ID. If a row identified by the primary key value is inserted, updated, and/or deleted at more than one node, the Distribution Agent will fail to deliver commands and remain in a stalled state until the conflict is resolved manually. You will see an example in this article series. Second, Peer-to-Peer replication builds on the foundation of transactional replication, and can be viewed as a multiple-node transactional replication with each node published to other nodes, and at the same time, subscribed to other nodes. Therefore, writes on one server are propagated to other servers in near real-time, thus making data changes available to other locations [size=14]quickly[/size] my note : this depends on wan speed and network availability . Peer-to-Peer replication also provides data redundancy and read scalability as each server has a copy of the replicated data"
-
OK, think I have resolved the issue, many thanks for all your help :y
For those interested, Merge Replication, set at column level tracking. With this scenario, and the database structure (few minor changes needed), and business processes, there should be little chance of conflict.
Now to dust off my copy of VB6, if I can remember how to use it (sadly, this app can't go to .net yet due to some funky direct hardware control) :-[
-
OK, think I have resolved the issue, many thanks for all your help :y
For those interested, Merge Replication, set at column level tracking. With this scenario, and the database structure (few minor changes needed), and business processes, there should be little chance of conflict.
Now to dust off my copy of VB6, if I can remember how to use it (sadly, this app can't go to .net yet due to some funky direct hardware control) :-[
Glad to hear you sorted.. :y
vb6 still in heavy use by many programmers..
and quickly solves many urgent problems and have
some different properties that I prefer instead of .net..
and the exe code is very small.. :y