Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
uberman
Posting Yak Master
159 Posts |
 Posted - 2006-01-12 : 10:38:45
|
| We run a web based application consisting of an IIS/ASP application on one box talking to a sql server on another box.We source control all our DDLS and Stored Procs and ASP files by version.When I want to upgrade any customer (e.g. release new db code (DDLs and/or stored proc files) and ASP code) we schedule a down time period because at some point the ddls have to be run, or the stored procs in the db are out of step with the ASP calls, so that application could break if accessed.I have been asked if there is any way of performing an upgrade without any customer down time......my initial reaction is NO!We do most patches without down time as any affected stored procs are created with a new name (SP1 = SP1.1) and the db is updated first and the asp files copied second; but this is not possible when a major upgrade is performed because of the the number of changes that have to be made (and the fact that the DDL changes may mean that existing stored procs break until the new code is applied)I can't think of a way of keeping the system running and performing a transparent upgrade and was wondering what other people in this situation do...?Any suggestions, experiences, shortcuts, ideas would be appreciated. |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-01-12 : 10:58:13
|
| The only way I can think of to offer zero downtime is to have mirrored systems at the back end, of which you upgrade one half at a time.-------Moo. :) |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-01-12 : 11:14:32
|
| If you had just ASP and DLL changes, you could do the following:1. Have dual web servers, with a hardware load balancer between them and the Internet. 2. When it is time to upgrade, take one server of out the load balancer and apply the patches.3. Put the upgraded server back in service in the load balancer, and take the "old" server out.4. Run in this "degraded" state for a bit until you feel that the new patch is good, then apply that new patch to the un-upgraded server. 5. Put the newly upgraded server back in service in the load balancer.If you have to make DDL changes, I'd say that's probably going to be a call for a scheduled downtime. Upgrading the webserver and the DDL at the same time without customer's getting some weird error messages for a few minutes is very hard. Major upgrades call for downtime, at way off-peak times.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2006-01-12 : 11:44:52
|
| Having mirrored/clustered/load-balanced system is a way of doing this but the costs of this are expensive and you have to question how much downtime is acceptable.You will have to duplicate all the hardware and software in you setup and it takes to do an upgrade will take much longer to implement but in theory for most cases you could have very little downtime.Going from a single webserver with a single db server to doubling you hardware/software/support costs is expensive and often too much so for a small not important (multiple 9 availability 99.9999%, etc).The fact that you are not on a setup that does not have this redundancy implies that the cost is not built in the website is not a mission critical or whatever site. The choice is a few minutes a month or two downtime when you perform an application upgrade or a service what will cost twice the amount to run? Like most things it is possible to achieve this if you throw enough money at it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-12 : 14:21:21
|
| Could you replace your tables when you need DDL changes?So MyTable_V1 becomes MyTable_V2 and a VIEW is put in place of MyTable_V1.To achieve this you may have to have some restrictions - e.g. a column that has changed from INT to CHAR [daft, but just for the purposes of this example!] may have to have MyOldCoumn_INT and MyNewColumn_CHAR columns, and a TRIGGER to replicate MyOldCoumn_INT into MyNewColumn_CHAR for legacy applications.This is more likely acceptable if it is only needed for the duration of the rollout - whereupon the (now a View) MyTable is then dropped - rather than being retained "for ever" to provide legacy support.But I share the view that the amount of testing of JUST the rollout transition would probably be prohibitive.Our Web Application comprises a single ASP file. It is just a list of INCLUDE files, each of which has a version number included in the filename - so the main ASP file for next version will definitely invoke different include files. So in theory we could make the database changes, and then drop in the new "Main" ASP file and we'd be in business!I suppose one other line of thinking might be to have the data in one database, and the "application" in another. So the "application" database contains all the SProcs and VIEWs onto the other database. In this way it would be possible to "restore" a new version of the application database (or in some other way upgrade it atomically). A bit like is commonly done in Access. But it sounds pretty messy to me!Kristen |
|
|
|
|
|
|
|
|
How do you handle upgrading your applications?