loadbalancing and failover SQL server

ChemicalAfinity · 10-17-2009, 07:09 PM

hi all

i am looking at building a loadbalancing and/or fault tolerant SQL server. can someone let me know of the various options of doing this??

i think i can do it using a windows server 2008 cluster, however i am surprised there isnt a documented way of just having 2 sql servers and one acts as a backup

any suggestions and methods would be greatly appreciated

cheers
murray

IT-Tech · 10-17-2009, 09:10 PM

Good SQL Clustering reading:

http://www.sql-server-performance.co..._intro_p1.aspx

You can download the content of the following articles:

http://www.scribd.com/doc/18530720/S...ustering-Guide

You can download this pretty detailed document, but at first it looks like there isn’t any information at the link provided. Look just above the comments, there is a download link. It’s in DOCX format, so you will need either the converter for OFFICE 2003 or below or simply Office 2007 installed.

http://sqlcat.com/whitepapers/archiv...lustering.aspx

Thanks!

ChemicalAfinity · 10-17-2009, 10:19 PM

thankyou heaps for that, it has made for a good afternoons reading.

summing up, the most common way it seems to create a cluster is to use microsofts clustering services, which would have other advantages such as print, file and other services.

i remember a while ago i briefly read about a method of clustering where you had your primary server with SQL server running, and the secondary one acted like a client, where it kept an upto date copy of all of the data, and if the primary server failed, you could select the secondary server

do you know if such a service exists or it that old/outdated??

cheers
murry

IT-Tech · 10-17-2009, 10:43 PM

No. I haven't seen that method used. I have seen something similar to that where they implement strictly NTFRS to replicate to a secondary SQL server (usually to a disaster recovery site for offsite storag), but it's not really a full FAILOVER setup that way either.

Sorry about all the reading I provided you, but at least you have a hard copy! LOL

IT-Tech · 10-17-2009, 10:45 PM

Oh and ......... some more reading for you... LOL

SQL Replication.....

http://msdn.microsoft.com/en-us/library/ms151198.aspx

Thanks and sorry again! LOL

ChemicalAfinity · 10-17-2009, 11:20 PM

beleieve me, it is good to have a hard copy so i can refer back to it, and it provides me with much more detailed information.

it seems as soon as you go for a fail over solution, you have to have a dedicated iscsi storage solution, which makes things much more complicated to setup and configure. i have been looking at the netgear readynas box's which are a similar price to a purchasing a proper server :(

and as soon as you have to impliment an iscsi solution the costs of the project seem to triple, for maximum performance you have to have vlan's and extra hardware. and if you want a TRUE fail over solution you then need to add a mirrored storage solution, ready to jump in if one fails. which then adds another few thousand dollers ontop of the costs

i guess i would be leaning towards a solution where the data is transferd to another server, and the users had to just select a different server. i beleieve that most software have the option to enter a secondary server anyway.

ill keep reading and see what solutions are out there and how well they work

ChemicalAfinity · 10-17-2009, 11:28 PM

that second link is exactly what i was thinking of, replicating the data to another server, then it would just require the software to select which server it would be useing

it is also extremly handy as it would enable me to have the 2 servers in different locations so that if there is a user at a different site they still have access to al of the databases

IT-Tech · 10-18-2009, 07:44 AM

Well, what ever you decide to do, I wish you LOTS of luck! LOL

I'm going to have to venture down this road in about another 3 months or so myself; as our Disaster Recovery System is getting antiquated and we have multiple new SQL platforms coming onboard too.

I’m not really looking forward to it either, between the time and the cost it will be tiresome!

Have fun and best of luck!

**bilbus** · 10-18-2009, 07:45 AM

Thats not clustering, thats replication.

Clustering is 2 servers, 2 installs of sql, and the data is on a san.

ChemicalAfinity · 10-25-2009, 04:43 AM

well ill let you know how i go,

i have realized, with clustering i could get away with only two servers as the cluster servers could also act as san servers, would speed it up a bit, so long as there was plenty of network bandwidth.

i have been approached to design a sql setup, (previously this was only for personal intrest) just to clarify, clustering only provides failover support, where as replication would only provide 2x the processing power.

10-17-2009, 07:09 PM	#1 (permalink)
ChemicalAfinity Registered User Join Date: May 2006 Posts: 108 OS: Apple Mac OS X 10.5; Windows Server 2003	loadbalancing and failover SQL server hi all i am looking at building a loadbalancing and/or fault tolerant SQL server. can someone let me know of the various options of doing this?? i think i can do it using a windows server 2008 cluster, however i am surprised there isnt a documented way of just having 2 sql servers and one acts as a backup any suggestions and methods would be greatly appreciated cheers murray

