March 17, 2010 at 8:39 am
I know only enough about SQL server to be dangerous -- and I have a question about multiple instances.
MS SQL Server 2005, running on Windows Enterprise 2003 server w/ 8 GB RAM, dual processors, and a whole bunch of disk space.
How do you decide whether to have a single instance with multiple databases or multiple instances, each with only 1 or 2 databases? What are the decision criteria?
Thanks
March 17, 2010 at 9:29 am
Personally, I would only create multiple instances if they are truly separate environments such as DEV and PROD, so that some users can have full access to the DEV instance, and minimal access to the PROD box. You add complexity because now you need to administer 2 environments instead of 1 .... double the jobs, double the logins .... and you might need linked servers. But the answer depends on more specifics about your business.
In our case, we have separate servers entirely to separate applications.
March 17, 2010 at 9:37 am
To add
If tempdb could be an issue because both use it heavily, multiple instances can make sense.
I prefer multiple databases for applications if possible. That's what SQL Server was designed to do
March 17, 2010 at 9:49 am
Steve Jones - Editor (3/17/2010)
To addIf tempdb could be an issue because both use it heavily, multiple instances can make sense.
I prefer multiple databases for applications if possible. That's what SQL Server was designed to do
When you say your prefer multiple databases -- do you mean multiple databases? or multiple instances? Because the applications would have separate databases regardless of single or multiple instances, right? Or am I confused?
March 17, 2010 at 10:43 am
multiple databases, single instance. I was responding to your initial question, and didn't make it clear.
In general, the balancing of memory between instances, and potentially CPU, isn't worth it to me. I prefer to let SQL Server maintain the balance, and it works well as long as both applications are not heavily loading or stressing your resources.
March 17, 2010 at 10:53 am
You can have up 32,767 databases per instance, and you can have 50 instances per server.
This is one of those questions, that the answer will be, "it depends"
It all depends on what you are trying to accomplish, or why would you think that you need to name another instance. If this is your only server, and you need a new environment, then, you may need to create a new instance.
I have had a 3rd party app that was designed to use a specific instance name. So I had to create on on all 4 environments, so they all match. Dev, Test, QA & Prod.
At home, I have only a couple servers. So to keep things simple, I create an instance for each version. SQL 2000, SQL 2005 & SQL 2008. That way they can all run on the same box.
But just to create a new instance because you believe it is better to have one instance per database, well that is not good. You have to look at other things. Since this is a single box, all of the databases on all instances will be using the same memory, drives and processors. You will not gain anything from doing that.
Now, if you think that you want to keep all the small databases on a certain instance, and all the really processor intensive databases on a separate instance on separate boxes, then yes, maybe. But I would still use the default instance for them. Naming an instance name can get a little tiring when you are trying to develop an app or a web app. You are constantly having to think about the instance name in the connection string. And if you happen to have one default instance and one named instance with the same database name, then your developers could get into real trouble. There is really no need in naming a new instance per say, unless you have a good reason too.
Andrew SQLDBA
March 17, 2010 at 10:59 am
It depends. I have servers that fall into both categories. It will depend on application needs, usage patterns, resource requirements, etc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 3:02 pm
Thanks for the helpful responses - I think I'll go with a single instance!
March 17, 2010 at 4:11 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply