Newbie to SQL Clustering..please help

Question

Newbie to SQL Clustering..please help

Low Rider

SSCommitted

Points: 1502
More actions
November 11, 2011 at 11:35 pm

#247678

Hi all,
I am totaly new to SQL clustering. I have two applications, A and B which are going on a
clustered environment (db sql 2008 R2).I am theoretically assuming my database servers
for clustering as below.I have 2 phyical database machines and i want to configure muti-node
or the old school term active/active clustering.Please tell me if am assuming it wrong.
PRODA will host application A's dB and PRODB will host application B's db
I have in mind a configuration like below.
PROdA (Default sql Instance)
-- Install Node on PRODB Server
-- as Failover for PRODA
ie Named instance ProdA\B
PRODB (Default sql Instance)
--Install on Node for PRODA Server
-- as Failover for PRODB
ie Named instance PRODB\A
My Questions are as follows.
1)Will each of the sql instance have a different IP ?
2)IF i want to failover to named instance on PRODB from the default instance of PRODA will
there be any changes to the IP address/Name for the application?
I am assuming the following.
I will be given 2 virtual IP/s name
1st virtual IP will be assigned to PRODA "and" to it's failover on PRODB ie PRODB\A
2nd virtual IP will be assigned to PRODB "and" to its failover on PRODA ie PRODA\B
Is my assumption correct ?If it's correct incase either server fails it will switch over to the
other server and ip adresses will remain the same ?
Please correct me if i am wrong.
Thanks a lot for your trouble.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

viiki.seth Ten Centuries Points: 1127 More actions · Answer 1

Low Rider (11/11/2011)
1)Will each of the sql instance have a different IP ?

Each resource group (instance) will have a distinct IP. Users will view each instance as a virtual server and use that instance's detail to connect to the database server. The instance may be owned by node a or node b at a given time. This detail is invisible to the end user.

2)IF i want to failover to named instance on PRODB from the default instance of PRODA will
there be any changes to the IP address/Name for the application?

The IP assigned to the instances are static IPs. Application connection string doesnt need to be changed. During failover, there will be little hiccups however once the failover completes, the application will be able to connect to the db server.

see this clustering white paper.

http://www.google.co.in/#sclient=psy-ab&hl=en&source=hp&q=sql+server+2008+clustering+whitepaper&pbx=1&oq=sql+server+2008+clustering+whitepaper&aq=f&aqi=&aql=&gs_sm=e&gs_upl=970l6184l0l6313l37l20l0l10l10l3l617l3904l0.13.4.1.0.1l25l0&bav=on.2,or.r_gc.r_pw.,cf.osb&fp=dbf7b31e88fb1529&biw=1366&bih=624

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

Or you may use DB Mirroring.

Server1

DB1 – Principal

DB2 – Mirror

Server2

DB1 – Mirror

DB2 - Principal

Database Mirroring Overview

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

Fabrizio Faleni Ten Centuries Points: 1249 More actions · Answer 3

Dev suggestion of using mirroring instead of clustering could be an interesting alternative to cluetring, especially in case you do not have a SAN available.

Getting back to Low Rider's request about a clustering solution, I would highlight a couple of things:

1- There is no Active/Active SQL Server Clusters, only Failover Clusters where each instance will be active on one node at a time. This might be important in terma of money, because you might spare a lot of Licensing money in consolidating both instances in one node only as hardware is not as expensive a SQL licences.

2- It is good practice to install named instances on clusters, but in your case the proposed solution cannot work:

PROdA (Default sql Instance)
-- Install Node on PRODB Server
-- as Failover for PRODA
ie Named instance ProdA\B

The install of the second cluster node will have to be made identical to the first node, as failing over to another node will be completely transparent to the applications accessing the clustered database.

Each Resource will: share only one SQL instance and SQL Agent service, have a dedicated static ip, have one or more shared SAN data disk drives.

