Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs

Dashboard
Notifications
Mark all as read
Q&A

What are the disadvantages of using SQL Server Replication - Transactional Replication type?

+4
−0

The context

A reports related process is directly reading the production operational database once about two hours. This involves reading all the data from some 70 tables which takes a couple minutes to complete.

My manager suggested that this is not OK and I concur also from a technical perspective, since those reads might block writers for a while.

The production database is part of a failover cluster: under normal circumstances the application uses the primary only and goes to secondary only if the primary is not available.

The secondary is a complete mirror (not sure about the replication mechanism) of the primary: data, users etc.

Solution proposed by management

We suggest to the reports dev team to use the secondary instance for reading the data. It is easy to do. However, there is no way to prevent the users from directly reading from the primary since rights are 1:1 replicated between the primary and the secondary.

Solution proposed by our team

Define an SQL Server Replication of Transactional Replication type (wizard here) only for the required tables and use a much smaller database to store the replicated data.

This clearly solves the access issue, but incurs a little bit of cost (configuration and having another database).

The application has a modest number of SQL writes (in the tens of thousands per day).

Are there any other disadvantages associated with defining an SQL replication as suggested above?

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

0 comment threads

0 answers

Sign up to answer this question »