SIMS and SQL 2008 DB and Log files on different drives

[jonwitts]

I have installed a brand new Server 2008 R2 machine and installed SQL 2008 on it ready for migrating our SIMS install to SQL 2008 this summer.

 

Following Microsoft's Best Practice Guide Separate database and transaction log files on different drives for optimal performance and disaster recovery I have three separate RAID volumes on this server. RAID 1 for system / C drive, RAID 5 for Data and RAID 5 for Logs

 

I have set up SQL 2008 to use the separate drives for Data and Logs as default for new databases and confirmed that these settings are correct in the Server Database properties page through SQL Server Management Studio.

 

However, when running the SIMSInst.exe to install SIMS SQL Server parts on this machine it always copies the SIMS database and log files into the SQL Program folder on the C Drive.

 

How do I get the SIMS installer to put its database and log files in the correct default locations as specified in SQL Server?

 

Jon

[Guest L3o]

Hi Jon,

Not worked with SIMs in a long time, but wondering if you can allow SIMs to install the files in the default location then in SQL detach them, move them to the correct location and attach them again?

 

There might be an easier way - but I'm not a SIMs expert and I couldn't say SIMs would work after, just know SQL can do this.

 

Leo

[RobFuller]

That works fine and is exactly the way I've done it.

[Hecate]

That works fine and is exactly the way I've done it.

 

Me too - it does work. I do it through SQL server management studio - detach the ldf file copy it to the new location then reattach

Edited June 22, 2010 by Hecate

[MYK-IT]

I thought Server 2008 R2 was not supported for SIMS??, or that was what I advised after posting something similar recently.

[vikpaw]

I thought Server 2008 R2 was not supported for SIMS??, or that was what I advised after posting something similar recently.

 

It's supported here, but not by Capita!

[jonwitts]

That works fine and is exactly the way I've done it.

 

I thought that Capita's recommendation was to only use their DBAttach to detach and attach the SIMS database?

 

Is their anyone from Capita out their that has a comment on this?

 

It seems a fairly large omission in the SIMS installer that it overrides the default settings defined in SQL...

 

Jon

[teejay]

Yeah, just had the conversation with Capita, Active Directory in 2008 R2 mode is supported from the Summer update, 2008 R2 as the server hosting SIMS is not supported and apparently won't be until next year. And yes, that does mean no support from Capita full stop apparently, as you are hosting it on an unsupported platform.

Very annoying as like most other people we're doing the 2008 R2/Windows 7 migration and its going to mean one server on 2008 :-(

[apeo]

I thought some people did have problems with running SIMS on 2008 r2.

 

In relation to this, I know its best to have 2 arrays (1 for data and 1 for log) but if its only the SIMS database is really worth having 2 arrays? I've been informed that Capita sets the logs to simple so is really worth having a another array for logs?

 

Sorry for the hyjack, I'll start another thread if its a problem. I have 4 HDD to use for Data and logs which only leaves 2 options:

* 2 RAID 1 Arrays

* 1 RAID 10 Array

[jonwitts]

I know its best to have 2 arrays (1 for data and 1 for log) but if its only the SIMS database is really worth having 2 arrays? I've been informed that Capita sets the logs to simple so is really worth having a another array for logs?

 

My thoughts on this are that I have seen my SIMS user base increase from maybe 25 Office based users to 250 classroom teachers and office based users over the past three years with the introduction of Lesson Monitor. Now for the first ten minutes of every lesson we see the SIMS server CPU hit 90 - 95% and the usability of SIMS and any other systems running from the SQL server reduce drastically until all registers have been submitted.

 

Our Head is pushing that all registers must be completed within the first ten minutes of lessons, and staff are (rightly so) complaining that the system is not responsive enough to handle this requirement.

 

With the required move from SQL 2005 to SQL 2008, I thought it was also the perfect time to spec an SQL server that was built as per the best practice advice from Microsoft and that should see us through the next five years of SIMS in the school.

 

Whilst Capita say it might not be worth it and it is OK to put everything on one RAID 5 array; I would sooner follow the best practice advice of countless DBAs and build the server the right way whilst I have the opportunity. :-) My understanding is that it is about the way that the logs are written and the way in which the database file is written; and their differences; which make it best to have them on separate arrays; not the size of the files...

 

Jon

[vikpaw]

@jinnantonnix; - that process for dbattach is how i understood it too, at least that's how i was told it worked by a Capita bod.

I think but i could be hugely mistaken, that the user info is in the master db, and so if all tables were taken from one server to another, then you wouldn't need the special db transfer / install routines.

[jonwitts]

@jinnantonnix

 

Thanks - this is helpful info. I am just trying to get the blank SIMS database installed on this server at the moment; but I am intending to perform the SQL 2008 migration on my live database soon, to get a 2008 install of SIMS running alongside my 2005 install. This way I can have all the SIMS power users in school test it before they go off for the summer holidays, sign a user acceptance and then I can perform the complete migration over Summer when they are not around...

 

I always get twitchy when doing anything like this with SIMS and your details of what DBAttach is performing should help me get this new server setup how we need it to be.

 

Cheers

 

Jon

[apeo]

