September 16, 2010 at 1:13 pm
Guy's,
I need some help, a huge potential client is currently evaulating our product with intentions of buying it. This is a multi-million $ contract. Very big for our business. the client is a well known software company out of Washington. It's owner is very famous and VERY VERY VERY rich.
But they are unhappy with something. We currently have 2 offline maintenance windows blocked off - Wednesday night from 9p-mid and Saturday night 8p-mid. these are the default times to bring the app off line, but we don't perform these offline activiies every week. i would say a few times a month the app is offline due to maintenance. In these windows we perform many changes, but since I'm a DBA, I'm concerned with DB changes. Schema changes, Data move, upgrades, etc.
Now this client doe snot want ANY maintenance windows. They are asking for these changes to be made online, without any offline window.
Our current environment - SQL Server 2008 R2, Clustered, 1TB datasbase. We use Red Gate SQL Compare to make schema changes in production.
I'm not sure this can be done. i was thinking maybe some kind of farm, where we have 4 servers that house an identical database. The app would have to write to all 4 servers in order to keep the data insync. We could bring 1 offline, make the changes, bring it online, point the app here, take the others offline, makes changes here, add them back to the farm?????
I don't know. Seems like I'd lose data along the way.
Any simple solutions I'm overlooking??
September 16, 2010 at 1:22 pm
No you're pretty much on the nose. No downtime really means no "user aware" downtime.
Assuming you are spreading the load accross servers, start the maintenance on the slowest time of day with 1-2 server and redirect to those servers once done.
There's also another option to reload all data live in a temp table, then once the load is done, rename both old and new tables to make the switch "instant".
Or you could use a slidding window approach where you gradually import with small increments on all servers at the same time.
Just out of curiosity, what are you doing during those maintenance windows?
September 16, 2010 at 1:26 pm
Depends. What changes?
Schema changes may or may not be online, depending what exactly you're doing. Index rebuilds can be online in Enterprise Edition. Backups are online. Data migrations, again depends what you're doing exactly.
Patches and the like cannot be online, but if you've got a clustered or mirrored database you can patch without downtime (patch one server while other is serving, then fail the cluster/mirror over and repeat)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2010 at 1:31 pm
BTW, our advice will only cost you 1% of the total value of the contract when you get it :w00t:.
September 16, 2010 at 1:36 pm
these windows are used to perform any DB changes, including those that go with App upgrades. We have a fairly "young" app, that we continue to add new features to and tend to have to apply hot fixes to. We have 200 different clients that are always demanding different features, so we do make a lot of database (schema) changes.
The no downtime window is tough. I'm suprised SQL Server\Microsoft does not have a solution in place.
September 16, 2010 at 1:47 pm
It does... it's called get more than 1 server and upgrade one at a time.
Not a fancy name but it does get the job done.
September 16, 2010 at 1:50 pm
Again, what kind of changes?
Things like alter procedure, alter view, create table are all online operations. Alter table may or may not be, depending on what exactly you're doing.
No (almost no) downtime is possible, but it tends to be expensive. You need Enterprise edition, clustered or mirrored servers and lots of redundancies in place (assuming they also want to unplanned downtime as well)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2010 at 1:59 pm
With another server, how do you sync data? If it takes 3 hours to perform an upgrade on one and the other is still online, how do you merge the data without losing it?
The operations are all the above. Yes we do alter table and that is the issue. How would clustering or mirroring help? You can not access a mirrored DB, it's used for availablity. Clustering is hardware redundency. Not sure how that would help.
But I'm up for any suggestions. Thanks
September 16, 2010 at 2:12 pm
Well here's another good question. Of that ±3 hours workload, what steps absolutely cannot be performed online assuming SQL Enterprise?
I'm pretty sure the list is not so long that there's no way around it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply