Understanding sql 2008 instances and clustering

staggerlee

Hi all,

currently studying for my 70-432 and work is about to roll out a new sql 2008 cluster.

trying to find info on instances and coming up a bit confused. Our SQL guy at work wants to use sql instances to just spread out our dbs, so he doesnt have them all in the same place. But i read somewhere that you need a new ip address (so i guess nic) for each clustered instance? and other complications with using instances while clustering.

Whats the main advantages cons to instancing. I see an added layer of security with instances, but thats about it and surely having what is basically 2 sql server+ running the same machine is going to run that server a lot harder than just having the dbs all on the default!?

Any insight or links welcome.

thanks
s

RobertKaucher

Yes, no, and maybe. It's not just a case of security. At a certain level having multiple instances can increase the managability of the databases. For example, I would never host hultiple SharePoint farms on different on the same instance of SQL Server.

I cannot imagine that you would require multiple NICs on a server (more than you would require for a single instance) unless you needed to have seperate networks on the cluster side. But I do not use clustering in my environment.

As far as resources go, if the server is properly sized and SQL Server is properly configured, it should not be a problem. For exmaple, you would want to run some tests and ensure that both instances have enough memory configured.

One benefit of two instances is that if one instance goes offline, the other instance might still be accessible. But that is kind of redundant in a cluster. I mean the point of a cluster is to ensure the availability of the application even if there is an issue. I would talk this over with your "SQL guy" and get to the bottom of why he wants two instances. If you do not fully understand why he thinks it would be a good idea, it is difficult for you to come to concluson about it.

staggerlee

Hi RobertKaucher,

So each instance doesnt need a different ipaddress for a cluster?! That would greatly simplify the process! i was worried he would want 10-20 instances and thus we would need 22+/- NICs and static ips O.o

apparently we have about 30 dbs at the moment and hes reasoning is that if he puts them on different instances it will be easier to manage. Doesnt seem a great reason for something that surely does slow down your system having these multi instances running.. Is there say a sheet somewhere saying the effects that 1-2-3-4-5-etc instances put on the server to start with or is it really nothing worry about and if it helps go for it? Ive just got the 2 books you suggested for taking the exam (real world skills and adminsitration in action, and going by the index all real world skills says is its a pro for secuirty (from what i see we dont really worry about that to much at the moment ><) different versions of sql or service packs (they all work with the same verison and sps) so it kinda makes it dedunant to our needs.

Saying that I know at present our SA account has a basically company wide known password and is in plain text files on all machines using a certain piece of software, so putting that on a different instance seems a good idea! again was mainly worried with how it works on clusters, somewhere it mentioned ip address though i cant find that link now at home, and another mention that when you cluster instances you can only use half the availble (50 enterprise, 16 rest) when clustering. Which just lead to confusion. i either find sites that say very little except the simple pros above or a bit to techy for what im currently ready for ><

Thanks for the advice

S

staggerlee

Found the webpage that has brought about my confusion.

When having SQL Server installed on a cluster, you can only have one
instance per cluster disk group/resource and network name. This means that
for every instance you want, you need to create a new cluster group having
at least a virtual ip-address, network name and a disk resource available,
so every instance will have it's own ip-address, network name and at least
one hard disk. One implication is that the maximum number of instances
possible go down from 50 for a single SQL Server to only 25 for a clustered
SQL server. This makes it in some cases impossible to use a cluster for
redundancy.

Allow multiple instances per cluster disk group on a SQL Cluster | Microsoft Connect

staggerlee

ok its talking about windows server cluster resource groups. starting to make sense now.

got a bit narrow minded to everything being done in sql server.

Thanks again for the help

s

RobertKaucher

staggerlee wrote: »

Hi RobertKaucher,

So each instance doesnt need a different ipaddress for a cluster?!

It *DOES* need a different IP address. But a NIC can handle more than one IP address. For clustering you will need at least two NICs. The second NIC is to keep the heartbeat isolated to its own network. This makes sure the heartbeat does not need ot compete with the data side of the network.

Now, if you are using iSCSI you will need 3 NICs. All of this really depends on your environment.

Check this book out: Amazon.com: Pro SQL Server 2008 Failover Clustering (Expert's Voice in SQL Server) (978143021966: Allan Hirt: Books

staggerlee

yeah, been reading up on it though the day while on a sql course. sadly the trainer isnt a sql expert and has in fact never used it.. he just read up and agreed to train us (normally does the windows server stuff) so sadly a 2 min conversation turned into a bit of a epic google search. but what can you do

i will check out the book. see if it explains how to install multiple instances on a cluster thanks

S

RobertKaucher

Make sure you post back with what you ended up doing and why!

staggerlee

will do at the moment im mainly trying to get us to implement the best practises from adminsitration in action (really enjoying that book on a site note, great to read what how something works then hows best to implement it) so using that a mix of internet links to sort out how to do the sql cluster from blank install to full functioning server

hoping it will be good experiance and help in my studys for my exams !