Top New SQL Server 2012 Features

SQL Server 2012 was released by Microsoft on April 1st 2012. There are many new features introduced in SQL Server 2012 and some existing features are enhanced as well. One of the most popular feature and most talked feature that comes in SQL Server 2012 is AlwaysON Availability Groups.

Below are some of the top new features in SQL Server 2012.

AlwaysOn Availability Groups – SQL Server 2012 AlwaysON Availability Groups is a revolutionary feature which replaces many other features such Database Mirroring, Logshipping and up to an extent Replication. AlwaysON also known as HADRON, which provides High Availability and Disaster Recovery solution for critical databases. AlwaysOn Availability Groups is a container which consists of one or more databases which can together failover as an unit. a set of replicas(servers) host the primary and secondary AlwaysON Availability Group databases. AlwaysON configuration Availability Group has One Primary Replica and up to 4 Secondary replicas including one Synchronous and 2 asynchronous replicas. We can perform Read/Write operations on Primary replica and can use Secondary replicas for Read_Only work loads, thus offloading primary server. We can have multiple Available Groups created between same server and failover happens at Availability Group level. Failing over an availability Group brings all databases part of that group ONLINE on the secondary replica. We can also offload backups to be performed on the secondary replicas. CheckDB can also be offloaded to the secondary replicas. It is also simple to implement AlwaysON Availability Groups.

AlwaysON SQL Server Failover Clusters – SQL Server AlwaysON Failover Clusters allows multi-subnet failover clustering where cluster nodes can be spread at geographically at different locations and data is moved to other node through SAN level replication. This provides both High Availability and Disaster Recovery. Another enhancement include flexible failover policy, where in we can choose the condition on which a failover should occur.

Contained Databases – SQL Server 2012 provides partial containment. In previous versions of SQL Server, moving a database from one server to another server also involved moving associated logins and other objects. Starting with SQL Server 2012 Contained Databases, users are associated with the database itself, thus no more dependent on the SQL Server instance. Moving contained database to new server does not require any additional actions of creating logins, or fixing orphan users. Also, previously there were issues related to collation where SQL Server instance is of different collation and databases are of different collation. SQL Server 2012 contained databases solves this problem where tempdb now creates objects related to contained database under same collation of the database.

ColumnStore Indexes – Microsoft SQL Server team brought the concept of column based indexes from VertiPaq. Columnstore indexes in the SQL Server 2012 Database Engine is used to significantly speed-up the processing time of some common data warehousing queries. In traditional clustered and non-clustered indexes which data is stored row wise in pages, where as in SQL Server 2012 ColumnStore Indexes, data is grouped and stored column-wise, so each column can be accessed independent of other columns.

Enhancements to ONLINE rebuild index operations – Starting SQL Server 2012, we can rebuild indexes ONLINE for BLOB indexes, which have data types like nvarchar(max), varchar(max), varbinary(max). This was not possible in previous SQL Server versions.

Database Recovery Adviser – In previous versions of SQL Server, DBA’s had to perform manual restores in order to recover a database from a failure for which Database administrators had to plan and restore a set of backup files in a logically correct order. New Database Recovery Advisor facilitates preparing the restore plans which implement optimal and correct restore sequences, thus reducing the overhead and any manual mistakes. Also with AlwaysON Availability Groups, backups could be performed on secondary replicas as well, in such cases Database Recovery Advisor will be helpful in preparing the restore sequence.

Reduced downtime for Application Upgrades – Adding new columns with default constraint is now meta data only operation, thus significantly reducing amount of time for changes.

New FILETABLE feature has been introduced in SQL Server 2012, which is built on top of FILESTREAM technology. With FILETABLES, we can now store the files and documents in special tables in SQL Server, but access them from Windows applications as if they were stored in the file system, without making any changes to the Windows applications.

Product Update is another new feature introduced in SQL Server 2012 Setup where, setup integrates the latest available product updates with the main product during the installation time, so that the main version and all applicable updates are installed at the same time, thus reducing additional overhead and downtime later.

New Startup Parameters has been added to the SQL Server 2012 Configuration manager, so it makes it simple and manageable for changing paths for master database files or to add any startup trace flags.

Starting with SQL Server 2012 support for partitions is increased to 15,000 by default, which in previous versions was limited to 1,000 by default.

