Introduction to Mirroring
Mirroring is mechanism used by DBAs to ensure high
availability of SQL database in production environment where the DB is to be up
all the time, It has always been a headache for DBAs in this article I am going
to cover SQL server 2008 with service pack 3 mirroring with two principals and
a witness by use of certificates
I decided to come up with this article after spending almost
2 weeks trying to figure out what’s wrong with my set up i.e. after going
through almost all blogs and official documents provided by Microsoft.
I will go through the process of configuration with high
safety mode with automatic fail over
Preparation of the Servers:
-
Ensure the principle, Mirror and witness server
are hosted on separate hosts and unique server instance name
-
Take the latest full back up of the database +
transaction log backup and restore the same on the mirror server with no recovery
-
Allow remote connections
a.
On all
the server instances by enabling the following protocols
Named pipes
Tcp/Ip
Shared
Memory
After
enabling the instances protocols restart the SQL server instances
b.
Allow exceptions on the firewall for SQL server
service in the following path - C:\Program
Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn
c.
Change the start-up parameter for the SQL
browser to automatic and add an exception for it in the firewall. The path for
the browser is : C:\Program Files (x86)\Microsoft SQL Server\90\Shared
d.
Open port 1433- TCP/IP and port 1434 – UDP
1st Principal server Preparation:
1. Create a master key for encrypting
the certificates
USE master;
CREATE
MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd2012';
GO
2. Create a certificate to be used for
authentication
USE master;
CREATE
CERTIFICATE cert1
WITH
SUBJECT = 'cert1 certificate';
Start_date = '1/17/2012';
expiry_date =
'1/7/2020'; //remember to include expiry date and start
date because by default
the life span is one year
GO
3. Create a mirroring endpoint
CREATE
ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE cert1
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
4. Backup the certificate to a secure
location and copy it across to the Mirror server and witness server using a
secure method
BACKUP
CERTIFICATE DEVKIHQ1 TO FILE = 'C:\cert.cer';
GO
1st Mirror Server
preparation:
1.
Repeat same procedure as on the Principal server
as follows
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'p@ssw0rd2012';
GO
2.
Create a certificate for authentication for the
mirroring
CREATE
CERTIFICATE MIRROR
WITH SUBJECT = 'MIRROR certificate ',
Start_date
= '1/17/2012',
expiry_date
= '1/7/2020';
;
GO
3. Create
mirroring endpoint
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE MIRROR
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
4. Back
up the certificate and use a secure method to copy it across to both principal
and witness server
BACKUP
CERTIFICATE MIRROR TO FILE = 'C:\MIRROR.cer';
GO
2nd Principal Preparation:
1.
Create a login for the mirror server
USE master;
CREATE LOGIN MIRROR WITH PASSWORD = 'p@ssw0rd2012';
GO
2.
Create a user for the login mirror
CREATE USER MIRROR_user FOR LOGIN MIRROR;
GO
3.
Get authorization
for the login from the certificate that you copied across from the mirror
server
CREATE CERTIFICATE
MIRROR
AUTHORIZATION
MIRROR_user
FROM FILE =
'C:\MIRROR.cer'
GO
4.
Grant permission to mirroring endpoint for the
login Mirror
GRANT CONNECT ON ENDPOINT::Mirroring TO [MIRROR];
GO
2nd mirror
Preparation:
1. Create
a login for the principal server
USE master;
CREATE LOGIN cert1 WITH PASSWORD =
'p@ssw0rd2012';
GO
2. Create
a user for the Login
CREATE USER
DEVKIHQ1_user FOR LOGIN DEVKIHQ1;
GO
3. Create
a certificate for the principal server with authentication from the copied
across certificate
CREATE CERTIFICATE prin1
AUTHORIZATION prin1_user
FROM FILE = 'C:\cert1.cer'
GO
4. Grant
permission for the user to access the mirroring endpoint
GRANT CONNECT ON
ENDPOINT::Mirroring TO [prin1];
GO
Witness Server Preparation:
1. Repeat
the same procedure on the witness server
-
Create key
-
Create certificate
-
Create mirroring endpoint
-
Back up certificate and copy securely across all
servers
2. Create
a login for the mirror and principal server on the witness server and a certificate
with authentication from respective
certificates and allow connection to mirroring endpoint
-
Repeat (2) on principal and mirror server for
the witness login
Mirroring Procedure:
Before Mirroring starts
you need to add host to each server host file in the following location
C:\Windows\System32\drivers\etc
Open the file called hosts
using a notepad scroll downwards to the last line then add the IP address of
the other two servers and the fully qualified domain name in this format
192.168.2.8 DEVKIHQ2MIR.devkigroup.local
After the last step on the last
server the instances are ready for mirroring, Open the instance on the
principal server using SQL Server Management Studio and follow this steps :
1. Right
click database point to tasks the click mirror
2. Click
configure security then next
3. On
the include witness server leave as it is , click next
4. On
the next screen choose the three servers to configure then click next, connect
to the principal instance then click next, connect to the witness server
instance and click next, connect to the witness server and click next
5. On
the service account screen leave it as it is and click next, then finish then
close
6. After
closing click start mirroring button and that’s it
No comments:
Post a Comment