Author: Mustafa EL-Masry

Problem

Now if I have 3 Servers with 3 SQl server Instance but this servers not trusted = Not in domain this servers in workgroup but now I won’t to create mirroring but the last step in Mirroring Configuration need from the Services Account What I will write and the servers with no trusted . so what we can do to make this servers is trusted .

Solution

We will make link between this SQL Server instance this link not the linked server feature but that’s meaning we will make master key and Certificate on all Servers then Create users in the 3 Servers then give grant Connection to this Users on this Mirroring Endpoint Example

Server name                            Certification                user

Server A(Principle)                 CERT_A                       User_A

Server B (Mirror)                     CERT_B                       User_B

Server C (Witness)                   CERT_C                       User_C

now I create difference Certificate  and user on the servers then I will

Create Certificate Server B and C  on server A

Create Certificate Server A and C on Server B

Create Certificate Server A and B on Server C

Also I make this Steps on user then give the 3 user in the Server A and B and C grant Connection on Mirroring Endpoint by this Way I make Bridge between the 3 Servers

Step 1 On Principle Server :

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD =‘Admin123’

GO

CREATE CERTIFICATE Principle_cert

WITH SUBJECT = ‘Principle certificate’

GO

CREATE ENDPOINT End_Mirroring

STATE = STARTED

ASTCP (LISTENER_PORT = 5025 , LISTENER_IP=ALL)

FOR DATABASE_MIRRORING

(AUTHENTICATION = CERTIFICATE Principle_cert , ENCRYPTION=REQUIRED ALGORITHM RC4 , ROLE=ALL)

GO

BACKUP CERTIFICATE Principle_cert

TO FILE =‘C:\certificate\Principle_cert.cer’

GO

Step no 2 On Mirror Server :

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Admin123’

GO

CREATE CERTIFICATE Mirror_cert WITH SUBJECT = ‘Mirror certificate’

GO

CREATE ENDPOINT End_Mirroring

STATE=STARTED

ASTCP (LISTENER_PORT= 5026,LISTENER_IP=ALL)

FOR DATABASE_MIRRORING

(AUTHENTICATION=CERTIFICATE Mirror_cert , ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE=ALL)

GO

BACKUP CERTIFICATE Mirror_cert

TO FILE=‘D:\certificate\Mirror_cert.cer’;

GO

Step No 3 on Principle Server : 

Copy the Certificate ” Mirror_cert  ” From Mirror Server to the Principle Server because I will Create the Certificate Mirroring Server on Principle Server With the User name Mirror_user

USE MASTER

GO

CREATE LOGIN Mirror_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Mirror_user FOR LOGIN Mirror_login

GO

CREATE CERTIFICATE Mirror_cert 

AUTHORIZATION Mirror_cert 

FROM FILE=‘C:\certificate\Mirror_cert .cer’  —–the Path of your Certificate After you Copy it

GO

GRANT CONNECT ON ENDPOINT ::End Mirroring TO [Mirror_login] ——-Give privilege to the User Mirror_login to Connect to the Mirroring Endpoint ” End_Mirroring “

GO

Step No 4 on Mirror Server : 

Copy the Certificate ” Principle_cert  ” From Principle Server to the Mirror Server because I will Create the Certificate Principle Server on Mirroring Server With the User name Principle_user

USE MASTER

GO

CREATE LOGIN Principle_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Principle_user FOR LOGIN Principle_login

GO

CREATE ERTIFICATE Principle_cert

AUTHORIZATION Principle_cert

FROM FILE=‘C:\certificate\Mirror_cert .cer’  —–the Path of your Certificate After you Copy it

GO

GRANT CONNECT ON ENDPOINT :: End Mirroring TO [Principle_login] ——-Give privilege to the User Principle_login to Connect to the Mirroring Endpoint ” End_Mirroring “

Step No 5 on Witness Server : Create master Key and Certificate Witness_Cert

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘Admin123’

GO

CREATE CERTIFICATE Witness_cert

WITH SUBJECT=‘Witness certificate’

GO

CREATE ENDPOINT End_Mirroring

