SQL Server 2005 One-Way Replication

Question

In the business I'm working on, we are discussing methods to reduce the reading load in our primary database.

One of the options that has been proposed is to have one-way replication from our main database to the production database. Then the applications will be read from the subordinate database and written directly to the main database. So that...

• Read applications from slave
• Writes applications to the main
• Slave primary updates automatically

What are the main pros and cons of this method?

Answer 1

A few cons:

• 2 points of failure
• The application logic must take into account the delay between writing and reading, since it will not be available immediately from the secondary database

The strategy I used is to send key report data to the secondary database at night, de-normalizing it along the way, so that you can run multitask queries into this database, rather than block tables and steal resources from the OLTP server. I do not use any formal tools for data storage or replication, instead I identify problems that are Ok without up-to-date data and create data structures on the secondary server specifically for these requests.

There are certain advantages to the “repeat all” approach:

• You can run any special request on the secondary, because it contains all your data.
• If your primary server dies, you can reassign the secondary quickly to take over

Answer 2

We use one-way replication, but not from one application. Our applications write the record to the main database, the data is synchronized with the replica database, and reporting tools use this replica.

We do not want our application to be read from another database, so in this scenario, I suggest using filegroups and partitioning into the main database. Using groups of files (especially on different drives), as well as splitting files and indexes, can greatly help in performance.