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
Best Answer
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).