Whenever there is a business requirement to add a new article to an existing publication, you want to know how the publication is going to behave during the initialization. Basically, you start asking following questions to yourself;

• Will a new snapshot (running the snapshot agent of the publication) cause to initialize\reinitialize to all the articles or only newly added articles of the publication?
• During the snapshot generation, is it going to drop and recreate the tables, going to delete\truncate all the records from the tables, or just going to do nothing with tables at the subscriber side?
• Are there any other articles which were added (subscribed) in the publication but not initialized (not published to a subscriber)?
• Are there other articles which have been marked for reinitializing in the publication?
• Which synchronization type was used for existing published articles like – automatic, replication support only..etc?

To check all those important parameters of the publications, you can run the following script.

Script to Capture Important Parameters of the SQL Server Replication

/*-------------------------------------------
----Run this query on the Publisher Server
--------------------------------------------*/--USE --<Select Publisher Database>
SELECT
@@ServerName as publisherserver,
DB_NAME() publisherdb,
sp.name as publicationname,
sp.immediate_sync,
sp.allow_anonymous,
OBJECT_SCHEMA_NAME(sa.objid, db_id()) as articleSchema,
sa.name as articlename,
s.status subscription_status,
CASE s.status
       WHEN 0  THEN 'Inactive'
       WHEN 1  THEN  'Subscribed (Not Published)'
       ELSE 'Active (Published)'
END AS subscription_status_desc,
sa.pre_creation_cmd,
CASE sa.pre_creation_cmd
       WHEN 0  THEN 'none'
       WHEN 1  THEN 'drop'
       WHEN 2  THEN 'delete'
       ELSE 'truncate'
END AS pre_creation_cmd_desc,
UPPER (srv.srvname) AS subscriberservername,
s.dest_db as subscriberdb,
case s.sync_type
             when 2 then
                           case s.nosync_type
                                  WHEN 3 THEN 5
                                  WHEN 2 THEN 4
                                  WHEN 1 THEN 3
                                  else 2
                END
                     ELSE s.sync_type
END as sync_type,
 
case s.sync_type
              when 2 then
                 case s.nosync_type
                    WHEN 3 THEN 'initialize with lsn'
                    WHEN 2 THEN 'initialize with backup'
                    WHEN 1 THEN 'replication support only'
                    else CAST (2 AS VARCHAR (2)) +'none'
                 END
                       when 1 THEN 'automatic'
              ELSE CAST (s.sync_type AS VARCHAR)
END as sync_type_desc
FROM dbo.syspublications sp
JOIN dbo.sysarticles sa ON sp.pubid = sa.pubid
JOIN dbo.syssubscriptions s ON sa.artid = s.artid
JOIN master.dbo.sysservers srv ON s.srvid = srv.srvid
go

Below is the query put. Click on the image to zoom it

Below are the columns which you will be getting as the query result. I have mentioned the description of the columns so that you can have a better understanding of them.

• publisherserver – It shows publisher server name

• publisherdb – It shows publisher database name

• publicationname – It shows publication name

• immediate_sync – It indicates whether the synchronization files are created or recreated each time the Snapshot Agent runs. It returns the value either 1 or 0

1 (True) = The synchronization files created every time the agent runs. This is default value.
0 (False) = The synchronization files are created only if there are new subscriptions. Subscribers cannot receive the synchronization files until the Snapshot Agents are started and completed.

• allow_anonymous – Indicates whether anonymous subscriptions are allowed on the publication. It returns the value either 1 or 0

1 (True) = Anonymous subscriptions are allowed. This is default value.
0 (False) = Anonymous subscriptions are not allowed.

• articleschema – It shows schema name of the article which is being published.

• articlename – It shows the name of the article which is being published. 

• subscription_status – It returns the subscription status value either 0, 1 or 2.

• subscription_status_desc – It shows the information about the subscription status in bit more details

Inactive = 0 = The subscription has been marked inactive. In other words, the subscriber entry exists without a subscription.
Subscribed (Not Published) = 1 = It means the article has been added to Publication but not initialized.
Active (Published) = 2 = It means the article has been added to Publication & initialized.

• pre_creation_cmd – It returns the values either 0, 1, 2 or 3. The pre-creation command values tell what is going to happen with the destination tables. For example, the tables will be dropped and recreated, only records will be deleted from the tables, tables will be truncated, or nothing.

• pre_creation_cmd_desc – It shows the description of the pre-creation command.

none (0) = If the table already exists at the Subscriber, no action is taken.
drop (1 default) = Drops the destination table.
delete(2) = Deletes data from the destination table before applying the snapshot. When the article is horizontally filtered, only data in columns specified by the filter clause is deleted. Not supported for Oracle Publishers when a horizontal filter is defined.
truncate (3)= Truncates the destination table. Is not valid for ODBC or OLE DB Subscribers.

• subscriberservername – It returns the name of the subscriber server

• subscriberdb – It shows the name of the subscription database.

• sync_type – It shows the value for the type of initial subscription synchronization. It returns the values either 1,2,3,4 or 5

• sync_type_desc – It shows the description of the sync_type command.

1 = automatic (snapshot)
2 = None (2000 only)
3 = replication support only
4 = initialize with backup
5 = initialize from log sequence number (LSN)

If you want to table out the initialization\reinitialization behavior with pre-creation command, this is how it looks like;

Hope you enjoyed learning about the most confusing part of replication!

The post Capture Important Parameters of the SQL Server Replication appeared first on .