SQL Server 2014 Support for Cluster Shared Volumes (CSV) in a Failover Cluster

Starting SQL Server 2014, Failover Cluster Instances supports Cluster Shared Volumes (CSV) in both Windows Server 2008 R2 and Windows Server 2012.

What is Cluster Shared Volumes (CSV)? How Cluster Shared Volumes (CSV) works?
As per Technet below is the definition of CSV.

To understand how Cluster Shared Volumes (CSV) works in a failover cluster, it is important to understand how a cluster works without CSV. Without CSV, a failover cluster allows a given disk (LUN) to be accessed by only one node at a time. Given this constraint, each Hyper-V virtual machine in the failover cluster requires its own set of LUNs in order to be migrated or fail over independently of other virtual machines. In this type of deployment, the number of LUNs must increase with the addition of each virtual machine, which makes management of LUNs and clustered virtual machines more complex.
In contrast, on a failover cluster that uses CSV, multiple virtual machines that are distributed across multiple cluster nodes can all access their Virtual Hard Disk (VHD) files at the same time, even if the VHD files are on a single disk (LUN) in the storage. The clustered virtual machines can all fail over independently of one another.

The above mentioned description is interms of Windows Operating System, which can be better understood by a System Administrator, so let’s try to put it in terms of SQL Server, as CSV is supported by SQL Server 2014 or higher.

Without CSV, a traditional failover cluster which we already know about, allows a given disk (LUN) to be accessed by only one node at a time, meaning SQL Group will own the disk resource and that disk resource cannot be used by any other groups.

Now, with CSV support in SQL Server 2014, we can have multiple groups which are owning different SQL Server Instances, can all use single LUN. Now, we can failover one instance from one node to another node, without affecting or having to failover the disk resources along.

Let’s take an example for better understanding.

– Let’s say we have two nodes Node1 and Node2.

– We then ask System Admins to create a windows cluster/a> and then ask them to add Disk Resources as Cluster Shared Volumes(CSV).

– When SysAdmins add the CSV, we will see the LUN in a path like C:\ClusterStorage\SQLDBFilesDisk\ , where we can rename to VolumeX to more meaningful name.

– We can access this path from both the nodes.

– We then Install one SQL Server 2014 cluster instance, which can failover between two nodes Node1 and Node2. During the SQL Server installation, in that page where we specify the path for “Data Directories” as shown in below image, we need to change the path like C:\ClusterStorage\SQLDBFilesDisk\MicrosoftSQLServer\Instance1\

Cluster Storage Volume CSV

Cluster Storage Volume CSV

– We then Install another SQL Server 2014 cluster instance, which can failover between two nodes Node1 and Node2. During the SQL Server installation, in that page where we specify the path for “Data Directories” as shown in above image, we need to change the path like C:\ClusterStorage\SQLDBFilesDisk\MicrosoftSQLServer\Instance2\

– Once the installation completes, we can browse to the database files by going to C:\ClusterStorage\SQLDBFilesDisk\MicrosoftSQLServer\ from any of the nodes. Here we are using the same LUN for both the SQL Server Instances, which was not possible before SQL Server 2014.

– If we open SQL Cluster Manager, and go to SQL Server Group, we will only see SQL Server, SQL Agent, SQL Virtual Network Name and IP Address resoures, but the Disk resources will not be there anymore as there are now independent of any one group.

Hope this clarifies what is Cluster Shared Volumes (CSV) and How it can be used with SQL Server 2014.

Cluster Storage Volume
Deploying SQL Server 2014 with Cluster Shared Volumes

Happy Reading,
SQLServerF1 Team


Microsoft SQL Server Licensing Questions

It is very common to see people has many questions related to Microsoft SQL Server Licensing. Questions about Licensing are better answered by a Microsoft licensing specialist.

If you are in North America you can call 1-800-426-9400, Monday through Friday, 6:00 A.M. to 6:00 P.M. (Pacific Time) to speak directly to a Microsoft licensing specialist, and you can get detailed information from them. Worldwide customers can use the Guide to Worldwide Microsoft Licensing Sites to find contact information in their regional locations:

Some References:
SQL Server 2014 Licensing Datasheet


Common Issues Handled by SQL Server Database Administrators(DBA)

As a SQL Server Database Administrator(DBA), we daily work on many issues and would like to highlight some of the very common issues handled on Day-to-Day basis. We may be alerted about these issues through some Monitoring software or by the Client or the Development/Support Teams.

We can basically classify the common issues in two main categories:

1. Production disrupting pages: Those causing production outage or service reduction such as:

– Unable to connect to the Server/Instance (services went down or offline).
– Database corruption
– Database in Suspect mode
– High CPU usage
– High Memory Usgae
– Stack Dumps
– High # of sessions or suspended SPIDs
– Very low space on drives containing DB files.
– Blocking
– SQL Server Performance/Slowness Issues

These need higher awareness of diagnosis techniques and faster reaction to resolve. These issues need to be escalated to a DBA ASAP in order to resolve these issues quickly.

2. Production non-disrupting pages: Issues that doesn’t affect the production with relaxed window of resolution such as:

– Backup failures; however, log backups on busy environment can be considered critical.
– Some job failures.
– Warnings from errorlog
– Root Cause Analysis(RCA) for various issues like SQL Server failure/Cluster Failover

There are not just the only issues handled, but are some of the common issues a DBA handles mostly on a Day-to-Day basis.

Happy Reading,
SQLServerF1 Team


What Happens to the Connections When we Move or Failover SQL Server Group to Another Node

It is common to see many people ask this question, What happens to the connections from Application to SQL Server, when we move or failover SQL Server Group from one node to another Node?

If we understand what happens when we Failover or Move SQL cluster instance from one node to other, we will get the answer to the question.

We we initiate a failover, All the resources in that group which include(SQL Server resource, SQL Server Agent resource, Multiple Disk Resources, Network Name and IP Address resource, other third party resources like backup, fileshare resources) will be taken OFFLINE on that node, then these resources are moved to the new node and then all these resources are brought ONLINE. Once all the resources are ONLINE, we can make connections to the SQL Server.

As we can see from the above process, SQL Server will be stopped, which means that any existing connections will fail, it may be possible that these failed transactions already would have modified some data in the database, but as per design of SQL Server engine, this ingormation is also logged into SQL Server Transaction log file(.ldf). When SQL Server starts on new node, it will read the transaction log file to see which transactions were committed and which were not, based on that it will rollback any incomplete work done by uncommitted transactions.

Hope this answers the question.

Happy Reading,
SQLServerF1 Team


Unable to Release Free Space from Database Data File Even After Shrink

I have worked on a issues where there was a requirement to release free space from the data file. There was a database whose data file grew very large. Development team worked and deleted/archived lot of data and now there was lot of free space available inside the database file. The next step was to run Shrink command to release the free space inside the file.

Although, we recommended against Shrinking, but the Development team mentioned that the database will not grow much as we will be archiving the data regularly, so we need to get the free space from the data file which can be used by other databases on the server.

So, we started working on releasing the free space available in the data file, but we ran into issues where we tried standard options, but they were not working. Below are some steps which we tried to release the free space.

– Ran DBCC SHRINKFILE against the data file. This command completed successfully, but did not release any free space.

– Tried running ShrinkFile command in batches to release small chunk of space, but still was not working.

– Rebuilded all the indexes in the database and then issued SHRINKFILE command, but still the free space was not released.

– Ran DBCC UPDATEUSAGE to correct any page and row count inaccuracies in the catalog views. Then ran the ShrnikFile command, but still we could not release the free space.

USE DatabaseName;

– We are SQL Server 2008 R2 SP2, so no known issues related to Shrink issues.

– Checked for any GhostRecords/Cleanup, but no such issues found.

– Finally, we were able to fix the issue by below steps.

1. Ran DBCC CLEANTABLE which reclaims space from dropped variable-length columns in tables or indexed views. Below code is used to reclaim space from all tables in current database.

Use DatabaseName
EXEC sp_msforeachtable ‘DBCC CLEANTABLE(0, ”?”) ‘;

2. Ran SHRINKFILE command which now released the free space.

Caution using DBCC CLEANTABLE command, DBCC CLEANTABLE runs as one or more transactions. If a batch size is not specified, the command processes the whole table in one transaction and the table is exclusively locked during the operation. For some large tables, the length of the single transaction and the log space required may be too much. If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows. DBCC CLEANTABLE cannot be run as a transaction inside another transaction. This operation is fully logged.DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after you make significant changes to variable-length columns in a table or indexed view and you need to immediately reclaim the unused space.

Happy Reading,
SQLServerF1 Team


Exporting Data from SQL Server to Microsoft ACCESS(MSACCESS) Database

Recently I worked on a requirement where we had to export data from few tables in SQL Server 2008 R2 to Microsoft Access database. Initially it seemed to be a simple request, but the challenge we faced was, there was no MSAccess database available or MSAccess software installed on any of the servers.

So, first we had to work on creating a MSAccess database and then export the data from SQL Server to MSAccess database.

How to Create MSAccess database when Microsoft Access is not installed on any of the servers?

Follow the below steps to create MSAccess database

1. Click Start -> All Programs – Administrative Tools -> Click on Data Sources (ODBC) tool which will open a window.

2. Click Add button -> Then you get an option to choose a driver there we need to select Microsoft Access related driver.

MSAccess Driver Selection

Selecting MSAccess driver

3. Click Finish

4. Now, we will be on Microsoft Access Setup window

5. Click on Create button

6. Choose the path where you want to save the MSAccess database physical file and provide a name to the database.

MSAccess Path and Database Name

MSAccess Path and Database Name

7. Click OK, which will give us a message that the new MsAccess database with the specified name has been created.

8. Browse to the path and we will be able to see the physical MSAccess database file.

Now, next step is to Export the data from SQL Server Database to newly created MSAccess database. Follow below steps to export the data

1. Connect to the SQL Server instance from SQL Server Management Studio.

2. Right click on the databases where the tables exist which you want to export -> Select Tasks -> Export Data, which will open a Export Wizard

3. Choose Source Server details and click next
Data Source – Leave default
Server Name – Choose the SQL Server instance name where tables exist
Select the authentication type
Make sure database name is correctly listed

4. Choose Destination Server details
Destination – Select Microsoft Access related driver
FilaName – Browse to the path and selected the MSAccess database which we created

5. Click On Advanced button and click Test Connection to make sure connection can be made to the Access database file

6. Click OK and Click Next

7. Select the radio button “Copy data from one or more tables or views” and click next

8. Select the tables which you want to export and once all required tables are selected, click next

9. Select the check box “execute immediately” and click next

10. Click Finish which will export the data from SQL Server database tables to the MSAccess database file.

Now, we can move the MSAcccess database file to other server to access the data

Happy Reading!

Keerthi Deep


Setting-up SQL Server AlwaysON Across Different Domains

Recently, I got a request to migrate servers which has SQL Server 2012 with AlwaysON setup to new environment. The new environment resides on a different subnet and has a different domain. There will be trust between both the domains.

Now the question they have is, can we setup AlwaysON between existing servers on one domain and new server in another domain?
No, this scenario is not possible, AlwaysON can only be setup between servers of same domain. Windows clustering which is a pre-requisite for AlwaysON setup can only be done between servers of same domain, so we are blocked at the very early stage itself from proceeding further.

Hope this clarifies someones doubt under same situation.


Authentication Mode Changed After Applying Service Pack 4 for SQL Server 2005

I recently worked on an issue wherein after applying Service Pack 4(SP4) for SQL Server 2005 on a cluster, we were unable to connect to the SQL Server instance using SQL Server authentication.

To explain it better, let’s say, there are 2 nodes in a cluster with names nodes Node1 & Node2, SQL Server 2005 instance was active on Node1. SP4 was run from Node1 which first applies the SP4 on passive node and then applies on active node. From Management studio both windows and SQL authentication works fine on Node1, but once the SQL instance is failed over to Node2 it comes online without any issue, but when we try to connect to the SQL instance using management studio using SQL Server authentication, it gives login failed error message.

Based on our initial research we found that the connectivity issue occurred because the Authentication Mode of the SQL instance was changed to WINDOWS ONLY on passive node whereas on active node the authentication mode was still MIXED. We changed the following registry key value to 2 and now both the windows and SQL authentication works fine.

SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INST1\MSSQLServer\LoginMode

Now, the million dollar question was why the authentication mode was changed to WINDOWS ONLY on Node2?

Based on our research, we found that in the SQL Server 2005 setup code when patch/reinstall scenario runs on secondary cluster node the authentication mode is always set to NT authentication (WINDOWS ONLY).
This registry key which has LoginMode is part of SQL Server cluster registry checkpoint. Once the failover happens the login mode on the secondary node is updated automatically from the primary node to the correct value. There is no manual work required in this process and is transparent to the user. The following are the Engine registry sub-hives that are checkpointed under the InstanceID registry hive in SQL Server 2005:

“Cluster” , “MSSQLServer” , “Replication” , “SQLServerAgent” , “PROVIDERS”, “SQLServerSCP” , “CPE”

In my clustered environment, I found that only CPE has checkpoint, all other registry hives do not have checkpoint. That means any change applied to any registry key value which belong to registry hives other than CPE will not get updated automatically from primary node to the correct value. And LoginMode registry key is one such key which belong to MSSQLServer which do not have checkpoint on it. Hence, during SP4 installation once the value of this key is changed to 1, it will remain one until it is changed manually to 2. To resolve this issue, we have to make sure that the above registry hives should have checkpoint. KB article http://support.microsoft.com/kb/953504 will help you in adding the checkpoint to the registry hives and the precautions you need to take in order to execute the steps. However, I have mentioned the steps below to add checkpoint to the above registry hives:

Run the following commands using Command Prompt:

Imp: Beware that change to checkpointed registry keys are saved only when the associated resources are online (In this case, SQL Server). When the resources are not online, changes to the checkpointed keys will be overwritten by the last saved values from the checkpointed file.
If the instance is default, please replace the InstanceName with MSSQLSERVER

cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\Cluster"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\Replication"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent"
cluster res "SQL Server ()" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.1\PROVIDERS"

Once these changes are performed, try failover failback.

Happy Reading,
SQLServerF1 Team


SQL Server 2005 Instance crashes while starting

Recently I was worked on a case wherein we were unable to start the SQL Server 2005 instance as it was failing with the below error:

Faulting application sqlservr.exe, version 2005.90.5057.0, time stamp 0x4d8d381e, faulting module kernel32.dll, version 6.0.6002.18449, time stamp 0x4da47b2f, exception code 0xc06d007e, fault offset 0x00000000000170cd, process id 0xe34, application start time 0x01ccd9ea6d1b2185.

Errorlog file was not getting created and the above error was seen in the event viewer application log. Initially, it looked to be some executable or binary was corrupt. Ideal solution for these kinds of issues is to uninstall & re-install the SQL instance after taking backup of all the database files.

As no one likes the option of un-install & reinstall of SQL instance, I kept that as a last resort and decided to try out other options to resolve this issue. As a first step I tried to start from command line but it was crashing and in the event viewer I saw the same error. Then I tried starting SQL Server in a single user mode with -c -m -f but still it was crashing.

As some of the SQL Server 2005 binaries seem to have been corrupted, I decided to rebuild the SQL Server binaries files & registry.

To set correct expectations, below option may or may not resolve the issue always, so before trying the below action plan, make sure to take the backup of all you database files and keep them in a safe location.

I ran the below command to rebuilt SQL specific files and registries which completed successfully after which I was able to start SQL Server 2005 instance:

start /wait setup.exe /qb INSTANCENAME=Instance_Name REINSTALL=ALL REINSTALLMODE=omus

Rebuilding the Registry
To rebuild the SQL Server 2005 registry, you must use the Setup command with the REINSTALL=ALL and REINSTALLMODE=omus parameters. Running Setup.exe with these parameter settings rebuild, verify, and repair a SQL Server instance and rebuild the registry. This procedure is most often used to rebuild the registry for a damaged SQL Server installation.
To rebuild the registry, you must use the same package file and options that you specified during the initial installation. If you do not know this information, uninstall and then reinstall SQL Server, rather than rebuild the registry.
Your syntax must specify either the /qb or /qn option. The /qb option displays basic Setup user interface and error message dialog boxes. If the /qn option is specified, all Setup dialog boxes, including error messages, are written to Setup log files.
In order to rebuild system databases, a new sa password is REQUIRED. Do not use a blank password. Use a strong password.
To repair all files, rebuild the registry, and replace all SQL Server shortcuts from the command prompt
1. Insert the SQL Server 2005 installation media into the disk drive.
2. Run the following command from the command prompt:
For INSTANCENAME, use MSSQLSERVER for a default instance. For a named instance, specify the instance name.
The resource database (mssqlsystemresource) is restored from SQL Server 2005 distribution media. When you rebuild the resource database from distribution media, all Service Packs and QFE updates are lost, and therefore must be reapplied. Before you proceed, see the [REBUILDDATABASE] section more information.

Happy Reading,
SQLServerF1 Team

1 2