Rolling Deployment of a web application in a web farm

We have asp.net web application with sql server that is deployed on server farm with federated databases. We use stored procedures (as opposed to prepared sql statements) and inproc sessions. As part of the achieving high availability (at least for service packs with controlled set of changes), we intend to use rolling deployments on the farm which means we do this:

1. Shut down a group of servers
2. Deploy the application on these servers
3. Bring up these servers
4. Shut down another group. Repeat 1-3 for all the groups.

Though this would mean some users would be kicked off, the application is still available and maintainence page need not be put up.

The easy part is to deploy the web application, but the tougher part is if there are changes in the stored procedure (for e.g. a new parameter is added). There will be a point when the both the versions of stored procedure would be required (the existing one and the new one being deployed).

We have considered 4 options for the stored procedures:

1. Do not use rolling deployments in case a release has a stored procedure change
2. If rolling deploym开发者_Python百科ent is being used in a release, only new stored procedures would be allowed, even if it means code duplication
3. Introduce stored procedure versioning and some framework component in the app tier to automatically append the version number to the sproc being invoked.
4. Overwrite the existing stored procedure and allow some stored procedure calls to fail.

All the approaches have pros and cons and of these 3) is the most viable but also most complex. Which one would you recommend? Are there any tricks in sql server to handle this scenario? Are there any other approaches?

If you want to cover any type of changes to your database, you might want to take a look at database mirroring and rolling upgrades.

Excerpt from link:

Improves the availability of the production database during upgrades.

To minimize downtime for a mirrored database, you can sequentially upgrade the instances of SQL Server that are participating in a database mirroring session. This will incur the downtime of only a single failover. This form of upgrade is known as a rolling upgrade.