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


Clustering


Clustering

Author
Message
Hany Ahmad
Hany Ahmad
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 15
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89329 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Schadenfreude-Mei
Schadenfreude-Mei
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1721 Visits: 1336
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. Smile

Adam Zacks

-------------------------------------------

Be Nice, Or Leave
Hany Ahmad
Hany Ahmad
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 15
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
Schadenfreude-Mei
Schadenfreude-Mei
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1721 Visits: 1336
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. Wink

Hope this helps and good luck.

Adam Zacks

-------------------------------------------

Be Nice, Or Leave
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89329 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Hany Ahmad
Hany Ahmad
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 15
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).
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89329 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Hany Ahmad
Hany Ahmad
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 15
Hi, all of the 3 servers are the same witht he same hardware but i don t have SAN storage i have SAS Storage.
Schadenfreude-Mei
Schadenfreude-Mei
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1721 Visits: 1336
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
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