Starting with SQL Server 2012, FileStream FileGroups can contain multiple files and can be on different drives, which improves I/O performance by spreading the I/O load.

There are some changes in Licensing of SQL Server 2012 Enterprise Edition, where two types of Enterprise Edition Licenses are sold based on Server/Client Access License (CAL) or Per Core licensing. Also Data Center Edition has been removed in SQL Server 2012 and Business Intelligence Edition has been introduced.

Starting with SQL Server 2012, we can install it on Windows Server 2012 Core, which basically does not have any GUI.

– SQL Server Business Intelligence Development Studio has been redesigned and modified into SQL Server Data Tools (SSDT).

There are many other features and enhancements made in SQL Server 2012 as well, refer MSDN for more information.

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


SQL Server Cluster Shared Volumes (CSV) Frequently Asked Questions and Answers (FAQ) in Interviews

You can find below, some of the Frequently Asked Interview Question and Answers on Cluster Shared Volumes (CSV) in relation with SQL Server.

What is Cluster Shared Volumes (CSV)?
Cluster Shared Volumes (CSV) provides multiple cluster instances in a failover cluster environment to have simultaneous read-write access to the same LUN (disk) that is provisioned as an NTFS volume.

Example: Two SQL Server instances installed on a failover cluster can use same disk D:\ to store its database files. You can failover one SQL instance from one node to another node and does not require the disk D:\ to be failed over to new node, rather the SQL instance will access the disk D:\ from other node, resulting in two SQL instances, each one running on different nodes can use disk D:\ for thier own database files. Refer Cluster Shared Volumes (CSV) for more information.

When was Cluster Shared Volumes (CSV) introduced?
Cluster Shared Volumes (CSV) was introduced with Windows Server 2008 R2 version, but has been completely re-architected in Windows Server 2012 version.

From Which version of SQL Server was Support to Cluster Shared Volumes (CSV) introduced?
Starting with SQL Server 2014, support for deployment of a SQL Server Failover Cluster Instance (FCI) with Cluster Shared Volumes (CSV) was introduced.

What are the advantages of Deploying SQL Server 2014 with Cluster Shared Volumes (CSV)?
Below are some of the advantages of deploying SQL Server with Cluster Shared Volumes (CSV)

Scalability – Allows multiple SQL Server instances to use or share the same LUN/Disk, which otherwise wound need separate disks for each SQL Server instance with traditional disks.

Availability – When connectivity between a cluster node and LUN/DISK fails, the connectivity will be established where Cluster Shared Volumes (CSV) routes the traffic to over the network to the LUN/DISK, thus allowing the SQL Server instance to allow connections without any failures.

Manageability – Cluster Shared Volumes (CSV) simplifies the management of multiple SQL Server instances on cluster nodes.

Performance – Cluster Shared Volumes (CSV) provide a read-only cache for unbuffered I/O to SQL databases.

Security – Cluster Shared Volumes (CSV) allows integration with BitLocker which allows to secure the deployments outside of the data-centers, such as at branch offices. Volume level encryption allows in meeting the security compliance requirements.

How does SQL Server installation procedure differs with using Cluster Shared Volumes (CSV)?
First, we need to setup Cluster Shared Volumes (CSV) instead of traditional cluster disks, this is to be done by System Administrator. Most of the SQL Server cluster instance installation steps are same even while we use Cluster Shared Volumes (CSV), only place where we change settings during the installation process is in Database Engine Configuration – Data Directories tab where we provide the Cluster Shared Volumes paths like C:\ClusterStortage\SQLServerInstance\ for data root directory and for user database data and log files.

How do I see the physical database files which are on Cluster Shared Volumes (CSV)?
We can browse to the Cluster Shared Volumes (CSV) just like we browse any other folder through windows file explorer. We need to check the path C:\ClusterStorage directory and under that the folder which we specified during the installation for the respective database files, which will have our database files.

Does Disk resource move to another node during the SQL Server instance failover process?
No, disk resource is nor part of the SQL Server group where we have our SQL instances, so disk resource will not failover when we failover a SQL Server instance.

What all cluster resources move during failover of SQL Server instance?
Below are the cluster resources which move to new node when we perform a failover of SQL Server instance from one node to another node.

