August 24, 2010 at 5:44 pm
I was wondering if someone can educate me or point to a resource that I can use to help me educate myself about how do web sites that use sql server keep up time while at the same time changes are being made.
is there a white paper that discusses how to maintain 24x7 uptime and at the same time manage changes to backend? how come amazon is never down? or ms is neverdown? how the databases changes are made to the backend?
thoughts?
August 24, 2010 at 9:34 pm
That's a WHOLE lot of questions. The reason sites like Amazon are never down is because the site/application is very widely distributed. By this I mean the web front end you're seeing is actually being served up to you by one of MANY different webservers. Those servers are all part of (and I'm taking a stab at this since I don't know their actual makeup) a load balanced cluster.
Load balancers allow you to add individual servers (or nodes as they're sometimes referred to as) to a resource. In the case of Amazon you'll have a load balancer with a virtual name/DNS entry registered to Amazon.com. That is the load balancer's virtual name. From there you assign nodes as needed to this virtual name. So...
Amazon.com
-ServerA
-ServerB
-ServerC
-
-
-ad nauseum
Each web server in this setup acts individually of each other but all tie in to a distributed back-end database (again, same type of setup only the databases are distributed/clustered/etc). As an end user you point your browser to Amazon.com. The load balancer then looks at its pool of web server resources and directs you to the least busy one. This continues in a round-robin type fashion (again highly simplifying load balancers here, just getting the general idea across to you). The load balancer's role is to make sure the load is evenly distributed across its many nodes. Now you know where the great name came from!
As each node needs maintenance such as service pack upgrades and whatnot you can pull them off the load balancer. When you take them offline like this the end users hitting the load balancer are directed to nodes that are still online. The balancer knows Node X is offline so it won't bother trying to connect to it. Once you're done with your maintenance/upgrade you simply assign it back to the load balancer and away you go.
On the database side of things you can read up on clustering and see how this is done. You can create clusters with multiple nodes. This allows you to apply SP's/patches to individual nodes. For great article on HA read Ted Krueger's post from his SQL University week: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-server-high-availability
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
August 25, 2010 at 8:33 am
Well clustering only allows you a passage to the SQL data files which sit on shared drive.
how do you make changes to the database structure while its being used by the web front end. how do these high end websites do change management on these highly used databases? i think the last thing they want to do is have an outage window to make a column change to table..
so mystery remains...
August 25, 2010 at 8:59 am
I'm actually a full-time webmaster (SQL is actually secondary -- but critical -- for me).
What changes are you talking about? Do you mean table/infrastructure changes, or are you talking about data updates? You shouldn't experience any downtime at all. Ideally, you should have two environments, one for testing, and one for live. I generally try everything out in my test environment (to make sure I don't break anything). If I'm confident that my changes won't break the web site, I'll take the time to make sure I document my changes, implement them, and make sure they work properly.
Also, @sqlchicken is correct. Large scale environments are likely running multiple servers/environments in order to maintain 24x7 uptime.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 25, 2010 at 9:07 am
I am concerned with changes to table strcuture.. for example removing columns, changing data types. I am not talking about data or rows.
I am concerned with change to the underlying structure and relationships.
August 25, 2010 at 9:26 am
shahab-205604 (8/25/2010)
I am concerned with changes to table strcuture.. for example removing columns, changing data types. I am not talking about data or rows.I am concerned with change to the underlying structure and relationships.
First thing, above all else, you need to figure out what will be affected by your change.
If you don't already have one set up, I would set up a testing environment that mirrors your production environment. This will allow you to safely test your changes, as well as set up a strategy for migrating your changes to production.
Tweak your web code according to the database changes, and test them out.
Once you've tested everything, and you're confident that everything works properly, back everything up and migrate your code. If this is done properly, the amount of downtime should be minimal (or, sometimes, nonexistent).
And make sure you TEST and DOCUMENT everything.
Hope this helps . . .
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 25, 2010 at 9:32 am
Amazon is absolutely down at times. For some people. I have had it break down, but pick back up in a minute, for me. Likely Ray, in NYC, didn't see the outage even if he was on at the same time since there are so many servers, as listed above.
For small environments, it's different. If you want to change schema, you will have some downtime. Depending on how busy the table is, you can minimize it, but there would be some downtime somewhere.
It's usually not a big deal for most companies. If it is, and it's costing your company lots of $$, then spend some $$ and get a consultant to help you manage transitions and build a better infrastructure with multiple servers. Otherwise, do what's talked about above. Test (a lot) and schedule changes, have backups, and rollback scripts.
August 25, 2010 at 9:44 am
Steve Jones - Editor (8/25/2010)
For small environments, it's different. If you want to change schema, you will have some downtime. Depending on how busy the table is, you can minimize it, but there would be some downtime somewhere.
Agreed. I'm working with a small environment, and I've gotten to know the environment pretty well. There will likely be some downtime, but I've actually gotten to the point where I can make changes and implement them with minimal downtime. If my plan works properly, I'm able to implement my production changes within minutes. If it doesn't work, I make sure that I have a backup plan to back out my changes.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 25, 2010 at 9:49 am
what that infrastructure would look like? multiple sql server instances? how do you synchronize them?
August 25, 2010 at 10:28 am
shahab-205604 (8/25/2010)
what that infrastructure would look like? multiple sql server instances? how do you synchronize them?
It would probably depend on your environment.
For me, personally, I'm working with a small environment (to give you an idea of scale, my database records number in the hundreds, maybe thousands in some cases, but not much more than that). I'm running two databases (one test, one production) on the same SQL Server (that I run locally on my own machine, so in a manner of speaking, I have two local test environments, even though one of them is called "production"). Additionally, my remote database is set up the same way (one beta test, one production, both running on the same SQL Server).
Whenever I make changes to my infrastructure, I first test it locally on my test environment, then I upload the changes to the remote test environment. If my changes work there, I make the changes to the local production environment, then test again. If those changes work, I implement them into production (and even then, I still do some more testing to make sure I haven't broken anything).
I'll generally make these changes when I know I can minimize downtime to a matter of minutes (if someone is on the site during that time, they'll likely experience a hiccup, but that's about it). If a change is more involved, I'll wait for a time when traffic is down and implement it then.
Granted, my environment is small enough that I can get away with doing that. If yours is larger, you might need to do something different. We have been talking about doing a restructure somewhere down the road; in that case, we'll likely build an entire web/data environment, and once we're confident that it's ready, we'll "flip the switch" (that is, redirect the domain name to the new environment).
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 25, 2010 at 1:31 pm
All good advice regarding test environments. Here's a couple of good articles on handling change control management on your database systems:
http://www.brentozar.com/archive/2006/01/automating-sql-server-version-control-with-visual-sourcesafe/[/url]
http://www.codinghorror.com/blog/archives/000743.html
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply