Publish and subscribe on the same server
Local publish database SQLTEST, publish table user_INFO Local subscribe database copy_for_SQLtest
1. Configure distribution
Configuration distribution is a pre-publish and subscribe basis and cannot be done without distribution libraries.
- Snapshot folder: Default or custom (F:\My_Code\MSSQL_ReplData)
- The distribution database name is the default: Distribution
2. Snapshot publishing
As the name implies, just like taking a photo, the publisher takes a snapshot of the table data you want to synchronize, and the snapshot data set is stored locally in a snapshot folder. The subscriber then sends the snapshot set to the subscriber at the intervals you set, and the subscriber completely overwrites the current corresponding table data according to the latest snapshot set received.
1. Create local publications
As you can see, after configuring distribution, a distribution database is generated under the system coefficient library
After clicking New release, the pop-up forms are respectively
New Publish Wizard — Next step — Publish database — SQLTEST
2. Release type
3. Select the project to release
Select the USER_INFO table for local publishing
- If you want to filter the user_INFO table data, add the filter criteria, I will publish the whole table, next step
Snapshot proxy
Set the time for running a snapshot task
I set it to run the snapshot at 0:50:0 every day and save the snapshot set of user_INFO table in the snapshot folder
5. Set security Settings for the snapshot proxy
The release is named Local Snapshot Release. The release is created
6. The snapshot is created successfully
7. Published jobs are automatically generated
After the local snapshot is created and published, you will notice that some more jobs are automatically created in the agent jobs.
The snapshot set with the publication table user_INFO is found in the local snapshot folder
Create a local subscription
New Subscription Wizard — Next step
Publish server is the name of the local server
Distribution agent location — Push subscription/request subscription
Since I’m doing replication on the same server here, the push and subscription will both be local and there will be no difference.
10. Subscribers
Select another database to subscribe to the USER_INFO table of the SQLTEST library
11. Set distribution agent security
Because it is the same server, the account password is set directly to the local account password, no matter the publishing or subscribing end is selected to run the agent.
12. Synchronization plan for subscriptions
A continuous run is a subscription that keeps pushing/requesting
Define a schedule that can be executed periodically: the time can be set after the snapshot generation time above
If you want to perform synchronization for every data change in real time, start it when the default agent starts, and customize the scheduled execution time if you don’t need real-time.
However, with snapshot publishing, it is best not to set real-time because it synchronizes all the subscribed table data to the subscriber at once. If the publisher rewrites the entire table of the subscriber with every minor change, this can cause frequent page data that cannot be displayed for short periods of time, as the entire table data of the subscriber is deleted and then overwritten.
Therefore, snapshot publishing is suitable for infrequent updates, but each update is relatively large data, select a time period for the entire table update.
13. Initialize the subscription
If a snapshot was not created when the local publication was created, this option would not work because there is no corresponding snapshot to overwrite the subscriber.
It doesn’t matter which one you choose, you can start publishing later to generate snapshots, and then start subscribing to get snapshots
Subscription created successfully
14. Subscribed jobs are automatically generated
As you can see, after the published job is generated and the subscription is created, the subscribed job is automatically generated.
15. Publish and subscribe to run tests after creation
② Push the latest snapshot to the subscribed end
③ Look at the subscription database copy_for_SQLtest
The entire user_INFO table on the publisher side has been overwritten
④ Data Modification
SQL > create snapshot from user_INFO SQL > create snapshot from user_info SQL > create snapshot from user_info SQL > create snapshot from user_info SQL > create snapshot from user_info SQL > create snapshot from user_info SQL > create snapshot from user_info SQL > create snapshot from user_info SQL > create snapshot from user_info SQL > create snapshot from user_info Push to opy_for_SQLtest to completely overwrite the user_INFO table in the subscription library.
(The steps for performing scheduled tasks are the same: ① The publisher generates the latest snapshot and ② pushes the latest snapshot to the subscriber.)
16, snapshot release schematic finish the above steps, now look at this picture, is not a moment to fully understand!
①, publish server, to publish the table data to do a snapshot, in the snapshot folder
② The publisher sends the snapshot to the subscriber/the subscriber requests the snapshot from the publisher
③ The snapshots in the snapshot folder are uploaded to the subscriber
Third, transaction release
Unlike snapshot publishing, where the entire table is overwritten to the subscribing library, transactional publishing sends transaction logs (that is, all changes to or deletes from the database) that occur on the publisher side of the database to the subscriber, who then performs the same operations on the publisher side based on the transaction logs.
However, the basis for transferring transaction flows is snapshot replication. The subscriber still needs to have an initial schema and data (this is done by snapshot replication), and then the subscriber can synchronize data on the initial schema and data after the publisher logs each change to the subscriber.
1. Create a release
2. Release type
3. Select a release project
Here you can see that, unlike the previous snapshot publication, there is an additional user_info_work table, and the first two tables cannot be selected.
This is because neither of the previous two tables has a primary key, so I copied a user_info_work table that is the same as user_info but has a primary key
Snapshot proxy
5. Set security Settings for the snapshot proxy
The difference here is that there is a log reader agent under the snapshot agent. (If you want to set another agent, uncheck the security Settings for using snapshot agents below.)
6. The transaction release is created successfully
[Wizard operation] — Next step
Completion Wizard — named Local Transaction Publishing
7. Published jobs are automatically generated
Unlike snapshot publishing, there is an additional task at the job that does not have the [local Transaction Publishing] suffix.
Right-click on the properties of the job to see if it is a log read job
Steps 8 to 14 are the same as those for snapshot publishing
Create a local subscription
Distribution agent location — Push subscription/request subscription 10. Subscriber 11. Set distribution agent security 12
If you want to perform synchronization for every data change in real time, start it when the default agent starts, and customize the scheduled execution time if you don’t need real-time.
Unlike snapshot publishing, transactional publishing is suitable for environments where real time is required.
14. The subscribed job is automatically generated
15. Publish and subscribe to run tests after creation
You will find that there is one more log reader than snapshot publishing.
① Generate the initial schema and data for the subscriber
This is usually done when the publication and subscription are created. If you look at the subscriber’s database and find that the tables are not synchronized, a snapshot of the original data is created
After the snapshot is generated, it is reinitialized and sent to the subscriber
Alternatively, you can re-initialize all subscriptions, re-initialize the table on the subscriber side with the latest snapshot, and then start the synchronization state of the subscription
② Modify the table data on the publishing end
③ Read the transaction log of update on the publishing end
④ Send the transaction log to the subscriber
5. Check the
User_info_work, the copy_FOR_SQLtest table of the subscription library, has synchronized the transactions of the publishing library
16. Transaction publishing schematic
(1) By snapshot replication, the infrastructure and table data of the publisher are generated and sent to the subscriber to form the initial architecture of the subscription service
(2) After the data of the publisher is modified (adding, deleting, modifying, etc.), transaction logs are generated
③ The distributor’s log reading agent reads the transaction logs of the changed data and stores the transaction logs in the publisher’s publication database.
(4) The distributor’s distribution agent distributes the transaction logs from the distribution database to the subscribers, and records the history and errors in the distribution database.
4. Peer-to-peer release
Publish and subscribe on different servers
Between server A and server B, database A publishes and database B subscribes. First of all, between server A and server B, the database can be interconnected, which can be seen from this article how to achieve [database remote connection between servers].
The steps for establishing a publish and subscribe are basically the same as above on the same server. The difference is that when you create a subscription, push subscriptions and request subscriptions are different.
I. Push subscription
Push is to push snapshot/transaction logs to subscribers according to the schedule set by the publisher.
(The account is filled in with the publisher)
Request a subscription
A request is a request to the publisher for a snapshot/transaction log on a schedule set by the subscriber.
The account running the distribution agent process is for the subscriber filled in, and the distributor connected is for the publisher.
(If the distributor is built on a different server, it’s another complicated situation…)
Some error problems encountered
1. An error is reported during synchronization. Snapshots in the snapshot folder cannot be read
Solution: the latest snapshot set was not read in the snapshot folder. I created a new snapshot.
2. [server name] cannot be configured as a distributor
Processing: this is by their own local anti-virus software to intercept the database process, open my 360, found that the security center of the interception records appear some interception records.
Set 360’s protection popup mode, every time the system wants to perform any operation, the popup will prompt, can choose to reject or allow
(As a programmer using 360, I have no F to say)
Access to the snapshot path is denied. This is a problem encountered when publishing, subscribing, and requesting subscriptions on different servers.
Because a subscription is requested to access the publisher’s snapshot folder at the subscriber, the subscription service cannot synchronize to the snapshot data if the publisher’s snapshot folder is not shared with the subscriber.
Solution: just search for how to share a folder with another service. I won’t show you here.
4. Unable to connect to [server name]
Network – specific or instance-specific errors occurred while establishing a connection to SQL Server. The server was not found or could not be accessed. Please verify…
Processing: this appeared, is also let me do for a long time, finally in the publisher firewall outbound rule found, is actually 1433 port is banned!