Thanks guys.. I've been reading round this subject and as i understand it the general rule is that 2 arrays would give better performance and redundancy. As i understand it having the logs on a a different array wont give better redundancy because of the way Capita has setup the logs. Im not entirely sure that you can restore using backup and logs. As for performance, again they way Capita has setup the logs means that its limited as to whats writen to the log.

 

I think if i could afford to have more drives then I would definately have 2 arrays but with 4 and the question of 2 raid1 or 1 raid10, i lean towards 1 raid10 because of what i've found out above. Is my logic wrong?

 

Only reason why raid5 isnt an option is because many say that raid5 isnt a good idea for databases.

[apeo]

OK, a few things here...

 

Whenever anything happens on the database, a copy of the transaction firstly gets written to the log file. If that is successful, the transaction get committed to your database. The log file is designed as an accurate record of events, which can be replayed to a database. The act of replaying is important, as this allows you to restore a database, then replay the transaction log to bring the restored database back up to date. This is why it's important to keep a log file on a physically separate disk to that of your data file. Eggs and baskets.

 

By keeping your log file separate from your datafile, you gain two advantages

1. It's safer - you don't have a single point of failure

2. Better performance - you're not writing to the log file and reading/writing to the database simultaneously through the same IO subsystem.

 

Raid5 allows you have several IO subsystems accessing the data concurrently. This is a good option for databases that are fairly static - i.e. read far more than they are written to. SIMS databases fall into the category. Log files are NOT good for RAID5, as they are basically written to all the time (the parity calculations would drag down the performance).

 

Thanks for that. Yeah i know how log files work, i was informed that the way the log files were configured for SIMS that you cant replay the transactions. Therefore with regards to the 2 advantages, if the logs cant be used for recovery (just relised i put redundancy earlier which i wrong lol) then thats point one out the window and if theres limited information then the performance gain would be higher with a better raid set.

 

As to SIMS falling into the category of having equal or more writes to the database, i must admit i didnt realise it fell into this category. I used general database rules i.e. more read then write, which has obviously coloured what raid sets i'm considering.

[apeo]

Someone's telling you porkies! If your databases are set to the 'Full' recovery mode, then you can restore the database then restore the transaction logs. If your databases are set to 'Simple' recovery mode, then you cannot use the log file. It's up to you how you set it up.

 

 

 

Everything's a compromise. You have to work with what you've got, read up, take advice and make the best of it!

 

Ah right, heres the thing.. i dont setup the database and its been setup as Simple.

 

Yup everything is a compromise, isnt it alway

[jonwitts]

Sounds like you're on the right track.

 

You might want to think about RAID 0+1 for logs.

 

@jinnantonnix

 

The server box we have has 8 HDD. I currently have two in RAID 1 for OS (C drive) and then 3 in RAID 5 (DB drive) and 3 in RAID 5 (Log drive).

 

To get RAID 0+1 for our log drive I would either: need one more drive (not possible - server is at maximum capacity), or drop the OS down to no RAID, again not really somewhere I want to go!

 

So would you recommend that we setup the log drive in a RAID 1 and then have a hot spare in the server or setup the Database drive as RAID 1 and increase the log drive to RAID 0+1?

 

Or third option - leave it as it is???

 

Jon

[jonwitts]

Great. I will drop the log drive down to RAID 1 and set the spare disc up as a JBOD and use it for the SQL and OS Backup drive.

 

Thanks again for your time on this.

 

Jon

[apeo]

Even if you keep it on Simple (which means back to last night's backup), you'll still get better performance with a dedicated disk or array for the log, even though the log restore feature has gone out of the window.

 

Yeah i get that having a dedicated disk or array would still give better performance but weighing up the 4 disk scenario, i think that 1 raid10 set would give overall better perfomance.

[adamf]

Is it possible to do the same thing with FMS, have the mdf and ldf on seperate drives?

[matt40k]

Is it possible to do the same thing with FMS, have the mdf and ldf on seperate drives?

 

Yer, but why would you want to? FMS is tiny. The hassle it causes it just doesn't make sense.

[adamf]

Fair point.... I'll leave it where it is.

[bossman]

@jinnantonnix:

 

Not an SQL expert but I have been running raid5 on our server for over 5 years and have not met with any speed or connectivity issues, granted there are probably only 50 or so users running lesson monitor and taking the register every lesson and have them taken within the first 5 mins.

 

Admittedly the very first login and connection to SQL is slower but not much and the time from entering credentials to user home page is 12 - 15 secs, have virtualised and tested the server and it is even faster at 5 secs.

 

It maybe the way in which our staff use the Sims but i would think that most schools use it the same as each other.

 

I am not sure that we would benefit from changing the way in which our SQL server is setup but I am thankful of the knowledge which you bring to this forum.

 

Thank you jinnantonnix

[powdarrmonkey]

I'd like to clarify a myth here: keeping your main database and log files on different physical disks is nothing to do with performance; it's for safety should a disk fail and you need to recreate one or the other.

 

edit: that is not that performance can't be increased by separating them, just that you probably won't notice one way or the other.

[matt40k]

This assumes your database isn't in simple recovery mode, which SIMS\FMS is....