Notes from Mirroring Deep Dive Session with Mohammed Sharaf: Microsoft Premier Engineer (I can never learn enough about Mirroring :)

 

One thing I have gotten wrong before, or not elaborated on during a past presentation on mirroring, although Roman Rehak mentioned it in the Burlington, Vermont PASS chapter meeting back in May, is that we can use Reporting Services on the Mirrored copy, as long as you are Enterprise edition and Snapshots are enabled.

Ports used in Mirroring, Clarification
Mirroring uses TCP port 5022 by default, and then goes up from there to 5023 for the next Mirror Security Setup, per instance on the same server installation.
This port 502# is used for mirroring is the dedicated connection pool, and the Database Engine is responsible for the communication between Mirror Partners. To troubleshoot port issues, please see a previous post on mirroring.  

You cannot use a local account for mirroring, but you can use certificates, with symmetric / asymmetric encryption (example below, after regular AD setup). Asymmetric encryption is the generation of a key pair – and you need the pair together, otherwise the data will not be useable – this is how SSL on both sides (general explanation). The private key is often sent over the network with the public, and used on the receiving end to decrypt.  These keys can be used for authentication, as an alternative to a domain service account.

General Mirroring Notes:

It is recommended that a dedicated NIC is exploited for mirroring, but this is definitely an optional performance advantage. If you can reserve NICs for administrative access that is great also, but we do not always have these options at our disposal.

The Filestream feature is not supported in Mirroring, so if you are using this functionality, it may not be a good idea to continue its use if you require a High Availability solution.

The limitations of Standard Edition mean no snapshots, synchronous only, and redos single-threaded (slower).

There is no real point in having a witness server if you are using high performance mode!

You will have to pay in cost to the application being slow if you are in auto-failover mode because the 30-45 seconds for the commit to be dual, for example,

Auto failover requires high safety/synchronous and a witness: thus you cannot benefit from auto failover without these two conditions.

You can set permissions on each  - first see
select * from sys.endpoints
 Mirroring will be in the result set, or whatever name you use set your Instance-level mirroring session to.
Then you can decide to:
grant / deny connection to TSQL::NamedPipes
– or whatever connection type you do/don’t want.

For applications that connect to Mirrored Databases, please see my previous post on Failover Partner Connection String Setup.

Versions: you can use SQL 2008 and R2 and Express all together (hybrid environment) in a mirror quorum.  

To switch easily between servers with your Mirroring Scripts:
If you want to switch between servers during a script (much easier this way), make sure you have setup Aliases within the configuration tools/manager (if readability is your desire) so you can simply do this:

:Connect ServerName

Script for that Server

GO -- must end the batch

:Connect OtherServerName

Run script for that Server

GO

 

The Prerequisite : Query Execution By default, SQL Server options – use by default SQLCMD mode.

 

There is only one Mirroring endpoint per instance: we called ours IT_Mirror since it describes which department is using it, but this can be simply ‘Mirroring’ also.
When you have another instance, you’ll be using 5023, or 5024, etc…for the other instance, the number will need to be increased because the port cannot be shared.

There are no limits on Mirroring with SQL Server’s 64-bit Edition – but you do not want to mirror, for example, 50 sessions. That is merely too much, and too many threads will cause poor performance, unless your hardware budget it not an issue (and since we are in a recession, and people want High Availability for cheap, I am assuming NOT!).

Now that we have enabled SQLCMD, here's an example Mirroring Setup Script:

-- enable Query Execution By default, SQL Server options – use by default SQLCMD mode.

--Create endpoint on the principal server

:Connect Toronto

select name,role_desc,state_desc from sys.database_mirroring_endpoints

go

CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP ( LISTENER_PORT = 5022 )

    FOR DATABASE_MIRRORING (ROLE=PARTNER);

GO

--Create endpoint on the mirror server

:Connect Montreal

select name,role_desc,state_desc from sys.database_mirroring_endpoints

go

CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP ( LISTENER_PORT = 5023 )

    FOR DATABASE_MIRRORING (ROLE=PARTNER);

GO

--create endpoint on witness server

