+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 24
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 ...
  1. #1
    jonwitts's Avatar
    Join Date
    Dec 2006
    Location
    England
    Posts
    132
    Thank Post
    17
    Thanked 12 Times in 11 Posts
    Blog Entries
    1
    Rep Power
    18

    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?

    Jon

  2. #2
    L3o
    L3o is offline
    Unvalidated User
    Join Date
    Jan 2010
    Location
    England
    Posts
    20
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    10
    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

  3. #3
    RobFuller's Avatar
    Join Date
    Feb 2007
    Location
    Chelmsford
    Posts
    312
    Thank Post
    82
    Thanked 39 Times in 29 Posts
    Rep Power
    22
    That works fine and is exactly the way I've done it.

  4. #4

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    Quote Originally Posted by RobFuller View Post
    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
    Last edited by Hecate; 22nd June 2010 at 04:54 PM.

  5. #5

    Join Date
    Dec 2007
    Posts
    864
    Thank Post
    90
    Thanked 164 Times in 139 Posts
    Rep Power
    49
    I thought Server 2008 R2 was not supported for SIMS??, or that was what I advised after posting something similar recently.

  6. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    Quote Originally Posted by MYK-IT View Post
    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!

  7. #7
    jonwitts's Avatar
    Join Date
    Dec 2006
    Location
    England
    Posts
    132
    Thank Post
    17
    Thanked 12 Times in 11 Posts
    Blog Entries
    1
    Rep Power
    18
    Quote Originally Posted by RobFuller View Post
    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

  8. #8

    teejay's Avatar
    Join Date
    Apr 2008
    Posts
    3,174
    Thank Post
    284
    Thanked 773 Times in 583 Posts
    Rep Power
    335
    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 :-(

  9. #9
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    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

  10. #10
    jonwitts's Avatar
    Join Date
    Dec 2006
    Location
    England
    Posts
    132
    Thank Post
    17
    Thanked 12 Times in 11 Posts
    Blog Entries
    1
    Rep Power
    18
    Quote Originally Posted by apeo View Post
    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

  11. Thanks to jonwitts from:

    apeo (23rd June 2010)

  12. #11

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    @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.

  13. #12
    jonwitts's Avatar
    Join Date
    Dec 2006
    Location
    England
    Posts
    132
    Thank Post
    17
    Thanked 12 Times in 11 Posts
    Blog Entries
    1
    Rep Power
    18
    @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

  14. #13
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    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.

  15. #14
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    Quote Originally Posted by jinnantonnix View Post
    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.

  16. #15
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    Quote Originally Posted by jinnantonnix View Post
    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

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. New SIMS Server - Windows 2008 R2 & SQL 2008 R2
    By MYK-IT in forum MIS Systems
    Replies: 23
    Last Post: 17th June 2010, 09:31 AM
  2. sql 2008 shrink log file
    By ful56_uk in forum Windows Server 2008
    Replies: 2
    Last Post: 26th May 2010, 07:45 PM
  3. Who's doing their SQL 2008 Migration for SIMS today?
    By chazzy2501 in forum General Chat
    Replies: 4
    Last Post: 30th April 2010, 11:31 AM
  4. Sims SQL 2008 install
    By Simcfc73 in forum MIS Systems
    Replies: 11
    Last Post: 4th February 2010, 10:23 AM
  5. SIMS SQL 2008 migration
    By matt40k in forum MIS Systems
    Replies: 0
    Last Post: 8th August 2009, 12:08 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •