Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 cluster.

My question is, are the INSERT/UPDATE/DELETE statements on the source database across all published tables guaranteed to be transactionally safe (as the name suggests)? For example, if I perform the following query on the source database:

BEGIN TRAN
INSERT  t1  (mycol) VALUES  ( 0 )
INSERT  t2  (mycol) VALUES  ( 1 )
COMMIT TRAN

Assuming that t1 and t2 are part of the same publication, will the following query on the destination always yield the result below?

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT 'Value in t1: ' + CAST(t1.mycol AS VARCHAR(10)) from t1
SELECT 'Value in t2: ' + CAST(t2.mycol AS VARCHAR(10)) from t2
COMMIT

Result:

Value in t1: 0
Value in t2: 1
share|improve this question
add comment

1 Answer 1

up vote 5 down vote accepted

Well, there are three different types of replication, but they are all transactionally safe, to a degree.

There are three types of replication in SQL Server: Transaction Replication, Merge Replication, and Snapshot Replication.

Transaction Replication

This type of replication transfers each modification that occurs in the master to each of the subscribing databases. This type of replication insures that you will get each changes as it occurs (more or less) on the master system.

This will perform like you describe. There may (will) be a time delay while the replication is moved from the master database to the subscriber database, though.

Merge Replication

This type of replication is essentially Transaction Replication, but it allows for conflicts. (Obviously, there's a bit more to it than that, but that's the essence.)

So, just like Transaction Replication, you will get the data on each subscriber as it changes (plus a little time lag).

Snapshot Replication

This is different from the previous two in that the database does not replicate individual changes. Instead, it takes periodic snapshots and sends down the snapshots.

This is transactionally safe in that you will only see completed transaction being replicated to the subscribers. However, you will not see this in real time (or anything like real time).


So, yes, replication is transactionally safe in that you will only see completed transactions. Any transactions that are partial or rolled back will not be applied to the subscriber databases.

However, they are not transactionally safe in that replication does not maintain perfect consistency between databases. (Thus breaking ACIDity and therefore transactions.) For this, you will need distributed transactions (which is an entirely different concept).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.