RSS

MSSQL 2008R2 Mirroring in Workgroup

04 Jan

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

 

 
1 Comment

Posted by on January 4, 2013 in Mirroring SQL Server 2008

 

One response to “MSSQL 2008R2 Mirroring in Workgroup

  1. santosh kumar

    March 21, 2015 at 11:34 PM

    thanks for help but give error for create end point
    This “ROLE=PARTNER/ALL ENDPOINT” statement is not supported on this edition of SQL Server.

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s