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
]]>