[sims] SQL setup for SIMS

[fiza]

Does anyone have any guidance on what settings should be implemented in SQL 2008 R2 Full to run a SIMS Database? I have a server ready with Server 2008 R2 and 16GB RAM and I just need to install SQL. Are there any tweaks I should be doing to enable it to use both Processors (2 quad cores) and as much RAM as it needs? I have bought 2 x Processor licence so am covered for 2 processors.

 

Any pointers would be greatly appreciated.

[vikpaw]

To begin with just leave it as is. It should be able to use and manage all it needs. You'd only tweak if you were having problems.

On install, you'll probably get the warning about firewalls, you just need to let the sqlbrowser.exe through http://www.edugeek.net/forums/mis-systems/74019-setting-up-test-backup.html#post662495

[matt40k]

I've been saying to install Capita's SQL Express then put the 2008 disc in the upgrade the edition then do a new install and install SQL Management studio. Once SQL management studio installed, enable AWE. Not recommending R2 yet. I've yet to hear of a good reason to install it.

[edutech4schools]

I have an install step by step for sql2008r2 but i dont have it with me. I'll mail it later. can you pm me your email.

[edutech4schools]

Are there any tweaks I should be doing to enable it to use both Processors (2 quad cores) and as much RAM as it needs? I have bought 2 x Processor licence so am covered for 2 processors.

 

Dont think you need to do anything with processor or ram in sql.

[ChrisMiles]

SQL should use all the memory and cores by default anyway, most of the optimisation comes from designing the IO subsystems.

 

As for tweaking SQL itself, you should start with adding additional tempdb files of a reasonable but equal size, one for each core assigned to SQL. You should also ensure that the database file growth options are set correctly. I've seen SIMS databases set to 1MB growth which has terrible overhead and causes horrific OS level fragmentation. I'd recommend you set the database to a fixed size with room to spare, set a reasonable growth size (depends on your database size) and use windows defrag while sql is disabled to ensure the database files are contiguous before going live.

 

- Chris

[vikpaw]

Growth rate of about an eighth of the maximum you'd want i think is reasonable. My initial is 140MB with growth at 125MB. It doesn't often go crazy, but when it does it's easy to shrink it down again. This was with a nearing 4GB mdf file. I worked it out a while ago, based on best practise, and it seems to be okay.

 

If we're talking best practise now, you could look up the threads regarding the type of raid to use for each db, and where to put them, i.e. separate data partitions with temp db separate etc. but there's no point reiterating all that here.