SQL Mirroring with SQL 2008 R2 principal/mirror + SQL 2012 Witness

Some quick notes on setting up mirroring with a 2012 witness. This is another lab experiment, but I ran into some silly mistakes. For posterity, here are mah notes.(I’m going to assume you understand mirroring and generally how to set it up).

Make sure the service account you are using has connect granted to it for each endpoint on each server. Just run this on each server (principal, mirror, and witness) and change the DOMAIN\USER to your own.

USE master;
GO
GRANT CONNECT on ENDPOINT::Endpoint_Mirroring TO [DOMAIN\USER];
GO

When restoring the database to the mirror server, make sure you use “NORECOVERY”. Yeah, I face palmed on this one but when you’re flying through the config you miss the simple stuff. 😛

The witness needs to be running under the service account. Another silly one. When I install SQL I leave everything on the defaults for the most part and config post-install. So of course my SQL 2012 instance was running under NT Service. If you are running under a local account, you’ll get the error below when you try and add the witness.You will also see a failed login within event viewer (app log).

“The ALTER DATABASE command could not be sent to the remote server instance”

Finally, here are some fun T-SQL commands to get info, create stuff.

Create an endpoint

CREATE ENDPOINT Endpoint_Mirroring — name
STATE=STARTED — Endpoint will be started and ready
AS TCP(LISTENER_PORT=5022 — Endpoint will use port 5022 ,
LISTENER_IP=ALL) — Endpoint will listen on all IP addresses
FOR DATABASE_MIRRORING — Specifies mirroring as the endpoint purpose
(AUTHENTICATION = WINDOWS[Negotiate] — Will negotiate NTLM or Kerberos for authentication ,
ENCRYPTION = SUPPORTED, — Mirroring traffic will be encrypted
ROLE=ALL); — This endpoint can be principal, mirror, or a witness

Find all endpoints.

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id

Start an endpoint:

ALTER ENDPOINT Endpoint_Mirroring STATE=STARTED

Find all the relevant info for setting this up.

select * from sys.database_mirroring_endpoints
select * from sys.sysusers
select * from sys.server_principals
select * from sys.symmetric_keys
select * from sys.certificates

Leave a Comment

Your email address will not be published.