STATE=STARTED

ASTCP (LISTENER_PORT= 5027,LISTENER_IP=ALL)

FOR DATABASE_MIRRORING

(AUTHENTICATION=CERTIFICATE Witness_cert,

ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = Witness)

GO

BACKUP ERTIFICATE witness_cert

TO FILE=‘C:\certificate\Witness_cert.cer’

GO

Step No 6 on Principle Server : 

Copy the Certificate ” ًWitness_cert  ” From Witness Server to the Principle Server because I will Create the Certificate Witness Server on Principle Server With the User name Witness_user

USE MASTER

GO

CREATE LOGIN Witness_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Witness_user FOR LOGIN Witness_login

GO

CREATE CERTIFICATE Witness_cert

AUTHORIZATION Witness_user FROM FILE=‘C:\certificate\Witness_cert.cer’

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Witness_login]

GO

Step No 7 on Mirror Server : 

Copy the Certificate ” ًWitness_cert  ” From Witness Server to the Mirror Server because I will Create the Certificate Witness Server on Mirror Server With the User name Witness_user

USE MASTER

GO

CREATE LOGIN Witness_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Witness_user FOR LOGIN Witness_login

GO

CREATE CERTIFICATE Witness_cert

AUTHORIZATION Witness_user FROM FILE=‘C:\certificate\Witness_cert.cer’

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Witness_login]

GO

Step No 8 on Witness Server :

Copy the Certificate ” Principle_cert  ” From Principle Server and  ” Mirror_cert  ” From the Mirror Server

” Principle_cert  “

USE MASTER

GO

CREATE LOGIN Principle_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Principle_user FOR LOGIN Principle_login

GO

CREATE CERTIFICATE Principle_cert

AUTHORIZATION Principle_user

FROM FILE=‘D:\certificate\Principle_cert.cer’

GO

GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Principle_login]

GO

” Mirror_cert  “

CREATE LOGIN Mirror_login WITH PASSWORD=‘Admin123’

GO

CREATE USER Mirror_user FOR LOGIN Mirror_login

GO

CREATE CERTIFICATE Mirror_cert

AUTHORIZATION Mirror_user

FROM FILE=‘C:\certificate\Mirror_cert.cer’

GO

GRANTCONNECT ON ENDPOINT::End_Mirroring TO [Mirror_login]

GO

Step No 8 :

take Full backup + Log backup from your Database Mirroring on Principle Server then Go to Mirroring Server and restore this Backup With NORECOVERY Option

Step No 9 :

GO TO the principle Server in make your Easy mirroring configuration but in the last Step don’t write any Service Account let it Empty then After this Step Mirroring will be Complete Successfully

See this post if you don’t know how to Configure mirroring Database With SQL management Studio

http://wp.me/p1Oidq-6w  Mirroring part 1

http://wp.me/p1Oidq-7h  Mirroring part 2

http://wp.me/p1Oidq-7w  Mirroring part 3

 

monitor Database mirroring by SQL Management:

1- I will Stop mirroring Server to input transaction into Principle Server and See the log what happened in It Go to your database Mirroring in Principle Server >> Write Click >>> tasks >>>Lunch Database mirroring monitor

2- Create Table on Principle Server  and insert 10000 Record into New table

CREATE TABLE TEST( A INT, B INT)

DECLARE @I INT=0

WHILE @I <100000

BEGIN

INSERTINTO TEST VALUES(@I,@I)

SET @I=@I+1

END

SELECT COUNT(*) FROM TEST

3- now run the Script and insert the Data then let’s go to Check the monitor to See what happened

 

4- Then Start the mirroring Server and Check the Monitor again

*********************************************************************************************************************

Check the database mirroring information :

SELECT M.database_id , D.Name , M.mirroring_state_desc  ,

M.mirroring_role_desc,M.mirroring_partner_name , M.mirroring_partner_instance,

M.mirroring_witness_name , M.mirroring_witness_state_desc

FROM sys.database_mirroring as M inner join Sys.databases as D

on M.database_id = D.database_id

Where D.database_id > 4