SQL Server Performance

Re-Configuring RAID on database server

Discussion in 'SQL Server 2005 General DBA Questions' started by Panik77, Feb 11, 2009.

  1. Panik77 New Member

    Hi Everyone, this is my first post here. [:)]
    This weekend I am re-configuring the RAID configuration on our database server running Windows Server 2003 and SQL Server 2005.
    The Server has 6 disks of identical size.
    The current setup is a single RAID 5 array with 5 disks and one hot spare, with the OS on one partition, and the database files and transaction logs on another.
    Obviously this is not a good configuration for SQL server as the OS, database and transaction files are all on the same logical disk.
    We believe that we have had some disk contention issues, which we hope will be resolved by changing the RAID setup to put the OS, Data and Transaction files on separate logical disks.
    The new planned setup is:
    • Logical Disk 0: RAID 1 mirror - OS and SQL Server program files
    • Logical Disk 1: RAID 1 mirror - Database data files.
    • Logical Disk 2: RAID 1 mirror - Transaction log files.
    We only have a single server available to us, so we can't carry out this change side by side.
    I am worried about making sure the databases are backed up and restored properly, so that they functional when we restore the server.
    The process I am planning on following for this is:
    1. Change the startup parameters of SQL server to the new location of the "Master" database and transaction log files
    2. Run Alter database commands to change the location of the other system databases and the user databases.
    3. Stop the SQL Server instance.
    4. Backup the C: drive and system state, and save to disk on another server over the network.
    5. Backup the D: drive containing the database and transaction files to another server over the network.
    6. Shut down the server.
    7. reboot the server and configure the RAID arrays as above.
    8. Carry out a clean Windows install, and get the network connection up and running
    9. Copy the backups back to the server
    10. Restore the C: and System state over the clean windows install.
    11. Restore the D: drive backup to a temporary location on Disk 0
    12. Move the database files to the new location on Disk 1
    13. Move the transaction log files to the new location no Disk 2
    14. Start SQL Server in Master only recover mode, change the "FILENAME" path to the new location of the master database and log files.
    15. Move the Resource database data and log files to the same location as the Master DB data files.
    16. Restart SQL Server.
    I will be following the instructions from SQL Server 2005 Books Online:
    • Moving System Databases: http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
    • Moving User Databases: http://msdn.microsoft.com/en-us/library/ms345483(SQL.90).aspx
    Am I going to make an huge mistakes by following the above procedure? Has anyone else gone through the same process themselves and found any big problems.
  2. rohit2900 Member

    Hello Panik,
    Welcome to Forums....
    As you said that you've only one server available and you can't do it side by side. So your database server will be down while you'll be doing all this reconfiguration. Correct....
    Below are some of my comments.
    [quote user="Panik77"]
    • Logical Disk 0: RAID 1 mirror - OS and SQL Server program files
      • Logical Disk 1: RAID 1 mirror - Database data files.
        • Logical Disk 2: RAID 1 mirror - Transaction log files.
      • [/quote]
        For log files I'll suggest RAID 0
        [quote user="Panik77"]
        1. Change the startup parameters of SQL server to the new location of the "Master" database and transaction log files
          • Run Alter database commands to change the location of the other system databases and the user databases.
        • [/quote]
          What will be the new location as you said you only have one server with one partitioned logical disk.
          [quote user="Panik77"]
          Restore the C: and System state over the clean windows install.
          [/quote]
          I don't think this going to work.
          [quote user="Panik77"]
          Am I going to make an huge mistakes by following the above procedure? Has anyone else gone through the same process themselves and found any big problems.
          [/quote]
          I think you're making things complicated and I'll suggest below steps. And I'm assuming that your server will be down while you are doing this configuration change.
          1. Make youe server offline i.e disconnect youe application or probably disable the nic card. this will make sure that the database backups you are taking are latest & greatest.
          2. Backup all your databases i.e. System and user databases. Also verufy each & every backup file by restore verifyonly.
          3. Stop sql server services, then enable nic card and copy those backups over netwrok and if possible copy all the mdf's & ldf's also.
          4. Rebuild your server install fresh copy of windows & sql server. Make sure your server & instance name are same and the build version is also same other wise you'll not be able to restore your system databases. http://support.microsoft.com/kb/264474
          5. Now either you can restore all the backups step by step or you can also attach the user databases and use old file of the master.
          I think this should be all. Lets wait for our other members to post their views.
          Hope this helps....
  3. Panik77 New Member

    [quote user="rohit2900"]
    For log files I'll suggest RAID 0
    [/quote]
    I have seen this recommended quite a lot, and I know that there is a read/write performance benefit in going for a stripe set instead of a mirror, but what happens when one of the disks in the stripe fails?
    Obviously you lose the transaction logs, but doesn't SQL Server go down as well?
    [quote user="rohit2900"]
    [quote user="Panik77"]
    1. Change the startup parameters of SQL server to the new location of the "Master" database and transaction log files
    2. Run Alter database commands to change the location of the other system databases and the user databases.
    [/quote]
    What will be the new location as you said you only have one server with one partitioned logical disk.
    [/quote]
    The databases data files will be moved from "C:MSSQL" to "Disk 1MSSQL". So they would be on the 2nd disk. The RAID array needs to be re-configured to create the three separate disks instead of the one big disk.
    My understanding is that the move commands need to be run while the data files are in the old locations, before shutting the server down, so that when SQL Server is restarted you have already moved the database files into the new location and "everything just works"...
    [quote user="rohit2900"]
    [quote user="Panik77"]
    Restore the C: and System state over the clean windows install.
    [/quote]
    I don't think this going to work.
    [/quote]
    Yes, I am concerned that this won't work either.
    The backup plan is to carry out a clean install and re-install and patch everything manually, like you have suggested.
    [quote user="rohit2900"]
    I think you're making things complicated and I'll suggest below steps. And I'm assuming that your server will be down while you are doing this configuration change.
    1. Make youe server offline i.e disconnect youe application or probably disable the nic card. this will make sure that the database backups you are taking are latest & greatest.
    2. Backup all your databases i.e. System and user databases. Also verufy each & every backup file by restore verifyonly.
    3. Stop sql server services, then enable nic card and copy those backups over netwrok and if possible copy all the mdf's & ldf's also.
    4. Rebuild your server install fresh copy of windows & sql server. Make sure your server & instance name are same and the build version is also same other wise you'll not be able to restore your system databases. http://support.microsoft.com/kb/264474
    5. Now either you can restore all the backups step by step or you can also attach the user databases and use old file of the master.
    I think this should be all. Lets wait for our other members to post their views.
    Hope this helps....
    [/quote]
    Thank you very much for your suggestions, I will probably end up following your suggestion to do a clean install and restore the databases.
  4. satya Moderator

    Welcome to the forums.
    As referred by Rohit I don't see any problem in your approach, but I would like to ask you whether you tested the moving of databases on development platform or not. If not you may find surprises in doing this on a production server, make sure you have a documented the approach and at the same time tested them step by step.
  5. preethi Member

    In addition to the above comments, please make sure the downtime planned is acceptable by the stakeholders
  6. Panik77 New Member

    [quote user="preethi"]
    In addition to the above comments, please make sure the downtime planned is acceptable by the stakeholders
    [/quote]
    Done [:)]
    This is a very important step for us, everyone who uses the system has already been emailed and we are taking the server down after hours.
  7. Panik77 New Member

    [quote user="satya"]
    ...
    but I would like to ask you whether you tested the moving of databases on development platform or not. If not you may find surprises in doing this on a production server, make sure you have a documented the approach and at the same time tested them step by step.
    [/quote]
    I have obtained a test server that I will use to test my approach today, I haven't carried out a test yet.
    I am documenting my plan and the required SQL commands.
  8. satya Moderator

    Then good luck in your testing and major implementation on the production server, hope you won't be surprised with few issues.
  9. rohit2900 Member

    All the best...
    And if you find any issues do post that... Which will help others to plan their things......
  10. Panik77 New Member

    Finished: All up it took us about 8 hours to complete the re-configuration. Most of that time was spent waiting for backups to complete, data to copy over the network, disks to initialise and for the restore to finish. Moving the databases themselves was actually the quickest part of the operation.
    I think the main lessons to take away from the experience are:
    • Test first!
    • Have a big, fast disk attached directly to the server you are backing up/ restoring, and use this to copy backups to. The network is very slow.
    • Make sure you have some good movies on your laptop. Waiting nearly 2 hours for disks to initialise is not fun all by yourself in a dark empty office.
    In the end we followed the this procedure:
    1. Stop the SQL Server instance.
    2. Backup the C: drive, D: drive and system state, and save to disk on another server, over the network.
    3. Shut down the server.
    4. reboot the server and configure the RAID arrays.
    5. Carry out a clean Windows install, format the disks, and get the network connection up and running.
    6. Copy the backups back to the server's E: drive.
    7. Restore the C:, D: and System state over the clean windows install, making sure to overwrite all files.
    8. Reboot the server after the restore completes.
    9. after the server restarts it found new hardware devices and required another re-boot.
    10. Start the SQL Server instance. (At this point the databases are still in their original locations.)
    11. Move the user database files to the E: drive.
    12. Move the transaction log files to the the F: drive
    13. Check that everything is working!

    The user database and transaction logs were moved using the procedures described here: Moving User Databases: http://msdn.microsoft.com/en-us/library/ms345483(SQL.90).asp, under the "Planned Relocation Procedure"section.
    The system databases have not been moved yet. I will move these as a separate task.
    During my testing I tried changing the paths for the user databases using the method described under the "Relocation for Scheduled Disk Maintenance"section. But this failed, as the drives did not actually exist at that point.
    In the end the procedure I followed was much simpler than what I thought was required.
    The original disk config was: 1 logical disk, made up of 6 Physical disks in a RAID 5 array, partitioned as C: and D:
    The new config is: 3 logical disks, each made up of 2 physical disks in RAID 1 mirrors.
    • Disk 0: C: and D: - OS and Applications.
    • Disk 1: E: - SQL Database files.
    • Disk 2: F: - SQL Transaction files.
    As the C: drive and D: Drive existed in the old and new configurations I was able to just restore the backups to their original locations. Also, as the hardware was the same there were no driver/hardware problems after the restore.

    I used "NTBackup", the backup utility that is included with windows, to carry out the backup and restore. The important points about using this were:
    • When restoring, make sure the "Always replace the file on my computer" option is set. (Tools --> Options --> Restore tab)
    • Make sure that you include the system state as part of the backup.
    • When you re-install Windows, make sure it is patched to the same service pack level, before carrying out the restore.
    • Restore all files to their original locations.
    I hope that this helps someone.
  11. rohit2900 Member

    [quote user="Panik77"]
    Restore the C:, D: and System state over the clean windows install, making sure to overwrite all files.
    [/quote]
    How did you performed this step.
    Have you done Hard Disk to Hard Disk copy while you server was off or something else.
  12. Panik77 New Member

    [quote user="rohit2900"]
    Have you done Hard Disk to Hard Disk copy while you server was off or something else.
    [/quote]
    Hi Rohit
    Nope, it was a straight restore from a backup file, using NTBackup.
    The initial backup was of the C: drive and system state, with SQL Server stopped, and the Volume Shadow Copy service started.
    1. First I re-installed windows server 2003 SP2, formatted all of the other hard drives and connected to the network.
    2. I copied the backup files from the file server on to the E: drive of the server I was restoring
    3. restored the C: and System state to their original locations, making sure the "Always replace the file on my computer" option was set.
    4. Started the restore
    5. When the restore completed, restarted the server.
    6. After the server restarted and detected some more hardware devices, I restarted it again.
    7. Once the server was finally back up I started SQL Server.
    The restore was back onto the server that was backed up, so there were no hardware changes, except for the disk configuration being different.
    I wasn't able to do an Automated System Restore (ASR) because the disk configuration was different, and I didn't have a tape drive available so the backups had to be moved over the network to a file server.
    This is why I had to re-install Windows before carrying out the restore, there was no way that I could do a bare metal restore easily.
    I hope this clears everything up for you.

  13. preethi Member

    Thank you for sharing. Many people post when they face a problem, but forget to post when they resolve it. How do we resolve is as important as the problem.
    You have followed the good practice. This will help many others.
  14. jerryhung New Member

    Very cool about the part you restore/override the entire OS/partitions using NTBackup
    I always think surprises will happen when you simply overwrite files especially in Windows
    I would've installed new SQL 2005, then restore the databases manually instead of restoring entire C drive

Share This Page