Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the knowledge points of sqlserver about publishing and subscribing replication_subscription", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn what sqlserver knows about publishing and subscribing to replication_subscription.

The working mechanism of transaction replication

Transactional replication is implemented by SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent.

The snapshot agent prepares snapshot files, which contain schemas and data for published tables and database objects, then stores these files in the snapshot folder and records synchronization jobs in the distribution database at the Distributor.

The log reader agent monitors the transaction log of each database configured for transaction replication and copies the transactions marked for replication from the transaction log to the distribution database, which acts as a reliable store-and-forward queue.

The Distribution Agent copies the initial snapshot file in the snapshot folder and the transaction in the distribution database table to the subscriber.

Incremental changes made at the publisher flow to the subscriber according to the schedule of the Distribution Agent, which can run continuously to minimize latency or at scheduled intervals.

Because the log reader agent (corresponding to TESTDB1-replicate2-2 of 3 below) copies a copy of the transaction log of the publication database to the distribution database, the publication database does not need to be under the full recovery model when the transaction log of the publication database is about to be truncated if the log reader fails to complete the replication The transaction log status sys.databases.log_reuse_wait_desc of the publication database will be displayed as Replication to prevent truncation of the transaction log of the publication database.

1. If the client version of SSMS for publishing and subscribing is lower than the server version, an error will be reported. For example, if service is sqlserver2016,ssms and sqlserver2014 is used, an error will be reported.

2. When only the distribution is established, 7 related job; will be added while the distribution is established for the first time, and 9 related job will be added

3. For each new release name, two new release job will be added to the publisher as follows: the first one is constantly generating release data, the job is constantly running, and the latter is initializing the release data (generating files and files in the unc directory), which can be run once.

TESTDB1-replicate2-2

TESTDB1-replicate2-pub_replicate2-2

Publish instance name-database name-serial number of publication name

Publish instance name-database name-publish name-serial number of publication name

4. Publisher-copy-Local publish-publish name-right-attribute-snapshot, select put files in the following folder, and you can put the files in the sharing path.

5. Subscriptions can be established at the subscriber or at the publisher. Publish server-copy-local publish-publish name, and right-select new subscriptions

6. For each new subscription, if it is a push subscription, a job will be added to the main library, and if it is a pull subscription, a job will be added from the library

TESTDB1-replicate2-replicate2-TESTDB2-6 (push subscription, publication instance name-publication database name-publication name-subscription instance name-number)

TESTDB1-replicate1-pub_replicate1-TESTDB2-replicate_01-CD7A365E-2DE7-47A3-B31E-70F785FA71F2 (pull subscription)

7. Publish job or subscribe to job, and you can modify scheduler as needed

8. The subscription icon below the local publication or local subscription has a small circle, indicating that under the current instance, the other party takes the initiative rather than the current instance, and the subscribed job is on the other side.

Push subscription, there is no small blue circle in the subscription icon under the main library publication, and there is a small blue circle in the subscription icon under the slave library subscription.

Pull subscription, the subscription icon under the main library publication has a small blue circle, and the subscription icon below the slave library subscription has no small blue circle

It is also suitable for both local publication and local subscription. For example, if a local library information is transferred to another local library, there are subscriptions under either local publication or local subscription. If it is a push subscription, the subscription icon below the publication does not have a small blue circle. The subscription icon below the subscription has a small blue circle. If it is a pull subscription, the subscription icon below the publication has a small blue circle. The subscription icon below does not have a small blue circle

9. For subscription deletion, there are different operation modes according to the difference between push subscription and request subscription. The unified operation method is to delete the subscription under the main database publication directly.

In the case of push subscriptions, if you are only deleting subscriptions from the library, the subscription information under the publication of the main library is still there, and the subscription job on the main library is still there (but the information will not be synchronized to the subscription library), and you need to delete it again in the main library.

In the case of push subscriptions, if the subscriptions are deleted in the main library, the subscription job on the main library is also gone, and the subscription information from the library is automatically deleted.

In the case of requesting a subscription, if the subscription is deleted in the main library, the subscription job on the library is also gone, and the subscription information from the library is automatically deleted.

In the case of requesting a subscription, if the subscription is deleted from the library, the subscription job on the library is gone, and the subscription information under the publication on the main library is no longer there.

-- it is also suitable for both local publication and local subscription. For example, if the information of one local library is transferred to another local library, there are subscriptions under both local publication and local subscription.

10. The published job is normally recorded in msdb.dbo.sysjobs, but when these job are running, they all display Microsoft SQL Server in sysprocesses.program_name, not as a specific job name. The following statement queries job, which is not suitable for subscription replication.