:Connect Ottawa

select name,role_desc,state_desc from sys.database_mirroring_endpoints

go

CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP ( LISTENER_PORT = 5024 )

    FOR DATABASE_MIRRORING (ROLE=WITNESS);

GO

 

--Security--

--Create logins on each server for the other two parties

--these are domain accounts

 

:Connect Toronto

USE master;

GO

--Partner

CREATE LOGIN [SQL2008Admin\svcSQL_Montreal] FROM WINDOWS;

GO

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Montreal];

GO

--witness

CREATE LOGIN [SQL2008Admin\svcSQL_Ottawa] FROM WINDOWS;

GO

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Ottawa];

GO

 

:Connect Montreal

USE master;

GO

--Partner

CREATE LOGIN [SQL2008Admin\svcSQL_Toronto] FROM WINDOWS;

GO

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Toronto];

GO

--witness

CREATE LOGIN [SQL2008Admin\svcSQL_Ottawa] FROM WINDOWS;

GO

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Ottawa];

GO

 

:Connect Ottawa

USE master;

GO

--Partner

CREATE LOGIN [SQL2008Admin\svcSQL_Toronto] FROM WINDOWS;

GO

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Toronto];

GO

--another partner

CREATE LOGIN [SQL2008Admin\svcSQL_Montreal] FROM WINDOWS;

GO

GRANT CONNECT on ENDPOINT::Mirroring TO [SQL2008Admin\svcSQL_Montreal];

GO

 

:connect Toronto

GO

ALTER DATABASE DB01 SET RECOVERY SIMPLE WITH NO_WAIT

GO

ALTER DATABASE DB01 SET RECOVERY FULL WITH NO_WAIT

GO

BACKUP DATABASE DB01 TO  DISK = N'E:\Backups\DB01.bak'

WITH NOFORMAT, INIT,  NAME = N'DB01-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

BACKUP LOG DB01 TO  DISK = N'E:\Backups\DB01.trn' WITH NOFORMAT,

no_truncate, INIT,  NAME = N'DB01-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

 

:connect Montreal

---NOW COPY DB BAK/TRN files to DB02 (UNC shared folder references could work too)

RESTORE DATABASE DB01 FILE = N'DB01' FROM  DISK = N'E:\Backups\DB01.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG DB01 FROM  DISK = N'E:\Backups\DB01.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

-- now right click on the database on DB01, Configure Security, use the domain account

-- Do Not Start Mirroring, click NO until you are sure that the FQDN is used.

-- make sure to have all the FQDN then click Start mirroring (high safety with auto failover)
-- OR, forget the darn GUI and use the below script :)

 

 

 

--enable mirroring first on the mirror

:Connect Montreal

ALTER DATABASE DB01

    SET PARTNER ='TCP://SQL2008Admin:5022';

GO

 

 

--then connect to the principal

:Connect Toronto

ALTER DATABASE DB01

    SET PARTNER = 'TCP://SQL2008Admin:5023';

GO

--set the witness on the principal

ALTER DATABASE Ottawa

    SET WITNESS = 'TCP://SQL2008Admin:5024';

GO

 

--- if every you need to (meaning you have lost the principal and witness, and need to get the Mirror to work):

:Connect Toronto

Alter Database DB01 set Partner FORCE_SERVICE_ALLOW_DATA_LOSS

go

If you know that a server site is isolated, and that the Witness will not be available to make the decision as to who should be the Prinicpal, only then should one use Force_Service_Allow_Data_Loss

New since 2008, thanks to Glen Berry for the script:
Check auto page repair history (again, SQL 2008 and 2008 R2 only)

SELECT DB_NAME(database_id) AS [database_name], database_id, file_id,

page_id, error_type, page_status, modification_time

FROM sys.dm_db_mirroring_auto_page_repair; 

-- if you get no rows, that’s a good thing J

If ever you need to resume mirroring (after pausing for network downtime/retart/etc), and if transactions are committed at the Principal (Toronto, in this setup) but the log was not sent to the former mirror server (Montreal, in this setup) instance, the transactions will be rolled back. This can occur after the following sequence of events: 

