March 19, 2008 at 12:16 am
Comments posted to this topic are about the item SQL Server Scalabilty - An Introduction
March 19, 2008 at 1:14 am
In a cluster of say 3 servers would'nt the resources of the 3 servers be used. So cant clustering be used for sacling out?
"Keep Trying"
March 19, 2008 at 2:58 am
Only if they were activeactiveactive, and as they can't share data disks you still need to figure out how to maintain data integrity across them.
March 19, 2008 at 3:18 am
Richard is right - because clustering is a 'Shared Nothing' architecture - only one node at a time would be able to host a particular database.
Imagine the extreme scenario where your single database server held a single database - and then you wanted to scale this - clustering would give you no benefits at all.
To take advantage of active/active clustering to scale, you would need to divide up the data into multiple databases and change the application to 'know' where data was - but that's a very different scenario to what I originally faced.
March 19, 2008 at 6:14 am
A nice start, but a little short. Are you planning a follow up article? How about links to additional resources?
March 19, 2008 at 11:56 am
March 19, 2008 at 12:52 pm
You can scale out with a cluster, but you need to think about the bigger picture. Picture a four node cluster with all the nodes active, each one with it's own instance. All four instances can fail over and run on any of the four nodes, so if you lose a node you still have all your data available, though with only 75% of the performance. Still better than losing the whole thing. The data is then partitioned across the four instances.
If you really want to get crazy, just install 64bit Windows Server 2003 Datacenter Edition, and put it on a server with 160 GB of RAM and 16 processors. Divide it up into 16 VMs running 32bit Windows Server Enterprise Edition, each with 8GB of RAM and 2 processors, leaving 32GB of RAM to the host server for managing the VMs. Set up the VMs in an sixteen node cluster. Install 32bit SQL Server Enterprise Edition on each VM, again using the sixteen node cluster. You can put up to 50 instances on each node, so you can have each VM running 2 instances, all of which can fail over to any other VM. In this configuration, you can partition the data across 32 instances and still have full failover capability. Sounds horribly expensive? Except that the only major upfront expense is the main server chassis, since you can add processors and RAM as needed. Tie it into a SAN and add disk as needed. Since you're using VMs, you can also add software licenses as needed. Now if you take advantage of the virtualization option for SQL Server licensing, you save even more on licensing - you only need one server license.
http://www.microsoft.com/sql/howtobuy/virtualization.mspx
Joshua Perry
http://www.greenarrow.net
March 19, 2008 at 1:11 pm
Joshua Perry (3/19/2008)
If you really want to get crazy, just install 64bit Windows Server 2003 Datacenter Edition, and put it on a server with 160 GB of RAM and 16 processors. Divide it up into 16 VMs running 32bit Windows Server Enterprise Edition, each with 8GB of RAM and 2 processors, leaving 32GB of RAM to the host server for managing the VMs. Set up the VMs in an sixteen node cluster. Install 32bit SQL Server Enterprise Edition on each VM, again using the sixteen node cluster. You can put up to 50 instances on each node, so you can have each VM running 2 instances, all of which can fail over to any other VM. In this configuration, you can partition the data across 32 instances and still have full failover capability. Sounds horribly expensive? Except that the only major upfront expense is the main server chassis, since you can add processors and RAM as needed. Tie it into a SAN and add disk as needed. Since you're using VMs, you can also add software licenses as needed. Now if you take advantage of the virtualization option for SQL Server licensing, you save even more on licensing - you only need one server license.
http://www.microsoft.com/sql/howtobuy/virtualization.mspx%5B/quote%5D
Only two things I see with this. One, Microsoft does not recommend clustering VM's in a production environment on a single server. Two, if the hardware dies, it takes all your virtual servers with it.
I like the idea of the BAM and connecting it to a SAN, but instead of VM's, I'd use multiple instances instead. Greater control over allocation of resources by the DBA to each of the instances.
the other quest, why burden the system with VM's, when you can just use multiple instances?
😎
March 19, 2008 at 1:23 pm
Microsoft does not recommend clustering VMs, but does support it and it does work very well in actual practice since the environment remains available when patching and doing other maintenance activities - software availability versus hardware availability. The same reason to use VMs. You can run DEV, TEST, and PROD environments all on the same piece of hardware, but reboot each environment independently because of the VM. I was simply pointing to scalability, but if you also want reliability, no reason you couldn't add another piece of identical hardware and cluster the host server two-way. Of course, when you're talking about datacenter server, you're also looking at things like hot add for processors and memory and using MIBs to monitor the hardware to keep it highly available. You get the same hardware availability as a mainframe because of the management interfaces. You also get a technician from the hardware vendor onsite within hours with replacement parts when necessary. You're also able to do things like memory and processor mirroring inside the server chassis.
Joshua Perry
http://www.greenarrow.net
March 19, 2008 at 1:56 pm
Would a nice solution be to create a federated database, with each node being a high availability cluster?
By having each node in the federation being a high availability cluster, that takes the issues regarding the entire DB being down with one node failure.
Being a high availbility cluster, that would remove issues about data syncronisation wouldn't it?
March 19, 2008 at 2:18 pm
Once you move into the Windows Datacenter Edition world the hardware is highly available even with only one server.
http://www-03.ibm.com/systems/x/solutions/os/windows/datacenter.html
http://www.microsoft.com/windowsserver2003/datacenter/dcprogram.mspx
http://technet.microsoft.com/en-us/windowsserver/bb429508.aspx
and you have support levels from the vendors that are way beyond anything you get with a commodity server.
Joshua Perry
http://www.greenarrow.net
March 21, 2008 at 7:28 am
I want such a server 😀 That'd be just awesome!!!
The article was correct but a tad short - it seems like the start of a series. I'd say that it's certainly easier from a developer's point of view to just throw more hardware at the problem for the database and have multiple identical web servers for the presentation layer.
Depending on your application you might be able to get away with some sort of merge replication or bidirectional transactional replication... Merge is probably safest but you'd want to have "sticky sessions" on your web servers so that a user who's working with database A will stick with database A for the duration of their session since their transactions may not have hit database B yet. For "admin" users who wish to see totals, etc they can either be aware of the latency of updates or use views to get bits of data from each database/server.
May 30, 2008 at 4:34 pm
@ Mr. Perry
32bit/x32 OS can not address more than 4GIG of RAM. Therefore, you can not assign 8gig of ram to a 32 bit guest VM. For additional detail re: how the USER MODE portion of the available RAM can be increased with caveats:
http://blogs.technet.com/askperf/archive/2007/03/23/memory-management-demystifying-3gb.aspx
I think the single node "massively VM" idea is a fun thought exercise, but that's all it is (with today's VM state of the art).
There is a third option I don't think was mentioned, forgive me if it was, and that would be using a distributed/n-Node read-only cache in front of the DB. A cache hit pulls the data/object from the distributed non-blocking cahce, a cache-miss is equivalent to a DB hit, of course all UPDATE/INSERT operatrions hit the DB. This is an accepted way to increasse read operations scalability in a linear fashion.
If you are interested in this concept, google/live search:
1. MEMCACHED
2. Oracle Coherence (Tangosol)
3. Scale Out Software
A fourth option is parallelization using multiple DB nodes fronted by a "parallel query and controller node" that sends the query to multiple back-end DB nodes (sort of like striping a drive, its like "striping a query" --> at a gross conceptual level).
May 31, 2008 at 8:37 am
adam cassel (5/30/2008)
@ Mr. Perry32bit/x32 OS can not address more than 4GIG of RAM. Therefore, you can not assign 8gig of ram to a 32 bit guest VM. For additional detail re: how the USER MODE portion of the available RAM can be increased with caveats:
http://blogs.technet.com/askperf/archive/2007/03/23/memory-management-demystifying-3gb.aspx
I am aware of the /3GB and can't use it because that does not leave enough free PTEs for the backup software to run each night - my data volumes are all 300GB or more.
As for the statement about not being able to run 8GB in 32 bit, I have to disagree since I am currently doing that using AWE on all of my 32 bit SQL Servers. I have minmemory set to 2048 and maxmemory set to 6144 and my servers all take advantage of the 6GB available to them. As mentioned, this does require 32 bit enterprise edition of windows, which actually allows for 32GB of physical RAM.
http://technet.microsoft.com/en-us/library/ms179301.aspx
http://technet.microsoft.com/en-us/library/ms190731.aspx
Joshua Perry
http://www.greenarrow.net
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply