Friday, June 8, 2012

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


No comments:

Post a Comment