Pages

Monday 11 June 2012

SQL Server : Moving System, resource and User Databases to New SAN disk

In my earlier post I have explained the steps that we have followed to move the MSDTC and Quorum drive to the new SAN. In this post let us go through the steps that we have followed to move the System,Resource and User databases to the new SAN.The environment that we are talking about is running on windows 2008 server and SQL server 2008

 When this task assigned to me, I did not had any idea about the steps that need to be followed. The first thing came in mind was copying the entire contents of each drive to the respective new drive and change the drive letter, but that is a time consuming process and need lot of down time. After spending lots of time, we came up with a plan with minimal downtime. Let us go through the steps.We stared our process from Wednesday and finished on Sunday. I will explain the steps that we have followed on each day.


Wednesday 

  • Give proper resource name for each disk in the available storage: This can be done right clicking the disk and selecting the properties option and change the resource name.This will help us to identify the disks in the future steps.I suggest to do it for old disk also for easy identification of disks.In many places it will never show the drive letter.
  • Move the new disk to resource group : This can be done right clicking on the available disk and selecting more actions. Do this for each disk and move to appropriate application group(SQL instance)










  • Add dependency on the disks for the SQL server instance : This can be done by selecting the right instance under the Services and Application group available in the left pane. Then right click on the SQL server engine resource (which will listed under the Other resource in the detail pane) and select the properties.On the dependencies tab add the new disks.
  • Test the failover to make sure that the disks are failing over to the other nodes with out any issue.

Thursday

  • Change the recovery model of the user database which are in simple recovery to full recovery.Note down the list of database which were in simple recovery.This will help us to revert back the database to simple recovery after moving to new SAN.
  • Schedule a full backup on friday early morning of all availabe user database in that instance. This can be changed depend on the environment and backup policy.The output of the below script can be used to schedule the  full backup of all user database.
