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