Deploy ASP.NET web site changes AND SQL Server changes without downtime

I have an ASP.Net 4.0 web site with a SQL Server 2008 database. I want to deploy dependent changes to both the web site and the database at the same time while keeping the site running. My normal procedure is to first deploy the web site changes, and while the web site compiles, deploy the database changes. This works if I am fast enough to get the database changes out before the first request finishes compiling.

I don't want an开发者_高级运维y down time on the web site.

EDIT: I can't purchase any new hardware or software.

Is there a better way?

EDIT: Note: My web sites do not use persistent information such as session state, so recompiling the application does not cause any problems for me.

---

There is really no good way to prevent IIS from restarting when you release changes to your application. It's possible in theory, but the changes required would outweight the benefits of doing it.

I think you should be looking to keep downtime to an absolute minimum rather than eliminating it altogether. Nobody likes downtime, but it's a necessary evil in a lot of cases.

For your database changes, there are tools which can make the process of updating your database much easier. I would suggest taking a look at SQL Compare and SQL Data Compare from Red Gate. These tools will allow you to compare schemas and data, and synchronize databases in a matter of seconds. I've been using both tools for several years now, and they really are fantastic time-savers.

SQL Compare:

http://www.red-gate.com/products/sql-development/sql-compare/

SQL Data Compare:

http://www.red-gate.com/products/sql-development/sql-data-compare/

---

You are going to have to make your database changes backwards compatible, deploy them first. Then you can keep doing what you are doing with timing issues.

---

I like @rick schott's idea about making db changes backward compatible. But, I think you'll need some kind of cluster/farm/garden ultimately. If you have clustered web servers and clustered db server, you can take one web and one db out of the pool, deploy to those, test the app there. Then put them back in pool, take the other ones out, update them and put them back.

---

How coupled is your code to the database? Does it look like this in some places:

dt.Rows[0]["CustomerId"];

ORMs are great for mitigating database changes. However, this may not be a viable option for you. Instead, outside of major database changes I would try to write code that works with the existing schema and the new schema. For example, make your code less dependent on a column existing (or not)...if not exists for an int, default zero.... if not exists for a varchar, default empty string.

"All problems in computer science can be solved by another level of indirection." Butler Lampson

---

If you are making stored procedure changes, perhaps you can default the parameters as you add new parameters? This let's you move the database code before the web site publishes.

Example of current production stored procedure...

ALTER PROCEDURE [dbo].[p_Stored_Proc_Name]
    @Some_Id INT
AS

Adding a new parameter to stored procedure...

ALTER PROCEDURE [dbo].[p_Stored_Proc_Name]
    @Some_Id INT,
    @New_Parameter = 0
AS

You need to make sure that your stored proc code handles @New_Parameter = 0 the way you'd like it to.

---

Automate the process!

I would write a batch:

1. Copy an App_Offline.htm file to the webserver
2. Deploy ASP.NET app
3. Deploy database changes (can be started in parallel to 2.)
4. Remove or rename App_Offline.htm