These resources will be active on one node at a time (in a two nodes cluster all resources usally are active on one node only but this is not mandatory: having an OLTP instance and an OLAP instance running on the same box is not advisable) and in case of problems will failover to one of the available failover nodes.

During failover, SQL services stop on active node and start on failover node after disks and network resources have become available on failover node. The process can take usually from 20 to 60 seconds. Applications that are not cluster-aware may need to be restarted or may report error messages during failover, but nothing more than a restart is required.

Failover must be accurately planned and designed, especially when you have a failover node that is not identical to the active node: this would be the case when you set SQLA on node 1 and SQLB on node 2. Failing SQLA on node 2 means letting two SQL instances fit suddenly in one box, with less RAM and CPU than during normal state: it may be OK to run in degraded mode while node 1 is failed, but you have to be sure that your performance still meets your SLA.

MCITP Database administrator 2008
MCTS SQL Server 2008 Implementation and maintenance
MCTS Sharepoint configuration
MCP Designing Deploying and Managing a Network Solution for the Small and Medium-sized Business
ITIL V3 Foundation

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

Low Rider (11/11/2011)
Hi all,
PROdA (Default sql Instance)
....
PRODB (Default sql Instance)
...

I'm not completely clear on what you intend to do. Is this a single, 2 node cluster with 2 sql instances installed?

If so you cannot have two default sql server instances. A cluster is a single entity and obeys the same rules as a single server.

Low Rider (11/11/2011)
1)Will each of the sql instance have a different IP ?

Yes. Each instance will have its own resource group and it will require its own vip address and virtual name

Low Rider (11/11/2011)
2)IF i want to failover to named instance on PRODB from the default instance of PRODA will
there be any changes to the IP address/Name for the application?

No. The ip address resides in the resource group and will failover along with sql.

Low Rider (11/11/2011)
Is my assumption correct ?If it's correct incase either server fails it will switch over to the
other server and ip adresses will remain the same ?

Correct.

Low Rider SSCommitted Points: 1502 More actions · Answer 5

MysteryJimbo (11/13/2011)
I'm not completely clear on what you intend to do. Is this a single, 2 node cluster with 2 sql instances installed?

Hi,

I have got 2 physical machines which are to be used as sql server 2008 cluster machines.I want to configure clustering in the active active method.

Low Rider (11/11/2011)
1)Will each of the sql instance have a different IP ?

In case of active active configuration would there be 2 virtual IPs ?

thanks

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 6

Same applies.

MysteryJimbo (11/13/2011)
Low Rider (11/11/2011)
1)Will each of the sql instance have a different IP ?
Yes. Each instance will have its own resource group and it will require its own vip address and virtual name

But you will either need two named instances or a default+named.

Each instance will reside in its own cluster resource group.

A resource group will contain as a minimum.

-Clustername

-VirtualIP

-SQL Server Agent

-SQL Server

-1 x Disk (preferably more)

This is so you can move the group independantly between nodes and still have an operational sql server.

In the case of an active/active failover cluster one group will be running on each node and you will have ideally specc-ed up both nodes so they could host both instances together in the event of a failure on one node.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

Recommended Reading: Top Tips for SQL Server Clustering

http://technet.microsoft.com/en-us/magazine/2007.03.sqlclusters.aspx

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

After all the articles and conversations on this topic and you still have this oh so wrong. MysteryJimbo has covered a lot of the key points. I would suggest you design and deploy a test cluster scenario to gain a valuable insight as to what is required. Please see my 3 part guide starting at the following link[/URL] to help you with this

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 9

After all the articles and conversations on this topic and you still have this oh so wrong.

What’s your point? What's wrong in the articles?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

Dev (11/14/2011)
After all the articles and conversations on this topic and you still have this oh so wrong.
What’s your point? What's wrong in the articles?

The point my friend is that despite all the articles and conversations surrounding clustering it seems the poster has not done enough research.

Why, what's your point?

At no point did I say there is something wrong with the articles, especially as there a couple of my own on the same topic!

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

"Ya can't make an omelette without breaking just a few eggs" 😉