SQL Server Publish Subscription (read/write Detach)

Distribution and subscription of distributed development

Publish subscription: Data real-time backup synchronization

Software environment: SQL server2008 R2

Hardware environment: Depending on the amount of data and the tasks assigned by the corresponding machine

Number of machines: depending on the split line standard (i.e. split line where data is stored separately)

Role

1. When the database server goes wrong, we also have a backup of its normal working
2. Can be used for load balancing when a server is not loaded
3. Database server can be uninterrupted without loss of migration
4. When the primary server is attacked or when the other service Sync machine can be emergency

Meaning: We can be used for two servers, one machine for adding and removing, and another machine as a query, in order to prevent read-write separation (that is, publish subscriptions) of the information has a difference, you can make a choice, that is, the current date of the week (time depends on the date of publication and subscription, because the subscription is automatically set time Data queries, such as continuous subscriptions or subscriptions at a time, run on the published machine, and data that precedes the current day of the week is run on the subscribing machine.

Extension

1. Create a temporary (publish) DB with an extension (subscription) DB, when you want to insert data in the database, inserted into the temporary DB, temporary DB published data, extension DB subscription to this data (the table can be subscribed according to the amount of data in the table fluctuations, the amplitude of small without subscription). Note: The temporary DB and all extension db in addition to some specific tables (amplitude, published tables), other data (metadata, such as: Issuetable, actiontable ...) You can ensure consistency to sacrifice extension machine hard disk space in exchange for runtime memory space and efficiency.
2. The ticket of extension db reaches millions of or the subscription is split for one year (the split line can vary depending on the case), and the extension DB1 is created. When the extension DB1 reaches the split line, the extension DB2 is created, and when the extension DB2 reaches the split line, the extension DB3 is created ... (if the subsequent discovery of the extension DB2 extension DB1 or some of the machine's data traffic is not large, these db can be more than 1). When you create a DB2, you can delete the same data in the temporary DB as in DB1 (delete the data that was published in the temporary DB and that has been subscribed to by the extension db. Note: If you implement this scenario, you first need to verify that the deleted data has been successfully subscribed to, followed by the real-time release of the subscription, when the temporary DB data is deleted, the data of the extension DB is also deleted, you need to check whether you need to cancel the publish subscription mechanism when the operation is performed)
3. In the database access layer, the configuration database corresponds to the table, that is, the partition line of the table in the database access layer according to the need to determine the data in that extension db, and configure the corresponding connection string.

Other: The database distributed development also has the partition table (sharding), the basic idea of the sharding is to divide a database into several parts into different database (server), thus alleviate the performance problem of single database. For databases with large amounts of data, you can use vertical slicing, which is to slice out tables that are closely related (such as the same module) and place them on a server. If there is a lot of data for each table, it is appropriate to split the table's data into multiple databases (servers) by a rule (for example, by ID hash) for horizontal segmentation.

Summary: Publish subscriptions compare to Sharding, where a publish subscription is at the expense of the physical storage space in exchange for query execution speed, and, by comparison, may be more difficult to maintain for a published subscription. For example, the metadata in each extension db is duplicated, and if the name of one of the tables (Issuetype) is changed, we need to write a script to perform an update on all extension DB name ( This does not use a publish subscription to the extension DB to guarantee the metadata of the extension db, because if there is too much extension db there is no way to guarantee their real-time. )。 Sharding is based on the association between some tables of the sub-Library, for the metadata will not be duplicated, each library is independent data, only when the query may join to multiple databases or multiple servers, which greatly reduces the efficiency of the query.

	Publish and subscribe	Sub-database Sub-table (sharding)
Principle	Sacrifice physical storage space for query execution speed	Reduce the performance of a single database by splitting a database into multiple parts onto different databases (servers)
Metadata	Repeat	Do not repeat
Maintenance Difficulty	Big	Small
Query efficiency	High	Low
The connection between servers	Small (the data on each machine is separate)	Big
Need to change proc	No	Yes
Query whether multiple machine joins are required

SQL Server Publish Subscription (read/write Detach)