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