SQL DBA Cheatcode Part 2 : Few Important Queries for any DBA

0

Hi Friends,

From last few days, I was thinking about writing a series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I use. There are 1000s features in SQL Server and each feature has its own set of infinite queries so any blog cannot cover all queries and all features but I will try to cover the queries that is needed in our day to day activities as a DBA. Here is the second installment of this series. (Replication Special)

Comments are most welcome by readers which can also enhance my database of queries.
**DISCLAIMER : I have personally used all these queries but these may or may not work in your environment depending on your server setting, server edition\version etc. Please test these queries in lower environment first before executing these in production directly**

Note :- I would like to thank countless sites and articles present there on google which helped me save all these queries to my personal QUERY DATABASE. This series is for knowledge sharing pupose only and you can share these queries again to your colleagues and friends.

 

Query 1 :- Important Scripts to find out Replication details.

Script to run on Distribution database

This script returns completed setup replication information. Unless an orphan article exists, this will return a complete set of replication information. I also added the distribution agent job name to show how easy it is to pull in other configuration information.

 

USE Distribution
 GO
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 -- Get the publication name based on article
 SELECT DISTINCT
 srv.srvname publication_server
 , a.publisher_db
 , p.publication publication_name
 , a.article
 , a.destination_object
 , ss.srvname subscription_server
 , s.subscriber_db
 , da.name AS distribution_agent_job_name
 FROM MSArticles a
 JOIN MSpublications p ON a.publication_id = p.publication_id
 JOIN MSsubscriptions s ON p.publication_id = s.publication_id
 JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
 JOIN master..sysservers srv ON srv.srvid = p.publisher_id
 JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
 AND da.subscriber_id = s.subscriber_id
 ORDER BY 1,2,3

Script to run on Publisher database

This script returns what publications has been setup. This will go through all the published databases and return information if the database has replication enabled. Sometimes, I just want to see the publication name and subscriber server names (no articles) to see what servers are being used with replication other times I want all of the information, so I added a variable called @Detail and if you set @Detail = 'Y' it will return data with the article list. Any other value will only return the publisherDB, publisherName and SubscriberServerName.
 -- Run from Publisher Database
 -- Get information for all databases
DECLARE @Detail CHAR(1)
 SET @Detail = 'Y'
 CREATE TABLE #tmp_replcationInfo (
 PublisherDB VARCHAR(128),
 PublisherName VARCHAR(128),
 TableName VARCHAR(128),
 SubscriberServerName VARCHAR(128),
 )
 EXEC sp_msforeachdb
 'use ?;
 IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1
 insert into #tmp_replcationInfo
 select
 db_name() PublisherDB
 , sp.name as PublisherName
 , sa.name as TableName
 , UPPER(srv.srvname) as SubscriberServerName
 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
 '
 IF @Detail = 'Y'
 SELECT * FROM #tmp_replcationInfo
 ELSE
 SELECT DISTINCT
 PublisherDB
 ,PublisherName
 ,SubscriberServerName
 FROM #tmp_replcationInfo
 DROP TABLE #tmp_replcationInfo

Script to run on Subscriber database

This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find orphaned subscribers. This is rather simple since there is not much information to pull.

-- Run from Subscriber Database
SELECT distinct publisher, publisher_db, publication
 FROM dbo.MSreplication_subscriptions
 ORDER BY 1,2,3

The following scripts will provide the name of the database on which merge replication is setup

select namefromsys.databaseswhereis_merge_published= 1

 

Query 2 :- Drop article from Existing Publication (Transactional Replication)

EXEC sp_dropsubscription
 @publication = 'Publication name',
 @article = N'Table Name',
 @subscriber = 'Subscriber Server Name';
 GO
EXEC sp_droparticle
 @publication = 'Publication name',
 @article = N'Table Name',
 @force_invalidate_snapshot = 0

 

Query 3 :- Adding new article to Existing Publication (Transactional Replication)

First of all run the Exec sp_helppublication in publication database and checked the following fields

 

  1. Immediate_sync
  2. Allow_anonymous

Both the fields were set to ON as they showed a value 1 which is enabled. If the Immediate_sync is enabled, every time you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone. Usually, the Immediate_sync publication property is set to true if we allowed anonymous subscriptions while creating the publication through the Create Publication wizard. To prevent the complete snapshot, run the script below.
Step 1:- Disable the two fields in publication database

 

EXEC sp_changepublication
 @publication = ‘Publication name’,
 @property = N'allow_anonymous',
 @value = 'false'
 GO
 EXEC sp_changepublication
 @publication = Publication name’,
 @property = N'immediate_sync',
 @value = 'false'
 GO

 

Step 2:- Add new article in publication database

exec sp_addarticle
 @publication = N'Publication name’,
 @article = N'Table Name',
 @source_object = N'Table Name',
 @destination_table = N'Table Name'
 GO
 exec sp_addsubscription
@publication = N'Publication name’,
 @subscriber = N'Subscriber Server Name',
 @destination_db = N'Destination Database Name',
 @subscription_type = N'Subcription Type' [Pull\Push]

Step 3:- Now start the snapshot agent in publisher, it worked perfectly. You can see that only the particular table added was replicated. So from now on to apply the snapshots of the entire articles you need to reinitialize the subscriptions since the Immediate_sync is set to off.

 

Query 4 :-List all tables that are currently published for replication MS-SQL

It’s possible to query the distribution database to see what articles (tables) are published and within which Publication they are in.

SELECT
 P.[publication]   AS [Publication Name]
 ,A.[publisher_db]  AS [Database Name]
 ,A.[article]       AS [Article Name]
 ,A.[source_owner]  AS [Schema]
 ,A.[source_object] AS [Table]
 FROM
 [distribution].[dbo].[MSarticles] AS A
 INNER JOIN [distribution].[dbo].[MSpublications] AS P
 ON (A.[publication_id] = P.[publication_id])
 ORDER BY
 P.[publication], A.[article];

 

Query 5 :- This is how transfer primary keys in replication:

 

 

To replicate primary/foreign keys, we need to enable an option for the
article.

1. In the SQL Enterprise Manager, drill down to Databases -> your
publishing database -> Publications.
2. Right click the transactional publication under the Publications node,
click Properties.
3. Click the Articles tab, click the ¡¬ button near an article that you
want to replicate the primary/foreign key.
4. Click the snapshot tab, check the “Include declared referential
integrity” option.
5. Click the snapshot tab, check the “delete all data in the existing table” option.

This requires us to have created the tables manual at first or provide the snapshot agent with an script to create those before applying the snapshot. I prefer to create the table manually before i set up the transaction.

 

I shall be back with more queries soon. Please comment your views. Thanks for reading!!

 

http://dbathings.com/sql-dba-cheatcode-part-1-important-queries-dba/

Vaibhav Gupta

  1. Perfect

  2. Anilkumar Jantikar March 5, 2015 at 6:26 am

    These Queries are perfect to troubleshoot any replication issue

  3. […] after gap of almost one month. In First three Parts of this series I covered, Few Generic Scripts, Replication Specific Scripts & Database BackupRestore Specific […]

  4. […] SQL DBA Cheatcode Part 2 : Few Important Queries for any DBA […]

  5. […] Few Important Queries for any DBA – Replication […]

%d bloggers like this: