While working on a customer implementation of Microsoft SQL Server 2008 R2 and Citrix DataStream I ran into a need to quickly failover mirror partners between servers for testing reasons. This can be done easily via the ALTER DATABASE command but to save time I wrapped the command up into a dynamic Stored Procedure.

mirroring_role being equal to 1 indicates it has the PRINCIPAL role where the mirroring_state being equal to 4 indicates it is SYNCHRONIZED.

The procedure should be run on the server where the database is the PRINCIPAL. Running it on the MIRROR server will not result in any changes. Using Linked Servers and 2 parameters in the Stored Procedure such as:

sps_FailoverMirror (@databaseName nvarchar(200), @serverOne nvarchar(200), @serverTwo nvarchar(200))

would allow the Stored Procedure to query both servers, find the PRINCIPAL server, and perform the failover.

Recently, while deploying a 50 user pilot of XenDesktop 5 and PVS 5.6 we ran into an issue wherein the install was failing with the following error:

Exception of type System.Data.SqlClient.SqlException caught, error=MODIFY FILE failed. Specified size is less than or equal to current size.

The cause of this error is the PVS database being larger than 20 MB at the time PVS is installed. In this case it was caused by the MODEL database being configured with a fixed size of 500 MB on the target SQL 2008 R2 Server.

Near the end of the install PVS attempts to “increase” the size of the database to 20 MB. I assume this is because it is preparing to insert significant amounts of data (around 20 MB) and proactively increases the database size to avoid the overhead and continually growing the data file. Of course, if they database size is already greater than 20 MB as it was in this case this operation will fail as it is actually attempting to shrink the size of the database.

Our solution was to temporarily reduce the size of the MODEL database while the PVS install was completed. Other workarounds are detailed here in this KB Article from Citrix.

It would be a better model for the installer to first check the size of the database and then increase it only if it is not already 20 MB.

I recently ran into a problem with a client’s SQL 2005 Standard server where the Express Edition of SSMS (Management Studio) had been installed. The Express Edition had to be completely removed and the Standard version of SSMS was installed.

However, I kept seeing the following error when I tried to open my Maintenance Plans…

Method not found: ‘Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()’. (Microsoft.SqlServer.MaintenancePlanTasksUI)

The problem was that SSMS was at a different patch level after I re-installed it. I installed SP4, which was the patch level of the Database Engine, and everything worked!

Please note: The first time you open the Maintenance Plan after patching you will see the following error, which can be ignored…

If you have never created a SQL Cluster before, then it can be a confusing and daunting task. Here are a couple resources to help you along…

Basic tasks:

1. Create shared LUNs and provide them to the servers in your cluster.  Make sure you can see the disks on your servers.

2. Add the Windows 2008 Failover Clustering Feature.  Setup your cluster on one node first and get it functional.

3. Run the SQL 2008 Setup to create a Failover Cluster

4. Add the Failover Clustering Feature to the other servers in the cluster and add them as nodes.

5. Run the SQL 2008 Setup to add the additional nodes in the cluster

Useful Links:

Microsoft SQL 2008 Clustering Guide
MSSQLTips.com Guide to SQL Clustering – Highly recommended!

If you are setting up a new SQL Cluster or any application/service that requires the DTC cluster service, and you get the following error…

“object already exists in Active Directory”

…then you either need to choose a new name for the DTC cluster service, or you can give the cluster computer name object in AD Full Control over the DTC cluster computer object.

For example, if you have the following setup…

Cluster01 – Node
Cluster02 – Node
Cluster00 – Cluster Name
Cluster00DTC – DTC Cluster Name

Find the Cluster00DTC computer object in AD and view it’s properties. Go to the ‘Security’ tab and add the ‘Cluster00′ computer object and give it Full Control. Run the DTC Cluster configuration again and it should work now.