none
How to add a 2nd iSCSI virtual drive to a 2 node sql server 2012 cluster?

    Question

  • current test environment:

    windows server 2012 datacenter running hyper-v 2012

    3 vms, 1 is domain controller, the other 2 are a two node cluster running sql server 2012 enterprise

    the DC also runs iSCSI target

    i was able to set up and install SQL Server with no problems. in created a small database and everything works

    next tasks was to create a new virtual drive, present it as an iSCSI drive to Cluster manager and add a new drive to the cluster which i would then install a new db on.

    iSCSI target set up ok, iSCSI initiator connects from each cluster member ok. drives are visible and formatted on each cluster.

    in cluster manager i connect to the cluster, click roles, expand to SQL Server (MSSQLSERVER), Add storage and add the new drive and it shows up under the storage listing under MSSQLSERVER.

    if i go to 'DISKs' under storage under my node it shows the disk, 'Online', Assigned to SQL Server (MSSQLServer), owner Clone1 (first member of cluster). this display is the same as the other drive successfully added PRIOR to installing SQL Server into the cluster.

    if i now connect to SQL Server via Enterprise Manager and attempt to restore a database from a backup file, all goes well until i change the file location to the new drive 'F:\SQLServer\databasefilename.mdf' at which point it gives me the error :

    System.Data.SqlClient.SqlError: cannot use file 'f:\sqlserver\data\datafilename.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the SQL Server does not have a dependency on it. (Microsoft.SQlServer.SmoExtended).

    I have searched for the 'dependency settings'. only examples i could find were for 2008. No exact examples for 2012 could be found.

    I did attempt to set the dependency of the new disc under properties for the drive in Cluster Manager. Choices were 'SQL Server Agent', 'Sql Server', 'Cluster Disk 2' (which is the disk that actually works and was set up prior to SQL Server being installed), 'SQL IP Address 1 (SSC)' and 'SQL Network Name (SSC)'.

    i tried about every combination i could think of. nothing works. This is my first attempt at clustering. 2008 examples provide some ideas but the GUI is different.

    Wednesday, January 09, 2013 8:26 PM

All replies

  • Hello,

    After the disk was added as a cluster resource and added to the resource group of the SQL Server instance in question, right-click on the sql server engine service and choose properties. From there you should be able to find the dependency tab put an 'AND' dependcy in and add the disk to it. Restart the service (may not need to restart in 2012, haven't tested it yet) and you should now be able to use it.

    -Sean


    Sean Gallardy | Blog | Twitter

    Thursday, January 10, 2013 12:20 AM
    Answerer
  • Hi there,

    I used Starwind to create virtual disks and connected to my windows2012 server with no issues.

    If you think your configuration is correct it is worthwhile running the cluster validation test. I am sure it will list errors.

    good luck

    Thursday, January 10, 2013 3:04 AM
  • Windows 2008 and later does not require a clustered resource to go offline to set a dependency, provided the dependency results is still online after the change.  SQL version here doesn't matter, just the OS version.


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Thursday, January 10, 2013 9:08 PM
    Moderator
  • i was able to figure it out. use cluster services manager, under node, sql server, dependences tab. the real issue is that there are examples for everything but 2012. the directions are similar but the navigation is different and it took me a while to figure out just how to get to the final configuration location.

    and prior to creating the cluster, i ran the validation test and it passed. in fact the thing worked. my last step in the process after successfully creating a sql server cluster and adding a db was to present a new disk to sql server, add it as a way of increasing disk space. that was the only part i had a problem with.

    Friday, January 11, 2013 7:34 PM
  • Good if you able to connect from cluster manager.

    Restart your servers and if you still able to connect with out any issues then you are good to proceed.

    If you have issues then you should log in as an administrator to the node and use administrativetools iscsci connector and connect from there. While you do that select the check box to connect automatically on log on.

    make sure your multipathIO is enabled. you need to restart after enable. i guess you must have completed this step in the first place or while adding quorums.

    good luck

    kumar

    Friday, January 11, 2013 11:09 PM