12/18/2014

Jon Morisi's Configuring MS SQL Mirroring


Contents

Configuring Mirroring
Prerequisite notes
Restore the full database backup
On the principal
On the mirror
Restore the log backup
On the mirror
Configure Certificate based Mirroring
Backup Master Key
Backup certificates
Configure Alerting
Removing mirroring
Drop Endpoint
Drop certificates
drop user
drop login
drop master key
Mirroring status


Prerequisite notes
  1. Features by version (2012)
    1. Safety Full Only
                                                               i.      Business Intelligence
                                                             ii.      Standard
    1. Full Mirroring Support – Enterprise Edition Only
  1. Restore the backup on the mirror database name WITH NORECOVERY. Also, all log backups that were created after that backup was taken must also be applied, again WITH NORECOVERY
  2. high-safety mode with automatic failover, the normal load on each of the failover partners should use less than 50 percent of the CPU
  3. If you run SQL Server under a non-domain account, you must use certificates
  4. Re-create all the metadata of the dependant entities and objects in master and msdb on the destination server instance. (THIS MEANS LOGINS).
  5. Recreate jobs on the destination server instance.
  6. Providers Supporting Database Mirroring Client Redirect:
    (
    http://msdn.microsoft.com/en-us/library/ms175484(v=sql.110).aspx)
a.     SQL Server Native Client
b.     .NET Framework Data Provider for SQL Server

Restore the full database backup

On the principal
Replace AdventureWorks with the name of your database

USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM  DISK = N’..\AdventureWorks2012.bak' WITH  FILE = 1,  NOUNLOAD, STATS = 5
GO

On the mirror

USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM  DISK = N'..\AdventureWorks2012.bak' WITH  FILE = 1,  NOUNLOAD,  NORECOVERY, STATS = 5
GO


Configuring Windows Authentication based Mirroring

Configure Certificate based Mirroring 


Create the certificates with extended expiration dates:

CREATE CERTIFICATE [CERT]
   WITH SUBJECT = ‘Certificate for Mirroring’,
   EXPIRY_DATE = '10/31/2099';
GO

Configure the Mirroring Partners

There are several ways to implement the network address; the important item is to make sure your using the dedicated VLAN for mirroring traffic, if one exists:

Configure the Mirroring Witness

ALTER DATABASE [ADVENTUREWORKS]
  SET WITNESS = 'TCP://Witness:7022'
GO
--Again, there are several ways to implement the network address.

Restart SQL services on the witness

Backup Master Key

BACKUP MASTER KEY TO FILE = 'path_to_file'
    ENCRYPTION BY PASSWORD = 'password'

Backup certificates          

BACKUP CERTIFICATE HOST_cert TO FILE = 'C:\HOST_cert.cer';
GO

Removing mirroring

DROP everything

Drop Endpoint

SELECT * from sys.endpoints
DROP endpoint <ENPOINT NAME>

Drop certificates

                SELECT * from    sys.certificates
                DROP certificate <HOST_A_cert >
                DROP certificate <HOST_B_cert >

drop user

SELECT * from sys.sysusers
DROP user <USERNAME>

drop login

SELECT * from sys.syslogins
DROP login <LOGIN>

drop master key

SELECT * from sys.symmetric_keys
DROP MASTER KEY

Mirroring status           


SELECT *
FROM sys.database_mirroring