Friday, September 2, 2011

SQL 2008 R2 Mirroring End Points - Different NICs

Leveraging dedicated NIC's for SQL Mirror NIC traffic (other than SQL Server system traffic).

Example:

SQL Services run on 10.X.X.Y on Principal and 10.X.X.Z on Mirror)
SQL Mirror traffic:

Mirror Node will run on 192.168.0.2
Principal Node will run on 192.168.000.1


Mirror Node:

CREATE ENDPOINT [DB_Mirror]
AUTHORIZATION [SURVEYSAMPLING\matt_fitzgerald]
STATE=STARTED
AS TCP (LISTENER_PORT = 5030, LISTENER_IP = (192.168.000.2))
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

GRANT CONNECT ON ENDPOINT::DB_Mirror TO [DOMAINNAME\sqlserverServiceAccount]

--------------------

Principal Node

CREATE ENDPOINT [DB_Mirror]
AUTHORIZATION [SURVEYSAMPLING\matt_fitzgerald]
STATE=STARTED
AS TCP (LISTENER_PORT = 5030, LISTENER_IP = (192.168.000.1))
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

GRANT CONNECT ON ENDPOINT::DB_Mirror TO [DOMAINNAME\sqlserverServiceAccount]

-----------

Restore the database on mirror node along with one transaction log...


restore database dbname
from disk = '' WITH NORECOVERY, stats = 10

Restore log dbname from disk='' with norecovery, stats=10

--On mirror node
alter database DBNAME
set partner = 'TCP://192.168.000.1:5030'

--On Principal Node
alter database entrycheck
set partner = 'TCP://192.168.000.2:5030'

By default this will create a Sync mirror.

No comments:

Post a Comment