Replicated stored procedure options with SQL Server 2005

Replicating stored procedures between SQL Server environments is a process for which you have many technologies to choose from, including native replication features, log shipping and manual and automated deployments.

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.

1. 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).

2. 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.

3. 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.

4. 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

Get more on this topic:
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.

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.

Dig Deeper on Microsoft SQL Server 2005