•         Connection between Mirror and Principal have been lost

•         Principal continues to commit transactions locally but cannot pass the log to mirror

•         For business continuity reasons (DRP) the Force_Service_Allow_Data_Loss on Mirror partner was used when you accepted to lose the transactions – meaning that the Principal is not coming back (long term connection loss, etc.).

•         Connection between partners is re-established, the original server becomes the mirror now, and mirroring is thus suspended.

Manual Failover – What Happens When I need to do a Failover?
There may be a situation when one has to reboot / update the Mirror Principal Partner Server, therefore, this can be done via the GUI (database properties-Mirroring) by simply clicking ‘Failover’, this will be proceeded by the following warning:

Prior to doing so, I would suggest checking the Activity Monitor to see that no big transactions are in progress on the current Principal database server host. If you do not check, you might receive an error 'The mirror instance is not caught up to the recent changes in the database 'DB01.' Unable to fail over.'  Microsoft SQL Server Error: 1422

Mirroring State Observations: Do Not Worry if it States Disconnected
During our recent disaster recovery exercise – where we purposefully shut off each server site from each other, and vice versa, the mirroring state was in disconnected mode. Do not worry about this if you application is running fine (thanks to the Failover_Partner in the connection string), because most likely, as soon as you go onto the actual server connections will behave properly.

Mirroing Script Setup Without Active Directory (using Certificates)

--Step 1

--Check whether we have database master key created for the master database on each server

--if you have it created, you should see a key named ##MS_DatabaseMasterKey##

:Connect Toronto

use master

go

Select * from sys.symmetric_keys

go

:Connect Montreal

use master

go

Select * from sys.symmetric_keys

go

:Connect Ottawa

use master

go

Select * from sys.symmetric_keys

go

 

--Step2

--Create the database master key for the master database

:Connect Toronto

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';

GO

:Connect Montreal

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';

GO

:Connect Ottawa

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';

GO

--Go execute Step 1 now

 

--Step 3

--Create certificates in the master database of each instance

:Connect Toronto

USE master;

CREATE CERTIFICATE Toronto_cert

   WITH SUBJECT = 'Toronto certificate';

GO

:connect Montreal

USE master;

CREATE CERTIFICATE Montreal_cert

   WITH SUBJECT = 'Montreal certificate';

GO

:Connect Ottawa

USE master;

CREATE CERTIFICATE Ottawa_cert

   WITH SUBJECT = 'Ottawa certificate';

GO

 

 

--Step 4

--Check that we have certificates created

:Connect Toronto

use master;

select * from sys.certificates

go

:Connect Montreal

use master;

select * from sys.certificates

go

:Connect Ottawa

use master;

select * from sys.certificates

go

 

 

--Step 5

--Check whether you have endpoints already created or not

:Connect Toronto

use master

select * from sys.database_mirroring_endpoints

go

:Connect Montreal

use master

select * from sys.database_mirroring_endpoints

go

:Connect Ottawa

use master

select * from sys.database_mirroring_endpoints

go

 

--Step 5.1

--If we don't have Endpoints, we will create them

:Connect Toronto

use master;

CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP ( LISTENER_PORT = 5022 )

    FOR DATABASE_MIRRORING (ROLE=PARTNER,AUTHENTICATION = CERTIFICATE Toronto_cert);

GO

--Create endpoint on the mirror server

:Connect Montreal

use master;

CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP ( LISTENER_PORT = 5023 )

    FOR DATABASE_MIRRORING (ROLE=PARTNER,AUTHENTICATION = CERTIFICATE Montreal_cert);

GO

--create endpoint on witness server

:Connect Ottawa

use master;

CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP ( LISTENER_PORT = 5024 )

    FOR DATABASE_MIRRORING (ROLE=WITNESS,AUTHENTICATION = CERTIFICATE Ottawa_cert);

GO

 

--Step 5.2 if they are already created. we will modify the authentication on them to use certificates

:Connect Toronto

use master;

alter endpoint Mirroring

