Query to check if all replication articles are created at Subscriber SQL Server 2012

Question

I am creating an automated build and test tool for our databases. In general there are two stages: - install databases in version A, install transactional replication - drop replication, update databases to version B, install replication

The problem is that replication is dropped before previous stage finish to create articles (tables) at Subscriber. In this case Subscriber is missing some tables and update script fails.

Can you give me a query or tables to check if all articles are created?

Subscriber is on different instance than Publisher. Articles are created with sp_addarticle and after that we execute this: sp_startpublication_snapshot

UPDATE: I managed to create a workaround but it is not 100% correct. This query is supposed to check if tables created from replication on Subscriber are the same as articles on Publisher. I found out that our actual situation is as follow: - tables on Subscriber exists, because they were created from initial scripts - replication is installed - below query is executed - in the meantime articles on Publisher are created - my query compares them with original tables from initial scripts and finishes with success - Replication is started and Subscriber tables are being dropped and created - update to version B is started - sql agent is stopped, replication is dropped and we have missing tables on Subscriber.

The query is executed on Subsciber with linked connection to Publisher.

WHILE (
  SELECT COUNT(1)
  FROM (
   SELECT a.dest_table
   FROM [10.149.000.000].PubDB.dbo.sysarticles a (NOLOCK),
    [10.149.000.000].PubDB.dbo.syspublications p (NOLOCK)
   WHERE a.pubid = p.pubid
    AND p.NAME = 'my publication name'

   EXCEPT

   SELECT t.NAME
   FROM sys.tables t (NOLOCK)
   WHERE t.is_ms_shipped = 0
   ) AS Z
  ) > 0
BEGIN
 PRINT 'Waiting for replication to finish.'

 WAITFOR DELAY '00:00:02'
END

Answer 1

I am not sure if there is a better way but something simple like this should do it. If you run this query on the Publisher, it will compare the tables and will return you the difference in tables. The Publisher needs to be linked to the Subscriber.

-- Publisher select t.name from sys.tables t where is_published = 1 except -- Subscriber select t.name from [SERVERNAME].[REPLICATED_DATABASE].sys.tables t where is_ms_shipped = 0;

Answer 2

This is my final working solution. I am querying MSdistribution_history table to look for comment like 'Delivered snapshot%' I suppose there must be more elegant way to check it.

DECLARE @TIMEOUT DATETIME
SET @TIMEOUT = DATEADD(MINUTE, 3, GETDATE())  --3 minutes for timeout

WHILE NOT EXISTS (
    SELECT NULL
    FROM distribution.dbo.MSdistribution_history dh WITH (NOLOCK),
     distribution.dbo.msdistribution_agents da WITH (NOLOCK)
    WHERE dh.agent_id = da.id
     AND dh.runstatus = 3
     AND da.publication = 'my publication name'
     AND dh.comments LIKE 'Delivered snapshot%'
)
BEGIN
  IF (GETDATE() > @TIMEOUT)
  BEGIN
    THROW 50000, 'Replication checking timeout.', 1;
  END

  PRINT 'Waiting for replication to finish.'
  WAITFOR DELAY '00:00:02'
END