Friday, April 1, 2016

"How do I know if the database is configured for SQL Server Replication?"

I answered that question for one of my customers today, and thought I'd share it here as well. Just a quick post, I've given you a couple different ways to verify whether a database is configured for SQL Server Replication.  Hope it helps!

 -- just a select on any of the replication flags      
 SELECT
    name [Database]
 FROM
    sys.databases
 WHERE
    databases.NAME = DB_NAME()
    AND databases.is_published = 1
    OR databases.is_merge_published = 1     
    OR databases.is_subscribed = 1
    OR databases.is_distributor = 1

You will see this output if a distribution database exists:











This next one is what I use to return the status of all user databases on an instance, indicating whether any are used in SQL Server Replication.

 /* check if any db is used in replication */
 SELECT
   name [Database],
   CASE is_published WHEN 0 THEN 'No' ELSE 'Yes' END [IsPublished],
   CASE is_merge_published WHEN 0 THEN 'No' ELSE 'Yes' END [IsMergePublished],
   CASE is_distributor WHEN 0 THEN 'No' ELSE 'Yes' END [IsDistributor],
   CASE is_subscribed WHEN 0 THEN 'No' ELSE 'Yes' END [IsSubscribed]
 FROM
   sys.databases
 WHERE
   database_id > 4

Here we see the cpriogbp is being published, and the distributor database is shown as well.














That's it!  See these for much ore detail regarding Microsoft SQL Server Replication: