Monday, August 13, 2012

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.

Wednesday, February 22, 2012

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.

Thursday, January 5, 2012

Revoke Read Access to ClearCase VOB

Say a VOB (VOB tag: VOB1) only allows a group of developers (group id: GRPa) to have read-write access. All other users do not have any access.

1. Make sure that GRPa is either listed as ownership group or additional group. This privilege defines who can modify the code.

> cleartool desc -l vob:VOB1
...
VOB ownership:
owner AD\ccadmin
group AD\clearcase
Additional groups:
group AD\GRPa
...

To add GRPa to the addiontinal group, run command
> clearcase protectvob -add_group GRPa

2. Make sure that the VOB root directory has 770 and owned by GRPa group. This privilege defines who can see the code under VOB root.

> cleartool desc -l VOB1
...
Element Protection:
User : ccadmin : rwx
Group: GRPa : rwx
Other: : ---
...

to change the protection of the VOB root directory, run command

> cleartool protect -chmod 770 VOB1


If another group (group id: GRPb) needs only read access to VOB1, then a third group (id GRPc) needs to be created to include both GRPa and GRPb. The group GRPc need to be the group of VOB root element. To change it, run command

> cleartool protect -chgrp GRPc VOB1

How to run cleartool mkattr from ant script

Managing escape charactor is never a simple task, especially when it involves xml, cleartool, ant exec, and trying to publish a piece of html code on a blog. Here is an example of how to set an attribute to a ClearCase baseline.

<!-- set baseline with the new build label-->
<exec dir="${clearcase.bin}" executable="cleartool" failonerror="true" >
   <arg line="mkattr"/>
   <arg line="BuildNO"/>
   <arg line="&apos;\&quot;${build.label}\&quot;&apos;"/>
   <arg line="${cc.baseline.new}"/>
</exec>