Availability Group Readable database disconnections

I've set up a 3 server database Availabilty Group scenario using SQL Server 2012 CTP3 to allow me to prepare some implementation tests:

• L02-DB-01 (Primary)
• L02-DB-02 (Synchronous Secondary)
• L02-DB-03 (Asynchronous Readable Secondary)

One of the tests I prepared was a query to continously query the number of rows of data in a table on the L02-DB-03 and print the result with output that looks like this:

2011-11-16 15:34:36.353: Occurence 1 has count =6
2011-11-16 15:34:36.353: Occurence 2 has count =6
2011-11-16 15:34:36.353: Occurence 3 has count =6

If it gets disconnected it is scripted to automatically reconnect. I planned to use this to show how the read only connection for reporting stays up during the failover from the primary to the synchronous secondary node replica. And if it does get disconnected it then shows how long it takes before this can occur.

It turns out that connections to the asynch readable secondary get disconnected during a failover from one replica to another - at least in CTP3. The disconnection message is:

2011-11-16 15:34:47.877: Occurence 179344 has count =6
TCP Provider: An existing connection was forcibly closed by the remote host.

Communication link failure

If you try to reconnect, using sqlcmd, it generates an error message:

Msg 976, Level 14, State 1, Server L02-DB-03, Line 12
The target database, 'AvGroupDB1', is participating in an availability group and is currently not accessible for queries. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

It's not what I was expecting but I guess it's reasonable given that the state of the database is a little vague during that time. If using this database to provide view access to the data it would be a good idea to check for disconnects and also for this error message when trying to connect / re-connect.

In my test it was not possible to re-connect to the database for approx. 16 secs although I guess this will depend on the servers and load on them.

The relevant section of the generated log is:

>sqlcmd -S l02-db-03 -E -i Read_Async.sql
Changed database context to 'tempdb'.
2011-11-16 17:00:55.307: Occurence 1 has count =6
2011-11-16 17:00:55.310: Occurence 2 has count =6
2011-11-16 17:00:55.310: Occurence 3 has count =6
2011-11-16 17:00:55.310: Occurence 4 has count =6
2011-11-16 17:00:55.310: Occurence 5 has count =6
2011-11-16 17:00:55.310: Occurence 6 has count =6

2011-11-16 17:01:04.273: Occurence 139693 has count =6
2011-11-16 17:01:04.273: Occurence 139694 has count =6
2011-11-16 17:01:04.273: Occurence 139695 has count =6
Msg 976, Level 14, State 1, Server L02-DB-03, Line 17
The target database, 'AvGroupDB1', is participating in an availability group and is currently not accessible for queries. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

>goto start

... 200 occurences of the msg 976 error removed to shorten log ...

>sqlcmd -S l02-db-03 -E -i Read_Async.sql
Changed database context to 'tempdb'.
Msg 976, Level 14, State 1, Server L02-DB-03, Line 12
The target database, 'AvGroupDB1', is participating in an availability group and is currently not accessible for queries. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

>goto start

>sqlcmd -S l02-db-03 -E -i Read_Async.sql
Changed database context to 'tempdb'.
2011-11-16 17:01:20.173: Occurence 1 has count =6
2011-11-16 17:01:20.173: Occurence 2 has count =6
2011-11-16 17:01:20.173: Occurence 3 has count =6
2011-11-16 17:01:20.173: Occurence 4 has count =6
2011-11-16 17:01:20.173: Occurence 5 has count =6

Is the vSphere Host Profiles feature broken?

I'm working with some hosts that are able to take advantage of the Host Profiles feature and have run into several issues. Apart from the fact that Host Profiles (at least in this version) don't support the iSCSI storage configuration information they don't seem to work very reliably - in my experience.

I set up one system with four standard vSwitches (yes I know Distributed ones are better):Screen capture showing the four vSwitches overview

•     Management
•     ipStorage
•     vMotion
•     Fault Tolerance

At this point I created a new Host Profile using this system as the basis and verified that it contains these details and that it is set to ask for the IP addresses.

I verified that my second host has the appropriate number of network adapters installed and applied the Host Profile. It fails at 15% through the process and at the same time loses connectivity to the host - The only way I've found so far to resolve it is to reset the host using the management console, re-enter the management IP address and then re-connect it to my vCenter.

I suspect that the application of the Host Profile is making a mess of the management interface but so far I haven't been able to identify what it is breaking - nor why...I'm still investigating.

I tested this on a further two hosts and they do the same thing. Seems odd that a profile created from a working host (that is compliant with the profile) cannot be successfully applied to a different host.

All of the hosts are running vSphere Hypervisor (ESXi) 4.1 Build 381591.

Update on Wednesday, August 10, 2011 at 3:26PM by Craig

I've just tried this again using two VM Hosts with the latest 4.1.0 Build 433742 and it is still the same.

I logged on directly to the hosts and all of the vSwitches and NICs appear to be correct - they just can't connect to anything.

I'm continuing to investigate and will also be interested to see what happens when I upgrade to version 5.

Useful Firefox Add-ons

I'm a big fan of Firefox and my favourite add-ons are (the links take you to Firefox Add-ons pages for relevant Extension):

• Adblock Plus
• ColorfulTabs
• CopyAllURLS (not currently supported by 4.x)
• Forecastfox Weather
• HTTPS-Everywhere
• IE Tab 2
• NoScript
• PDF Download
• Xmarks

Configure Citrix XenApp XML Service to use port other than 80

All of the documentation I read describing the process to change the Citrix XenApp XML Service from port 80 to something else only mentioned 3 places that need to be changed. When working with my servers I discovered that there are in fact 4:

Citrix Presentation Server

• Change the service using the ctxxmlss /Rxxx command to change the port.

Web Interface

• Change the port used in the Server Farm.

Secure Gateway

• You need to change the Secure access portion of the Web Interface.
• You also need to change the Secure Gatway configuration.

Shutdown XenServer when UPS detects power outage

This question has recently come up and I found the following useful links:

• APC UPS Management on XenServer
• Xenserver Ups Shutdown