SELECT 'Backup database ['+name +'] to disk =''R:\DISKmoveFullBackup\'+ REPLACE(name,' ','')+'_Diskmovefriday.bak'' with password =''Password@121'''   FROM sys.databases WHERE name NOT IN ('MAster','model','msdb','tempdb','distribution')
  • Take a copy of current database file location to excel file.It will be helpful if you need to refer the location at the later stage. I have copied the output of the below query to excel sheet.
SELECT database_id,DB_NAME(database_id),FILE_ID,type_desc,name,physical_name,state_desc FROM sys.master_files


Friday

The first task on Friday is to restore all user database as _New in the new disk. In our environment we have equal number of new disks. As a first step we need create the same folder structure new disk  as in the old disk. As it is data drives , the folder structure may not be complicated  to create. You can ignore the folder structure where the system files resides. At later point we will be copying the entire folder to the new drive.This script can be used to generate the restoration script based on the latest full backup.

The next step is to change the default data/log path and backup path. To do that on the server node in the object explorer and select properties.On left pane of the property window select the database settings and change the default location of data and log. Using the regedit change the value of BackupDirectory key under the HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL   Server\MSSQL10.instname\MSSQLSERVER

Disable any full backup job that is schedule to run before the disk movement maintenance windows. Any full backup will break the backup chain.If the environment have the mirroring/replication , prepare complete script to remove and create replication and mirroring.

Sunday : The Big Day

Take differential backup of all user database and apply on top of the _new database which we created on Friday.The output of the below script can be used to take the differential backup.Before starting the differential backup, disable all the transaction log backup jobs and make sure that none of the transaction backup jobs are  running at the point.

SELECT 'backup database ['+name +'] to disk =''R:\DiskMoveDiffBackup\'+REPLACE(name,' ','')+'_diffsunday.bak'' with DIFFERENTIAL'FROM sys.databases WHERE name NOT IN ('Tempdb','Master','model','msdb' , 'distribution') AND name NOT LIKE '%_new'


To restore the differential backup, this script can be used. Once the restoration of differential backup  completed, the actual maintenance window starts. It will take  minimum of 30 minutes ,depends on the number of database and time required to recover the database after the transaction log restoration. In our environment (with around 60 database and approx 1 TB size)  it took around an hour to complete the process. Recovering the database after the transaction log backup is the most time consuming process.

Follow the below steps once the maintenance window start:
  1. Disable all logins used by the application. This will be helpful to avoid unnecessary connection request from the application.
  2. Stop the SQL server agent service from the cluster admin window.
  3. Kill all existing user session especially from application and agent service.
  4. Remove mirroring and replication using the script which we have prepared on Friday.
  5. Take transition log backup of all user database and rename the existing database as _OLD.
  6. Restore the transaction log backup on top of _new databases with recovery.
  7. Rename _new database (remove the _new).Each section of this script will help us to perform the the steps 5,6 and 7.
  8. Detach and attach the distribution database to new drive.
  9. System databases and resource database will be there in one of the disk and moving them to new disk is a tedious task. We followed  following step to do that
    1. Identify the drive in which the system database and resource database resides .In our case it was M drive and associated new drive is U.
    2. Alter all database file which is  there in M drive (many _old database) to point to U drive.This script will be useful to perform this task.
    3. Alter all database file which is there in U drive (newly restored databases) to point to M drive.This script  will be useful to perform this task.
  10. Alter the tempdb database to point the data and log file to new disk.
  11. Bring down the instance offline through the cluster admin tool. Make sure that that, disks are online.
  12. Copy the system database root folder from M drive to U drive with all sub folders. We have used the XCOPYcommand to move the all the files along with folder structure to the new drive.
  13. Swap the drive letter M and U. We can perform this task through the cluster admin.
  14. Bring the instance online.
  15. Set up the replication
  16. Enable the logins disabled in the step 1 and the instance is ready to use.
  17. Set up the mirroring.
  18. Change recovery model to simple for those recovery changed as part of Thursday task.
  19. Enable all backup jobs.
  20. Drop the _OLD database and remove the dependency on old disks.
  21. Now the old disks will be available under the 'Available disk group'. Right click on each one and delete.
  22. Inform your SAN team 
Thank you for reading such long post !

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba






14 comments:

  1. GREAT article. This will be a huge help. THANKS

    ReplyDelete
  2. Really nice, it will help a lot of professionals.

    The script link is broken in "Alter all database file which is there in M drive (many _old database) to point to U drive.This script will be useful to perform this task."

    ReplyDelete
    Replies
    1. Thank you for reading this article. I have fixed the link

      Thanks
      Nelson John

      Delete
  3. Great post. I just like to use ROBOCOPY instead of XCOPY to move the database files it works faster. Also strange that you have databases in production in Simple recovery mode, but if the applications do not need to be in Full you are good.

    ReplyDelete
    Replies
    1. Thanks for reading this article. I am not sure ROBOCOPY and XCOPY will make lot of difference in this case as the size of the folder which we will be moving very small.
      Regarding simple recovery mode, in our environment we have couple of database which is almost kind of static databases. So we kept them in simple.

      Delete
  4. can use this line in my view

    SET ARITHABORT OFF
    SET ANSI_WARNINGS OFF
    plz let me know ..............

    ReplyDelete
  5. Great post. Can I use your materials by writing my term paper? If yes, write me an answer.

    ReplyDelete
  6. I guess this system is the next step in technologies development thanks for sharing!

    ReplyDelete
  7. I do not really like this server. It is easier for me to use another. It seems to me that it is very convenient

    ReplyDelete
  8. SKARTEC Digital Marketing Academy is an institute dedicated to meet the integrated marketing needs of the industry. Our Digital Marketing Course in Chennai is ideal for those, who wish to manage a successful and sustainable digital marketing strategy.

    This digital marketing certification explores all the core digital marketing and management concepts, techniques and disciplines from planning, implementation and measurement to success and failure factors. Enrolling in this marketing course will prepare you to join an exclusive community of highly-recognized digital marketing experts.

    Digital Marketing Course in Chennai
    Digital Marketing Training in Chennai
    Online Digital Marketing Training
    SEO Training in Chennai
    Digital Marketing Course
    Digital Marketing Training
    Digital Marketing Courses

    ReplyDelete
  9. شركة تنظيف منازل بجدة
    شركة تنظيف منازل بمكة
    افضل شركة تنظيف بجدة
    شركة تنظيف خزانات بالمدينة المنورة
    لدينا في شركتنا من أهم الخدمات الخاصة بتنظيف خزانات المياه اطلب من شركة تنظيف خزانات بجدة خدمات متنوعة فنحن تقوم بعمل كشف على خزانك ولو وجد خلل بالخزان تقوم بتصليح خزانك ونقوم بعمل اللازم وعمل عزل كامل للخزان من الداخل لمنع تسربات المياه من الخزانات شركة صيانة خزانات بجدة نضمن لك عزيزي العميل بان تكون عملية الصيانة والتنظيف تتم على أكمل وجه فلدينا فريق محترف خاص بعملية عزل الخزانات بجدة واخلاءها من الأتربة والترسبات الموجودة بقاع الخزان وترك الخزان نظيف تماما من جميع الأتربة الموجودة بالقاع .
    فلدى شركة لمسات جدة خدمات أخرى متمثلة في تنظيف المنازل من الداخل لأن عمليات تنظيف المنازل من الأمور الصعبة التي تحتاج الى مكالمة شركة تنظيف منازل بجدة لصعوبة عملية التنظيف المنزلية ولدينا ايضا قسم خاص بتنظيف وغسيل المفروشات والسجاد والكنب بالبخار في شركة تنظيف كنب بالبخار بجدة نتميز بالدقة العالية لاستخدامنا أفضل أنواع المطهرات والمعقمات للمفارش والكنب المصرح بها عالميا
    وقد تعاملنا في جدة باحترافية في شركة تنظيف بجدة للحصول على خدمات تنظيف متميزة
    وايضا في مجال التنظيف في مكة لدينا شركة تنظيف منازل بمكة متميزة ومتخصصة وعلى خبرة كبيرة بكل مجالات التنظيف

    ReplyDelete
  10. Hi dear, This is a nice and valuable post thanks for this information!
    Digital Marketing Course in Kolkata

    ReplyDelete
  11. This is the very good post. Thanks for sharing with us. It is more informative...

    Oracle DBA Training
    Sybase DBA Training
    Mysql DBA Training

    ReplyDelete