October 26, 2008 at 11:51 pm
Dears,
i have 6 databases and 3 servers.
i need and advice on how to cluster them i want to divide the databases onto 2 server where i will install 3 on the first server and 3 on the other one.
what is the best practice to do clustering for this scenario?
i have discuss this with one friend where he recommend to make the 2 servers Active/Active and the third server as a data warehouse for the reporting with replication of the 2 other servers, cause the databases contain a huge number of records and in the backend system there is a very complicated queries.
thanks in advance for your help and advices.
October 28, 2008 at 4:16 am
What's your reason for clustering? What are you trying to gain?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2008 at 6:38 am
Not sure what you are trying to achieve here. Clustering is essentially for fault tolerance NOT performance (load balancing), so active/active is not going to share your work load.
With regard to your replicating databases to your third server and reporting on that, it is a good plan but not sure how replication would cope with a failover (never tried this before) but you do need to ensure that your databases lend themselves to replication, ie triggers and active content wont replay on replication (that caught me out once).
Hope this is of use, otherwise if you could explain the why and wherefores like Gail asked, will be easier to advice. 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 29, 2008 at 5:30 am
Thx For your replay.
what i m trying to do is that i need to cluster the database for recovery at the same time with performance.
we are running a big project with SQL server databases (where the number of databases is mention in the previous question) all what i want is to advice me on how to cluster them (isn t clustering in case of any server is down there will be other to replace it??)
and this s is true that i want to divide the 6 databases on 2 servers active/active for performance .
if you have any better idea please advice and i will be thanksfull.
thx in advance again.
Hany
October 29, 2008 at 7:05 am
Ok, so think I understand a bit more now what your trying to achieve. Below is a link to a web cast which will explain basic SQL clustering setup and maybe be of use. The sites got loads of other casts so just browse around.
http://www.jumpstarttv.com/clustering-sql-server-2005_33.aspx?searchid=4453
SO, you have three options with what you wish to do and these are below. All require multiple shared drives (SAN) and ideally separate drives for DB's and Logs. I will assume that you know about Windows clustering, SQL clustering, versions, quorum and have the appropriate licenses.
Option 1: Active/Passive failover cluster:
Here you will install SQL and have it active on one node with all 6 databases active. In the case of a hardware failure with the active node, the clustered group (including SQL) would fail over to your second server (passive node).
You have already said that you are not interested in this so I will leave it there.
Option 2: Active/Active failover cluster
In this example you would essentially have two Active/Passive clusters.
Firstly you will need two Windows clusters; one will be primary on svr1 and secondary on svr2 and the other will be primary on svr2 and secondary on svr1. Then you would install a/p cluster on the first cluster. You would setup a virtual server 'v-sql-clus1' with a named instance of 'instance1'. Once installed you would have 'v-sql-clus1\instance1' as an active/passive server.
That takes care of the first a/p, now you need to repeat the procedure from the primary node of the second windows cluster (svr2). Install SQL again as a a/p, though this time you would have virtual server name 'v-sql-clus2' and named instance 'instance2', giving you 'v-sql-clus2\instance2'. Once configured this will be a a/p cluster (though active on the passive node of the first cluster).
Together they form an Active/Active cluster. If node1 of cluster 1 fails then it would fail over to node2 (or node1 of cluster2) and visa versa. You would then put 3 databases on one cluster and the other 3 on the other.
Keep in mind that this solution doubles the number of SAN drives required, IP addresses and network ports required, Licences required, configuration and maintenance overhead etc.
Option 3: Two separate SQL installations with mirrored database redundancy.
Here you would have two servers with SQL installed on each. Each server can be thought of as a stand alone (SAN drives are not required, or windows clustering, additional IP's etc).
You have 3 databases on one server and the other 3 on the second server. Then you mirror the databases on server1 to server2 and mirror the databases on server2 to server1.
It’s really a cheap, not so sophisticated version of Option 2, with lower costs and configuration but with higher risk.
------------------------------------------------------------------------
What ever you decide, remember to check the pre-requisites and ensure you have the correct licenses and have checked hardware to service compatibility. Then load balance your databases between the servers so not all high usage DB's are on one server. 😉
Hope this helps and good luck.
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 29, 2008 at 9:25 am
Hany Ahmad (10/29/2008)
Thx For your replay.what i m trying to do is that i need to cluster the database for recovery at the same time with performance.
Clustering is not a performance enhancement. It's purely for high availability.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2008 at 10:21 am
Schadenfreude-Mei: thx you very much for the valuable information.
GilaMonster i know that clustering is not for performence but i mention that cause i will use the third server for reporting and performance and i didn t want to use all of the three servers in the farm for clustering cause microsoft recommand to use 6 servers for as a perfect clustering moreover i want to keep the last server for reporting only as WAREHOUSE database (read only).
October 29, 2008 at 1:51 pm
Is the hardware of the two servers you're planning to cluster identical? Is the OS the same on both?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2008 at 6:48 am
Hi, all of the 3 servers are the same witht he same hardware but i don t have SAN storage i have SAS Storage.
October 30, 2008 at 6:56 am
Is it direct attached or NAS/Shared Disk?
Unless you can set disk shares up you cannot run a clustered solution. It is possible with vmware.
There must be shared disk for quorum, storage and heartbeat.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply