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

2. 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

http://msdn.microsoft.com/en-us/library/ms179306.aspx

Configure Certificate based Mirroring 

http://msdn.microsoft.com/en-us/library/ms191140.aspx

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:

http://msdn.microsoft.com/en-us/library/ms189921.aspx

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

http://msdn.microsoft.com/en-us/library/ms174387.aspx

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           

http://msdn.microsoft.com/en-us/library/ms178655(v=sql.110).aspx

SELECT *
FROM sys.database_mirroring