SQL SERVER – Attaching and Restoring Database in Clustering Generates An Error – Notes from the Field #115

[Notes from Pinal]: In my career, I have seen many database experts who are great with what they do, but when they have to work with clustering or AlwaysOn solutions, they usually avoid it. The reason is that there are not many experts who know this subject well enough. One thing I always personally felt that the documentation is also not widely available when it is about clustering. If one receives some error, they are usually lost.  This is when I reached out to Eduardo and asked him what can we do to if we face error while attaching or restoring database in clustering environment in SQL Server.

In this 108th episode of the Notes from the Fields series database expert Eduardo Castro (partner at Linchpin People) shares very interesting conversation related to how to improve performance of SQL Server by offloading backups to a secondary replica in SQL Server standard edition.

A common problem faced by SQL Server consultants is a lack of good documentation that explains the existing database configuration and infrastructure. Troubleshooting a database blindly, without proper knowledge of the existing configuration can lead to errors in your work.

In some cluster environments you may face the following error messages when you are trying to attach or create a new database in a specific file path:

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

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

There could be different sources for that error in this blog, I will talk about some DMV and T-SQL that can help you map the current cluster configuration to lay the groundwork for working on an unknown SQL Server instance and how this information can help you to understand and solve the errors enumerated above.

The first basic T-SQL you should run is to verify whether the current SQL Server is part of a cluster or not. You can check it by running the following query:

SELECT 'IsClustered', SERVERPROPERTY('IsClustered')

• If the returned data column is a 0, the current instance is NOT part of a cluster.
• If it is a 1, the instance IS part of a cluster.

The second command we should run is to find the name of the node we are connected to. Run the following T-SQL to return the value of the node name:

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]

Another very handy function you should know is fn_virtualservernodes. This function returns information about the nodes that are part of cluster where a SQL Server instance can run.

SELECT * FROM fn_virtualservernodes()

When you run this query, you will get the following columns:

• NodeName (name of the node)
• Status (indicates the current status of the node)
• Status description (more descriptive description about the node status)
• is_current_owner (indicates which node is the current owner of the resources in the cluster)

The following DMV sys.dm_os_cluster_nodes will return the same information as the previous T-SQL, and can replicate the results of the previous T-SQL function, but it will be available in the most recent versions of SQL Server:

SELECT * FROM sys.dm_os_cluster_nodes

One other important check is to read the current status of the nodes, shown in the table below. It is important for you to check that all the nodes are up and running before doing any other work in the instance, you will get the status as result of running a query using the sys.dm_os_cluster_nodes DMV. (HOW DO YOU CHECK THIS?)

When you are working with a clustered instance, you must know what drives are configured in the cluster. To do so, you can use the fn_servershareddrives() or the sys.dm_io_cluster_shared_drives DMV. The T-SQL will return the list of the drive name for each of the shared drives used in the current cluster configuration.

You might also face the case where you are working with Azure SQL Data Warehouse or Parallel Data Warehouse. If this is the case, the T-SQL will return an extra column called pwd_node_id that is the identifier of the node.

SELECT * FROM fn_servershareddrives()

SELECT * FROM sys.dm_io_cluster_shared_drives

After running the previous T-SQL, you will have an idea about the cluster configuration. But if you need to know more information about all valid shared disks and the current owner of a specific drive, you can also use the dm_io_cluster_valid_path_names DMV.

SELECT * FROM sys.dm_io_cluster_valid_path_names

Now that you have all the information about the cluster: node names, disk drives and state of the nodes, you should check what valid path names are being reported by the sys.dm_io_cluster_valid_path_names if you are trying to attach or restore a database that is a path not listed by the DMV then you must check the cluster resources dependencies and be sure to add the cluster disk to the dependencies in the SQL Server resource as show bellow:

After you add the dependencies you should be able to attach and restore your database.

Conclusion

If you are going to work on a SQL Server instance that does not have good documentation available, it is a good practice to first check if you are working with a cluster, and then learn what the basic configuration of that cluster is. Knowing the cluster status will help you to determine the best approach to solving the problem at hand.

Reference: Pinal Dave (https://blog.sqlauthority.com)