dentyne
asked on
SQL Server 2005 replication of views, functions, and stored procedures?
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.
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.
http://www.informit.com/articles/article.aspx?p=601350
This link is very good and explain all your options
This link is very good and explain all your options
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
They have excellent tools for creating scripts to synchronize schema's (or data)