10-17-2009, 09:10 PM	#2 (permalink)
IT-Tech Tech Hardware Team Join Date: Oct 2009 Location: Indiana Posts: 233 OS: All Microsoft Operating Systems My System	Re: loadbalancing and failover SQL server Good SQL Clustering reading: http://www.sql-server-performance.co..._intro_p1.aspx You can download the content of the following articles: http://www.scribd.com/doc/18530720/S...ustering-Guide You can download this pretty detailed document, but at first it looks like there isn’t any information at the link provided. Look just above the comments, there is a download link. It’s in DOCX format, so you will need either the converter for OFFICE 2003 or below or simply Office 2007 installed. http://sqlcat.com/whitepapers/archiv...lustering.aspx Thanks!

10-17-2009, 10:19 PM	#3 (permalink)
ChemicalAfinity Registered User Join Date: May 2006 Posts: 108 OS: Apple Mac OS X 10.5; Windows Server 2003	Re: loadbalancing and failover SQL server thankyou heaps for that, it has made for a good afternoons reading. summing up, the most common way it seems to create a cluster is to use microsofts clustering services, which would have other advantages such as print, file and other services. i remember a while ago i briefly read about a method of clustering where you had your primary server with SQL server running, and the secondary one acted like a client, where it kept an upto date copy of all of the data, and if the primary server failed, you could select the secondary server do you know if such a service exists or it that old/outdated?? cheers murry

10-17-2009, 10:43 PM	#4 (permalink)
IT-Tech Tech Hardware Team Join Date: Oct 2009 Location: Indiana Posts: 233 OS: All Microsoft Operating Systems My System	Re: loadbalancing and failover SQL server No. I haven't seen that method used. I have seen something similar to that where they implement strictly NTFRS to replicate to a secondary SQL server (usually to a disaster recovery site for offsite storag), but it's not really a full FAILOVER setup that way either. Sorry about all the reading I provided you, but at least you have a hard copy! LOL

10-17-2009, 10:45 PM	#5 (permalink)
IT-Tech Tech Hardware Team Join Date: Oct 2009 Location: Indiana Posts: 233 OS: All Microsoft Operating Systems My System	Re: loadbalancing and failover SQL server Oh and ......... some more reading for you... LOL SQL Replication..... http://msdn.microsoft.com/en-us/library/ms151198.aspx Thanks and sorry again! LOL

10-17-2009, 11:20 PM	#6 (permalink)
ChemicalAfinity Registered User Join Date: May 2006 Posts: 108 OS: Apple Mac OS X 10.5; Windows Server 2003	Re: loadbalancing and failover SQL server beleieve me, it is good to have a hard copy so i can refer back to it, and it provides me with much more detailed information. it seems as soon as you go for a fail over solution, you have to have a dedicated iscsi storage solution, which makes things much more complicated to setup and configure. i have been looking at the netgear readynas box's which are a similar price to a purchasing a proper server :( and as soon as you have to impliment an iscsi solution the costs of the project seem to triple, for maximum performance you have to have vlan's and extra hardware. and if you want a TRUE fail over solution you then need to add a mirrored storage solution, ready to jump in if one fails. which then adds another few thousand dollers ontop of the costs i guess i would be leaning towards a solution where the data is transferd to another server, and the users had to just select a different server. i beleieve that most software have the option to enter a secondary server anyway. ill keep reading and see what solutions are out there and how well they work

10-17-2009, 11:28 PM	#7 (permalink)
ChemicalAfinity Registered User Join Date: May 2006 Posts: 108 OS: Apple Mac OS X 10.5; Windows Server 2003	Re: loadbalancing and failover SQL server that second link is exactly what i was thinking of, replicating the data to another server, then it would just require the software to select which server it would be useing it is also extremly handy as it would enable me to have the 2 servers in different locations so that if there is a user at a different site they still have access to al of the databases

10-18-2009, 07:44 AM	#8 (permalink)
IT-Tech Tech Hardware Team Join Date: Oct 2009 Location: Indiana Posts: 233 OS: All Microsoft Operating Systems My System	Re: loadbalancing and failover SQL server Well, what ever you decide to do, I wish you LOTS of luck! LOL I'm going to have to venture down this road in about another 3 months or so myself; as our Disaster Recovery System is getting antiquated and we have multiple new SQL platforms coming onboard too. I’m not really looking forward to it either, between the time and the cost it will be tiresome! Have fun and best of luck!

10-18-2009, 07:45 AM	#9 (permalink)
bilbus TSF Enthusiast Join Date: Aug 2006 Posts: 949 OS: OS2 Warp	Re: loadbalancing and failover SQL server Thats not clustering, thats replication. Clustering is 2 servers, 2 installs of sql, and the data is on a san.

10-25-2009, 04:43 AM	#10 (permalink)
ChemicalAfinity Registered User Join Date: May 2006 Posts: 108 OS: Apple Mac OS X 10.5; Windows Server 2003	Re: loadbalancing and failover SQL server well ill let you know how i go, i have realized, with clustering i could get away with only two servers as the cluster servers could also act as san servers, would speed it up a bit, so long as there was plenty of network bandwidth. i have been approached to design a sql setup, (previously this was only for personal intrest) just to clarify, clustering only provides failover support, where as replication would only provide 2x the processing power.