Separate databases or multiple named instances?

Question

Separate databases or multiple named instances?

Steven Swenson

Newbie

Points: 5
More actions
May 16, 2008 at 12:20 pm

#122334

My company is hosting multiple clients on a db server. It will be using windows authentication for access to the db. Clients are not expected to be logging directly on the server but some may have access to management tools.
So I am curious what are the pros, cons, and caveats involved in implementing each customer database under a default instance of server vs installing each client on an independent named instance?
It seems to me that from a management standpoint a single default server would be easiest... but are there security concerns here that might not be easily dealt with?

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

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

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Unless you have some kind of government directive that the data has to actually be housed on separate servers, you're not going to gain anything from using different instances. Instances add overhead that you just don't need to incur. There's nothing you have security-wise that you need to put on a separate instance. The only thing I can think of would be if you needed to keep an sa out of certain DBs, then you'd give him a separate instance, but maybe he doesn't need to be sa in the first place.

Anyway, in my opinion, separate instances are only viable in certain very restrictive circumstances, and most don't even come close to meeting that criteria.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

What does "access to management tools" mean to you?

If they need any server-level permissions (backup admin, sysadmin, create database, etc.) they will possibly be able to get to other people's data. If they can only access their specific database, one instance would be fine as long as you are careful with your security.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

I'm sorry, it looks like you don't need my help because you've got it all figured out.

I was talking theory to help you narrow down your decision, which is what I thought was the point of the question.

I'll go away then, and you can deal with it on your own. Sorry I didn't hang on your every word.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

I think you took offense to my reply to the original post - I can see how it would sound if I were the original poster, but I am not.

Are you sensitive because it's Friday? I liked your post.

Have a good weekend.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 5

oh, you're not the original poster... Fine... sorry then.

No, i'm not sensitive because it's friday... I've got this huge hemorrhoid, and nobdy will look at it for me.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

"hemorrhoid" ?

Is that anything like a Vice President, or even worse, the dreaded and feared Partner?

SQL = Scarcely Qualifies as a Language

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 7

To my opinion, multiple instances are only useful if you want to prioritize databases. Each instance has it's own settings for memory and CPU usage. So if you're in the situation that a production database has to be on the same server as the development database :w00t:, I would recommend 2 instances.

Wilfred
The best things in life are the simple things