Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wardsan
Starting Member
48 Posts |
 Posted - 2004-06-29 : 07:20:43
|
| Folks,I just need advice on the setup of a new production sql server, it will be used for multiple applications. I will install enterprise edition on it but wonder about the configuration of the databases. Some of the application databases may be critical or have large number of users and I wonder if it would be better to place these databases in their own instances. Does anybody have any views on organisation of databases? I am concerned about placing them all in the default instance and there may be contention for the system databases, virtual memory issues and the problem that if the master db went down, the whole lot would follow. Should I go ahead and create a default instance and then add named instances for critical databases?The spec of the machine is: 2*3.2GHz, 4GB RAM, 2*73GB disks (raid 1, partitions of 8 and 64 for C and D drivesThanks |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-29 : 07:51:26
|
| You want to create them on the default instance. Limit the instances on your main production box to one instance if possible. The big thing with this server will be to seperate out the data, tempdb files as much as possible on the SAN or disk subsystem so you're getting your max I/O throughput.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-06-29 : 07:55:30
|
| Classic DBA answer - *it depends*Bearing in mind that each SQL instance will create additional drain on you server resources, and that you will need to be clever in terms of trying to split your resources between your instances (given only 2 processor's, that may not be of much worth), I would say you might consider going with 2 instances, one CRITICAL and one DEFAULT. Limit resource usage on the "default". Consider that if you want to go this route, you will also want to think about setting up more drives: I like the following, IF it can be done:c:\OS & softwared:\CRITICAL instance DATA FILESe:\CRITICAL instance LOG FILESf:\CRITICAL instance tempdbg:\NORMAL instance DATA FILESh:\NORMAL instance LOG FILESi:\NORMAL instance tempdbThat may not be feasible, given you hardware.Please make sure that the "default" or "NORMAL" instance is NOT used for dev/pre prod/testing ... people I've seen think that they should run their other environments on the same box using instances - and then they manage to take down not SLQ, but the box, and boom - test just took down prod :-(I look forward to hearing the recommendation of some of our more learned brethern :-)*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-29 : 08:14:57
|
| I'd go with the one instance myself. Adding another is not going to mystically increase the resources. To do that you'd need to improve the hardware. Your issue about the master db going down is interesting, but I think lack of space is more likely to do this than anything else, and, again, multiple instances wouldn't help.-------Moo. :) |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-06-29 : 08:40:44
|
| Moo, Derrick,Given the quality (sic) of (L)users I had at my previous site, and the quality of the 3rd party application code, as well as some of the fun things that local programmers did, I have seen SQL get taken down while a server was still running. I would like to believe in those (hopefully VERY) limited occaisons, having a seperate instance might help.That said, the box at my previous site that housed the "consolidation server" was spec'd to NEVER carry mission critical databases, (b) was an 8x1.5 Ghx X440 IBM server with 4 GB ram, and SAN attached storage. More resources, so they went with CPU's assigned to instances - "lesser" systems getting 1 CPU, and more NB instances getting 2 ... can't remember the specifc detail, but they did have 5 instances. They also, against all my recommendation, had one of those instances as the PreProduction environment (hence my previous comment :-) ).I get the feeling that Wardsan has only 1 SQL server? At least, for production. As I said, "it depends" ... it depends on whether you currently have stability issues with SQL being impacted, or performance issues ...More thoughts?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-29 : 10:08:06
|
| WOuld it help wardsan to add more drives (more arrays), rather than worry about instances? (I'm thinking about having the LOGs on one set and the DATA on another, and maybe the O/S on a third)Kristen |
|
|
|
wardsan
Starting Member
48 Posts |
Posted - 2004-06-29 : 10:29:30
|
| Folks,This server has access to only 2 logical SAN drives so I'm fairly restricted here, I have just been a request to see if I can add a drive. There are initially about 5 sql server applications coming our way, this server will only be used for production databases. Hyperthreading is enabled on this server, so there are 4 CPUS availableNow, you have the full spec., in light of this what do you think would be best? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-29 : 10:44:42
|
| I think that it would be best to add more drives ;)-------Moo. :) |
|
|
|
wardsan
Starting Member
48 Posts |
Posted - 2004-06-29 : 11:04:10
|
| thanks folks, I have requested a 3rd drive be added so OS/software on local disks, system/temp dbs on SAN drive 1, user data on SAN drive 2, logs on SAN drive 3. Backups can go on local drive until additional SAN storage available? How does this sound? This assume user data and indexes kept in one filegroup. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-06-29 : 11:11:43
|
| Wardsan - unless I miss my guess, you cannot allocate only a hyperthread CPU to a SQL instance ... I think I read that somewhere, anyway.Given what you have now, I would go with Derrick and Moo.1 instance.SQL installed to local drives. (c:)Data Files to 1 SAN drive. (d:)Log Files to other SAN drive. (e:)Have funEDITED : I see you went that way, while I was typing :-)*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
setting up sql server corporate server