SQL - Database Mirroring

Principal Server Database

/** Database Mirroring **/

USE MASTER

GO

-- For Principal Server Database

-- Step A - Recovery Modal

ALTER DATABASE DatabaseM

SET RECOVERY FULL

-- Step C-1 - Create login name

CREATE LOGIN [SIMON-PC\Test]

FROM WINDOWS

WITH DEFAULT_DATABASE = [MASTER],

 DEFAULT_LANGUAGE = [English]

GO

-- Step C-2 - Create Endpoint

CREATE ENDPOINT DbMirroring

STATE = STARTED

AS TCP(LISTENER_PORT=5022)

FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED)

GO

-- Step C-3 - Grant Permission

GRANT CONNECT

ON ENDPOINT::DbMirroring

TO [SIMON-PC\Test]

GO

/**NETSTAT -A to check the listening point 5022**/

-- Step E - Set the partner of Principal Server database to Mirroring Server database

USE MASTER

GO

ALTER DATABASE DatabaseM

SET PARTNER = N'tcp://SIMON-PC:5023'

-- Step F - TESTING

USE DatabaseM

GO

CREATE TABLE STUDENT

(

ID INT PRIMARY KEY IDENTITY(1,1),

NAME VARCHAR(50)

)

INSERT INTO STUDENT VALUES('ZHANG SAN')

INSERT INTO STUDENT VALUES('LI SI')

INSERT INTO STUDENT VALUES('WANG WU')

GO

-- Set database failure

USE MASTER

GO

ALTER DATABASE DatabaseM

SET PARTNER FAILOVER

-- Step H - Add a witness

USE MASTER

GO

ALTER DATABASE DatabaseM

SET WITNESS = N'tcp://SIMON-PC:5033'

Mirroring Server Database

/** Database Mirroring **/

-- For Mirroring Server Database

-- Step B-1 - Create login name

USE MASTER

GO

CREATE LOGIN [SIMON-PC\Test]

FROM WINDOWS

WITH DEFAULT_DATABASE = [MASTER],

 DEFAULT_LANGUAGE = [English]

GO

-- Step B-2 - Create Endpoint

CREATE ENDPOINT DbMirroringM

STATE = STARTED

AS TCP(LISTENER_PORT=5023)

FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED)

GO

-- Step B-3 - Grant Permission

GRANT CONNECT

ON ENDPOINT::DbMirroringM

TO [SIMON-PC\Test]

GO

/**NETSTAT -A to check the listening point 5023**/

-- Step D - Set the partner of Mirroring Server database to Principal Server database

USE MASTER

GO

ALTER DATABASE DatabaseM

SET PARTNER = N'tcp://SIMON-PC:5022'

-- Set database failure

USE MASTER

GO

ALTER DATABASE DatabaseM

SET PARTNER FAILOVER

Witness server database

-- For witness server database

USE MASTER

GO

-- Step G

-- Create login name

CREATE LOGIN [SIMON-PC\Test]

FROM WINDOWS

WITH DEFAULT_DATABASE = [MASTER],

 DEFAULT_LANGUAGE = [English]

GO

-- Create Endpoint

CREATE ENDPOINT DbMirroringW

STATE = STARTED

AS TCP(LISTENER_PORT=5033)

FOR DATABASE_MIRRORING(ROLE=WITNESS,ENCRYPTION=SUPPORTED)

GO

-- Grant Permission

GRANT CONNECT

ON ENDPOINT::DbMirroringW

TO [SIMON-PC\Test]

GO