SQL Server Network Name
SQL Server IP Addresses
SQL Server
SQL Server Agent
Any other resources in the group file fileshare or third party backup resources, etc.

What is the state of Cluster Shared Volumes (CSV) during the failover of SQL Server instance?
Cluster Shared Volumes (CSV) remains ONLINE and is not affected during the time when SQL Server instance is failed over from one node to another node. There is no requirement to failover the disk resources to the node where SQL Instances are running.

Receiving error during the installation of SQL Server while using the Cluster Shared Volumes (CSV)?
There are some people who received various errors while they try to specify the data directories path to Cluster Shared Volumes (CSV) and may receive errors like ” The volume that contains the SQL server Data directory does not belong to Cluster Group”.

These errors are mostly related and occurs when you try to use the Cluster Shared Volumes (CSV) with SQL Server 2012 or lower versions. Support to Cluster Shared Volumes (CSV) for SQL Server was only introduced with SQL Server 2014.

Is Cluster Shared Volumes (CSV) similar to Oracle RAC and does SQL Server allow all cluster nodes to perform Read/Write, thus allows load balancing?
Cluster Shared Volumes (CSV) provides a clustered file system which is basically a storage infrastructure for SQL Server, it is possible to allow RAC like support. Even without a Cluster Shared Volumes (CSV), you can still achieve a load balance where read workloads can be spread on primary and multiple secondary replicas.

Unable to view Cluster Shared Volumes (CSV) from SQL Server Management Studio (SSMS) while trying to perform Backup or Restore?
This ideally should work, where one should be able to view the Cluster Shared Volumes (CSV) from SQL Server Management Studio (SSMS) backup or restore wizards. This has been notified as a bug to the Microsoft Product team and most likely a fix would be released, so make sure that SQL Server 2014 instance is patched with latest updates and test the backup and restores.


SQL Server 2012 Logshipping Backup Job Completes Successfully but No Backup File Created

I encountered a strange issue while working on SQL Server 2012 AlwayaON Availability Group databases which also has Logshipping configured. After failover of AlwaysON Availability Group databases from primary to secondary, and also after moving the logshipping from new primary server to DR server, logshipping backup job was having issues where the job was completing successfully, but was not creating the log backup files. Below is the environment setup.

Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02. Logshipping has been setup from Server01 to Server03(This is just same way how we setup logshipping between any two server databases). AlwaysON and Logshipping has been working without any problems after the initial setup.

Now, when there was a planned maintenance to replace Server01 and Server02 with new hardware, we can failover AlwaysON AG to secondary server and also configure or move logshipping from new primary server to DR server. We decided to do this with minimum downtime to production users by moving all databases to one of the server and to perform the maintenance on the AlwaysoN AG secondary server.

As part of planned maintenance, we failed over everything to Server01 and performed maintenance on Server02 and added it back to the AlwaysON AG. Verified and AlwaysON AG databases got synchronized in some time and logshipping also was working fine without any problems.

Now we have to perform maintenance on Server01, so we moved the AlwaysON AG “Prod_LSTNR” to Server02, which was successful and databases are synchronized as well. As expected, there is no logshipping configuration on databases now as logshipping was only setup when databases were primary on Server01 to Server03. We decided to configure logshipping from Server02 to Server03 now, so that logshipping to Server03 happens when the AlwaysON AG databases are either on Server01 or Server02.

Disabled Logshipping Backup job on Server01, disabled logshipping copy and restore jobs on Server03, which disabled logshipping from Server01 to Server03. Took server01 down for maintenance and is no more available now. Configured logshipping from Server02 to Server03 which created new backup job on Server02 and new copy and restore jobs on Server03. All logshipping jobs started running without any errors.

Later, maintenance of Server01 finished and was online and got connected to AlwaysON AG “Prod_LSTNR” and successfully synchronized as secondary.

After some time, we started receiving alerts for logshipping that the copy and restore latency was above the thresholds. We checked and found backup, copy and restore jobs from Server02 to Server03 are all running successfully. Logshipping report shows that there was no backup, copy or restore performed since Server01 was back online. Checked the history of backup job and noticed that backup was not performing any backups, it was just skipping performing the log backup on Server02.

