Replicating Stored Procedure Execution


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

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |