January 10, 2003 at 4:40 pm
We have an OLTP database where we are able to make changes currently to database Objects like Stored procs, UDF's and even data once every week at Night afteter 11 PM.
The problem is that the management wants to have the database up 24*7 and we are a shop where we do have to put in database fixes and changes from time to time.
Now the middle tier programmers have no problem rolling in their changes as they have multiple servers in the WEB farms and can roll their changes to one server at a time bringing it out and then into the farm.
For the DBA's we have one server, and even if we ask for another server, and set replication or some other mirroring process
on, how would we deal with apps that hit the database while we are making changes to procs or data or switching servers.
Is there a way of not having any downtime and yet not hinder any transaction processing
while changes are made to sql code. How would we need to set up our servers or is their software that we can buy to do this.
Thanks!
January 10, 2003 at 5:01 pm
I dont run 24x7, but I use a technique that might help. Rather than change the code, I create a new proc. See this article for more:
http://www.sqlservercentral.com/columnists/awarren/versioncontrolforstoredprocedures.asp
Basically this puts the work back where it belongs, on the developer! Its easy to fight for, since it makes rollbacks almost entirely painless. Table changes are harder. Adds are cake, but alters lock the table and you can't avoid that. On big tables it will take long enough for users to time out. Potentially you could put up a view to redirect them while you change the table, then repoint the view when the change is done, something like that.
Not many places truly need 24/7. You may not like the timing of the window, but I bet its there. If you can get 3:00 - 3:30 am, thats time enough for most any change (maybe even a service pack).
Andy
January 15, 2003 at 3:33 pm
Is there any way to have maybe a clustering at the software or hardware level where you can have multiple copies of the same database and can somehow apply changes to one copy and then somehow the changes get moved to the other databases in the cluster. Someone suggested that something like this is done on 24*7 sdystems, is that true. Someone also suggested tools like double take, but it seems we would still need to interrupt transactions possibly because we would have to change the IP address from one database server to another. Need some advice.
Thanks..
January 15, 2003 at 4:25 pm
Im new to the SAN stuff, but maybe you could do the BCV thing where you split off a copy, then maybe could use it to sync the other copy.
Andy
January 15, 2003 at 5:57 pm
The feature of two active database on a cluster is an oracle feature. The understanding which you are having is correct and that's how the oracle cluster works. but with sql server, clustering is more at the os/hardware level.
January 15, 2003 at 8:58 pm
You could try an option (or a variation) MS presented at TechEd last year. Replicate you data to SQL Servers that are load balanced and used to support all of your "read" type queries. This can be used to get around your availability issue when a database is updated (i.e. have at least 2 replicas and upgrade one at a time letting the load balancer redirect all requests to the other SQL Server).
For updates, use a product such as MSMQ. When you need to upgrade the master copy of the data, simply stop processing messages from MSMQ, stop replication and then apply your upgrades.
Yep, this is heaps more complicated than your current environment but it should get you pretty close to 24*7.
January 16, 2003 at 12:07 pm
Thanks for the tips. Also, has anyone used Double take or similar third party software that basically replicates all changes made to a server to a standby one. It's not possible to swap server's uninterrupted if you use something like double take is it ???
January 23, 2003 at 8:31 am
The onely way to have true 24x7 is with multimaster replication which even Oracle parallel server doesn't really have. This would require one or many servers that can independently handle the entire database function so that if one goes down, the others still have all the schema and data. Data is maintained on all the machines by a replication scheme between them. If a machine is brought offline to make ddl modifications, it doesn't affect the others except for throughput. Once the modified machine is ready, it can be brought online and the others can be brought off and modified. Planning has to take into account data captured during the transition that will not conform to the new schema and some plan of modifying the data needs to have been developed. Replication between the machines has to be disrupted until they all can accept the new form of data. There is alot more to be said about this e.g. keeping one master and one read only replica which can become the master during changes.
We use double take but it wouldn't work here because you don't want to replciate while you're making changes.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply