June 12, 2017 at 8:02 am
Firstly, please understand this is completely new to me, so I dont understand a lot of this.
On Server01 I have (Under Local Publications)
Database01: reporting_database01
Server02.reporting_database01
Database21: reporting_database01
Server02.reporting_database01
Database22: reporting_database22
Server02.Reporting_database22
No Local Subscriptions
On Server02
I have 3 tables:
reporting_database01
reporting_database21
reporting_database22
Local Subscriptions
reporting_database01 - Server01.Database01.reporting01
reporting_database21 - Server01.Database21.reporting21
reporting_database22 - Server01.Database22.reporting22
====================================================================================================
Sorry I have a couple of questions:
Can you tell me what you think this is actually doing ?
From the Publication I see Database21 -> Server02 - reporting_database01. YET on Server02 the subscription is different.
Is this not copying the data to 2 databases, should this not be reporting_server21 - Server01.Database21.reporting_21 ?
How can I change the frequency with which data is replicated ( or is this a SQL Job )
Lastly, Can I not specify what tables are replicated or are they ALL replicated.
Any other info you can give me would be greatly appreciated.
Darryl
Darryl Wilson
darrylw99@hotmail.com
June 12, 2017 at 11:59 am
--run in the distribution database
SELECT DISTINCT
srv.srvname publication_server,
a.publisher_db,
p.publication publication_name,
a.article,
a.source_object,
ss.srvname subscription_server,
s.subscriber_db,
a.destination_object,
da.name AS distribution_agent_job_name,
CASE p.Publication_type
WHEN 0 THEN 'Transactional'
WHEN 1 THEN 'Snapshot'
WHEN 2 THEN 'Merge'
END as publication_type,
CASE s.subscription_type
WHEN 0 THEN 'Push'
WHEN 1 THEN 'Pull'
WHEN 2 THEN 'Anonymous'
END as subscription_type,
CASE s.sync_type
WHEN 1 THEN 'Automatic'
WHEN 2 THEN 'No synchronization'
END as sync_type ,
CASE s.status
WHEN 0 THEN 'Inactive'
WHEN 1 THEN 'Subscribed'
WHEN 2 THEN 'Active'
END as subscription_status
FROM MSArticles a
INNER JOIN MSpublications p
ON a.publication_id = p.publication_id
INNER JOIN MSsubscriptions s
ON p.publication_id = s.publication_id
INNER JOIN master.dbo.sysservers ss
ON s.subscriber_id = ss.srvid
INNER JOIN master.dbo.sysservers srv
ON srv.srvid = p.publisher_id
INNER JOIN MSdistribution_agents da
ON da.id = s.agent_id
ORDER BY srv.srvname, a.publisher_db, p.publicationSome times digging into the tables can give you better information. The tables are listed in this document:
Replication Tables
The frequency of the updates in replication is based on a job. The schedule can be recurring and scheduled however often you want the synchronization to happen or it can be set to start when Agent starts which would be it's continuous. Or you can set it to run one time.
The publications don't need to be and usually are not an entire database. The publications have articles and when the articles are tables you can filter those rows or select the columns.
Sue
June 13, 2017 at 2:19 am
Hi Thank you for the response.
I have tried running the code on both servers in every database. I keep getting "Invalid object name 'MSArticles'."
Can you tell me what im doing wrong please
Thanks
Darryl
Darryl Wilson
darrylw99@hotmail.com
June 13, 2017 at 3:05 am
Hi Darryl,
Run it on the "distribution" database. Please re-execute the script below. I've indicated the database name,
--run in the distribution database
USE distribution
GO
SELECT DISTINCT
srv.srvname publication_server,
a.publisher_db,
p.publication publication_name,
a.article,
a.source_object,
ss.srvname subscription_server,
s.subscriber_db,
a.destination_object,
da.name AS distribution_agent_job_name,
CASE p.Publication_type
WHEN 0 THEN 'Transactional'
WHEN 1 THEN 'Snapshot'
WHEN 2 THEN 'Merge'
END as publication_type,
CASE s.subscription_type
WHEN 0 THEN 'Push'
WHEN 1 THEN 'Pull'
WHEN 2 THEN 'Anonymous'
END as subscription_type,
CASE s.sync_type
WHEN 1 THEN 'Automatic'
WHEN 2 THEN 'No synchronization'
END as sync_type ,
CASE s.status
WHEN 0 THEN 'Inactive'
WHEN 1 THEN 'Subscribed'
WHEN 2 THEN 'Active'
END as subscription_status
FROM MSArticles a
INNER JOIN MSpublications p
ON a.publication_id = p.publication_id
INNER JOIN MSsubscriptions s
ON p.publication_id = s.publication_id
INNER JOIN master.dbo.sysservers ss
ON s.subscriber_id = ss.srvid
INNER JOIN master.dbo.sysservers srv
ON srv.srvid = p.publisher_id
INNER JOIN MSdistribution_agents da
ON da.id = s.agent_id
ORDER BY srv.srvname, a.publisher_db, p.publication
June 13, 2017 at 4:07 am
I realise I may push your patience but,
1. How do I find out where the distribution server is? I have tried running your script on VSQL01 (Publisher) and VSQL02 (Subscriber)
2. On the Replication Monitor, Ive got errors, any suggestions, greatly appreciated:
Error messages:
The process could not execute 'sp_repldone/sp_replcounters' on 'VSQL01\VSQL01'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
The specified LSN {001c386d:00000083:0016} for repldone log scan occurs before the current start of replication in the log {003abe06:0006c9ac:001c}. (Source: MSSQLServer, Error number: 18768)
Get help: http://help/18768
The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
Get help: http://help/MSSQL_REPL22017
The process could not execute 'sp_repldone/sp_replcounters' on 'VSQL01\VSQL01'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Darryl Wilson
darrylw99@hotmail.com
June 13, 2017 at 12:12 pm
You would normally have a publisher, subscriber and distributor. Look for the default database named distribution although it can be named something else.
You can also execute sp_helpdistributor on your servers to find the distributor.
In terms of the errors, it looks like the publisher and distributor could be out of sync. If you double click on a publication in Replication Monitor on the All Subscriptions tab, it brings up a windows with three tabs for Publisher to Distributor, Distributor to Subscriber and Undistributed Commands.
Look at the last 100 synchronizations, synchronizations with errors and undistributed commands. Where are the errors and what is the number of undistributed commands? I guess more importantly Is this even transactional replication - do you know? I'm guessing it is. Do you know anything about checking the LSNs?
This article has more information on the error you currently have:
SQL Server Replication Error - The specified LSN for repldone log scan occurs before the current sta
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply