16. SQL Server replication and common error handling

SQL Server Replication

Replication is a combination of technologies that you can use to replicate data and database objects from one database to another.

The copy of the English is replication, repeating the meaning, not copy. The core function of replication is to store and forward, repeat this action to other databases after adding, deleting, and changing in the source database.

Reasons for using replication

1. Load balancing: Reduce the load on the current server by replicating data to other database servers.

2. Partitioning: Isolate frequently used data and historical data.

3. Authorization: To provide part of the data to the person who needs to use the data.

4. Data merging: Each region has its own data and merges its data.

5. Failover: When the source database cannot connect, the standby database can be connected. Or the source database remains linked, test the stored procedure with a standby database, and so on.

Composition of the Copy

The concept of reproduction is similar to the issue of a magazine, which is published by publishers and distributed to subscription magazines through kiosks and other places. In replication, publishers, kiosks, subscribers correspond to publishers, distributors, subscribers, respectively.

Publishing Server

Contains the source database that needs to be published.

Distribution server

Contains the distribution database, which is used to store the data sent by the forwarding publisher. A Distributor supports multiple publishers, similar to a newsstand that sells magazines from multiple publishers. The distributor can also be the same instance as the Publisher, and publishers do not sell themselves directly through kiosks.

Subscriber server

Contains a copy of the data published by the publisher, which can be a database, or a table, or even a subset of a table.

Publications and articles

Publishing refers to a collection of articles that can be published, including tables, stored procedures, views, and user-defined functions.

When you publish a table, you can also publish only a subset of the tables based on qualifying criteria.

Subscription

A subscription is a concept of relative publishing, which defines which distributor the Subscriber receives the publication from. There are two types of subscription, the recommended subscription push and pull subscription, in the case of a referral subscription, when the publisher generates an update, the Distributor updates the subscription directly, and the pull subscription requires the subscriber to periodically check the Distributor for available updates and, if there is an available update, the Subscriber updates the data.

Type of replication

There are 3 types of replication in SQL Server, with only one replication type for each publication: snapshot replication, transactional replication, merge replication.

snapshot replication

Snapshot replication makes all of the published tables a mirror (a snapshot of the database objects) and then copies them to the Subscriber at once. Because the entire dataset is being sent, the DML (deletions) that occur in the middle are not automatically routed to subscribers like other replications.

Snapshot replication is characterized by:

1, occupy the network broadband, because one-time transfer of the entire image, so the content of the snapshot copy is not too large.

2, suitable for updating infrequently, but each update is relatively large data.

3. Subscribers are read-only environments.

Transactional replication

Transactional replication also starts with a snapshot, and after the first set of transactional replication, the published tables, stored procedures, and so on, are mirrored, and each change to the publisher is sent to the Subscriber as a log. Enables the Publisher and Subscribers to remain in sync almost.

Transactional replication is characterized by:

1, the Publisher and subscriber content can be basically synchronized.

2, Publisher, distributor, network connection between subscribers should be kept unblocked.

3. Subscribers can also be set up as pull subscriptions so that subscribers can keep links with the distributor without having to stay on the server.

4, the data volume has sustained growth, updated frequently.

Merge replication

Merge replication allows the Publisher to update the database and also allows subscribers to update the data. These updates are periodically merged to keep the published data consistent across all nodes. Therefore, it is possible that the Publisher and Subscribers update the same data, and when conflicts occur, they are not handled exactly as the publisher takes precedence. Instead, it is processed according to the settings.

Configure replication

The first two instances are created locally, with the default instance MSSQLServer as the Publisher and Distributor and MySQLServer as the Subscriber.

Reinstall SQL Server Once, select a new instance, and do not select the default instance.

1. Configure the Publisher and distributor in the MSSQLServer instance

Use the Publisher and distributor as the same server

Set Storage snapshot Folder

You need to add read and write permissions to this directory for everyone else, or you cannot write to create a failure.

Configure the distribution database

And then you're done.

This allows the Publisher and distributor to complete the configuration.

2. Establish a release

Select a database to publish

Publish type Select things to publish

Select the tables you want to copy

Select Create now and keep available status

Proxy security Select the default proxy account to

Click Finish

The release was found to be successful, but there was a warning. This is because the proxy service is turned off by default and needs to be started.

After starting the agent service, go to view the Snapshot Agent status

It's ready to start.

This will create the release.

3. Create the subscription in the MySQLServer instance.

Choose to find a SQL Server server

Connecting MSSQLServer instances

Select the publication you just created

Then select a push subscription so that updates are automatically pushed to the subscriber each time the publisher is present.

Create a new database locally to receive the data.

Distribution Agent Security, select the impersonation process account, here's a question.

Then click Finish to

To this subscription creation complete

Then view the synchronization status

When this error occurs, in the MSSQLServer instance, locate agent--------the job is stopped.

Using Replication Monitor

The process could not connect to the Subscriber, and the user NT Service\sqlserveragent login failed because the MSSQLServer instance was trying to connect with its SQL Server proxy account, but in the MySQLServer instance, The agent's account is not correct. So change the way subscribers are connected.

Local subscriptions at the Subscriber are not viewable and can be viewed under the corresponding publication in local publishing at the publisher.

Here, modify the way the Subscriber is connected, and check the synchronization status again.

Already started to pass things over, view Replication Monitor

View subscribers the number of tables

To add test data to the publishing server

Querying the number of subscribers again

You can see that subscribers and publishers are almost kept in sync.

16. SQL Server replication and common error handling