We often encounter this situation in a production environment:
The front-end OLTP business is busy, but these operational data need to be OLAP, in order to not affect the front-end normal business, so the database needs to be read-write separation.
Here I will be a few can be used to read and write separation of the program summary, the scheme itself is not good or bad, only to see whether it is suitable for business use scene, so only a few of the characteristics of the program listed, encountered specific problems in accordance with their own needs and environmental comprehensive consideration after the trade-offs
Read-Write separation scheme |
Real-time synchronization |
Whether the copy data is directly readable |
Number of replicas |
Minimum grain size |
Copy indexing |
Environment |
Disadvantages |
Mirror |
Is |
No (need to open snapshot, read only) |
1 |
Library |
Whether |
Domain/non-domain (using certificates) |
In high-safety mode for the main library Performance has a certain impact |
Log shipping |
Whether |
Yes (read only) |
N |
Library |
Whether |
UNC way to access |
Replica library disconnects connected users when doing resotre/may affect regular log backups |
Publish a subscription |
Is |
Yes (read and write, but write may result in inconsistent data) |
N |
Table (query) |
Is |
Domain/non-domain |
When there is a large number of DML operations on the main library , there is a certain impact on the Distributor, and the subscription database may have data synchronization delays |
Always on |
Is |
Yes (read only) |
4 (SQL 2012) 8 (SQL 2014) |
Library |
Whether |
Domain |
Non-domain environment is not available |
Comparison of several read/write separation schemes for SQL Server