Select * from msdb.dbo.sysjobs where name='jobname'

Select a.programmatic name. * from master..sysprocesses a where a.program_name like'% 0D1CE57E8AC5%'

11. The subscribed job is normally recorded in msdb.dbo.sysjobs, but these job runtimes are displayed as empty in sysprocesses.program_name

12. When deleting the subscription database, the following appears. The solution is to stop the subscription job, then offline the subscription database, and then delete it.

Cannot drop the database 'XXX'because it is being used for replication

13. Delete a publication. If there is a subscription under the publication, the subscription will become invalid when you delete the publication.

In the case of requesting subscriptions, there are still subscriptions and job under the local subscriptions from the library, but they are invalid. You also need to delete the subscriptions manually. In this case, the job will be deleted automatically.

In the case of push subscriptions, both the publication and job of the main library are deleted, and there are subscriptions under the local subscriptions of the slave library, but they are invalid. You still need to delete them manually.

-- it is also suitable for both local publication and local subscription. For example, if the information of one local library is transferred to another local library, there are subscriptions under both local publication and local subscription.

14. Subscription library, you can execute DML. Executing delete will not have any sequelae. If you execute update or insert, there may be conflicts if you receive the data from the publishing library later.

15. Publish and subscribe related stored procedures

EXEC distribution.dbo.sp_replmonitorhelppublisher-execute on the publishing library to check local publishing at the publisher

16. After the release is established, a linked sever on the publisher points to the distributor. After the name is generally repl_distributor,disable publishing and distribution, the linked server will be deleted automatically.

After a subscription is established (either a pull subscription or a push subscription), a linked server pointing to the subscriber is automatically established at the publisher. Delete a publication or disable publishing and distribution, and the linked server will not be deleted automatically

17. If the schedules of the subscribed job is not clearly specified, but only start automaticaly when SQL Server Agent starts, then once the job is stopped, it will not be synchronized again. The Subscription Watch List in Replication Monitor sees that the status of this subscription is not running.

18. Push subscription: go to the local publication-publication name-subscription name under the publisher, and right-select view Synchronization Status to see the subscription status.

Pull subscription: go to the local subscription under the subscriber-subscribe name, right-click to subscribe, and select view Synchronization Status to see the subscription status and the operation of the subscription job.

19. Copy-local subscription-subscription name, right-select view Synchronization Status to see that the status is No replicated transactions are available, then go to the publisher, copy-local publish-publish name, right-publish, and select the following two to check whether the job status is running in start. If so, whether the master-slave table data of count (*) is consistent. If it is consistent, it means that no DML transaction has generated new data at this time.

View Snapshot Agent Status checks the status of the snapshot agent, and the corresponding job is actually "instance name-publication database name-publication name-release sequence number". It is usually run only once to generate initialization data stored in unc.

View Log Reader Agent Status checks the status of the log reader agent, and the corresponding job is actually "instance name-publication database name-release sequence number", which generally runs all the time.

20. The so-called distribution service is to create a distribution database, which defaults to distribution, and to create a snapshot directory. If no distribution is created, the distribution service will be established automatically when the release is established for the first time, and the original distribution service will be used the second time. When you create a publication, you must first have a distribution, or where is the published data stored? Distribution is the place where the data to be published is stored.

21. The distributor is the bridge between the publisher and the subscriber, acting as a storage area, responsible for replicating specific data associated with one or more publishers. Each publisher is associated with a single database (called a distribution database) at the distributor. After obtaining the data to be published from the publisher, the distribution database stores replication status data and metadata about the publication and, in some cases, queues data that is moved from the publisher to the subscriber. In most cases, a database server instance acts as both a publisher and a distributor. When the publisher and distributor are in the same database instance, it is called the local distributor. When the publisher and distributor are configured according to their respective database server instances, the distributor is called a "remote distributor"

22, when no distribution has been established, when you right-click replication, there is configure distribution, and selecting configure distribution means only configuring the distribution and not doing other actions. In this process, if you check publisher and distribution database on the Publishers page, then when you right-click replication, there is no configure distribution, instead of publisher properties, distributor properties, disable publishing and distribution; if publisher and distribution database are not checked on the Publishers page, then when you right-click replication, there is no configure distribution, instead of Distributor Properties and Disabled Publishing and Distribution Wizard. In fact, when you right-click replication to select configure distribution, check publisher and distribution database, that is, select which publishers can use this distributor (enable servers to use this distributor when they become publishers). There is no choice for distribution database, but the distributed database. The default is distribution.

