Thursday, August 18, 2011

Adding new article without generating a complete snapshot

Today I got a request from a client that I had to add a new article to an existing publication. But they didn't want me to generate a snapshot for the entire database. It takes more than 2 hours for the snapshot to be taken and then additional 4 hours to apply it. This would cause significant outage.
So after a lot of research I found the following method to achieve this.

1)      Make sure that your publication has IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties set to FALSE or 0.

Use yourDB
select immediate_sync , allow_anonymous from syspublications

If either of them is TRUE then modify that to FALSE by using the following
command

EXEC sp_changepublication @publication = 'yourpublication', @property =
N'allow_anonymous', @value='False' 
Go
EXEC sp_changepublication @publication = 'yourpublication', @property =
N'immediate_sync', @value='false'
Go

2)      Now add the article to the publication

Use yourDB
EXEC sp_addarticle @publication = 'yourpublication', @article ='test', 
@source_owner = 'schemanameofobject',
@source_object = 'sourceobjectname',
@source_owner = 'schemanameatsubscriber', @force_invalidate_snapshot=1

If you do not use the @force_invalidate_snapshot option then you will receive the
following error
Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 99
Cannot make the change because a snapshot is already generated. Set
@force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

3)      Verify if you are using CONCURRENT or NATIVE method for synchronization by running the following command.

Use yourdb
select sync_method from syspublications

If the value is 3 or 4 then it is CONCURRENT and if it is 0 then it is NATIVE.
For more information check

4)      Then add the subscription for this new article using the following command

           EXEC sp_addsubscription @publication = 'yourpublicationname', 
           @subscription_type = N'pull',
           @article = 'yourarticlename', 
           @subscriber ='subscriberservername', @destination_db = 'destinationDB', 
           @reserved='Internal'

It is upto you if you want to use the PULL subscription or not.
If you are using the NATIVE  method for synchronization then the parameter
@reserved=’Internal’ is optional but there is no harm in using it anyways. But if it is CONCURRENT then you have to use that parameter. Else the next time you run the snapshot agent it is going to generate a snapshot for all the articles.

Lastly start the SNAPSHOT AGENT job from the job activity monitor. To find
the job name follow these steps.

·        select * from msdb..sysjobs where name like '%yourpublication%'
·        Right click on each of those jobs and find which one contains the step    
      ‘Snapshot Agent startup message’. This is the job that you want to      
                         start from the first step.

Verify that the snapshot was generated for only one article.


Do not use the Replication monitor to Re-initialize the subscribers.




Friday, August 5, 2011

SQL Server : Get Index usage report

For some time now I have been using the following query to find the index usage statistics for all indexes in a database.

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
         ON I.[OBJECT_ID] = S.[OBJECT_ID]
         AND I.INDEX_ID = S.INDEX_ID
WHERE    I.[NAME] not like 'PK%'
OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1   order by user_updates desc

But yesterday one of me colleagues asked me if it is possible for two objects in two different databases to have the same object_id. The DMV sys.dm_db_index_usage_stats had a column for database_id and my query would never check the DB_ID of the object_id. S

Upon further investigation I found that this was indeed true. It is possible the the OBJECT_ID for two objects in two different databases could be the same. Closer inspection revealed that the query was actually giving a cumulative value for the index stats for all the objects with the same object_id, irrespective of whether the objects were in the same database or not.
So the above mentioned query would not give the exact index stats for a given database. So I made a slight modification to the query and now I check the database_id for each record in the DMV. This gives me more accurate results.
So I learned two things today.
1) Object_id is only unique within a database but not within the instance. Two objects in two different databases can have the same object_id.
2) The DMV sys.dm_db_index_usage_stats contains info for all the databases, so it is important to query that DMV for a specific DB_ID.

The correct query to find index utilization statistics is
SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
WHERE    I.[NAME] not like 'PK%' and s.database_id = DB_ID() and
OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1   order by user_updates desc