SQLServerCentral.com / Article Discussions / Article Discussions by Author / Discuss content posted by Kev Riley / SQL Server Scalabilty - An Introduction / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 13:45:29 GMT20RE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspx[quote][b]adam cassel (5/30/2008)[/b][hr]@ 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 [/quote]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.[url]http://technet.microsoft.com/en-us/library/ms179301.aspx[/url][url]http://technet.microsoft.com/en-us/library/ms190731.aspx[/url]Sat, 31 May 2008 08:37:55 GMTJoshua M PerryRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspx@ 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 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. MEMCACHED2. Oracle Coherence (Tangosol)3. Scale Out SoftwareA 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" --&gt; at a gross conceptual level).Fri, 30 May 2008 16:34:19 GMTadam casselRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxI want such a server :D 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.Fri, 21 Mar 2008 07:28:26 GMTIan YatesRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxOnce 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.htmlhttp://www.microsoft.com/windowsserver2003/datacenter/dcprogram.mspxhttp://technet.microsoft.com/en-us/windowsserver/bb429508.aspxand you have support levels from the vendors that are way beyond anything you get with a commodity server.Wed, 19 Mar 2008 14:18:35 GMTJoshua M PerryRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxWould 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?Wed, 19 Mar 2008 13:56:08 GMTmarklegoszRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxMicrosoft 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.Wed, 19 Mar 2008 13:23:49 GMTJoshua M PerryRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspx[quote][b]Joshua Perry (3/19/2008)[/b][hr]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[/quote]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:Wed, 19 Mar 2008 13:11:42 GMTLynn PettisRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxYou 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.mspxWed, 19 Mar 2008 12:52:51 GMTJoshua M PerryRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxIf 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.Wed, 19 Mar 2008 11:56:48 GMTPedro R. LopezRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxA nice start, but a little short. Are you planning a follow up article? How about links to additional resources?Wed, 19 Mar 2008 06:14:18 GMTDavid MoutrayRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxRichard 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.Wed, 19 Mar 2008 03:18:35 GMTkevrileyRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxOnly 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.Wed, 19 Mar 2008 02:58:43 GMTRichBRE: SQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxIn a cluster of say 3 servers would'nt the resources of the 3 servers be used. So cant clustering be used for sacling out?Wed, 19 Mar 2008 01:14:37 GMTChiragNSSQL Server Scalabilty - An Introductionhttp://www.sqlservercentral.com/Forums/Topic471387-1228-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Administration/62465/">SQL Server Scalabilty - An Introduction</A>[/B]Wed, 19 Mar 2008 00:16:48 GMTkevriley