Recently, I was on a project in which the customer needed real time reporting from a custom order management application. The application ran on a Microsoft SQL Server 2005 back end. The solution needed to provide reports, pulled from live data, without causing any database contention that would negatively impact the performance of the application. Normally, this would not be a problem as you can write reports to read uncommitted and not worry about placing locks on database objects. The problem, as it turned out, is that all end users of the application needed to be able to run reports against the one million+ row order tables. As you can imagine, giving this functionality to over 100 concurrent users can cause resource availability issues on the server whether or not there is any blocking. So, I needed a solution that would address this reporting requirement without negatively impacting the application's performance.
I decided to decouple the reports from the application, so we stood up a second database server for reporting. That server housed an identical data model and to keep the data on the two servers in synch, we used SQL Server 2005's transactional replication. Transactional replication sends DML and DDL transactions from the publishing database to the subscribing database as transactions are committed. This means that table definition changes, stored procedure changes, index definition changes, etc. each get replicated as they occur as well as the inserts, updates and deletes. This design fit our needs and guaranteed the availability of the database to the application.
GOTCHA WITHIN TRANSACTIONAL REPLICATION
We noticed that replication was failing at the subscriber level when we deployed stored procedure changes. As I stated before, stored procedure changes are pushed to the subscriber as they occur on the publisher. At first I thought that the error might be caused by the formatting produced through the SQL Diff tool that we used for deployment. This idea proved to be false when changes through scripting caused the same error. Replication worked fine if I generated a new snapshot and applied it to the subscriber via reinitialization, but some stored procedures, when changed, were causing replication to fail. After some research, we found this Microsoft knowledge base article: http://support.microsoft.com/kb/278324. The article basically states that transactional replication of stored procedures requires that SET QUOTED_IDENTIFIERS ON be specified, or at least SET QUOTED_IDENTIFIERS OFF not be specified since "on" is the default configuration. What replication does is replace the square brackets around a stored procedure name with double quotes when replicating. So when the double quoted string represents a literal as opposed to a system name, as occurs with quoted identifiers turned off, the process will throw an error stating "Incorrect syntax near dbo.uspSprocName", causing replication to fail. Once this issue was resolved and all stored procedures across their three environments were scripted with SET QUOTED_IDENTIFIERS ON, replication ceased to error out.
SQL Server 2008 seems to have been modified to set quoted identifiers on at the subscriber regardless of the setting at the publisher. The only time that an error will be thrown in 2008 is if SET QUOTED IDENTIFIERS is turned off at the publisher and a literal is used in double quotes within the stored procedure.
When using transactional replication with SQL Server 2005 in order to keep database schemas in synch, beware of the SET QUOTED IDENTIFIERS OFF setting. It is a not a well-published issue and can cause some errors that are hard to decipher.