Adding new instances of SQL Server to an existing cluster

Question

Post reply

Adding new instances of SQL Server to an existing cluster

UncleRoydee

SSC Enthusiast

Points: 100
More actions
August 6, 2004 at 10:26 am

#162336

Two part question.

1. Is there a significant performance hit caused by installing/running multiple instances of SQL server as opposed to running all of the same databases under one instance?

2. I need to add one new case sensitive instance of SQL Server 2000 to an existing SQL Server cluster installation to enforce case sensitivity in passwords and I would like to install a second instance for house keeping purposes.

I thought I'd just run through the installation process, as I would for a non clustered instance, and the world would be a beautiful place.

But NOOOOOooooo...

As this is a production environment, I don't want to mess up the existing installation and I don't want to have to reboot the servers any more than necessary.

Can someone give me some pointers on this... both the theoretical and the practical? I've read BOL, but it seems a little obtuse on the subject.

Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

sa24 SSCrazy Eights Points: 8027 More actions · Answer 1

sa24

SSCrazy Eights

Points: 8027

August 8, 2004 at 8:14 pm

#518115

Are you runnng Active/Active or Active/Passive clustering ?

Brett Hacker Old Hand Points: 339 More actions · Answer 2

Not much detail here, but if you're needing the other instance to allow case sensitivity on your passwords, I'll assume you're storing your passwords in clear text and the default sorting doesn't care about that.

You would be much better served to hash your passwords before you store them, then store only the hashed password. There are numerous samples around the Internet on how to do this. I currently do it using an SHA1 hashing algorithm with salt, and this allows (forces in fact) my passwords to be case-sensitive. It also has the added benefit of increased security by shielding your passwords from viewing in the database.

Search Google for "one-way hash passwords in SQL"; there is an article at 15Seconds that discusses this.

Thanks,

Brett Hacker

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

Each instance adds an overhead plus you will have to allocate fixed memory to each instance, well you don't have to - but expect problems if you don't. So in answer yes really another instance will be an overhead and you'll need another resource group.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 4

I've worked with active/active in the past with 2 or 3 instances on each cluster. My advice with installs and upgrades is they work best on one instance if the others are shut down. I would schedule the time, shut down SQL (and the SQL service amanger in the system trays) on all nodes in a cluster and then do the installation. From my experience, there are times the SQL installations (especially SP3) doesn't understand that other instances have certain files locked and the installation or upgrade of one instance will fail due to lock on files held by another instance - even on another node in the cluster. This is even true of the sql service manager in the system tray. Even if the files that are locked have already been upgraded by another instances' upgrade, SQL installation will still check them for locks.

kisssql Newbie Points: 1 More actions · Answer 5

Here's way to make case sensitive in query (got this from sql server mag):

SELECT * FROM Authors WHERE au_lname COLLATE Latin1_General_CS_AS = 'green' and au_lname = 'green'

Here's another less efficient way (also from sql server mag):

SELECT * FROM Authors WHERE CAST(au_lname AS varbinary(40)) = CAST('green' AS varbinary(40)) and au_lname = 'green'

-- au_lname = 'green' is necessary according to SQL Server MVP Umachandar Jayachandran for the query optimizer to consider using an index on au_lname column, if there is one.

UncleRoydee SSC Enthusiast Points: 100 More actions · Answer 6

Thanks for all the feedback guys. I've opted to not install the second instance of SQL Server and to use a 3rd party application to encrypt and compare my passwords.

I was previously using the undocumented SQL PWDENCRYPT() and PWDCOMPARE() functionality to encrypt and validate passwords, that's why I needed the case sensitive instance of SQL. If I ran PWDCOMPARE against a case insensitive instance of SQL I could not enforce case sensitivity of passwords.

Thanks again!