locked
Change SAN cluster sql 2005 RRS feed

  • Question

  • Hello,

    I am planning to change my SAN storage

    I have a cluster of Windows Server 2003 with Microsoft SQL database 2005

    What would be the steps to make the change with minimal impact?

    thanks,

     

     

    Monday, October 24, 2011 1:11 PM

Answers

  • hi,

    Replicate your San and reconnect to the cluster.

    Else bind new targets and stop the SQL cluster lets say drive S: is your DB disk name the new disk X: Copy all the data from S to X

    change drive letter from S: to Z: then change drive letter from X: to S: en start SQL and you are ready to go.

    But if you can replicate the sans it is much easyer.

     


    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    • Marked as answer by Vincent Hu Wednesday, October 26, 2011 3:38 PM
    Monday, October 24, 2011 4:57 PM
  • Make sure you have your new disks under the control of the cluster BEFORE you start copying data to them. If you have disks presented to both nodes w/o them being cluster resources, it's possible both nodes can see and access the disks and you risk corruption. When you've moved your data to the new disks and changed drive letters, make sure you relink all your SQL resource dependencies from the old disks to the new.
    • Marked as answer by Vincent Hu Wednesday, October 26, 2011 3:38 PM
    Tuesday, October 25, 2011 12:45 PM

All replies

  • hi,

    Replicate your San and reconnect to the cluster.

    Else bind new targets and stop the SQL cluster lets say drive S: is your DB disk name the new disk X: Copy all the data from S to X

    change drive letter from S: to Z: then change drive letter from X: to S: en start SQL and you are ready to go.

    But if you can replicate the sans it is much easyer.

     


    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    • Marked as answer by Vincent Hu Wednesday, October 26, 2011 3:38 PM
    Monday, October 24, 2011 4:57 PM
  • But if you can replicate the sans it is much easyer.


    I agree with the Disk Migration / rolling drive-letters as Robert details above, and I also agree that a old-SAN to new-SAN replication would be much easier, and possibly faster.

    Contact the vendor of your new SAN, I am sure they have cluster-replication/migration tools to make it easy for you

    HTH,

    Edwin.

    Tuesday, October 25, 2011 11:35 AM
  • Make sure you have your new disks under the control of the cluster BEFORE you start copying data to them. If you have disks presented to both nodes w/o them being cluster resources, it's possible both nodes can see and access the disks and you risk corruption. When you've moved your data to the new disks and changed drive letters, make sure you relink all your SQL resource dependencies from the old disks to the new.
    • Marked as answer by Vincent Hu Wednesday, October 26, 2011 3:38 PM
    Tuesday, October 25, 2011 12:45 PM
  • Make sure you have your new disks under the control of the cluster BEFORE you start copying data to them. If you have disks presented to both nodes w/o them being cluster resources, it's possible both nodes can see and access the disks and you risk corruption. When you've moved your data to the new disks and changed drive letters, make sure you relink all your SQL resource dependencies from the old disks to the new.


    Absolutely! when doing this manually

    but if you use a Storage Vendor tool, then you need to follow the Vendor's instructions.

    Tuesday, October 25, 2011 1:13 PM
  • first, thank you all for the replies

     

    I did not find software for copying data to NetApp, then, I've thought about doing the copy, with a backup and restore of SQL databases. Is this correct?

     

    Thanks,

    Wednesday, October 26, 2011 8:20 AM
  • Hi,

    Make sure you have the new cluster disks in place and that they are working with a failover.

    after this. make a SQL backup of all the DB's . turnoff the SQL services.

    old disk is S: copy all the info to the replacement disk of S: ( X:) after this is done replace the X: with the S: drive letter.

    and do the same with the Log file Drive.

    now that all disks are ready to go start the SQL services and see if all the DB are working. unpresent the Old disks and keep checking if the DB is working correctly.

    if it fails just replace the Drive letters to the old disk and everything is as it was. ( unless you do a move )

     


    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    Wednesday, October 26, 2011 10:34 AM
  • Robert,

    We are doing the same migration except we are on SQL 2008 std x64 on windows 2008 x64.  It is a 2 node active/passive clustering setup.     With that in mind, would the manual implementation steps that you outlined be the same for our setup.

    Also, we will be using the existing 2 nodes for the new SAN.  We will put in the third sas cable to connect the 2 nodes to the new SAN while these same nodes are connecting to the existing SAN.  As you mentioned, I need to make sure that the new cluster disks in the new san is working with a failover.  can u advise me on how to make sure the new cluster disks would work with failover?  I don't have any testing equipments to test this and not sure if I can create a new SQL resource for the new SANunder the same cluster or not.  The bottom line is that I will be making any changes in a live cluster setup and I need to make sure what I am doing first.

    OC


    Ocean Deep
    Thursday, November 10, 2011 12:07 AM
  • Hi,

    Yes the rolling drive letters works also for you.

    First make sure you present all the needed disks to the cluster in your new resource group , format them and give them drive letters.

    Now you can play with them failover do a test copy etc. if everything is working then stop the SQL server resource and copy all the Data from old to the new disk if this is done then change the drive letters in the old resource and give the Drive letters to the new resource.

    now you are almost ready : Place the new disks in the old SQL resource this is where the SQL services is !

    You can turn the old disk offline and leave them for a brief test period in the resource or you could remove the old disk out of the resource and put them in a temp cluster resource. I would prefere the last option.

     

     

     


    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    Thursday, November 10, 2011 1:28 PM
  • Rob,

    Thanks for the reply.

    I have couple follow-up questions specific to our environment. Let me give you a bit more detail about our setup first.  In Services and Applications under the Failover cluster Management tool, I have two items, one is MSDTC related (which I created during the windows cluster setup, it is required during the setup even though we don't use MSDTC) and this MSDTC is tied to one cluster disk say H.  This disk bascially contains a folder with one old text file in it.  The other item in Services and Applications is the group I created to hold SQL resources.  In that SQL resource group, it shows all the cluster disks involved (which is 3 of them, say E,F, G) and the SQL server and agent as other resources.  When I go to "Storage' under the Failover Cluster Management tool, it will list all the cluster disks that it sees.  Besides the four (E, F, G, H disks) that I mentioned earlier under 'Services and Applications' , it has one more cluster disk say Q, which is designated as Quorum (the so-called Witness disk).  I created this during the windows cluster setup.   When I right-click on each cluster disk, it gives me a 'change drive letter' feature.   It also gives me the 'Move this resource to another service or application' feature which I think this is what I would use to put the new disks in the old SQL resource like you mentioned.  However, this 'Move'  feature for some reason is not availble for the quorum cluster disk.  Do u know why?  On the other hand, since this disk doesn't belong to SQL resource group, as long as the new SAN has the same letter disk and content (which I will move via Windows manually), would SQL service or Windows cluster service care how I move this disk?

    Since I can't really test the SAN move until the go-live on the live system,  I like to think about the contingency plan.   say if something were to go wrong like after SQL resource move and drive letter switch, SQL service won't start, and I can't resolve the issue and need to switch back to the old SAN, would it be a matter of switching the drive letters back in the old SAN and start the SQL service again?  I am concerned if the initial failure of SQL service start may corrupt something that would prevent me from going back to the old SAN.

    Looking forward to your reply.

     


    Ocean Deep
    Thursday, November 10, 2011 10:40 PM
  • if something went wrong no problem if you do a copy and not a move you can present the old disk back but it would not fail.

    If you present the new disk and you can configure them and placed them in the SQL resource group the migration is already done except the copy and change drive letters.

    if SQL can't start that is only if non of the disk are there.

    step one is present the new disk to the cluster in a new resource group. and give drive letters format etc. do a failover test see if you can write to the new disk.

     

     


    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    Friday, November 11, 2011 10:36 AM
  • Rob,

    Thanks for the prompt reply.  Maybe I am thinking this more complicated than I should.   I am quite clear now.  Bear with me on this.  Please correct my statements below if i am not right.

    1. I should just think of such move as if I was adding a new hard disk to a server and move the database from an existing hard disk to that new hard disk. Regardless this is done under a cluster environment or not, the underlying concept is to make sure when SQL services starts, it sees the same letter of hard drive(s).  I don't need to worry about any SQL server changes (i.e reinstallation) at all and there is no need for me to move SQL server and SQL agent in 'Other Resources' under the SQL resource group in the Failover Cluster Managerment tool.

    2. this whole migration steps only involves moving disks that windows cluster uses in the old SAN.  There are two 'moves' here. The first move actually is a copy, a phyically data copy.  I would copy all the content from the cluster disks from the old SAN to the new SAN.  The second move is a 'logical' move using Failover Cluster Managerment tool, where I need to use the  'Move this resource to another service or application' feature to move the new Disks from the new SAN to the existing SQL resource group.  This is a software (cluster management tool) level move so the cluster software knows where things are. 

    3. Whatever I do to the configuration of the new SAN regarding RAID, it is not a concern to SQL server.  Even the new Raid in the new SAN is raid 5 comparing to Raid 10 in the old SAN and it has different LUN# under the new SAN, it is not an issue at all.  As long as I have the same Drive letter after the mirgration that SQL likes, that's it.

    4. As far as dependencies, SQL server is depending on disks and it is referring to those disks by number instead of letter.  If I can't make the same numbering in the new SAN.  I need to make sure that the dependency properties under SQL server has the correct disks from the new SAN.   This should be only the dependencies I need to worry about.

    Please let me know if I need to clarify any of the above.

    OD

     


    Ocean Deep
    Friday, November 11, 2011 10:57 PM
  • correct.

    Lun Id's etc won't hurt the SQL server as long as you keep the same drive letters and put the disk in the sql resource group.


    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    Saturday, November 12, 2011 9:21 AM
  • Robert,

    We have the SAN in place and raids are set up.  The SAn is not attached to the host server yet.  I am going to format the RAIDs under Windows.  By default, windows block size is 4KB.  I think for the raid for the Log file, it is ok with 4 KB.  For the two Raids that would contain datadatabases and tempdb, do you recommend I should set it to 64KB?  I read an article (for SQL 2005 however, we are SQL 2008 std) stating that SQL Server writes are 8KB and readahead is 64KB.   Since readahead is 64BK, it seems to make sense to set the block size to 64KB for the formatting.   Please advice.

     

    OC


    Ocean Deep
    Monday, January 30, 2012 5:26 PM
  • Large Block size is better for large files. DB are most of the time Large.

    But 64K blocksize takes more room for small files.

    I would do Large blocksize.


    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    Monday, January 30, 2012 5:38 PM
  • Robert,

    Couple things to add, The two Raids for the user databases and tempdb are both Raid 10.   The segment size of our Raids in the SAN is set to 128KB. It is set by the vendor.  Is it important to match the windows block size to the segment size of the Raid set by vendor

     

    OD 


    Ocean Deep
    Monday, January 30, 2012 5:46 PM
  • hi,

     

    here is a great article about block size and disk alignment.

    http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

     

    http://holyhandgrenade.org/blog/2011/08/disk-performance-part-2-raid-layouts-and-stripe-sizing/

     

     


    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    Monday, January 30, 2012 5:50 PM
  • Thanks for the articles.   I will use 64K.

    Rob,

    Say my mirgration went fine.  Now it is time for me to disconnect the old san from the hosts.  Is there a graceful way to remove the hard disks from the old SAN first? 

    At the end of mirgration, the last step is either remove all sas cables between the hostsand old SAN  or power down the old SAN.  Since the old SAN disks are still managed by the cluster, I assume we need to tell it that it no longer needs to manage those disks.   In the Windows 2008 'Failover Cluster Management' tool, does the following approach make sense?

    1. On each old SAN disk, right click and select 'Remove from TempSQLResourceGroup'. 

    2. On each old SAN disk being seen by cluster, right click and select 'Take this resource offline'

    3. shut down the old SAN or disconnect all sas cables that are connected between the hosts and the old SAN.

    Please advice if this is correct way.

    Thanks in advance.

    OD

     


    Ocean Deep
    Tuesday, January 31, 2012 11:31 PM
  • Hi,

    If the migration is finished then I should remove first all the old entries in the cluster after this is done then I would do a failover and reboot the servers just to make sure everything is working fine.

    then I would remove the connections to your old san.

     

     


    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    Wednesday, February 1, 2012 7:40 AM
  • Robert,

    Just double checking, besides user database files, this rolling drive letter method will also work on the disks that contains tempdb, master, msdb.   As long as the disk that holds these system databases has the same drive letter back, SQL will come up.

     

    OD


    Ocean Deep
    Sunday, February 5, 2012 3:10 AM
  • yes just copy all the data to the new disk. 
    Greetings, Robert Smit [MVP] http://robertsmit.wordpress.com/ “Please click "Vote As Helpful" if it is helpful for you and Proposed As Answer”
    Sunday, February 5, 2012 1:23 PM