SQL Server local replication subscription implements database server read-write detach

Original: SQL Server local replication subscription for database server read-write separation

The previous paragraph Echosong wrote about MySQL data synchronization to realize the separation of business read and write, today we look at SQL Server replication subscription implementation of data read and write separation

SQL server replication is relatively powerful compared to MySQL replication

I. Noun interpretation 1, the composition of the duplication of the organization (analogy newspaper circulation): 1), publishing Server (newspaper publishing)

Production maintenance data source, review all published data changes sent to Distributor (Post Office)

2), distribution server (Post office)

The distributor includes the distribution database and stores metadata, historical data, and transactions.

3), Subscribers (subscribe, readers)

Keep a copy of the data and receive changes to the published change. Depending on the replication options that are implemented, it may also allow the updater to update the data and replicate it back to the server or to other Subscribers.

2. Replication type 1) snapshot replication

Snapshot replication is the process of copying and distributing data exactly as it appears in the presence of database objects.  Snapshot replication does not require continuous monitoring of data changes, because changes to published data are not propagated incrementally to subscribers, but are periodically replicated. Applies to data that is primarily static, such as when copying data warehouses to data marts that allow obsolete copies of data over time

2) Transactional replication

With transactional replication, the initial snapshot data is propagated to the Subscriber, so the Subscriber has a so-called initial load, which is something that can begin to work. When data modifications occur on the publishing server, these individual transactions are captured and replicated to subscribers in a timely manner. and preserves the transaction boundary, when all changes are propagated, all subscribers will have the same value as the propagation server that requires data modifications that are often propagated to subscribers within a few seconds of their occurrence to require that the transaction be an atomic subscriber in the normally connected to the publishing server. Application cannot tolerate high latencies for subscribers receiving changes

3) Merge replication

Merge replication allows a group of sites to work autonomously, online or offline. Then at some point in the future, the data is merged into a single unified result based on modifications or insertions that occurred at multiple replicated sites. The initial snapshot is applied at the Subscriber as its initial load, and then SQL Server tracks changes to published data on the publishing server and at the Subscriber. Data is synchronized between servers at predefined or scheduled times, or on demand. The update is then applied independently on multiple servers.  This means that the same data may have been updated by the publishing server or by multiple subscribers, so conflicts will occur when data updates are merged.  Multiple Subscribers need to update the data at different times and propagate that data to the publishing server and other Subscribers. Subscriber needs to receive data, change data offline, and then synchronize changes to the publishing server and other subscriber applications the latency requirements can be high low site autonomy is critical

3, Copy mode 1), push mode (push)

The Distribution Agent runs at the Distributor, which is push mode

2), Pull mode

The Distribution Agent runs at the Subscriber, which is pull mode

Ii. Workflow 1, snapshot replication

　　　　

1), the Publisher, the database to be published a snapshot of the whole,

2), the subscriber's Snapshot Agent reads the snapshot from the publisher and places it in the local snapshot folder.

3), the publisher agent of the Subscriber publishes the snapshot in the snapshot folder to the Subscriber. History and snapshots are recorded at the Distributor.

2. Transactional replication

1), initial data and schema (through snapshot replication), from here can be reflected in snapshot replication, is the basis of all replication. Snapshot replication establishes a subscription database at the Subscriber.

2), after the data modification of the publisher, write the transaction log,

3), the log Read agent of the distributor reads the transaction log of the changed data, and stores these transaction logs in the publisher's publication database.

4), the Distribution Agent of the Distributor distributes the transaction logs from the distribution database to the individual subscribers, and then records the history and errors in the distribution database

Third, the specific operation process 1, release replication (push mode)

1), expand the "Replication" node under SQL Server 2008 server, find "local publishing" and "local subscription" two nodes, right click on the "Local Publishing" node, select "New Publication"

　　　　

2), set the Distributor and publisher to the same (push mode)

3), select the Data element location

4. Select the database object to synchronize

5. Release replication type

6. Set up synchronization schedule

　　

7. Set Agent Security

8, publish the completion of the name

2. Subscription replication

1), right click on the "Local subscription" node, open, "New Subscription Wizard" dialog box

　　　　

2) Search the Publisher to subscribe

3), because the above published and distributed is the same server, so select push subscription

　　　　

　　

4), select the object accepted by the subscription (that is, the database to obtain the data, the above published the test library Pce_admin_info, where the native test1 to accept, synchronization if no table will automatically create a table in the Test1 library)

5), set up connection security

6) Follow the default point next, complete the subscription

3. Viewing effects

Synchronize the above steps, synchronize the Pce_admin_info table of the test library of a server to the test1 of B server, create the table automatically after the first execution of the subscription, and then set the Synchronized Update table according to the schedule Pce_admin_info

　　

Iv. Matters of note

1, whether it is publishing replication or subscription services must be installed in SQL Server native operation, cannot be a remote connection operation

2. Share this folder, and then use the UNC path of the shared folder as the snapshot folder path. This folder provides read and write permissions to SQL Server service and Agent service.

3, SQL Server another image to achieve a copy of the data, the image is only the primary server read and write, from the server is not support read and write, and replication can be read from the server to write the master server. Therefore, the realization of read-write separation is often achieved through database replication.

4. UNC can do network-driven mapping or FTP connection

5, the Redistribution server set up the subscription when the system will be prompted to use the computer name, and the computer name in different networks can not directly access, this time need to set the alias, alias to note that both the client and 32client to add

V. Supplementary

When synchronizing replication, the Subscriber generates the corresponding table for deletion, modification, and added storage to execute the data.

-

The left-hand stored procedure is controlled by the right call to the stored procedure, and the options can be selected without generating the stored procedure

When we need to modify the stored procedure in our business, we modify the stored procedure, if we change the structure of the source data, this time the stored procedure will initialize, if we want to keep the stored procedure unchanged, we can make the following changes

The true of the picture above is changed to false on OK

SQL Server local replication subscription implements database server read-write detach