Quick & Dirty — setting up SQL Server Mirroring

on Primary and Failover servers:
create endpoint endpoint_mirroring
state=started
as tcp (listener_port = 7022)
for database_mirroring (role=partner);
go

on witness server:
create endpoint endpoint_mirroring
state=started
as tcp (listener_port = 7022)
for database_mirroring (role=witness);
go
make sure the SQL service account that is running has CONNECT permissions
make sure the ports are open (no firewall, etc…)

create backup of the main DB to be mirrored
restore it as WITH NORECOVERY on the mirror
on the primary, go to Tasks -> Mirror -> and configure the mirroring

If a certificate is wrong, or the service account does not have permissions it will
give a misleading network error saying it cant reach the host (“The server network address TCP://developer02.dev.com:7022 cannot be reached or does not exist”, Alter <dbname> failed). Check the event log for what the real error is.

The connection string for mirrors is:
SERVER=DEVELOPER;FAILOVERPARTNER=DEVELOPER02;network=dbmssocn

Apparently the dbmssocn makes it so that it automagically switches to the failover – I dunno.

If necessary, you can create a linked server that behaves normally even when failed-over.  I tried with a default SQL Native Client but it didn’t work. SQL OLEDB seemed to work.

Querying it from a linked server uses the following syntax:
select * from developer.sandbox.dbo.person

Bing bang boom.

Leave a Reply

Your email address will not be published. Required fields are marked *