How To: Failover a Mirrored Data Tier when the Principle Data-Tier is Not Available

Recently I've been working on SQL mirroring with TFS and concentrating on worst case scenarios, when the principle data tier goes down.  The whole point of mirrors with TFS, as I understand it, is high availability.  With regards to setting up mirroring for your data tier I the documentation is quite clear (https://msdn.microsoft.com/en-us/library/aa980629.aspx) however the articles for fail-over are not.

The official documentation (https://msdn.microsoft.com/en-us/library/aa980528.aspx) for failing over when the principle data tier isn't available, think worst case and server down, is not great.  When I tried to follow this through I had no luck, things did not go well.  To that end I came up with my own solution, it was a lot quicker than following the official documentation and it works.  In server down scenarios time is of the essence.

The following steps are a guide only, please don't see this is a stone set guide and certainly don't rely on this for production.  I would say use this as a base, test it thoroughly in staging and once you're comfortable do as you wish.  I've also only tested this with TFS 2008 and SQL Server 2005 SP2, as most of the work is in SQL I believe this guide will hold true for TFS 2005 but some steps will vary a little.

You will need to set up an alias on the mirror so that connections referencing the principle will not be rejected.  To do this follow these steps;

Connect to the mirror and open “SQL Server Configuration Manager”, expand the “SQL Native Client Configuration” and right click on “Aliases”. Click on “New Alias” and enter the following information;

 

Alias Name: <hostname of the principle data tier>

Port Number: 1433 <may be different on your setup however this is default>

Protocol: TCP/IP

Server: <hostname of mirror>

 

Then click on OK. 

 

To failover follow these steps;

1.       Connect to the mirror and run the following script, if your WSS databases are named differently you may need to change those entries;

ALTER DATABASE ReportServer SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE ReportServerTempDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE TfsActivityLogging SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE TfsBuild SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE TfsIntegration SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE TfsVersionControl SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE TfsWarehouse SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE TfsWorkItemTracking SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE TfsWorkItemTrackingAttachments SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE WSS_AdminContent SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE WSS_Config SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

ALTER DATABASE WSS_Content SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Please note, if one of these entries fail then you will need to remove those that succeeded before the failure.

 

2.       Open the hosts file on the application tier

a.       C:\Windows\System32\Drivers\Etc\Hosts (no file extension)

b.      Add a reference forwarding the hostname of the principle to the IP address of the mirror

c.       Save the changes

3.       Open a command prompt and run “ipconfig /flushdns” and “iisreset”

4.       Run the following command on the application tier;
setupwarehouse –o –s MirrorDT –d TfsWarehouse –c warehouseschema.xml –ra TFSReportAccount –a TfsServiceAccount –mturl https://at:8080

5.       Once complete, connect to Analysis Services on the mirror using SQL Server Management Studio, browse to TfsWarehouse, right click then “Process”. This may take some time to complete as it is rebuilding the TfsWarehouse from historic data, wait for the processing to complete

6.       Open “Reporting Services Configuration Manager”

a.       Click on “Database Setup”

b.      In server name, enter the name of the mirror then click apply then connect

7.       Open the report manager website (https://apptier/reports) and change both the connection strings such that they point to the mirror rather than the principle data tier

Done, from here you should be back up and running.  The benefit of the above is that once you get your principle DT back up and running it is a little easier to switch back.  I haven't test this fully yet though and will update this once I have.

Comments welcomed!

Regards,
Jason