Started verifying the logshipping and AlwaysON settings and noticed that AlwaysON AG backup preferences was set to run backups on any replica with priority of 60 for Server01 and priority of 40 for Server02. As now, we have both Server01 and Server02 online and Server02 acting as AlwaysON Primary, log backups cannot be performed on Server02 due to low priority and that Server01 is online. Changed the AlwaysON AG backup preferences to run backups on Primary Server, after which backup job started creating the backup files successfully. Later copy and restore jobs also starting working without any issues or latency.

You can find AlwaysON backup preferences from SQL Server management Studion (SSMS) -> Expand Always On High Availability -> Availability Groups -> Open properties of Availability Group where the databases in part of -> Go to Backup preferences tab, where we can change the settings.

If we see the issue only from logshipping perspective, we could not have identified the issue, looking into the issue as AlwaysON along with logshipping helped us in identifying the issue.

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Unable to perform Backup or Restore to Cluster Shared Volume(CSV) Using SSMS GUI in SQL Server 2014

I had been playing with new features of SQL Server 2014 and I encountered an issue with SQL Server 2014 Cluster Shared Volume(CSV) related to Backup/Restore. Refer, if you are new to Cluster Shared Volume(CSV) whose supported has been introduced starting SQL Server 2014. This is a Windows feature which has been available since Windows Server 2012, but SQL Server only started supporting from SQL Server 2014.

I have setup two node Windows Server 2012 cluster and installed one SQL Server 2014 clustered instance. I have added a disk as Cluster Shared Volume(CSV).

When I try to backup a database, I encountered an issue where I tried to perform backup of a database and normally we see file structure where we can browse to choose the location where to backup the databases, but I see a blank file structure, so nothing there to select any path. If I try to specify the backup path manually to CSV disk, I get an error as below. as mentioned in the error, I have checked permissions to the CSV path, but no issues with the permission. I am able to create databases on CSV and access them, but just could not use the CSV path to perform backups/restores.


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.

After some research, I found out couple of workarounds to perform backups or restores.

Alternative 1 – Use T-SQL for performing backup from SSMS Query Analyzer

Alternative 2 – Follow below mentioned steps

– Add a regular cluster disk(Non Clustered Shared Volume(CSV)) to SQL Server Group in the cluster from Cluster Administrator and then add the new disk resource as dependency to SQL Server Group.

– From SSMS, right click on the SQL Instance properties, go to database settings tab, copy the existing path and save it for future reference and then change the path to the new regular disk which we just added, this is just like how we used to be in SQL Server 2012 or previous versions.

– Now try to perform the backup and you will be able to open the locate backup file and locate database files dialog with out any errors. We can also specify the backup path manually to CSV, but the path still will not be visible from the GUI to browse.

– Later, the new regular storage which we added before could be removed and then change back the default backup location to CSV path which we saved before changing to to new disk in Instance properties -> Database Settings tab.

– You should not encounter any more issues and now you can also see CSV path from GUI to choose path to backup/restore.

There is a Connect bug opened for this issue where we cannot see CSV path using SSMS GUI while trying to perform backup/resstore. You may check on the connect page to see any updates on fixing this issue in coming Service Pack/Cumulative Updates.

Hope this was helpful.

SQLServerF1 Team


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


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


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.


Could Not Open SQL Server Errorlog File Errors in Event Viewer of Passive Node

You may sometimes notice errors like below on Passive cluster node.

initerrlog: Could not open error log file 'F:\MSSQL10_50.SQLInst\MSSQL\Log\ERRORLOG'. Operating system error = 3(failed to retrieve text for this error. Reason: 15100).

All SQL Server, IP Address, Network Name, Disk resources will be owned by the Active node, so if you try to start the SQL Services on passive node, it would result in this error.

At times, although no one tries to start the SQL Services on passive cluster node, still you may see this error in the Application Eventlog. This could be result of possibly some application is attempting to start the SQL Server service on passive node.

By running a process monitor(procmon) you should be able to see which process is trying to start the SQL instance on passive node. Some application which may do this are some Virus, Monitoring or Audit software. On one of the occasion we noticed SCCM application which has a service similar to “SMS_SITE_SQL_BACKUP_SCCMXXX” was trying to start the SQL instance on passive node.

Disabling this service on the passive node stopped these errors in our case.