SQL Server Database Mirroring - Creating and using Mirror Endpoints in Principal, Mirror and Witness

Creating a Mirror Endpoint in Principal or Mirror Servers

 

1CREATE ENDPOINT Mirroring

2    STATE=STARTED 

3    ASTCP (LISTENER_PORT=5022) 

4    FORDATABASE_MIRRORING (ROLE=PARTNER)

Creating an Endpoint for a Witness Server

 

1CREATE ENDPOINT Mirroring

2    STATE=STARTED 

3    ASTCP (LISTENER_PORT=7022) 

4    FORDATABASE_MIRRORING (ROLE=WITNESS)

Creating a Login for a Domain User Account for Partner Instances

 

1-- Domain Name: SYNTAXHELP

2-- User Name: jacob

3  

4USE master ;

5GO

6CREATE LOGIN [SYNTAXHELP\jacob] FROM WINDOWS;

Grant connect permissions on Endpoint to login account

 

1-- Domain Name: SYNTAXHELP

2-- User Name: jacob

3-- Endpoint Name: Mirroring

4  

5USE master ;

6GO

7GRANT CONNECT ON ENDPOINT::MirroringTO [SYNTAXHELP\jacob];

Creating a Mirroring Endpoint that uses an encrypted Certificate

 

01CREATE ENDPOINT Mirroring

02   STATE = STARTED

03   ASTCP (

04      LISTENER_PORT=7024,

05      LISTENER_IP =ALL

06   ) 

07   FORDATABASE_MIRRORING ( 

08      AUTHENTICATION = CERTIFICATE ServerPriCert,

09      ENCRYPTION = REQUIRED ALGORITHM AES,

10      ROLE =ALL

11   );

12GO

Creating a Mirroring Endpoint that uses Windows KERBEROS Authentication

 

1CREATE ENDPOINT mirroring

2    STATE = STARTED

3    ASTCP ( LISTENER_PORT = 7022 )

4    FORDATABASE_MIRRORING (

5       AUTHENTICATION = WINDOWS KERBEROS,

6       ENCRYPTION = SUPPORTED,

7       ROLE=ALL);

8GO

Granting Connect Permission on the Login for Remote Mirroring Endpoint

 

1-- Login Name: jacob

2GRANT CONNECT ON ENDPOINT::MirroringTO [jacob];

3GO

Dropping a Mirroring Endpoint

 

1-- Endpoint name: Mirroring

2DROP ENDPOINT Mirroring

Querying the Mirroring Endpoints

 

01

SELECT

02

    name, 

03

    role_desc ASRole, 

04

    state_desc ASState, 

05

    connection_auth_desc AS ConnAuth, 

06

    encryption_algorithm_descAS Algorithm

07

FROM sys.database_mirroring_endpoints;

08

/*

09

name      role    state   connAuth  Algorithm 

10

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

11

Mirroring PARTNER STARTED NEGOTIATE NONE

12

*/

http://syntaxhelp.com/SQLServer/Mirroring/endpoints