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.
