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

Creating a Mirror Endpoint in Principal or Mirror Servers

 

1	CREATE ENDPOINT Mirroring

2	STATE=STARTED

3	AS TCP (LISTENER_PORT=5022)

4	FOR DATABASE_MIRRORING (ROLE=PARTNER)

Creating an Endpoint for a Witness Server

 

1	CREATE ENDPOINT Mirroring

2	STATE=STARTED

3	AS TCP (LISTENER_PORT=7022)

4	FOR DATABASE_MIRRORING (ROLE=WITNESS)

Creating a Login for a Domain User Account for Partner Instances

 

1	-- Domain Name: SYNTAXHELP

2	-- User Name: jacob

3

4	USE master ;

5

GO

6	CREATE 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

5	USE master ;

6

GO

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

Creating a Mirroring Endpoint that uses an encrypted Certificate

 

01	CREATE ENDPOINT Mirroring

02	STATE = STARTED

03

AS TCP (

04	LISTENER_PORT=7024,

05	LISTENER_IP = ALL

06

)

07	FOR DATABASE_MIRRORING (

08	AUTHENTICATION = CERTIFICATE ServerPriCert,

09	ENCRYPTION = REQUIRED ALGORITHM AES,

10	ROLE = ALL

11

);

12

GO

Creating a Mirroring Endpoint that uses Windows KERBEROS Authentication

 

1	CREATE ENDPOINT mirroring

2	STATE = STARTED

3	AS TCP ( LISTENER_PORT = 7022 )

4	FOR DATABASE_MIRRORING (

5	AUTHENTICATION = WINDOWS KERBEROS,

6	ENCRYPTION = SUPPORTED,

7	ROLE=ALL);

8

GO

Granting Connect Permission on the Login for Remote Mirroring Endpoint

 

1	-- Login Name: jacob

2	GRANT CONNECT ON ENDPOINT::Mirroring TO [jacob];

3

GO

Dropping a Mirroring Endpoint

 

1	-- Endpoint name: Mirroring

2	DROP ENDPOINT Mirroring

Querying the Mirroring Endpoints

 

01

SELECT

02

name,

03	role_desc AS Role,

04	state_desc AS State,

05	connection_auth_desc AS ConnAuth,

06	encryption_algorithm_desc AS 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