September 19, 2007 at 4:48 am
September 19, 2007 at 7:13 am
Without knowing anything about those databases, it's likely that the 'best' would be as separate db's in a single instance.
There's a lot of considerations, such as:
What these databases really are?
Sort order and collation settings.
Security aspects.
Hardware considerations regarding a single instance vs four instances on the same box.
.. and more...
/Kenneth
September 19, 2007 at 7:19 am
September 19, 2007 at 7:42 am
Performace given the worload is constant will be better with a single instance because of less "extra" resources required by services etc and more memory allocated to the the instance.
To use more than one instace boils down to "other" than performance factors. Look at what Kenneth posted he is right on the money.
Hope this helps
* Noel
September 19, 2007 at 12:05 pm
One instance would be my vote, unless you have a compelling reason for multiple instances. The only case where we have multiple instances on 1 server is to allow a small developement group to have admin permissions for their instance, and keep them away from databases in the other instance. Otherwise, multiple instances is just more admin work for you.
September 19, 2007 at 5:57 pm
Hey there, Jules... long time no see.
I think these folks are all steering you in the right direction... One instance and multiple databases would be my inclination, as well, for all the reasons stated. Just mind your login security so users of one database can't see the others... not a hard thing to do, by the way, but something you should be keenly aware of.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2007 at 4:43 am
Thanks for your help. Hi Jeff yeah been out of the DB world for a while.
There are 4 database which run third party applications. I gather performance and maintenance is going to be better with one instance.
In what circumstances would admin of one DB require all DBs to be down in a way that having them on different instances wouldnt? Obviously restarting the server would take all instances off line for a time
are there situations where only a specific instance would need to be stopped and therefore if each db were on its own instance the other apps could continue running?
Cant databases within one instance have different collations?
Thanks alot.
September 25, 2007 at 4:49 am
September 25, 2007 at 6:32 am
>In what circumstances would admin of one DB require all DBs to be down in a way that having them on
>different instances wouldnt? Obviously restarting the server would take all instances off line for a time
>are there situations where only a specific instance would need to be stopped and therefore if each db
>were on its own instance the other apps could continue running?
These are all 'consolidation issues' and are valid questions when it comes to deciding how to set up and run your apps/systems. The answers are highly dependant on what requirements each of these apps pose. If you bring them all together, some things are 'easier' and 'cheaper'. If you spread them out those things tend to become more 'expensive', though some other aspect may then become available as 'cheap'.
It's all a tradeoff between different good and bads. There's no absolute (or easy) answer to which is best.
Money tends to be the deciding factor, though. In general 'one' costs less than 'many' when it comes to $$$.
>Cant databases within one instance have different collations?
Yes they can.
But... it doesn't come free. Doing this opens up a whole can of worms regarding collation issues between tempdb and databases with some other collation that the default that tempdb has.
This is otoh one reason that some decides to go separate instances. To separate different collation needs on instance level.
/Kenneth
September 25, 2007 at 6:42 am
Jules Bonnot (9/25/2007)
Also what are the implications of multiple versus one instance for load on tempdb?i have 2 physical disks.
c = raid 1 for OS and log files.
d = raid 5 for data files
Which disk should i put tempdb on?
What you have here is the absolute barebone minimum for a server.
This *may* work if the load/traffic on these 4 db's are very light.
One deciding factor on how far this setup may take you is how many spindles D:\ is made up of?
3..? not good... won't take much stress untill the whole box starts to crawl..
14? much better.. you may get away with it from a pure performance perspective.
But, if this is all you have to work with, I'd leave C:\ alone for the o/s - no tranlogs or anything there, everything db-related (all databases of all kinds and transaction logs) goes on D:\
Again - this is really barebones and won't leave much (if any) leevay for heavy activity of any kind. Everything will fight for resources on the same disks all the time. Your disksystem is one of the most critical components of your server. It will set the limits for what and how much you can place on it and expect it to respond fast enough to actually be useful.
/Kenneth
September 25, 2007 at 6:54 am
Thanks, the collation and tempdb is certainly an issue.
regarding the other points you makes do you have any actual example where having multiple instances would lead to less down time if some maintanence work had to be performed on one of the DBs. Any example pros and cons not related to money?
thanks
September 25, 2007 at 7:16 am
September 25, 2007 at 7:18 am
Actually no.. When thinking about it, I don't consider single- or multi-instances on the same box being any different from an 'app' point of view.
Most, if not all routine things we do on db's as maintenance and such, are done 'online' - that is it's never needed to take the instance offline like stopping the service.
The one exception that comes to mind may be if you need to apply a sp or hotfix. Other than that, 'normal maintenance' is the same whether it's on one instance or several.
The scope that all instances share is the physical server itself, and the o/s.
Everything that happens on that level also affects everything on the box, no matter if it's a single instance or several.
In any case, I personally woldn't consider more than one instance on a disksytem that was no more than a c:\ (raid1 ?) and d:\ raid5 if that is what you have to play with.
.. then again.. if this is teeny weeny db's and apps, it still *may* be feasible, but is this the case here?
/Kenneth
September 25, 2007 at 7:31 am
September 25, 2007 at 7:35 am
Jules Bonnot (9/25/2007)
so i should order more disks smaller physical disks rather than 1 extra large one? to put in the raid 5. we currently have 3.will it use the extra disks/spindles to search for the data?
Shouldnt the tran logs be on a seperate disk?
c has two disks.
Thanks alot
Oh, ok... Well, you need to tell a bit more about these four db's.
Size of each? (approximate - 10 mb's or 10 gigs)
Expected number of concurrent users on each?
Type of apps/systems? Mostly reads or writes?
Users read at day, batch loads at night? Volumes?
In short, what are the characterstics of each.
If they are similar, that makes them good candidates for sharing the same server.
Very different, perhaps not so good to consolidate.
As for drives/spindles, what you want is as many as you can afford.
You don't want one big drive, you want many smaller drives that adds up to the 'big' size.
My own 'standard' consists of four arrays.
(Irrespective of raidlevels and such, but as physically separate arrays - NOT logical volumes)
C:\ - for OS and binaries
D:\ - dedicated for database (*.mdf) files only
E:\ - dedicate for transaction logs only
F:\ - misc - logs, backups, misc files etc...
This is the 'core structure' I try to start out with, both from performance and redundancy and backup/recovery perspectives. It's also a good start for easy maintenace in the long run (very small chance of filefragmentation on the o/s level etc)
If requirements dictates, more arrays may be need - like a dedicated for tempdb.
It's all dependent on the particular system, size, usage patterns etc.
One thing is sure, though. An undersized disksystem can make the largest multi-way TB RAM mega-turbo server crawl like a dog, no matter the horsepower of CPU's and RAM.
In the end - you have to test what you have against the requirements given in order to size your hardware appropriately.
/Kenneth
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply