Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
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 minu...
#1: Initial revision
by
Alexei
·
2021-10-15T09:00:11Z (about 3 years ago)
What are the disadvantages of using SQL Server Replication - Transactional Replication type?
### 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](https://www.spotlightcloud.io/blog/setting-up-and-configuring-sql-server-replication)) 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?