SQL Mirroring[Hot back up with Double machine]

Background:

It's fairly common for businesses to want to provide some high availability for their SQL Server databases, and one option is to have two SQL Server databases on separate machines with a SQL Server database mirrored. Microsoft provides mirroring out of the box in SQL Server 2005 and SQL Server 2008, and is a much cheaper alternative than going down the clustering/failover route, but does provide some protection. In mirroring, there is always one Principal database which serves the requests, and a standby Mirror that is always synchronizing. If the Principal database goes down, then the Mirror can be forced to become the Principal, and will then serve the requests. Once the original Principal is available again, it will become the new Mirror.

 

Restrictions on Backup and Restore During Mirroring

While a database mirroring session is active, the following restrictions apply:

• Backup and restore of the mirror database are not allowed.

• Backup of the principal database is allowed, but BACKUP LOG WITH NORECOVERY is not allowed.

• Restoring the principal database is not allowed. 

 

Script in Principal:

-- Step 1USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Password01!';CREATE CERTIFICATE HOST_pri_cert WITH SUBJECT ='HOST_pri certificate' , START_DATE = '01/11/2014', EXPIRY_DATE = '01/11/2015';-- Step 2alter ENDPOINT Endpoint_Mirroring STATE = STARTED ASTCP ( LISTENER_PORT=50221 , LISTENER_IP = ALL ) FORDATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_pri_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )--SELECT * FROM sys.endpoints;-- drop ENDPOINT Mirroring_Endpoint2 Go-- Step 3BACKUP CERTIFICATE HOST_pri_cert TO FILE = 'D:\Hot backup\HOST_pri_cert.cer';-- Step 4use masterCreate LOGIN HOST_min_login WITH PASSWORD ='Password01!';CREATE USER HOST_min_user FOR LOGIN HOST_min_login;--drop CERTIFICATE HOST_min_certCREATE CERTIFICATE HOST_min_cert AUTHORIZATION HOST_min_user FROM FILE ='D:\Hot backup\HOST_min_cert.cer';GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_min_login]; Create LOGIN HOST_pro_login WITH PASSWORD ='Password01!';CREATE USER HOST_pro_user FOR LOGIN HOST_pro_login;-- drop CERTIFICATE HOST_pro_certCREATE CERTIFICATE HOST_pro_cert AUTHORIZATION HOST_pro_user FROM FILE ='D:\Hot backup\HOST_pro_cert.cer';GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pro_login];-- select * from sys.database_principalsGO--  Step 5USE master;ALTER DATABASE TestBackup01SET RECOVERY FULL;Go--  Step 6USE TestBackup01--back up dataGOBACKUP DATABASE [TestBackup01] TO  DISK = N'D:\Hot backup\testbackup01.bak' WITH NOFORMAT, NOINIT,  NAME = N'TestBackup01-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOBACKUP LOG [TestBackup01] TO  DISK = N'D:\Hot backup\TestBackup01.trn' WITH NOFORMAT, NOINIT,  NAME = N'TestBackup01-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO

Script in Mirror:

-- Step 1USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Password01!';CREATE CERTIFICATE HOST_min_cert WITH SUBJECT ='HOST_mirror certificate' , START_DATE = '01/11/2014', EXPIRY_DATE = '01/11/2015';-- Step 2Alter ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=50221 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_min_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )-- drop endpoint Mirroring     -- SELECT * FROM sys.endpoints;Go-- Step 3BACKUP CERTIFICATE HOST_min_cert TO FILE = 'D:\Hot backup\HOST_min_cert.cer';--SELECT * FROM sys.certificates;Go-- Step 4CREATE LOGIN HOST_pri_login WITH PASSWORD ='Password01!';CREATE USER HOST_pri_user FOR LOGIN HOST_pri_login;drop CERTIFICATE HOST_pri_certCREATE CERTIFICATE HOST_pri_cert AUTHORIZATION HOST_pri_user FROM FILE ='D:\Hot backup\HOST_pri_cert.cer';GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pri_login];      CREATE LOGIN HOST_pro_login WITH PASSWORD ='Password01!';CREATE USER HOST_pro_user FOR LOGIN HOST_pro_login;drop CERTIFICATE HOST_pro_certCREATE CERTIFICATE HOST_pro_cert AUTHORIZATION HOST_pro_user FROM FILE ='D:\Hot backup\HOST_pro_cert.cer';GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pro_login]; -- Step 5create database TestBackup01USE master;ALTER DATABASE TestBackup01SET RECOVERY FULL;RESTORE DATABASE TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD;RESTORE LOG TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.trn' WITH FILE = 2, NORECOVERY, NOUNLOAD

Script in Witness:

-- Step 1USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Password01!';CREATE CERTIFICATE HOST_pro_cert WITH SUBJECT = 'HOST_Witness certificate' , START_DATE = '01/11/2014', EXPIRY_DATE = '01/11/2015';-- Step 2Create ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=50221 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING     ( AUTHENTICATION = CERTIFICATE HOST_pro_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )select * from sys.endpointsGo-- Step 3BACKUP CERTIFICATE HOST_pro_cert TO FILE = 'D:\Hot backup\HOST_pro_cert.cer';  --Note: Full Name-- Step 4CREATE LOGIN HOST_min_login WITH PASSWORD ='Password01!';CREATE USER HOST_min_user FOR LOGIN HOST_min_login;CREATE CERTIFICATE HOST_min_cert AUTHORIZATION HOST_min_user FROM FILE ='D:\Hot backup\HOST_min_cert.cer';GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_min_login];   CREATE LOGIN HOST_pri_login WITH PASSWORD ='Password01!';CREATE USER HOST_pri_user FOR LOGIN HOST_pri_login;CREATE CERTIFICATE HOST_pri_cert AUTHORIZATION HOST_pri_user FROM FILE ='D:\Hot backup\HOST_pri_cert.cer';GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pri_login];--select * from sys.database_principalsGo-- Step 5Create database TestBackup01USE master;ALTER DATABASE TestBackup01SET RECOVERY FULL;RESTORE DATABASE TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD;RESTORE LOG TestBackup01 FROM DISK = 'D:\Hot backup\backup_TestBackup01.trn' WITH FILE = 2, NORECOVERY, NOUNLOADGo

 

Note:

1. copy the certificates over to the other server(Mirror, Witness and Principal).

2. After coping the certificates over I setup the logins.

3. Back up and restore db with log.

• Perform a full backup of the database on the Principal server.
• Perform a Transaction Log backup on the Principal server.
• Copy the backup file to the Mirror.
• Important: Do a restore of the full backup into a new step, but before doing the restore, go to Options, then ensure you check the No Recovery option! This is vital!
• Perform another restore of the Transaction Log, also with the No Recovery option. (This is important, otherwise you'll get an error when starting the mirror - See Gotchas section for explanation).