SQL Server 2005 replication of views, functions, and stored procedures?

dentyne
dentyne used Ask the Experts™
on
We are using SQL Server 2005 replication and it currently replicates data, table objects, triggers, etc.  It is currently configured not to replicate stored procedures, views, and functions.  The DBA guidance was that it may not be a good idea.

Can you help me understand if replicating stored procedures, views, and functions is possible? And if it is, what are some of the risks, if any?  The challenge we have is deploying the updated stored procedures, views, and functions to the subscriber database.  It is largely a manual process and is error prone.  I felt if they were replicated, it would make the process much more efficient and reliable. However, I am curious if SQL Server would resolve dependencies...like a view that uses a function that hasn't been replicated over yet because the view made it there first, etc.

Thanks for the help and the sharing of your experiences.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ted BouskillSenior Software Developer
Top Expert 2009

Commented:
Have you looked at SQL Delta or SQL Compare (from Red Gate)?

They have excellent tools for creating scripts to synchronize schema's (or data)

Commented:
http://www.informit.com/articles/article.aspx?p=601350

This link is very good and explain all your options

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> It is currently configured not to replicate stored procedures, views, and functions.  The DBA guidance was that it may not be a good idea.

I would recommend including Stored procedures if those procedures involve tables that are published in Publisher.
If those procedures don't contain any tables involved in Publication, then its not a good idea to include those procedures in Replication.

Similar is the case for Functions, views and Stored Procedures too.

>> Can you help me understand if replicating stored procedures, views, and functions is possible?

Yes, it is possible.
Ref:
http://msdn.microsoft.com/en-us/library/ms152754.aspx

And if you are using Wizard to create Replication, just add the required SPs, Views and Functions.

>> I felt if they were replicated, it would make the process much more efficient and reliable.

Yes, it is only if the objects depends upon tables/ views that are already replicated. If not, then it is not recommended.

>>  However, I am curious if SQL Server would resolve dependencies...like a view that uses a function that hasn't been replicated over yet because the view made it there first, etc.

No, SQL Server doesn't applies dependencies in this case.
You have to manually check for dependencies and replicate those objects in Publisher accordingly.

Hope this answers..
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Thank you all for the input. TAB8 and RRJEGAN17, I have a few questions based on my reading of the informit and microsoft articles you posted.  First, I learned from the articles that the EXECUTION of the stored procedures would be replicated to the subscriber...meaning that if I execute procedure A on the Publisher, it would also execute Procedure A on the Subscriber.  I definitely wouldn't want that and am unsure why that is necessary since the data affected by the stored procedures would replicate anyway.  The articles do seem to indicate that you can prevent the forwarding of the execution of the stored procs though.

In essence I am interested in just the *objects* being replicated over with no ties to the execution.  For instance if I create a procedure called "UpdateEmployee" on the Publisher, I would like the "UpdateEmployee" procedure to be replicated to (created on) the Subscriber.  But I want the actual execution of UpdateEmployee to be independent between the Publisher and Subscriber.

Can you assist me in leveraging the articles you posted to understand if this is possible? Thanks for your help.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Regret for the delay dentyne as I got stuck up in other works.

>> The articles do seem to indicate that you can prevent the forwarding of the execution of the stored procs though.

Yes, its possible.

>> For instance if I create a procedure called "UpdateEmployee" on the Publisher, I would like the "UpdateEmployee" procedure to be replicated to (created on) the Subscriber.  But I want the actual execution of UpdateEmployee to be independent between the Publisher and Subscriber.

If your requirement is this, then you need to exclude this procedure from Publisher and create it in both Servers (Publisher and Subscriber) individually so that it would work as you expected.

If you include it in Publication, then the procedures output data alone would be replicated to Subscriber server and Procedure would not be executed at Subscriber.

Hope this answers and clarifies
Commented:
You can select not to replicate stored procedures and triggers viwes etc..  from your publication ..  goto your publication select properties, under articles , select your article and it will allow you select what you wish to replicate

Author

Commented:
Thank you all for the replies.  I apologize I am so late in getting back to you.  Your replies have helped me get the information I need to make some decisions around the feasibility of replicating stored procedures.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial