Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 7th, 2004, 06:37 AM
Registered User
 
Join Date: Nov 2003
Location: bangalore, karnataka, India.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mpradeep Send a message via Yahoo to mpradeep
Default Database Available during changes

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
Reply With Quote
  #2 (permalink)  
Old June 7th, 2004, 09:19 AM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old June 10th, 2004, 05:49 AM
Registered User
 
Join Date: Nov 2003
Location: bangalore, karnataka, India.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mpradeep Send a message via Yahoo to mpradeep
Default

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
Reply With Quote
  #4 (permalink)  
Old June 10th, 2004, 11:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old June 10th, 2004, 09:14 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #6 (permalink)  
Old June 11th, 2004, 09:34 AM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old June 11th, 2004, 10:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #8 (permalink)  
Old June 14th, 2004, 11:46 AM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old June 14th, 2004, 07:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #10 (permalink)  
Old June 17th, 2004, 08:42 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem to restore database in C#2005 database acmuralee MySQL 0 March 25th, 2008 04:42 AM
Microsoft JET Database Database Engine (0x80040E09 cannielynn0312 Classic ASP Professional 2 December 17th, 2007 02:50 AM
Copying Table From one Database To Anoter Database jayanth_nadig VB Databases Basics 1 June 19th, 2006 02:39 PM
Upload image to database or not to database? thachnn SQL Server ASP 0 October 11th, 2004 03:00 AM



All times are GMT -4. The time now is 06:21 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.