articles >> general dba >> Replicating Stored Procedure Execution

Replicating Stored Procedure Execution

By : Muthusamy Anantha Kumar Jan 31, 2004

Page 3 / 3


Now, check the archive tables on the publisher and subscriber.

On the Publisher “EBONY”

Use Sales
go
select count(*) from orders
result: 0
select count(*) from order_details
result: 0
select count(*) from orders_archive
result: 5
select count(*) from order_details_archive
result: 8


On the Subscriber “MAK”

Use sales
go
select count(*) from orders
result: 0
select count(*) from order_details
result: 0
select count(*) from orders_archive
result: 5
select count(*) from order_details_archive
result: 8

You will notice that both the publication and subscription databases will be in synch after the execution of the procedure. You can update "USP_Archive_Purge" according to your environment and also schedule this "USP_Archive_Purge" according to your environment requirement.

 

Conclusion

As mentioned before, the main intent of this article is to guide you, step-by-step, through the process of how to replicate a stored procedure execution in a transactional replication environment. Doing so reduces overhead and boosts your SQL Server’s overall performance.

 

Author

Muthusamy Anantha Kumar (a.k.a. MAK) has more than 9 years experience in Information Technology, including Database Administration, System Analysis, Design, Development & Support of MS SQL Server's 2000/7.0/6.5/6.0/4.X for production/development/testing. He is experienced in Microsoft SQL Server installation, configuration, performance tuning, client/server connectivity, query optimization, back-up/recovery. He has handled VLDBs and involved in SDLC of various data warehouse projects. Currently, MAK is Senior Database Administrator/Data Architect responsible for development, test, contingency and productions servers for Merrill Lynch in NJ and NY. Previously, MAK was Senior Database Administrator / Data Architect, and responsible for development, test, DR and production servers for many finance and .com companies. He plays an active role in forums as a SQL Expert and moderator.

Published many articles, scripts on database journal. Here is the list of all articles and author’s page.

http://www.databasejournal.com/article.php/3300441

 

Reprinted with the express written permission of the author. Copyright 2004