How to bring up a mirror database after a failure of the principal(Prod)

If you have a Witness server then this should happen autmatically, but if there is no witness server then you need bring up the mirror manually with the following command:

ALTER DATABASE dbname SET PARTNER OFF
RESTORE DATABASE dbname WITH RECOVERY

If for some reason you are running with Safety Off (High Performance) then there may be some transactions lost so you have to acknowledge that when restoring the database:

ALTER DATABASE dbname SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Failing Over

• This is as straight forward as clicking properties on the principal mirrored database, then clicking Mirroring and then click Failover.
• One thing to look out for are databases running in High Performance mode, these can’t be failed over so you will need to convert them to high safety (select high safety then click ok to save) before you fail them over. Later on they can be turned back into high performance.

Set up Mirror Endpoints

Set up Endpoints for Database Mirroring

Create Certificate and end-points (On Principal Instance):

use master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@55wordxx’;
GO

CREATE CERTIFICATE principal_cert WITH SUBJECT = ‘Principal Server Certificate for database mirroring’;
GO

CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP (LISTENER_PORT = 51200, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE principal_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

BACKUP CERTIFICATE principal_cert TO FILE = ‘G:\Sqlbackup\Mirroring_certxx_P.cer’
GO

Create Certificate and end-points (On Mirror Instance):

USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@55wordxx’;
GO

CREATE CERTIFICATE Mirroring_cert WITH SUBJECT = ‘Mirror Server Certificate for database mirroring’;
GO

CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP (LISTENER_PORT = 51200, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE Mirroring_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL );
GO

BACKUP CERTIFICATE Mirroring_cert
TO FILE = ‘G:\Sqlbackup\Mirroring_cert_M.cer’
GO

Create User and Associate Certificate (On Principal Instance):

At this stage we need to exchange certificates to other instance. Copy Principal instance certificate on mirror server and Mirror instance certificate to Principal server manually.

USE MASTER
GO

CREATE LOGIN ##MirrorProxy## WITH PASSWORD = ‘P@55wordxx’;
GO

CREATE USER ##MirrorProxy## FOR LOGIN ##MirrorProxy##;
GO

CREATE CERTIFICATE Mirroring_cert
AUTHORIZATION ##MirrorProxy##
FROM FILE = ‘G:\Sqlbackup\Mirroring_cert_M.cer’;
GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [##MirrorProxy##];
GO

Create User and Associate Certificate ( On Mirror Instance):
USE MASTER
GO
CREATE LOGIN ##MirrorProxy## WITH PASSWORD = ‘P@55wordxx’;
go

CREATE USER ##MirrorProxy## FROM LOGIN ##MirrorProxy##;
go

CREATE CERTIFICATE principal_cert
AUTHORIZATION ##MirrorProxy##
FROM FILE = ‘G:\Sqlbackup\Mirroring_cert_P.cer’;
Go

GRANT CONNECT ON Endpoint::Endpoint_Mirroring TO [##MirrorProxy##];
go

Also refer to https://tecadmin.net/setup-database-mirroring-sql-server/

Steps to Set up Mirroring

Steps to set up mirroring

Prerequisite: Make sure endpoints are set up

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 51200 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
both primary and mirror

1) Alter the log backup job, DBM.LogBackup, to ignore the database temporarily, do this by editing and adding the dbname to @Databases e.g = ‘ALL_DATABASES, -ETWeb11SRBank’

2) Make sure that the database is in full recovery mode and take a full backup and a log backup.

3) Copy the full and log backups over to the mirror server. e.g
FF2SQLETMIR01 (\Ff2sqletmir01\mir)

4) Restore the backup (full and log) with norecovery using following sql, this will set them as mirrored databases;
RESTORE DATABASE [DBSYSSPK]
FROM DISK = ‘G:\Sqlbackup\DBSYSSPK_db_20180804_174400_1_of_1.bak’
WITH NORECOVERY
go
RESTORE LOG [DBSYSSPK]
FROM DISK = ‘G:\Sqlbackup\DBSYSSPK_tlog_20180804_180101.bak’
WITH NORECOVERY
go
ALTER DATABASE [DBSYSSPK]
SET PARTNER = ‘TCP://JKTINDBP0001.id.db.com:51200’
go

5) On the server where you are mirroring from run the folloiwng sql:
ALTER DATABASE [DBSYSSPK]
SET PARTNER = ‘TCP://JKTINDBB0001.ID.DB.COM:51200’
go

6) The final step is to edit job DBM.LogBackup on FF1SQLETW01 and take out the database you added in step (1) e.g “, -ETWeb11SRBank”.

Mirroring Issues

Mirroring issues

If Mirroring stops working between 2 endpoints then there are a few things you can try before you rebuild the mirroring.

One quick thing to try is to stop and start the mirroring endpoint:

select * from sys.endpoints

–To stop an endpoint:

ALTER ENDPOINT dbMirror STATE = STOPPED

–To start an endpoint:

ALTER ENDPOINT dbMirror STATE = STARTED

If all that fails then you will just need to rebuild mirroring by making anote of the mirroring connections, removing it, taking a database dump and tran log dump, copying it over, loading it up and setting up mirroring, For further details refer to “Steps to set up mirroring”

Mirroring Check

–SQL to check status of mirroring:

SELECT
DB_NAME(database_id) As DatabaseName,
CASE WHEN mirroring_guid IS NOT NULL THEN ‘Mirroring is On’ ELSE ‘No mirror configured’ END AS IsMirrorOn,
CASE WHEN mirroring_safety_level=1 THEN ‘High Performance’ WHEN mirroring_safety_level=2 THEN ‘High Safety’ ELSE NULL END AS MirrorSafety,
mirroring_state_desc,
mirroring_role_desc,
mirroring_partner_name AS MirrorServer,
CASE WHEN mirroring_witness_name IS NOT NULL THEN mirroring_witness_name ELSE ‘NULL’ END AS [WitnessServer],
mirroring_witness_state_desc
FROM sys.database_mirroring
where mirroring_guid IS NOT NULL
GO