SQL Server Survival Guide

From time to time I refer to this sql server servival guide and would like to share with you. It focuses on SQL server 2000. For more details click SQL Server Survival Guide

Transactional replication optimisation

The following article from microsoft is a good starting point on how to optimise Transactional replication. Click the link for more info.Transactional replication Optimisation

Query replicated articles

Query replicated articles
If you are using an environment where there many publications and subscriptions it is difficult to find out the list of articles for particular subscriber.

The following code uses system tables in distribution to accomplish the task.
This query will retrieve publisher, publication, subscriber database, subsriber_id

select distinct pub.Publisher_db,pub.Publication, sub.subscriber_db,
sub.subscriber_id,art.article, art.destination_object from
distrib_.dbo.MSsubscriptions sub
join .dbo.MSPublications pub
on sub.Publication_id = pub.Publication_id
join .dbo.MSArticles art
on art.publication_id = pub.publication_id
where sub.subscriber_id = [subsriber_id]

If you have multiple distribution databases on the same server you can use union all with pre-fix of database name.

Removing registered servers

From time to time your registered servers will get out of date for example if the server is de-commissioned or the instance is removed. Using Enterprise manager some times frustrating. The easiest way to remove your registered server is by removing it from registry. The following step will help you how to do this.

a. Go to start menu and click run
b. Enter Regedit and click Ok
c. The registery editor will come up
d. Click on HK_Users and go to edit menu and click find
e. Type in Registered Servers X
f. Find the registered server from the list and delete