Replication

Stored procedures can also be used to work with databases involved in replication. By creating a specialized custom stored procedure, which is placed on each publishing database, it is possible to resolve any row UPDATE conflicts that may occur. Stored procedures can aid in replication processing, and also ensure that a replicated database is optimized. We won't be teaching you about replication in this book, but we bring up the subject to ensure that you are aware of potential problems that can arise with stored procedures in a replication scenario.

Consider a scenario where either a stored procedure or T-SQL updates or deletes a large number of rows. We can't really put a deterministic figure on this, as there are a large number of factors, such as connection between the databases, network traffic, network size, and data type content of each row, defining this level.

For example, the publisher places each update into the log reader for publication to each subscriber of the database. The Distribution Agent takes one row at a time, distributes them to the subscriber, and updates the subscriber database. If there is more than one subscriber, or if there is a delay due to connection speed or network traffic, the subscriber can lag behind, if there is no break in processing at the publisher's side.

In a real life scenario, at one installation, we had a very fast publisher and subscriber link (between London and New York). Traffic on one table was relatively light, but due to stock market conditions an update had to take place. The developer in question didn't realise that they had to update 100,000 rows, so off they went, and altered the data. The publisher updated fairly quickly, but the subscriber took 3 hours to catch up.

How does replication, and problems like this fit into this book? In the above example, if the developer had placed the updates in a stored procedure and made it a procedure execution article, then SQL Server would have replicated the execution of the stored procedure and not the updates that took place.

Important

You have to be 100% sure that the data in the publisher and the subscriber databases are consistent. If not, you can have a failure on one of the databases, but a success on another, thus compounding the data inconsistency. This can come about as a result of other transactions and data modifications being successful on the publisher, but not yet applied by the log reader on the subscriber.

It's preferable to have the stored procedure set up as a serializable procedure execution article. By setting the isolation level to SERIALIZABLE within the stored procedure, we can make a stored procedure into this form. We'll see more information on isolation levels in Chapter 3.

If we have an isolation level that can allow dirty reads, then we can insert data into a table before a previous modification has been committed.