If you are running Developer or Enterprise edition, consider restoring from a database snapshot rather than a full backup (https://msdn.microsoft.com/en-s/library/ms189281.aspx). This can also be less resource-intensive than a full restore.
The time needed to revert from a snapshot is largely proportional to the amount of changes made since the snapshot was taken so it can be significantly faster than a normal restore in many cases. Like a regular full restore, the log is initialized so keeping the log size small will reduce the restore time.
Windows Server Failover Clustering requires a minimum of two computers (Virtual Machines or Physical Machines). Lets refer to these as Node01
and Node02
.
Part of the install process for the Windows Server Failover Clustering role defines the name of the cluster. For our purposes let's call that MyCluster
.
Once you have created MyCluster
, you can add SQL Server instances to the cluster as independent "cluster resource groups". We'll refer to our SQL Server clustered instance as MyClusterSQL\INSTANCE
.
So, to clarify, we have:
╔════════════════════════╦═══════════════════════╗
║ Item ║ Names ║
╠════════════════════════╬═══════════════════════╣
║ Computers ║ Node01 and Node02 ║
║ Cluster Virtual Server ║ MyCluster ║
║ SQL Server Instance ║ MyClusterSQL\INSTANCE ║
╚════════════════════════╩═══════════════════════╝
Now, if you run SELECT SERVERPROPERTY('ComputerNamePhysicalNetbios')
it will return either Node01
or Node02
depending on which computer is hosting the SQL Server Instance.
SELECT SERVERPROPERTY('MachineName');
will return MyClusterSQL
. The Microsoft Docs for SERVERPROPERTY says this about "MachineName":
Windows computer name on which the server instance is running.
For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.
SELECT @@SERVERNAME;
will return MyClusterSQL\INSTANCE
.
Server Manager always returns the name of the computer you connect to. In our case, this will be either Node01
or Node02
. If the SQL Server Clustered Instance is running on Node02
, and you connect to it using Remote Desktop to the MyClusterSQL server, Server Manager will show Node02
, even though you connected to MyClusterSQL
.
Best Answer
Try running sp_Blitz, a free health check for your SQL Server (disclaimer - I'm the author). You can also run it with @OutputType = 'markdown' if you want to share the results here at Stack.
I've got a hunch that you're running into THREADPOOL waits, and sp_Blitz alerts about that. THREADPOOL means your SQL Server ran out of worker threads to service incoming queries. It won't show up in the OS or SQL Server error logs.
When it's happening, you'll be able to connect to SQL Server using the Dedicated Admin Connection (DAC) (disclaimer: that's a blog post on my site.) The DAC is a set-aside CPU scheduler used just for emergency troubleshooting. From there, you'll be able to see which queries are burning up all the worker threads - typically it's a blocking problem.