 |
|
| SQL Server Tips: |
|
|
|
STORED PROCEDURES
Replicated stored procedure options with SQL Server 2005
Jeremy Kadlec, Edgewood Solutions
01.29.2007
Rating: --- (out of 5)
|
Replicating stored procedures between databases can be a straightforward task if the right processes and technologies are available in SQL Server. These would include the native replication features or log shipping. Without them, you can still use manual processes to achieve the same result. In fact, the alternatives may be preferable in particular environments and scenarios.
- Native replication features
With SQL Server 2005 replication, stored procedure definitions are replicated in a few different ways. The Snapshot replication option moves all objects and data, so the definition of the database object is replicated between the Publisher and Subscriber. Transactional and merge replication has the same capability to move the definition of the stored procedures when creating the publication and defining an article. This is achieved by specifying the correct parameter for the @type variable of the sp_addarticle system stored procedure. The options for this variable are:
- proc schema only – supports the stored procedure schema
- proc exec – supports the stored procedure execution
- serializable proc exec – supports the stored procedure execution in a serializable transaction
- func schema only – supports a schema-only user-defined function
One item worth noting is the ability with the @type variable of the sp_addarticle to replicate the execution of the stored procedures on the subscribers. You can do this via the proc exec and/or serializable proc exec parameters. So if you have major transactions that execute via stored procedures, this is one option to replicate the data via the stored execution rather than replicating the data directly. For additional information on these options, reference Publishing Stored Procedure Execution in Transactional Replication and How to: Publish the Execution of a Stored Procedure in a Transactional Publication (SQL Server Management Studio).
- Log shipping
With log shipping, all of the stored procedure code is replicated from the production server to the backup server by issuing a combination of full and transaction log backups. This ensures that as the code changes in the production environment, it will be equal on the backup server. The value here is that without completing any other tasks – besides setting up and monitoring the backups and restores – no other actions will be needed to ensure the code is accurately replicated between the environments. This is very beneficial for disaster recovery and high availability needs, but it may not support your application.
- Manual deployments
As you build your code or use the native features from SQL Server 2005 Management Studio, you can script out the changes to be able to execute the same code on multiple servers. This gives you the ability to group the changes together and manually replicate the code by connecting to the correct SQL Server database. Enhance this function by integrating with a version control system. One such system is Visual SourceSafe, where the code can be built in development and checked into and out of the version control system when testing in the QA environment and deploying to production.
- Automated deployments and synchronization
A final option is to leverage some of the third-party products in the market in order to automate the stored procedure deployment process. Some tools offer the ability to package and deploy the code in parallel across servers in an automated manner. Other tools let you synchronize the code between environments. Contact your favorite vendor to learn about these tools.
Don't forget the process and technology
The technologies listed in this tip provide a reasonable means to technically
replicate the stored procedures between SQL Server environments. Unfortunately, no stored procedure is an island, nor can you take the people and process out of the equation. The reality is that code changes typically require front-end, middle-tier and other DML\DDL changes. In addition, a solidified process must be determined for the change management process between the development, test and production environments. Be sure to balance the technology with your people and processes for the right solution.
| ABOUT THE AUTHOR: |
|
Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
Copyright 2006 TechTarget
|
|
|
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
|
|
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
