Comparison of several read/write separation schemes for SQL Server

Source: Internet
Author: User
Tags sql 2014 safety mode

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.