Print

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

[TheBoy]

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

[cem_devecioglu]

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

[TheBoy]

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.

[cem_devecioglu]

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..

[TheBoy]

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.

[cem_devecioglu]

"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..

[Martin_1962]

Sorry not a help but I really like Advantage Database Server been using from V3 to V9

[TheBoy]

"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 

[TheBoy]

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?

[TheBoy]

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

[Martin_1962]

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

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.

[Martin_1962]

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

[TheBoy]

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

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.

[TheBoy]

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

[cem_devecioglu]

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"