Database mirroring on MS SQL

Good day. I decided to describe here my experience in setting database mirroring. Not having, until recently, a similar profit, I began to surf the Internet in search of information on this subject. And I will try to arrange the post as a step-by-step instruction to tell about the main points, in general, what would be nothing superfluous.

Intro
To reserve a database, we considered 2 options:
- replication
- mirroring
Replications have disappeared, because some tables cannot be replicated and, in general, a database structure should be immediately provided specifically for this case.
Mirroring works with a bang! As a result of tests, he put the main base, translated the mirror into the main one. After raising the main thing - that machine became a mirror, changed their places. Everything went without a hitch, without a hitch. (God grant her long health!)

In general, there are 3 modes of mirroring:
- protected with automatic recovery
- protected with manual recovery
- not secure / asynchronous
Protected are different from asynchronous in that they do not wait for confirmation of the acceptance of a transaction on a mirror server, but continue to work and throw new and new transactions into the queue.
Protected with automatic recovery requires automatic recovery to use the 3rd server (witness) and, in principle, is useful only if you can specify a backup server in your application to switch in case the primary server is not working. Since I was sorry, the information space and applications working with the database could not be switched by the monitoring servers either.
I set up the base to work in protected mode with manual recovery .

Here is a good instruction on TechNet .
And here in the pictures shows how to do it through the GUI.
')
Part 1. Setting up a server connection.
To connect servers with each other, checkpoints are created on both machines, ports for connections are opened, users, certificates, etc. are created.
We will create checkpoints, for authorization we will use a certificate generated by MS SQL server (other certificates can also be used).

1. Create a certificate on the main server and save it in the D: \ Certs package
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' '
GO
IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1)
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'PrincipalServerCert')
CREATE CERTIFICATE PrincipalServerCert
WITH SUBJECT = 'Principal Server Certificate',
START_DATE = '08/15/2011',
EXPIRY_DATE = '08/15/2021';
GO
BACKUP CERTIFICATE PrincipalServerCert TO FILE = 'D:\Certs\PrincipalServerCert.cer'


2. Create a DBMirrorEndPoint checkpoint on the core server.
USE MASTER
GO
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirrorEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE PrincipalServerCert, ENCRYPTION = REQUIRED
,ROLE = ALL
)

3. Create a certificate and control point DBMirrorEndPoint on the mirror, by analogy with the main one.
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' '
GO
IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1)
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'MirrorServerCert')
CREATE CERTIFICATE MirrorServerCert
WITH SUBJECT = 'Mirror Server Certificate',
START_DATE = '08/15/2011',
EXPIRY_DATE = '08/15/2021';
GO
BACKUP CERTIFICATE MirrorServerCert TO FILE = 'D:\Certs\MirrorServerCert.cer'

IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirrorEndPoint
STATE=STARTED AS TCP (LISTENER_PORT = 5023)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MirrorServerCert, ENCRYPTION = REQUIRED
,ROLE = ALL
)


Certificates and checkpoints we created. Now so that the servers can communicate with each other, on each server you need to create accounts and bind them to certificates.

4. Copy certificates from one server to another, so that the D: \ Certs folder contains 2 certificates each.


5. Create MirrorServerUser on the main server of the user, this user is tied to the MirrorDBCertPub certificate generated and copied from the mirror server
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'MirrorServerUser')
CREATE LOGIN MirrorServerUser WITH PASSWORD = '2'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'MirrorServerUser')
CREATE USER MirrorServerUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'MirrorDBCertPub')
CREATE CERTIFICATE MirrorDBCertPub AUTHORIZATION MirrorServerUser
FROM FILE = 'D:\Certs\MirrorServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerUser
GO


6. Let's create on the backup server of the user PrincipalServerUser , this user is tied to the PrincipalDBCertPub certificate generated and copied from the main server
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'PrincipalServerUser')
CREATE LOGIN PrincipalServerUser WITH PASSWORD = '2'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'PrincipalServerUser')
CREATE USER PrincipalServerUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'PrincipalDBCertPub')
CREATE CERTIFICATE PrincipalDBCertPub AUTHORIZATION PrincipalServerUser
FROM FILE = 'D:\Certs\PrincipalServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerUser
GO

Communication between servers is configured!

Part 2. Setting up databases.
Here we will need to remove the backup from the working database, raise it on the mirror server in the NORECOVERY mode and turn on the mirroring mode.
The mirrored database must have a FULL recovery model.

1. Remove the backup database.
BACKUP DATABASE [MIRROR_TEST] TO DISK = N'D:\MIRROR_TEST.bak'
WITH FORMAT, INIT, NAME = N'MIRROR_TEST-Full Database Backup',STATS = 10

2. Raise it on the mirror (the script implies that the backup file is transferred to the mirror server on disk D)
RESTORE DATABASE [MIRROR_TEST]
FROM DISK = 'D:\MIRROR_TEST.bak' WITH NORECOVERY
,MOVE N'MIRROR_TEST' TO N'D:\MSSQL_DB\MIRROR_TEST.mdf'
,MOVE N'MIRROR_TEST_log' TO N'D:\MSSQL_DB\MIRROR_TEST_log.ldf'


3. To start mirroring on a mirror server, do the following:
ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMAINSERV:5022'

4. Then on the main point:
ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMIRRORSERV:5023'

If you get an error like:

The mirror database, “MIRROR_TEST”, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

or

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

Make a backup of the log from the base on the main server and restore it on the mirror (again in the NORECOVERY mode).
Backup:
BACKUP LOG MIRROR_TEST TO DISK = 'D:\MIRROR_TEST.trn'

Recovery:
RESTORE LOG MIRROR_TEST
FROM DISK = 'D:\MIRROR_TEST.trn' WITH NORECOVERY

Part 3. Disaster recovery. Changing roles.
You can change the roles of the server so that the mirror becomes the main one and look at the species through the GUI by clicking the right button on the database - Task - Mirror - Failover or through the T-SQL command
ALTER DATABASE MIRROR_TEST SET PARTNER FAILOVER

If the mirror base crashed down, the main one continues to work in an unprotected mode (this is not reflected on the clients). After the mirror is resumed, the backup database automatically connects and overtakes the main one.

If the main base crashed, then to revive the backup, you need to perform a forced recovery.
ALTER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

however, in this case there is a risk of losing some data (much is written about it here )

When performing a forced restoration, the mirror base becomes the main one, and the former main one will automatically become a mirror one after restoration, awaiting permission to continue the mirroring session. What you need to do
ALTER DATABASE MIRROR_TEST SET PARTNER RESUME
That's all! While working 8-)