Database Available during changes

mpradeep · June 7th, 2004, 06:37 AM

Hi,

I have a database DB1 it for discussion sake imagine it has around 10 tables and i have a ASP application which serves 24/7 hence i need to minimize my downtime.

This downtime iam talking about is not about disaster recovery or backup or any phyisical solution.

I need to maintain zero down time when my application is undergoing a change some thing like when iam changing my table structures and altering stored procedures adding columns from tables ,dropping columns from tables stuff like that.

During these changes users should be able to use the applications read data ans also save update data .

Any help will be appriciated.

regards
Pradeep

Pradeep

Hal Levy · June 7th, 2004, 09:19 AM

Maintaing the database while live is a big mistake and WILL result in data loss, if you can even get it to work.

If you want to be up 24/7 you will need to have 2 servers and update the offline one.. then repoint your app to the upgraded server and then upgrade the other server.

Using NLB in Win2k3 you can have it failover itself.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

mpradeep · June 10th, 2004, 05:49 AM

Hi,

Your idea of keeping 2 server is fine and acceptable but if iam upgrading my server2 keeping server1 online some data entry would have been done on SERVER1 then once my server2 updgrade is finished i have to point to Server2 THen make the changes on server1 but how to replicate only the new data that has been changed in server1 when server2 is down and viceversa.

Pradeep

bmains · June 10th, 2004, 11:17 AM

Hello,

Will you also be upgrading the app when you make these table changes? Aren't dropping or altering tables/columns going to affect the app that is using them?

Brian

happygv · June 10th, 2004, 09:14 PM

Hi Pradeep,

I think the purpose of having two DB servers is to have replication setup on them, so that when any change is made to one, that gets replicated automatically to the other. Else there would be no point in having 2 servers with data on one server that does not exist on the other.

The point is, when one wanted to updgrade, one of those replicated servers can be brought offline, and upgraded, tested and if everything is fine then it can be brought online meanwhile take the other server offline and get that upgraded too and bring online. Then allowing replication to occur from both the servers to have same data.

PS: But never forget to stop replication during upgradation of either of the servers, which means, replication has to be stopped once before one of the servers is taken offline.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

Hal Levy · June 11th, 2004, 09:34 AM

As both Brian and Vijay have stated- if your making changes to the table, the expectation is the app would change also.. at least at some level.

What I don't understand is why you can't find the slowest time and bring the system down for 10 minutes to make the change, it's not like changes are made on an hourly basis or something. Every system I have ever worked on has a short period of downtime for data maintenance.

It's just how things work.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

happygv · June 11th, 2004, 10:21 PM

I agree with what you say Hal.

But when that can be done without down time too, why can't one stick to that is what I am trying to tell here. In my case, it is a 24/7 environment, where customers from all around the world would be messaging and it is very critical to keep the system up always. And we do make changes to dbs or apps depending on the adhoc requirements. Still we do that without downtime in most of the cases, as in case of downtime it has to be notified to all the customers well in advance, say a week ahead, which is not so good to let the customer know that we do maintenance every now and then.

Cheers!

_________________________
-Vijay G

Strive for Perfection

Hal Levy · June 14th, 2004, 11:46 AM

If it can be done without down time- great!
The question is- what is appropriate to do with the sytem still online.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

happygv · June 14th, 2004, 07:07 PM

Aah... Here it is.

Having the replica of the system, the one of the DB servers can stopped with replication and brought offline also one of the app servers, upgraded along with app server too, and test that out thoroughly and keep things ready to bring this set online and to bring offline whichever is currently online. As in our case, these are firewall changes, so that direct the request from firewall to the upgraded ones and take the other offline, so that they too can be upgraded and brought online later. And then the replication of DB server can be brought back on.

This is how it works for us without downtime, though the customers are notified of a maximum 10 mins downtime;) which can be used for worst case.

This works perfect for us.
Cheers!

_________________________
-Vijay G

Strive for Perfection

sal · June 17th, 2004, 08:42 PM

Keep your changes to a minimum. Do them after a lot of testing and do not use two servers. Do not complicate your life with the issues that come with two servers If you are shooting for the five nines (99999) then you are shooting your self in the foot by using Winblows. There is no shuch thing as five nines, and that is ok. With 10 tables, I am surprised that you would have so many changes.

Instead, take time to plan the database upfront and you will not have so much down time.

Sal