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.

4 comments:

Unknown said...

true religion jeans, hollister pas cher, vans pas cher, tn pas cher, michael kors, true religion outlet, sac guess, nike free run uk, michael kors outlet, oakley pas cher, true religion jeans, replica handbags, ugg boots, burberry outlet online, mulberry, converse pas cher, michael kors outlet, ugg boots, true religion jeans, michael kors outlet, michael kors, nike air max, air force, hogan, new balance pas cher, vanessa bruno, ray ban uk, hollister, kate spade handbags, north face, nike air max, lululemon, coach outlet, burberry, michael kors, timberland, ray ban pas cher, michael kors outlet, coach purses, lacoste pas cher, hermes, nike air max, nike blazer, north face, ralph lauren uk, abercrombie and fitch, michael kors outlet, michael kors, nike roshe, coach outlet

Unknown said...

moncler, lancel, canada goose, moncler outlet, canada goose, louis vuitton, juicy couture outlet, swarovski crystal, sac louis vuitton pas cher, louis vuitton, canada goose outlet, moncler, moncler, moncler, pandora charms, montre pas cher, canada goose uk, ugg,ugg australia,ugg italia, hollister, canada goose, ugg,uggs,uggs canada, bottes ugg, marc jacobs, doudoune canada goose, ugg boots uk, moncler, louis vuitton, moncler, pandora jewelry, thomas sabo, pandora charms, toms shoes, barbour, coach outlet, supra shoes, replica watches, links of london, barbour jackets, canada goose, juicy couture outlet, swarovski, pandora jewelry, canada goose outlet, louis vuitton, moncler, karen millen, wedding dresses, ugg pas cher

على محمد said...


شركة رش مبيدات بالدمام
شركة رش مبيدات بالقطيف
افضل شركة مكافحة حشرات بالخبر
افضل شركة مكافحة حشرات بالجبيل
افضل شركة مكافحة حشرات بالاحساء
افضل شركة مكافحة حشرات بالقطيف

seocom said...


شركة مكافحة فئران بالمدينة المنورة
شركة تنظيف فلل بالمدينة المنورة
شركة تنظيف عمائر بالمدينة المنورة
شركة عزل خزانات بالمدينة المنورة
شركة تنظيف خزانات بالمدينة بالمنورة
شركة تركيب طارد حمام بالمدينة
شراء اثاث مستعمل بالمدينة المنورة
معلم بلاط بالمدينة المنورة
معلم بلاط بالمدينة المنورة
_____________________________