Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Scalabilty - An Introduction Expand / Collapse
Author
Message
Posted Wednesday, March 19, 2008 12:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:22 AM
Points: 2,684, Visits: 2,438
Comments posted to this topic are about the item SQL Server Scalabilty - An Introduction
Post #471387
Posted Wednesday, March 19, 2008 1:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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"
Post #471405
Posted Wednesday, March 19, 2008 2:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 1,070, Visits: 900
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.




Post #471424
Posted Wednesday, March 19, 2008 3:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:22 AM
Points: 2,684, Visits: 2,438
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.
Post #471435
Posted Wednesday, March 19, 2008 6:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 12:48 AM
Points: 218, Visits: 784
A nice start, but a little short. Are you planning a follow up article? How about links to additional resources?
Post #471516
Posted Wednesday, March 19, 2008 11:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 4, 2009 1:49 PM
Points: 160, 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/
Post #471786
Posted Wednesday, March 19, 2008 12:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:39 AM
Points: 420, Visits: 487
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.usesage.com
Post #471837
Posted Wednesday, March 19, 2008 1:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:09 PM
Points: 23,292, Visits: 32,025
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?




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)
Post #471850
Posted Wednesday, March 19, 2008 1:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:39 AM
Points: 420, Visits: 487
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.usesage.com
Post #471857
Posted Wednesday, March 19, 2008 1:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 7:57 AM
Points: 1,110, Visits: 1,148
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?
Post #471872
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse