MIS Systems Thread, SIMS and SQL 2008 DB and Log files on different drives in Technical; I have installed a brand new Server 2008 R2 machine and installed SQL 2008 on it ready for migrating our ...
22nd June 2010, 03:02 PM #1
SIMS and SQL 2008 DB and Log files on different drives
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?
IDG Tech News
22nd June 2010, 04:10 PM #2
- Rep Power
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.
22nd June 2010, 04:32 PM #3
That works fine and is exactly the way I've done it.
22nd June 2010, 05:47 PM #4
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
Originally Posted by RobFuller
Last edited by Hecate; 22nd June 2010 at 05:54 PM.
22nd June 2010, 06:30 PM #5
I thought Server 2008 R2 was not supported for SIMS??, or that was what I advised after posting something similar recently.
23rd June 2010, 08:21 AM #6
It's supported here, but not by Capita!
Originally Posted by MYK-IT
23rd June 2010, 10:04 AM #7
I thought that Capita's recommendation was to only use their DBAttach to detach and attach the SIMS database?
Originally Posted by RobFuller
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...
23rd June 2010, 10:21 AM #8
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 :-(
23rd June 2010, 10:37 AM #9
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
23rd June 2010, 11:17 AM #10
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.
Originally Posted by apeo
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...
23rd June 2010, 11:27 AM #11
@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.
23rd June 2010, 12:09 PM #12
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.
23rd June 2010, 12:16 PM #13
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.
23rd June 2010, 12:50 PM #14
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.
Originally Posted by jinnantonnix
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.
23rd June 2010, 01:11 PM #15
Ah right, heres the thing.. i dont setup the database and its been setup as Simple.
Originally Posted by jinnantonnix
Yup everything is a compromise, isnt it alway
By MYK-IT in forum MIS Systems
Last Post: 17th June 2010, 10:31 AM
By ful56_uk in forum Windows Server 2008
Last Post: 26th May 2010, 08:45 PM
By chazzy2501 in forum General Chat
Last Post: 30th April 2010, 12:31 PM
By Simcfc73 in forum MIS Systems
Last Post: 4th February 2010, 11:23 AM
By matt40k in forum MIS Systems
Last Post: 8th August 2009, 01:08 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread