SQL Server Replication – Configuring Transactional Replication for OLTP Database

database-tuningdeadlockoptimizationreplicationsql server

We conduct backup/restore (every 24 hours) on an OLTP database for reporting and SSRS queries. This prevents contention from simultaneous writes and reads; preventing locking, blocking, writer-reader deadlocks.

Now we want to apply transactional replication (to obtain more real time data). However, would the same issue occur?

If the reporting subscription database is being written into, updated/deleted, and we have read queries, would we still get the locking, blocking issue?

Although at least it would not be on a live OLTP system.

Best Answer

Using RCSI on the subscriber(s) is a common way to design this type of a scenario to avoid blocking. RCSI will allow readers and writes to play nice together but won't solve writers blocking writers. Since the report queries are readers and the transactional replication is a writer, this feature is a good fit for this.

You just need to make sure that your TempDB is configured to support your workload for the versioning. Also remember that enabling RCSI adds 14 bytes to each inserted/updated row which might cause internal fragmentation.

Related Solutions

Sql-server – Compatibility level for published database and transactional replication

Do these features depend on the compatibility level of the published database or does this rely only on the version of the server?

The short answer is Yes they do depend but to a certain extent only.

Compatibility level can have an affect on the publishing and subscribing databases, with respect to object ownership and the existence (or not) of a particular schema.

Also, have a look at sp_vupgrade_replication

From BOL:

SQL Server 2000 supports common schema changes to an existing publication database. You can add columns to, and drop columns from, a published table without dropping and recreating the publications and subscriptions referencing that table. Schema changes can be replicated during snapshot replication, transactional replication, and merge replication. Column additions and deletions are implemented at the table level and propagated to all Subscribers that receive data from that table.

Important: Schema changes to a published table must be made only through the replication publication properties dialog box in SQL Server Enterprise Manager or through replication stored procedures. Do not make schema changes to published tables using the SQL ALTER TABLE statements in a tool such as SQL Query Analyzer or by using SQL Server Enterprise Manager visual database tools. Changes made to the schema of a published table using these tools will not be propagated to Subscribers.

Refer to :

Sql-server – Snapshot for transactional replication taking too long for large database

Yes, initializing a large database through a snapshot can be very slow and time consuming. If it is appropriate to your case, you should start by restoring a copy of the database to the target machine.

In setting up the subscription choose "allow initialization from backup files".

Because of limitations in the user interface for setting up replication, you will need to create a script instead of relying on the UI tools. There is a blog post on this at:

http://blogs.msdn.com/b/repltalk/archive/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication.aspx

The post from msdn includes the code sample:

exec sp_addsubscription @publication = N'Repl2000', …..
    @sync_type = N'initialize with backup', @backupdevicetype='Disk',
    @backupdevicename='C:\Repl2000_RestoreThis.bak'--the last backup used to restore on the subscriber 
go
exec sp_addpushsubscription_agent …….
go

The article also includes some troubleshooting tips.

Best Answer

Related Solutions

Sql-server – Compatibility level for published database and transactional replication

Sql-server – Snapshot for transactional replication taking too long for large database

Related Question