23. Right-click Replicattion and select Disabled Publishing and Distribution Wizard to disable subscription and publication. All subscription and publication information has been lost, including the distribution database in system databases and all directories and files in the unc directory, so you need to back up or take screenshots of the subscription and publication information before execution.

24. To initialize a subscription, that is, to re-push the data from the publisher to the subscriber, you can select a single subscription pushed to the publication (publisher-publication name-subscription name-right-reinitialize) or all subscriptions pushed to this publication (publisher-publication name-right button-reinitialize all subscriptions). Then in the publisher-copy-local publish-publish name, right-select View Snapshot Agent Status and select start, or run job "instance name-publication database name-release name-release serial number"

25. When you manually insert a statement in the table of the subscriber, and then when the publisher synchronizes the same data, the subscriber will report an error

Violation of PRIMARY KEY constraint 'PK_XX'. Cannot insert duplicate key in object 'dbo.TXX'. The duplicate key value is (33583).

26. Bug problem: when you right-click Replicattion and select Disabled Publishing and Distribution Wizard, an error will be reported in the last step, but the publication and distribution will be deleted

27. How to export the script for subscription replication: right-click Replication and select Generate Scripts (there are comments in the export script, whether it is executed on the publisher or subscriber side)

The above will export the distribution, publication and subscription under the current server. If distributor properties is selected in the Generate Scripts pop-up interface, the distribution will be exported; if publications in the following data sources is checked, the publication will be exported; if subscriptions in the following data sources is checked, the subscription will be exported.

28. To monitor whether there are any exceptions to the publication and subscription, execute the following five statements on the publisher side.

Select * from [distribution]. [DBO]. [MSlogreader _ history] WHERE error_id! = 0 AND [time] > = DATEADD (HOUR,-1, GETDATE ())

Select * from [distribution]. [DBO]. [MSdistribution _ history] WHERE error_id! = 0 AND [time] > = DATEADD (HOUR,-1, GETDATE ())

Select * from [distribution] .[ dbo] .[ MSsnapshot _ history] WHERE error_id! = 0 AND [time] > = DATEADD (HOUR,-1, GETDATE ())

Select * from [distribution] .[ DBO] .MSrepl _ errors order by 2 desc

Select * from msdb.dbo.sysreplicationalerts order by 7 desc

29. Two ways to query the published database objects of a published XX

29.1. Execute on the release database (data source of these three tables, distribution.dbo.MSpublications view query release name,

Distribution.dbo.MSarticles view query published objects such as tables, sysarticlecolumns view query published table which fields)

Select a.article,a.source_object,a.destination_object,b.colid from

(select article,article_id,source_object,destination_object

From [distribution]. [dbo]. MSarticles where publication_id in

(select publication_id from

[distribution]. [dbo]. MSpublications where publication='XX'

)

) a

Inner join

(select * from dbo.sysarticlecolumns) b

On a.article_id=b.artid order by a.article

29.2. Execute on the subscription database

Select distinct article from MSreplication_objects where publication='XX'

30. The way to deal with the loss of a publication XX but the existence of a pull subscription

The subscriber executes as follows, finds the published database object, creates the publication at the publisher, and then rebuilds the subscription. Method 2 is feasible.

Select distinct article from MSreplication_objects where publication='XX'

The following methods are not good.

Restore the publication database to see if the publication information can be recovered before it is lost, and find that the publication database executes the following error report

Select * from sysarticlecolumns

Invalid object name 'sysarticlecolumns'.

31. When you need to add a new table for publishing and subscription, just right-click the publication name and select the articles project, check the new table in it, and then right-click the publication name-- view snapshot agent status--start. If the log reading job (right-publishing name-- view log reader agent status) on the publishing side is running normally, the subscription job (right-click subscription name-- view sysnchronization status) is also running normally. The new table can be seen immediately on the subscription database.

32. SSMS graphical interface, when new release to step 4 articles project or existing release right click to select articles project, the interface is very slow and can not normally display database objects such as table view stored procedures, indicating that there is a blockage. SSMS clicking on some interfaces is actually an action of select. After finding the blocking source and killing it, it can be displayed normally.

33. Instance-- replication--Local Subscriptions-- subscription name-- right-- View Synchronization Status error: An error occurred connecting to server 'XX'.SQL Server repliaction requires the actual server name to nake a connection to the server.

Reason: the computer name may have been changed. SSMS connects to the new computer name, but the database does not modify Servername, resulting in this error. SSMS connection can use the old computer name or modify the database to change Servername to the new computer name.

