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

Clustering Expand / Collapse
Author
Message
Posted Sunday, October 26, 2008 11:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:44 AM
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.
Post #591917
Posted Tuesday, October 28, 2008 4:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
What's your reason for clustering? What are you trying to gain?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #592718
Posted Tuesday, October 28, 2008 6:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
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
Post #592806
Posted Wednesday, October 29, 2008 5:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:44 AM
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
Post #593482
Posted Wednesday, October 29, 2008 7:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
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
Post #593568
Posted Wednesday, October 29, 2008 9:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #593704
Posted Wednesday, October 29, 2008 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:44 AM
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).
Post #593768
Posted Wednesday, October 29, 2008 1:51 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #593912
Posted Thursday, October 30, 2008 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:44 AM
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.
Post #594238
Posted Thursday, October 30, 2008 6:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
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
Post #594242
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse