TFS Mirroring failover Checklist

In a mirrored TFS environment, there are quite a few items to verify after the failover from the primary site to the mirrored site. Here is the checklist.

• Verify latest code. Before failover, make some changes. Verify the change after failover.
• Verify changesets. Before failover, make some changes to create a new changeset. Verify the changeset after failover. May need to refresh a couple of time to get the latest changeset displayed.
• Verify build definition and build history is also up-to-date      
• Check in new code. Verify TFS functionality.
• Verify build configuration from TFS admin console on the build machine. build service, build controller and build agent should all be up and running. May need to unregister the build service and create a new to connect to the new TFS server. For the build agent on the old primary TFS server to function, the TFS on the old server need to be started if not (%TFSInstallRoot%\tools\TFSServicecontrol.exe unquiesce). If want to use build controller on the new primary server, may need to configure build configuration from the new server and create a new controller and agent.
• Verify the status of build controller and build agent from client VS -> build. Status should be available. 
• Start new build using whichever build agent.   
• Verify TFS web access. http://newserver:8080/tfs. click on links to verify.
• Verify reports. http://newserver/reports. Generate at least one report to verify.

Configure SQL Server Mirroring Using Private Network

This article covers the steps to change the SQL Server database mirroring to use private network. Two additional NICs are installed on the principal and mirror TFS servers, connecting using cross-over network cable. The IP addresses are configured 10.10.10.1 and 10.10.10.2 respectively.

The list of databases to configure:
• ReportServer
• ReportServerTempDB
• STS_Content_TFS
• Tfs_Configuration
• Tfs_DefaultCollection
• Tfs_Warehouse

Step 1. Stop database alerts for both servers if any
SQL Server management studio -> SQL Server Agents -> Alerts -> disable all.

Step 2. Stop mirror job email alert for both servers if any
SQL Server management studio -> SQL Server Agents -> Jobs -> Database Mirroring Monitor Job -> Notification -> uncheck email.

Step 3. Backup the Database to be mirrored.
-- Backup the all databases on the Principal Server
use [master]
go

backup database [ReportServer]
to disk = 'F:\Backups\TFS1\ReportServer_mirror.bak';
go
backup log [ReportServer]
to disk = 'F:\Backups\TFS1\ReportServer_Log-mirror.trn';
go

backup database [ReportServerTempDB]
to disk='f:\backups\tfs1\ReportServerTempDB_mirror.bak';
go
backup log [ReportServerTempDB]
to disk='f:\backups\tfs1\ReportServerTempDB_Log-Mirror.trn';
go

backup database [STS_Content_TFS]
to disk='f:\backups\tfs1\STS_Content_TFS_mirror.bak';
go
backup log [STS_Content_TFS]
to disk='f:\backups\tfs1\STS_Content_TFS_Log-Mirror.trn';
go

backup database [Tfs_Configuration]
to disk='f:\backups\tfs1\Tfs_Configuration_mirror.bak';
go
backup log [Tfs_Configuration]
to disk='f:\backups\tfsfulldbbackups\tfs1\Tfs_Configuration_Log-Mirror.trn';
go

backup database [Tfs_DefaultCollection]
to disk='f:\backups\tfsfulldbbackups\tfs1\Tfs_DefaultCollection_mirror.bak';
go
backup log [Tfs_DefaultCollection]
to disk='f:\backups\tfsfulldbbackups\tfs1\Tfs_DefaultCollection_Log-Mirror.trn';
go

backup database [Tfs_Warehouse]
to disk='f:\backups\tfsfulldbbackups\tfs1\Tfs_Warehouse_mirror.bak';
go
backup log [Tfs_Warehouse]
to disk='f:\backups\tfsfulldbbackups\tfs1\Tfs_Warehouse_Log-Mirror.trn';
go

Step 4. Remove mirroring for every database from Principal server.
SQL Server Management Studio -> databases -> select a database -> properties -> mirroring -> remove mirroring

Step 5. Change endpoints on principal server.
-- alter Database Mirroring Endpoint on Principal Server
alter ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.1))
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4);
GO

GRANT CONNECT ON ENDPOINT::Mirroring TO [ad\MYSERVICEACCOUNT];
GO

Step 6. Change endpoints on mirror server.
-- alter Database Mirroring Endpoint on Mirror Server
alter ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.2))
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4);
GO

GRANT CONNECT ON ENDPOINT::Mirroring TO [ad\MYSERVICEACCOUNT];
GO

Step 7. Verify endpoints from both servers
-- Verify the Database Mirroring Endpoint Status
SELECT name, protocol_desc, state_desc FROM sys.database_mirroring_endpoints
GO

Step 8. Copy the back up files from TFS1 to TFS2, using cross-over interfaces

Step 9. Restore the all databases on the Mirrored instance using NORECOVERY option. make sure the TFS_Configuration is the first one to restore
-- Restoring the database ReportServer from the backup file
USE [master]
GO
alter database [TFS_Configuration] set partner off

RESTORE DATABASE [TFS_Configuration]
FROM DISK = 'F:\TFS1_Backups\TFS_Configuration_mirror.bak'
WITH REPLACE, NORECOVERY;
GO
RESTORE LOG [TFS_Configuration]
FROM DISK = 'F:\TFS1_Backups\TFS_Configuration_Log-mirror.trn' WITH NORECOVERY;
GO

USE [master]
GO
alter database [ReportServer] set partner off

RESTORE DATABASE [ReportServer]
FROM DISK = 'F:\TFS1_Backups\ReportServer_mirror.bak'
WITH REPLACE, NORECOVERY;
GO
RESTORE LOG [ReportServer]
FROM DISK = 'F:\TFS1_Backups\ReportServer_Log-mirror.trn' WITH NORECOVERY;
GO

USE [master]
GO
alter database [ReportServerTempDB] set partner off

RESTORE DATABASE [ReportServerTempDB]
FROM DISK = 'F:\TFS1_Backups\ReportServerTempDB_mirror.bak'
WITH REPLACE, NORECOVERY;
GO
RESTORE LOG [ReportServerTempDB]
FROM DISK = 'F:\TFS1_Backups\ReportServerTempDB_Log-mirror.trn' WITH NORECOVERY;
GO

USE [master]
GO
alter database [STS_Content_TFS] set partner off

RESTORE DATABASE [STS_Content_TFS]
FROM DISK = 'F:\TFS1_Backups\STS_Content_TFS_mirror.bak'
WITH REPLACE, NORECOVERY;
GO
RESTORE LOG [STS_Content_TFS]
FROM DISK = 'F:\TFS1_Backups\STS_Content_TFS_Log-mirror.trn' WITH NORECOVERY;
GO

USE [master]
GO
alter database [TFS_DefaultCollection] set partner off

RESTORE DATABASE [TFS_DefaultCollection]
FROM DISK = 'F:\TFS1_Backups\TFS_DefaultCollection_mirror.bak'
WITH REPLACE, NORECOVERY;
GO
RESTORE LOG [TFS_DefaultCollection]
FROM DISK = 'F:\TFS1_Backups\TFS_DefaultCollection_Log-mirror.trn' WITH NORECOVERY;
GO

USE [master]
GO
alter database [TFS_Warehouse] set partner off

RESTORE DATABASE [TFS_Warehouse]
FROM DISK = 'F:\TFS1_Backups\TFS_Warehouse_mirror.bak'
WITH REPLACE, NORECOVERY;
GO
RESTORE LOG [TFS_Warehouse]
FROM DISK = 'F:\TFS1_Backups\TFS_Warehouse_Log-mirror.trn' WITH NORECOVERY;
GO

Step 10. Setup the Mirroring sessions on mirror server.
-- Adding the database to Database Mirroring Session (Execute it on Mirror Server)

USE [master]
ALTER DATABASE [ReportServer]
SET PARTNER = 'TCP://10.10.10.1:5022';
GO

USE [master]
ALTER DATABASE [ReportServerTempDB]
SET PARTNER = 'TCP://10.10.10.1:5022';
GO

USE [master]
ALTER DATABASE [STS_Content_TFS]
SET PARTNER = 'TCP://10.10.10.1:5022';
GO

USE [master]
ALTER DATABASE [Tfs_Configuration]
SET PARTNER = 'TCP://10.10.10.1:5022';
GO

USE [master]
ALTER DATABASE [Tfs_DefaultCollection]
SET PARTNER = 'TCP://10.10.10.1:5022';
GO

USE [master]
ALTER DATABASE [Tfs_Warehouse]
SET PARTNER = 'TCP://10.10.10.1:5022';
GO

Step 11. Setup the Mirroring sessions on principal server.
-- Adding the database to Database Mirroring Session (Execute it on Principal Server)
USE [master]
GO
ALTER DATABASE ReportServer SET PARTNER OFF;
ALTER DATABASE ReportServerTempDB SET PARTNER OFF;
ALTER DATABASE STS_Content_TFS SET PARTNER OFF;
ALTER DATABASE Tfs_Configuration SET PARTNER OFF;
ALTER DATABASE Tfs_DefaultCollection SET PARTNER OFF;
ALTER DATABASE Tfs_Warehouse SET PARTNER OFF;

USE [master]
ALTER DATABASE [ReportServer]
SET PARTNER = 'TCP://10.10.10.2:5022';
GO

USE [master]
ALTER DATABASE [ReportServerTempDB]
SET PARTNER = 'TCP://10.10.10.2:5022';
GO

USE [master]
ALTER DATABASE [STS_Content_TFS]
SET PARTNER = 'TCP://10.10.10.2:5022';
GO

USE [master]
ALTER DATABASE [Tfs_Configuration]
SET PARTNER = 'TCP://10.10.10.2:5022';
GO

USE [master]
ALTER DATABASE [Tfs_DefaultCollection]
SET PARTNER = 'TCP://10.10.10.2:5022';
GO

USE [master]
ALTER DATABASE [Tfs_Warehouse]
SET PARTNER = 'TCP://10.10.10.2:5022';
GO

Step 12. On the Database Mirroring Properties change the Operation Mode to High Performance (asynchronous) for every database.
SQL Server Management Studio -> databases -> select a database -> properties -> mirroring -> Operation mode -> change to High Performance (asynchronous)

Step 13. Verify mirroring setting from principal server
SQL Server Management Studio -> databases -> select a database -> properties -> mirroring

Step 14. Verify mirroring by checking database status from both servers
SQL Server Management Studio -> databases

Step 15. Enable database alerts for both servers
SQL Server management studio -> SQL Server Agents -> Alerts -> enable all.

Step 16. Start mirror job email alert for both servers
SQL Server management studio -> SQL Server Agents -> Jobs -> Database Mirroring Monitor Job -> Notification -> check email.

Change Shelveset owner in TFS 2010

Inside TFS database schema, shelvesets are saved in tbl_workspace table with type=1.
use Tfs_defaultcollection;
select * from tbl_Workspace where type = 1 ;

One of the ways to get ownerID is from a developer's workspace name.
use Tfs_defaultcollection;
select * from tbl_Workspace where WorkspaceName='MYWORKSPACENAME';

To change the owner of a shelveset, run the following command:
use Tfs_defaultcollection;
update tbl_Workspace set ownerid=5 where type = 1 and WorkspaceName='shelvetest_3';

This may be useful after a develop leaves the team and his Active Directory ID is revoked.

Note: It is highly recommended not to modify TFS database directly.