34. The data of the ADB1 library of server A has made the ADB1 database from replication to server B. A server and A1 server have built AG and added ADB1 to AG, but the database ADB1 corresponding to AG on A1 is abnormal. Right-click the publication name corresponding to server An ADB1-- View Log Reader Agent Status found that the publication of ADB1 reported an error: Replicated transaction are waiting for next log backup or for mirror partner to catch up (the replicated transaction is waiting for the next log backup or the mirror partner update)

Solution method

Method 1. Make sure that the ADB1 in the AG of A1 is normal (preferred method)

Method 2. Remove the database ADB1 from AG on server A

Method 3. Execute on A server

DBCC TRACEON (1448,-1)-do not want replication to be affected by other node of alwayson

Reason:

We know that secondary, the auxiliary copy of always on, is a redo action that obtains the log information of primary and realizes the synchronization of the database. When the secondary goes down abnormally, in order to ensure that the secondary can continue to do the redo action in the place where the log was last read when the db gets up, the db transaction log will not be backed up, but will continue to grow. In the worst case, the disk will be full and unusable. For example, if 01 is primary,02 and secondary, in order to avoid switching 02 to master copy after failover, this node does not get the same new replication information as 01, resulting in an exception. So all always on node should be aware of synchronization, otherwise synchronization will not continue. That is, only after all node have obtained replication information can replication be allowed to continue.

35. SSMS for DB1 creation and release Times error: this database is not enabled for publication does not allow this database to be used for publishing. (Microsoft SQL Server, error: 14013)

Manually execute the creation of a publication to DB1

USE master

EXEC sp_replicationdboption @ dbname = 'DB1',@optname =' publish',@value = 'true'

GO

Error

The OLE DB provider "SQLNCLI" of the linked server "repl_distributor" returns the message "Login timeout expired".

The OLE DB provider "SQLNCLI" of the linked server "repl_distributor" returns the message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."

This instance is Enterprise Enterprise Edition, not Express Development Edition. You can create a release, so it is not a version problem.

Reason: found that there is a linked server named repl_distributor, and the linked server cannot be deleted. Query the linked server and find that it has been used for publishing, that is, master.sys.servers.is_distributor=1

Solution: USE master

EXEC sp_serveroption 'repl_distributor',' dist', 'false'

36. Error number: 18483

Could not connect to server "XX" because 'distributor_admin' is not defined as a remote login at the server

Could not connect to the 'XX' server because distributor_admin is not defined as remote login on that server

Could not connect to server 'XX' because' YY' is not defined as a remote server.

Failed to connect to the 'XX' server because' YY' is not defined as a remote server

Reason: hostname is inconsistent with servername. Hostname is XX, but servername is YY. At this time, the operation of creating a distributor cannot be completed through a graphical interface.

Solution: use the graphical interface wizard, and finally choose not to configure the distribution, but to generate a script, use the code of the script, modify the code in the script, and change XX to YY

37. Replication to different schema, for example, the dbo.table1 of library A can be copied to the repl.table1 of library B, but it cannot be operated by graphical interface and needs to be operated by script. Of course, you can also use graphical interface to operate, but do not click to confirm. Instead, export the script from the graphical interface, and then modify the schema information in the script. In the following example, copy the schema table of dbo to the schema of repl:

Use [PatternRecDB]

Exec sp_addarticle @ publication = Null PatternRecDBMS Baseball, @ article = Noble Baseball, @ source_owner = Noble Baseship, @ source_object = Noble Baseball, @ type = Noble logbasedBaseship, @ description = Noble Baseship, @ creation_script = Noble Baseship, @ pre_creation_cmd = Noble truncation, @ schema_option = 0x000000000803509F, @ identityrangemanagementoption = Noble Baseship, @ destination_table = Noble Baseship, @ destination_owner = Noble replication, @ status = 24 @ vertical_partition = Neverfalse, @ ins_cmd = N'CALL [sp_MSins_dboBase]', @ del_cmd = N'CALL [sp_MSdel_dboBase]', @ upd_cmd = N'SCALL [sp_MSupd_dboBase]'

GO

38. Modify the database name and report an error can not rename the database name because it is published or it is a distribution database. Solution: sp_removedbreplication @ dbname=XXX

39. Modify servername error There are still remote logins or linked logins for the server 'DBMASTER'

Solution idea

Find out which users are remotely connected to the DBMASTER server

Sp_helpremotelogin 'DBMASTER'

Server local_user_name remote_user_name options

DBMASTER distributor_admin distributor_admin

