Disk resource could not be moved error when installing SQL Server 2012 failover cluster

SQL Server 2012 failover cluster installation fails with the following error:

"The resource 'Disk Z' could not be moved from cluster group 'Available Storage' to cluster group 'SQL Server (MSSQLSERVER)'. Error: There was a failure to call cluster code from a provider. Exception message: Generic failure . Status code: 183. Description: Cannot create a file when that file already exists."

This comes up when you are installing the SQL instance onto mount points under a root mount point. The cluster install has trouble adding these disk resources to the SQL Server cluster group and setting its dependencies.

To fix:

1. The failed installation may have partially installed the SQL instance. If that is the case, uninstall the instance first. Do this by using the "Remove node from a SQL Server failover cluster" option.

2. In the Failover Cluster Manager, right click on Roles and select Create Empty Role:

3. Rename the role to your desired instance name, eg. "SQL Server (MSSQLSERVER)"

4. Right click on the role and click Add Storage

5. Select the mount points to be used for this instance, including the root mount point, and add them to this role.

Now when you re-run the cluster installation, it should complete successfully.

Setup.rll error when installing SQL Server 2012

Error when trying to run setup.exe for SQL Server 2012:

"C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\resources\1033\setup.rll is either not designed to run on Windows or it contains an error. Try installing the program again using the original installation media or contact your system administrator or the software vendor for support."

To fix, copy the following:

%InstallMedia%\resources\1033\setup.rll

to:

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\Resources\1033

.NET Unhandled Exception error when running installing SQL Server 2012

Error when launching setup.exe for SQL Server 2012:

"Unhandled exception has occurred in your application.  If you click Continue, the application will ignore this error and attempt to continue.  If you click Quit, the application will close immediately."

This was resolved by deleting the following folder:

C:\Users\%username%\appdata\local\Microsoft_Corporation

T-SQL Enhancements in SQL Server 2012

There are a number of new T-SQL enhancements added in SQL Server 2012.  The video below covers the following new objects, functions, and options:

SEQUENCE object

Logical Functions:
CHOOSE
IIF

Date and Time Functions:
DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS
EOMONTH

OFFSET and FETCH

Window Functions:
FIRST_VALUE
LAST_VALUE
LAG
LEAD
CUME_DIST
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC

Below are SQL statements used in the video:

---

/*********************************************
 SEQUENCE Object
*********************************************/

CREATE SEQUENCE MySeq1
 START WITH 1
 INCREMENT BY 1

SELECT * FROM sys.sequences

SELECT NEXT VALUE FOR MySeq1

ALTER SEQUENCE MySeq1
 RESTART WITH 2
 INCREMENT BY 2
 MINVALUE 2
 MAXVALUE 10
 CYCLE

CREATE TABLE TestTable1
(
 SequenceNo int,
 ItemName varchar(50)
)

INSERT INTO TestTable1 VALUES (NEXT VALUE FOR MySeq1, 'Item1')

SELECT * FROM TestTable1

DROP TABLE TestTable1
DROP SEQUENCE MySeq1


/*********************************************
 Logical Functions: CHOOSE and IIF
*********************************************/

SELECT CHOOSE(2, 'Peter', 'Paul', 'Mary')
SELECT IIF(1=2, 'Apples', 'Oranges')


/*********************************************
 Date and Time Functions
*********************************************/

/*
DATEFROMPARTS (year, month, day)
DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
EOMONTH (start_date [, month_to_add])
*/

SELECT DATEFROMPARTS (2012, 8, 4)
SELECT DATETIMEFROMPARTS (2012, 2, 20, 14, 30, 5, 10)

DECLARE @start_date datetime
SET @start_date = '8/22/12'
SELECT EOMONTH (@start_date)
SELECT EOMONTH (@start_date, 2)
SELECT EOMONTH (@start_date, -1)


/*********************************************
 OFFSET and FETCH
*********************************************/

SELECT * FROM Production.Product ORDER BY ProductID

SELECT * FROM Production.Product ORDER BY ProductID OFFSET 10 ROWS
FETCH NEXT 25 ROWS ONLY


/*********************************************
 Window Functions
*********************************************/

