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.

7 comments:

Coach Factory said...

tory burch outlet, air max, michael kors outlet online, nike free, kate spade outlet, louis vuitton outlet, jordan shoes, nike shoes, polo ralph lauren, longchamp handbags, chanel handbags, prada handbags, burberry outlet online, louis vuitton, louis vuitton outlet, ray ban sunglasses, kate spade handbags, ray ban outlet, michael kors outlet, louis vuitton handbags, coach outlet store online, oakley sunglasses cheap, polo ralph lauren outlet, michael kors outlet online, tiffany and co, louboutin shoes, prada outlet, louboutin, longchamp handbags, burberry outlet online, coach outlet, longchamp outlet, michael kors outlet store, louis vuitton outlet online, nike air max, coach purses, christian louboutin shoes, oakley sunglasses, coach outlet, michael kors outlet, oakley vault, michael kors outlet online, louboutin outlet, coach factory outlet, gucci outlet

Coach Factory said...

nike huarache, longchamp soldes, longchamp, karen millen, abercrombie and fitch, air force, nike free pas cher, air jordan, louis vuitton, hollister, barbour, hogan sito ufficiale, lululemon, lacoste, ralph lauren, nike air max, sac guess, tn pas cher, new balance pas cher, nike trainers, nike blazer, vans outlet, michael kors, timberland, ralph lauren pas cher, oakley pas cher, louboutin, burberry, north face, north face pas cher, hollister, vans pas cher, longchamp, sac louis vuitton, air max, montre femme, hollister, nike roshe, vanessa bruno, mulberry, louis vuitton uk, nike free, nike roshe run, ray ban pas cher, sac louis vuitton, converse pas cher, michael kors, ray ban uk, michael kors, hermes pas cher

Coach Factory said...

canada goose outlet, moncler, juicy couture outlet, ugg boots, p90x3, babyliss pro, lululemon outlet, bottega veneta, herve leger, ugg, asics shoes, giuseppe zanotti, beats by dre, canada goose outlet, ugg outlet, insanity, nfl jerseys, valentino shoes, birkin bag, new balance shoes, reebok outlet, canada goose uk, ferragamo shoes, soccer shoes, mcm handbags, supra shoes, ugg boots, uggs on sale, north face jackets, chi flat iron, ghd, juicy couture outlet, moncler, canada goose pas cher, soccer jerseys, abercrombie and fitch, jimmy choo shoes, canada goose, marc jacobs, moncler outlet, north face outlet, canada goose outlet, celine handbags, moncler, wedding dresses, roshe run, instyler ionic styler, mont blanc, rolex watches, mac cosmetics

ninest123 said...

burberry, tory burch outlet, jordan shoes, sac longchamp, uggs on sale, ugg boots, louis vuitton, oakley sunglasses, michael kors, tiffany jewelry, cheap oakley sunglasses, ray ban sunglasses, christian louboutin outlet, ralph lauren pas cher, louboutin pas cher, ray ban sunglasses, air max, nike free, longchamp outlet, longchamp pas cher, ray ban sunglasses, tiffany and co, oakley sunglasses, polo ralph lauren outlet, louis vuitton, prada outlet, kate spade outlet, longchamp outlet, nike free, ugg boots, nike air max, gucci outlet, oakley sunglasses, louis vuitton outlet, longchamp, nike outlet, replica watches, louis vuitton, replica watches, prada handbags, chanel handbags, louboutin outlet, oakley sunglasses, nike roshe run, louis vuitton outlet, nike air max, louboutin shoes, louboutin, polo ralph lauren outlet, air jordan pas cher

ninest123 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

ninest123 said...

converse outlet, giuseppe zanotti, hollister, insanity workout, herve leger, north face outlet, asics running shoes, nike air max, louboutin, converse, instyler, vans shoes, reebok shoes, iphone cases, abercrombie and fitch, mac cosmetics, wedding dresses, nike huarache, baseball bats, p90x workout, valentino shoes, chi flat iron, nike trainers, ghd, babyliss, oakley, soccer shoes, vans, timberland boots, gucci, celine handbags, nike air max, north face outlet, new balance, ralph lauren, hollister, jimmy choo shoes, mcm handbags, birkin bag, soccer jerseys, longchamp, beats by dre, bottega veneta, ferragamo shoes, nfl jerseys, lululemon, nike roshe, hollister, mont blanc, ray ban

ninest123 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