39.2. Delete the remote connection, and the continued error indicates that the server is a publisher for subscription replication

Exec sp_dropserver 'DBMASTER', @ droplogins =' droplogins'

Continue to report Cannot drop server 'DBMASTER' because it is used as a Publisher in replication.

39.3. Delete publication information for subscription replication

Sp_dropdistpublisher @ publisher = 'DBMASTER'

Continue to report an error Cannot drop the local distribution Publisher because there are Subscribers defined.

39.4. Delete subscription information

Sp_dropdistributor

Continue to report Could not drop the Distributor 'DBMASTER'. This Distributor has associated distribution databases.

Finally, delete the subscription information and change DBMASTER to DBMASTERNEW again, which is successful.

EXEC sp_dropdistributor @ no_checks = 1, @ ignore_distributor = 1

Exec sp_dropserver 'DBMASTER', @ droplogins =' droplogins'

Sp_addserver 'DBMASTERNEW','local'

Establish distribution

Copy-right-configure distribution

1. Select a distributor

2. Select the snapshot directory

3. Create a distribution database (database name defaults to distribution, database file directory, database log name)

4. Choose which publisher can use this distributor, enable servers to use this distributor when they become publishers, in which distribution database has no choice, that is, the distributed database. The default is distribution.

-- if publisher and distribution database are checked in step 4 above, right-click Replicattion with Publisher Properties, Distributor Properties and Disabled Publishing and Distribution Wizard. If unchecked, right-click Replicattion and there is no Publisher Properties, there are Distributor Properties, Disabled Publishing and Distribution Wizard

After the distribution is established, you will have the following job

1 、 Agent history clean up: distribution

2 、 Distribution clean up: distribution

3 、 Expired subscription clean up

4 、 Monitor and sync replication agent jobs

5 、 Reinitialize subscriptions having data validation failures

6 、 Replication agents checkup

7 、 Replication monitoring refresher for distribution.

Establish and publish

Copy-Local publish-right-create a new publication

Create release 1: when there is no distribution, establish the distribution at the same time as the release (although there are no steps 3 and 4 of "create distribution" above, the database distribution is also created by default)

Right-click local publications (local release)-select new publication (new release)

1. Select a distributor

2. Select the snapshot directory

3. Select the database to publish

4. Select the type of publication

5. Select the objects to publish, such as tables, views, and stored procedures

6. Select the snapshot agent, whether the snapshot is created immediately or periodically, whether the security of the snapshot agent uses the user name and password or the sqlserver agent service, how the snapshot agent connects to the publisher, whether it is an OS domain account or an DB account.

7. Create a publication name

After the release is established, the following job is available

1 、 Agent history clean up: distribution

2 、 Distribution clean up: distribution

3 、 Expired subscription clean up

4 、 Monitor and sync replication agent jobs

5 、 Reinitialize subscriptions having data validation failures

6 、 Replication agents checkup

7 、 Replication monitoring refresher for distribution.

8. WONCNTESTDB1-replicate1-1

9. WONCNTESTDB1-replicate1-pub_replicate1-1

Create release 2: when a distribution has been established, only the release is established

1. Select the database to publish

2. Select the type of publication

3. Select the objects to publish, such as tables, views, and stored procedures

4. Select the snapshot agent, whether the snapshot is created immediately or periodically, whether the security of the snapshot agent uses the user name and password or the sqlserver agent service, how the snapshot agent connects to the publisher, whether it is an OS domain account or an DB account.

5. Create a publication name

After the release is established, the following job is available

1. WONCNTESTDB1-replicate1-1

2. WONCNTESTDB1-replicate1-pub_replicate1-1

Establish a subscription

1. Select a publisher and select a publish

2. Choose push subscription or pull subscription

3. Select a subscriber and a subscription database

4. Select the subscription proxy server, how the distribution agent uses to connect, whether it is an OS domain account or a DB account, how the distributor uses to connect, whether it is an OS domain account or an DB account, how the subscriber uses to connect, and whether it is an OS domain account or an DB account.

5. Is subscription synchronization persistent or on demand

6. Whether to initialize the subscription object immediately

After the subscription is established, there is the following job. The first half of this job name is the same as the subscription name, namely WONCNTESTDB1-replicate1-pub_replicate1.

WONCNTESTDB1-replicate1-pub_replicate1-WONCNTESTDB2-replicate_01-CD7A365E-2DE7-47A3-B31E-70F785FA71F2

At this point, I believe you have a deeper understanding of "what are the knowledge points of sqlserver about publishing and subscribing to replication_subscription". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.