SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Scalabilty - An Introduction


SQL Server Scalabilty - An Introduction

Author
Message
kevriley
kevriley
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3772 Visits: 2630
Comments posted to this topic are about the item SQL Server Scalabilty - An Introduction
ChiragNS
ChiragNS
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4845 Visits: 1865
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"
RichB
RichB
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2302 Visits: 1065
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.



kevriley
kevriley
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3772 Visits: 2630
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.
David Moutray
David Moutray
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 895
A nice start, but a little short. Are you planning a follow up article? How about links to additional resources?
Pedro R. Lopez
Pedro R. Lopez
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 140
If you server host more than one application, you can have an instance in the cluster by application. On this case you archive failover and scaling out. (1 DB by cluster instance.)

Good introductory article.

Pedro R. Lopez
http://madurosfritos.blogspot.com/
Joshua M Perry
Joshua M Perry
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 551
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64355 Visits: 38778
Joshua Perry (3/19/2008)
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


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?

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Joshua M Perry
Joshua M Perry
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 551
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
marklegosz
marklegosz
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1266 Visits: 1180
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search