syntaxhelp.com Your online cheat sheet!

Creating a Mirror Endpoint in Principal or Mirror Servers

CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5022) 
    FOR DATABASE_MIRRORING (ROLE=PARTNER)

Creating an Endpoint for a Witness Server

CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=WITNESS)

Creating a Login for a Domain User Account for Partner Instances

-- Domain Name: SYNTAXHELP
-- User Name: jacob

USE master ;
GO
CREATE LOGIN [SYNTAXHELP\jacob] FROM WINDOWS;

Grant connect permissions on Endpoint to login account

-- Domain Name: SYNTAXHELP
-- User Name: jacob
-- Endpoint Name: Mirroring

USE master ;
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [SYNTAXHELP\jacob];

Creating a Mirroring Endpoint that uses an encrypted Certificate

CREATE ENDPOINT Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024,
      LISTENER_IP = ALL
   ) 
   FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE ServerPriCert,
      ENCRYPTION = REQUIRED ALGORITHM AES,
      ROLE = ALL
   );
GO

Creating a Mirroring Endpoint that uses Windows KERBEROS Authentication

CREATE ENDPOINT mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (
       AUTHENTICATION = WINDOWS KERBEROS,
       ENCRYPTION = SUPPORTED,
       ROLE=ALL);
GO

Granting Connect Permission on the Login for Remote Mirroring Endpoint

-- Login Name: jacob
GRANT CONNECT ON ENDPOINT::Mirroring TO [jacob];
GO

Dropping a Mirroring Endpoint

-- Endpoint name: Mirroring
DROP ENDPOINT Mirroring

Querying the Mirroring Endpoints

SELECT
    name, 
    role_desc AS Role, 
    state_desc AS State, 
    connection_auth_desc AS ConnAuth, 
    encryption_algorithm_desc AS Algorithm
FROM sys.database_mirroring_endpoints;
/*
name      role    state   connAuth  Algorithm 
--------- ------- ------- --------- ----------
Mirroring PARTNER STARTED NEGOTIATE NONE
*/