/*
FIRST_VALUE - Returns the first value in an ordered set of values
LAST_VALUE - Returns the last value in an ordered set of values
LAG - Returns the value of the previous row in an ordered set
LEAD - Returns the value of the next row in an ordered set
CUME_DIST - Calculates the cumulative distribution of a value in a group of values
PERCENT_RANK - Gives the rank of a row relative to all the rows in the partition
PERCENTILE_CONT - Calculates a percentile based on a continuous distribution of the column value
PERCENTILE_DISC - Returns the smallest CUME_DIST value that is greater than or equal to a specified percentile
*/

--DROP TABLE TestTable2
CREATE TABLE TestTable2
(
 SaleDate datetime,
 CustomerID int,
 SalePrice float
)

INSERT INTO TestTable2 VALUES
 ('2012-01-03', 395, 81),
 ('2012-01-08', 550, 62),
 ('2012-01-22', 244, 130),
 ('2012-02-11', 395, 17),
 ('2012-02-14', 100, 45),
 ('2012-03-17', 484, 20),
 ('2012-03-31', 100, 33),
 ('2012-04-02', 244, 59),
 ('2012-04-26', 395, 192),
 ('2012-05-12', 100, 20),
 ('2012-05-18', 395, 114)

SELECT * FROM TestTable2

-- FIRST_VALUE, LAST_VALUE, LAG, LEAD
SELECT
 SaleDate,
 CustomerID,
 SalePrice,
 DateOfLowestSale = FIRST_VALUE(SaleDate) OVER (PARTITION BY CustomerID ORDER BY SalePrice),
 DateOfHighestSale = LAST_VALUE(SaleDate) OVER (PARTITION BY CustomerID ORDER BY SalePrice
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
 PreviousSaleDate = LAG(SaleDate, 1) OVER (PARTITION BY CustomerID ORDER BY SaleDate),
 NextSaleDate = LEAD(SaleDate, 1) OVER (PARTITION BY CustomerID ORDER BY SaleDate)
FROM
 TestTable2
ORDER BY
 SaleDate

-- CUME_DIST, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC
SELECT
 SaleDate,
 CustomerID,
 SalePrice,
 CumeDist = CUME_DIST() OVER (PARTITION BY CustomerID ORDER BY SalePrice),
 PctRank = PERCENT_RANK() OVER (PARTITION BY CustomerID ORDER BY SalePrice),
 PctCont = PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SalePrice)
  OVER (PARTITION BY CustomerID),
 PctDisc = PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SalePrice)
  OVER (PARTITION BY CustomerID)
FROM
 TestTable2
ORDER BY
 SaleDate

---

Error when failing over Availability Group with Listener

The following error shows up in the System event log on the secondary server when trying to fail over an availability group that has a listener configured:

"Cluster network name resource 'resource_name' cannot be brought online. The computer object associated with the resource could not be updated in domain '' for the following reason:

Unable to set DnsHostName attribute.

The text for the associated error code is: A constraint violation occurred.

The cluster identity 'cluster_object' may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain."

This was due to inadequate permissions for the computer account of the Windows cluster.  The cluster computer object needs the "Read All Properties" and "Create Computer Objects" permission in the domain.  It also needs Full Control to the computer object for the cluster itself.  Once those permissions were granted, the availability group failed over successfully.

Here is the Microsoft article that details the permissions needed.

Create failed for Availability Group Listener

Error when setting up a listener for an availability group for SQL Server AlwaysOn:

"Create failed for Availability Group Listener 'listener_name'.

An exception occurred while executing a Transact-SQL statement or batch.

The WSFC cluster could not bring the Network Name resource with DNS name 'dns_name' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible.  Use a different DNS name to resolve the name conflict, or check the WSFC cluster log for more information.
The attempt to create the netowrk name and IP address for the listener failed.  The WSFC service may not be running or may be inaccessbile in its current state, or the values provided for the network name and IP address may be incorrect.  Check the state of the WSFC cluster and validate the network name and IP address with the network administrator."

This was due to inadequate permissions for the computer account of the Windows cluster.  The cluster computer object needs the "Read All Properties" and "Create Computer Objects" permission in the domain. Once those permissions were granted, the listener was created successfully.

Here is the Microsoft article that details the permissions needed.

Error Installing SQL Server Cluster - The cluster resource could not be brought online

Error when creating a SQL Server cluster:

"The following error has occurred:
The cluster resource 'SQL Server (InstanceName)' could not be brought online. Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736)"

The following error shows up in the System event log:


"Cluster network name resource 'resource_name' failed to create its associated computer object in domain 'ad.weil.com' for the following reason: Unable to create computer account.

The text for the associated error code is: Access is denied.

Please work with your domain administrator to ensure that:
- The cluster identity 'computer_object' can create computer objects. By default all computer objects are created in the 'Computers' container; consult the domain administrator if this location has been changed.
- The quota for computer objects has not been reached.
- If there is an existing computer object, verify the Cluster Identity 'computer_object' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool."

This was due to inadequate permissions for the computer account of the Windows cluster.  The cluster computer object needs the "Read All Properties" and "Create Computer Objects" permission in the domain. Once those permissions were granted, the install completed successfully.

Here is the Microsoft article that details the permissions needed.

Quorum Error When Creating an AlwaysOn Availibility Group

I ran into the following error today when trying to create a new availability group for AlwaysOn.  This was on a fresh install of SQL 2012.

"The local node is not part of quorum and is therefore unable to process this operation. This may be due to one of the following reasons:

• The local node is not able to communicate with the WSFC cluster.

• No quorum set across the WSFC cluster.

For more information on recovering from quorum loss, refer to SQL Server Books Online."

To fix this issue, make sure you have the prerequisite hotfixes applied:
Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)

In particular, this specific error requires hotfix 2494036.

If the error still comes up, disable and re-enable AlwaysOn in SQL Server Configuration Manager, then restart the SQL instance.

Using XQuery to Query XML Data

T-SQL implements a subset of the XQuery language to query the XML data type.

The following video contains a tutorial on how to use XQuery to query XML data in SQL Server:

Below are SQL statements used in the video:

---

DECLARE @x xml
SET @x = '
 <Orders>
  <Order OrderID="100" OrderDate="1/30/2012">
   <OrderDetail ProductID="1" Quantity="3">
    <Price>350</Price>
   </OrderDetail>
   <OrderDetail ProductID="2" Quantity="8">
    <Price>500</Price>
   </OrderDetail>
   <OrderDetail ProductID="3" Quantity="10">
    <Price>700</Price>
   </OrderDetail>
  </Order>
  <Order OrderID="200" OrderDate="2/15/2012">
   <OrderDetail ProductID="4" Quantity="5">
    <Price>120</Price>
   </OrderDetail>
  </Order>
 </Orders>'

SELECT @x.query('/Orders')
SELECT @x.query('/Orders/Order/OrderDetail')
SELECT @x.query('/Orders/Order[1]/OrderDetail[2]')
SELECT @x.query('/Orders/Order/OrderDetail[@ProductID="2"]')
SELECT @x.query('/Orders/Order[@OrderID="100"]')
SELECT @x.query('/Orders/Order[@OrderID="100"]/OrderDetail[Price>600]')
SELECT @x.value('/Orders[1]/Order[@OrderID="100"][1]/OrderDetail[Price>600][1]', 'float')
SELECT @x.value('/Orders[1]/Order[@OrderID="200"][1]/@OrderDate', 'datetime')

---

How to Identify Your SQL Server Version and Edition

There are several ways to determine the version of SQL Server that is installed.  This article will go over a few of the most common methods to find the current SQL Server version number and the corresponding service pack (SP) level.

Method 1:
Applicable on all versions of SQL Server

Run the following command in Management Studio:

SELECT @@VERSION

Sample results:

Method 2:
Applicable on versions 2000 and up

Run the following command in Management Studio:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Sample results:

Method 3: 
Applicable on versions 2005 and up

Run the following command in Management Studio:

EXEC xp_msver

Sample results:

Click here for a complete list of SQL Server versions and build numbers.

Reporting Services error: "Service Unavailable"

While trying to access the Report Manager for Reporting Services 2005 today, I encountered the following error in Internet Explorer: "Service Unavailable".  Looking through IIS, I noticed the ReportServer application pool was stopped.  It would start back up initially, but stop immediately whenever someone tried to access the Report Manager.  In the Application event log, the following two errors appeared:

"Could not load all ISAPI filters for site/service.  Therefore startup aborted."

"ISAPI Filter 'C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\\aspnet_filter.dll' could not be loaded due to a configuration problem. The current configuration only supports loading images built for a AMD64 processor architecture. The data field contains the error number. To learn more about this issue, including how to troubleshooting this kind of processor architecture mismatch error, see http://go.microsoft.com/fwlink/?LinkId=29349."

Reporting Services 2005 uses .NET framework v2.0.  The fix for issue was to run the following to reinstall v2.0 and update its scriptmaps:

C:\Windows\Microsoft.NET\Framework64\v2.0.50727> aspnet_regiis.exe –i 

After doing this, the ReportServer application pool stayed up and we were able to access the Report Manager again.

How to Load XML Documents Into SQL Server

You can load documents into SQL Server using the OPENROWSET option.  This will load the XML file into one large rowset, into a single row and a single column.  This rowset can then be queried using the OPENXML function.  The OPENXML function allows an XML document to be treated like a table.

The following video contains a tutorial on how to load and read an XML document using OPENROWSET and OPENXML.

Below are SQL statements used in the video:

---

DECLARE @x xml

SELECT @x = P
FROM OPENROWSET (BULK 'C:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

--SELECT @x

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT *
--INTO #tmp_MySubcategories
FROM OPENXML (@hdoc, '/Subcategories/Subcategory', 1)
WITH (ProductSubcategoryID int, Name varchar(100))

SELECT *
--INTO #tmp_MyProducts
FROM OPENXML (@hdoc, '/Subcategories/Subcategory/Products/Product', 2)
WITH (ProductID int, Name varchar(100), ProductNumber varchar(50), ListPrice float, ModifiedDate datetime)

SELECT *
FROM OPENXML (@hdoc, '/Subcategories/Subcategory/Products/Product', 2)
WITH (
ProductSubcategoryID int '../../@ProductSubcategoryID', 
ProductSubcategoryName varchar(100) '../../@Name', 
ProductID int, 
Name varchar(100), 
ProductNumber varchar(50), 
ListPrice float, 
ModifiedDate datetime)

EXEC sp_xml_removedocument @hdoc

SELECT * FROM #tmp_MySubcategories
SELECT * FROM #tmp_MyProducts

DROP TABLE #tmp_MySubcategories
DROP TABLE #tmp_MyProducts

---

Generating XML From SQL Server Tables

SQL Server has the capability to generate XML documents from its tables.  This is done by using the FOR XML clause along with the SELECT statement.  The FOR XML clause offers 4 different modes:

• RAW
• AUTO
• PATH
• EXPLICIT

The following video contains a tutorial on how to use FOR XML with the PATH option, which is the most widely used option.

Below are SQL statements used in the video:

---

SELECT *
FROM Production.Product
FOR XML AUTO

SELECT
ProductID,
Name,
ProductNumber,
ListPrice,
ModifiedDate
FROM Production.Product
FOR XML PATH('Product'), ROOT('Products')

SELECT
ProductID AS [@ProductID],
Name AS [ProductInfo/@Name],
ProductNumber AS [ProductInfo/ProductNumber],
ListPrice AS [ProductInfo/ListPrice],
ModifiedDate AS [ModifiedDate]
FROM Production.Product
FOR XML PATH('Product'), ROOT('Products')

SELECT * FROM Production.Product
SELECT * FROM Production.ProductSubcategory

SELECT
PSC.ProductSubcategoryID AS [@ProductSubcategoryID],
PSC.Name AS [@Name],
(SELECT
ProductID,
Name,
ProductNumber,
ListPrice,
ModifiedDate
FROM Production.Product P
WHERE P.ProductSubcategoryID = PSC.ProductSubcategoryID
FOR XML PATH('Product'), ROOT('Products'), TYPE)
FROM Production.ProductSubcategory PSC
FOR XML PATH ('Subcategory'), ROOT ('Subcategories')

DECLARE @x xml
SET @x =
(SELECT
ProductID AS [@ProductID],
Name AS [ProductInfo/@Name],
ProductNumber AS [ProductInfo/ProductNumber],
ListPrice AS [ProductInfo/ListPrice],
ModifiedDate AS [ModifiedDate]
FROM Production.Product
FOR XML PATH('Product'), ROOT('Products'), TYPE)

SELECT @x

---

How to Reverse Log Shipping Roles

There are times when you may need to reverse the roles of a primary and standby server.  This is common when you need to patch the primary server and still need to allow users access to the data.  When using database mirroring or SQL clustering as your SQL Server high availability option, reserving the roles of the primary and standby servers is quite easy.  With log shipping, however, it is not as straight forward.

You can always bring the standby database online, create a full backup of it, and restore onto the (previously) primary database to initialize it for log shipping.  However, this approach can be cumbersome especially if the database is large. The following steps will allow you to reverse log shipping roles without the need to initialize the (new standby) database.

1. Disable the log shipping backup job on the primary server.
2. On the standby server, run the log shipping copy and restore jobs to restore any remaining transaction log backups.
3. Disable the log shipping copy and restore jobs on the secondary server.
4. On the primary server, create on last transaction log backup using the NORECOVERY option.
5. On the standby server, restore this transaction log backup using the RECOVERY option.
6. On the standby server (which will now be the primary server), right click on the database and select Properties -> Transaction Log Shipping.  Enable the database to become the primary database and configure the backup and secondary server settings.

The following video demonstrates how to reverse log shipping roles.

Memory Utilization for Multiple Instances of SQL Server

I was recently asked if having multiple instances running on the same machine can cause performance issues. Resource contention can become an issue, though it depends on the utilization of the DBs and the amount of resources on the box.  SQL Server does not allocate memory evenly across its instances.  Each instance will consume whatever memory it needs (even if it's all the memory on the machine) and is stingy when it comes to freeing it back up for other processes to use.

It is usually a good idea to set the max server memory and min server memory for each instance to control memory usage, especially if multiple instances are installed.  Setting the max server memory will ensure that an instance will not take up all the memory on the box.  The min server memory option guarentees that the specified amount of memory will be available for the SQL instance.  Once this min memory usage is allocated, it cannot be freed up until the min server memory setting is reduced.  The following example will set the max server memory to 4 GB.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory', 4096
GO
RECONFIGURE
GO

These memory settings will take affect without having to restart the instances.

How to Address TempDB Space Issue

You may have come across situations where the TempDB has grown very large, sometimes even completely running out of space.  When this happens, you need to shrink the TempDB.

DBCC SHRINKFILE ('tempdev', 1024)

A couple of problems sometimes comes up after doing this:
1. The TempDB shrinks successfully.  However, there is still a process running out there that will fill up TempDB again.
2. The TempDB does not shrink.

For the 1st issue, use the following query to check how much space is being used in TempDB.


SELECT
[TotalSizeOfTempDB (MB)] = 
SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count
+ unallocated_extent_page_count) * (8.0/1024),
[UsedSpace (MB)] = 
SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count) * (8.0/1024),
[FreeSpace (MB)] = 
SUM(unallocated_extent_page_count * (8.0/1024))
FROM sys.dm_db_file_space_usage
WHERE database_id = 2


Most likely, you will see the a large amount of used space, or the used space continue to grow.  To find the culpable transaction, run the following command, which will give you the SPID for the oldest open transaction.

DBCC OPENTRAN('tempdb')

With this SPID, you can now run the following to find the SQL command being executed as well as the user and machine executing it.

DBCC INPUTBUFFER (<SPID>)
SP_WHO2 <SPID>

From here, it should be easy to track down the person/process responsible and take the appropriate action to rectify.  It may be to stop a job on the front end application, re-write the SQL command to be more optimal, or even just kill the SPID.


For the 2nd issue, you may be unable to shrink TempDB.  The reason TempDB cannot be shrunk may be because there are uncommitted transactions or the proc/system cache needs to be cleared.  Re-starting the SQL Server instance will shrink the TempDB.  However, this is not always an option, especially in a production environment.  To get around having to restart the SQL instance, run the following to shrink TempDB.

USE tempdb
GO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE ('tempdev', 1024)
GO