for Database_Mirroring (ROLE=PARTNER,AUTHENTICATION = CERTIFICATE Toronto_cert);

go

:Connect Montreal

use master;

alter endpoint Mirroring

for Database_Mirroring (ROLE=PARTNER,AUTHENTICATION = CERTIFICATE Montreal_cert);

go

:Connect Ottawa

use master;

alter endpoint Mirroring

for Database_Mirroring (ROLE=WITNESS,AUTHENTICATION = CERTIFICATE Ottawa_cert);

go

 

--Step 6

--Backup the certificates from the servers to exchange them

:Connect Toronto

use master;

BACKUP CERTIFICATE Toronto_cert TO FILE = 'C:\Backup\Toronto_cert.cer';

GO

:Connect Montreal

use master;

BACKUP CERTIFICATE Montreal_cert TO FILE = 'C:\Backup\Montreal_cert.cer';

GO

:Connect Ottawa

use master;

BACKUP CERTIFICATE Ottawa_cert TO FILE = 'C:\Backup\Ottawa_cert.cer';

GO

 

--Step 7

--Create logins.

--One each server we will create logins to be used by other instances to login to this instance

:Connect Toronto

Create login Ottawa_login With Password='Password1'

Create login Montreal_login with password='Password1'

go

:Connect Montreal

Create login Toronto_login With Password='Password1'

Create login Ottawa_login with password='Password1'

go

:Connect Ottawa

Create login Toronto_login With Password='Password1'

Create login Montreal_login with password='Password1'

go

 

--Step 8

--Create users for these logins in the master database

--Associate these users with certificates

--Be aware of the ACL issue.

:Connect Toronto

Create user Ottawa_user for login Ottawa_login

Create user Montreal_user for login Montreal_login

go

CREATE CERTIFICATE Ottawa_Cert

   AUTHORIZATION Ottawa_user

   FROM FILE = 'C:\Backup\Ottawa_cert.cer'

go

CREATE CERTIFICATE Montreal_Cert

   AUTHORIZATION Montreal_user

   FROM FILE = 'C:\Backup\Montreal_cert.cer'

GO

:Connect Montreal

Create user Toronto_user for login Toronto_login

Create user Ottawa_user for login Ottawa_login

go

CREATE CERTIFICATE Toronto_Cert

   AUTHORIZATION Toronto_user

   FROM FILE = 'C:\Backup\Toronto_cert.cer'

go

CREATE CERTIFICATE Ottawa_Cert

   AUTHORIZATION Ottawa_user

   FROM FILE = 'C:\Backup\Ottawa_cert.cer'

GO

:Connect Ottawa

Create user Toronto_user for login Toronto_login

Create user Montreal_user for login Montreal_login

go

CREATE CERTIFICATE Toronto_Cert

   AUTHORIZATION Toronto_user

   FROM FILE = 'C:\Backup\Toronto_cert.cer'

go

CREATE CERTIFICATE Montreal_Cert

   AUTHORIZATION Montreal_user

   FROM FILE = 'C:\Backup\Montreal_cert.cer'

GO

 

--Step 9

--Grant connect permission on each EndPoint for logins for the other servers

:Connect Toronto

use master;

Grant Connect on EndPoint::Mirroring to Ottawa_login

Grant Connect on EndPoint::Mirroring to Montreal_login

go

:Connect Montreal

use master;

Grant Connect on EndPoint::Mirroring to Toronto_login

Grant Connect on EndPoint::Mirroring to Ottawa_login

go

:Connect Ottawa

use master;

Grant Connect on EndPoint::Mirroring to Toronto_login

Grant Connect on EndPoint::Mirroring to Montreal_login

go

 

 

--REPEAT BACKUP/RESTORE AND ALTER OPERATIONS IN PREVIOUS AD SCRIPT

As usual, I hope you enjoy Mirroring as much as I do.  It is very much set it and forget it - just make sure your intensive DBs have Transaction Log backups frequently - since Full Logging has to be on for Mirroring.

For an even Deeper Dive, here is a free Book on SQL 2008 Mirroring, thanks to the great work of Robert L. Davis and Ken Simmons.