4 ways to efficiently monitor DB Mirroring with/without email/SMS Alerts

A setup of DB Mirroring (See my post here) cannot be full without
a setup of DB Mirroring monitoring.
I want to share with you 4 ways to monitor the DB Mirroring:

1) Querying the "sys.database_mirroring" catalog view and sending Email Alerts.
2) Using SQL Server Events Notifications, State-change (WMI) events.
3) Setting up Performance Threshold Events in the "Database Mirroring Monitor".
4) Setting up SQL Performance counters related to DB Mirroring.

Now more in details:

1) Querying the "sys.database_mirroring" catalog view and sending Email Alerts

We can query the sys.database_mirroring catalog view for checking the status
of the mirrored databases, and in case the DB status is not in valid state, like
SYNCHRONIZED or SYNCHRONIZING, we will send an email and/or SMS.

DECLARE @DBMirroringState VARCHAR(30),@DB_ID INT,@ErrorMessageToSend VARCHAR(100)
DECLARE @MirroredDatabases TABLE (DatabaseID INT, mirroring_state_desc VARCHAR(30))

-- get status for mirrored databases
INSERTINTO @MirroredDatabases(DatabaseID,mirroring_state_desc)
SELECT database_id, mirroring_state_desc
FROM    [sys].[database_mirroring]
WHERE  mirroring_role_desc IN ('PRINCIPAL','MIRROR')
             AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')

WHILE EXISTS (SELECT TOP 1 DatabaseID FROM @MirroredDatabases WHERE mirroring_state_desc IS NOT NULL)
BEGIN
     SELECT TOP 1
                   @DB_ID = DatabaseID,
                   @DBMirroringState = mirroring_state_desc
     FROM    @MirroredDatabases

    SET @ErrorMessageToSend = 'DBMirroring Error on DB:'+CAST(DB_NAME(@DB_ID) AS VARCHAR)+
                                           ',DBState='+@DBMirroringState

   -- Send Email
   EXEC msdb.dbo.sp_send_dbmail @profile_name='XXXXX',@recipients='DBA@Company.com',
                                              @body = @ErrorMessageToSend,@subject = @ErrorMessageToSend
  -- Send SMS
  -- put here code to send SMS

   DELETE FROM @MirroredDatabases WHERE DatabaseID = @DB_ID
END

Source: http://www.mssqltips.com/tip.asp?tip=1859

--------------------------------------------------------------------------------------------------------------------------------------------------------------
The second way is:
2) Using SQL Server Events Notifications, State-change (WMI) events
--------------------------------------------------------------------------------------------------------------------------------------------------------------
There is a way in the SQL Server to create an Alert for various inner events.
We will create an alert for specific DB mirroring events.

When a mirroring session changes from one state to another, SQL Server generates
a WMI event of type DATABASE_MIRRORING_STATE_CHANGE.
We will create WMI-based alert by querying the DATABASE_MIRRORING_STATE_CHANGE.

For example:
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6

In the Principal Server we will check for following events:

   - Connection with Mirror Lost
   - Mirroring Suspended

In the Mirror Server for following alerts:

  -  Connection with Principal Lost
  -  Mirroring Suspended

You can setup such alerts via the SSMS GUI or by executing a script.
In SSMS, go to "SQL Server Agent", right click on Alerts and choose "New Alert"
and define it as follow, for example:

And do not forget to specify to send the alert email:

or you can run a script, for example:

USE [msdb]

GO
EXEC msdb.dbo.sp_update_alert
         @name=N'DB Mirroring State Change: Connection with Mirror Lost',
         @message_id=0,
         @severity=0,
         @enabled=1,
         @delay_between_responses=0,
         @include_event_description_in=1,
         @database_name=N'',
         @notification_message=N'',
         @event_description_keyword=N'',
         @performance_condition=N'',
         @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
         @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6',
         @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification
         @alert_name=N'DB Mirroring State Change: Connection with Mirror Lost',
         @operator_name=N'DBA', @notification_method = 1
GO

Source: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/how-to-monitor-database-mirroring

3) Setting up Performance Threshold Events in the "Database Mirroring Monitor"

With these alerts, most of us should be familiar.
We can setup the thresholds in the "Database Mirroring Monitor":

As the result, SQL server will create "Database Mirroring Monitor Job" on
Principal and Mirror servers. This job will run every minute (by default) and update
undocumented database mirroring status table in the msdb database.

Now, after setting the thresholds, you need to create alert(s) using SQL Server Agent:

1. In SSMS, connect to the principal or mirror server instance for which you want to define an alert.
2. Expand the SQL Server Agent folder in Object Explorer.
3. Right-click Alerts and select New Alert.
4. In the New Alert dialog box, type a name you can use to identify the alert, such as "DB Mirroring: Unsent log warning."
5. Select Error number and type the number from Table below.
6. In Select a page, click Response to specify the actions to take when the event occurs, such as executing a job or notifying an operator.
7. In Select a page, click Options to specify how often to send a notification on this event, the format of the message sent to operators, and other options.

 Event IDs for Performance Warnings :

The first two warnings in table above should be defined in the Principal Server,
the rest in the Mirror Server.

Now very important note that can save you time,

When you define such alerts, do not specify the database, choose "All Databases",

Otherwise the alert will not occur (not fire) and the email will not be sent.
The SQL Server will write the alert to the Windows Event log and to
SQL Server Error Log, but SQL Server will not be able to recognize it
and will not raise the alert, because it has some bug recognizing such alerts

where database name was specified.
(The event logged as a server-level event and not a database-level event)

I have this bug in SQL Server 2008 R2 ENT with CU6 and Microsoft does not
provided a workaround yet.

http://connect.microsoft.com/SQLServer/feedback/details/657230/alerting-on-database-mirroring-events

If you have more that one mirrored database in one SQL Server instance and you
want to define the alerts per database, then you will have a problem when you
choose "All Databases".

Also when the alert defined with "All Databases", an email is sent without

specifying the database that exceeded its threshold.

4) Setting up SQL Performance counters related to DB Mirroring.

We can fetch SQL performance counters related to DB Mirroring from DMV called

"sys.dm_os_performance_counters".

I recommend to fetch following counters:

- Redo Queue KB :         The number of bytes of transaction log that remains to be

                                          applied to the mirror to roll it forwards.

                                              

- Log Send Queue KB:   The number of bytes in the log that has not been sent to

                                         the mirror

SELECT   [counter_name] as CounterName,[cntr_value] as CounterValueFROM      sys.dm_os_performance_counters

WHERE   [object_name] LIKE ('%Database Mirroring%')

                 AND [counter_name] IN ('Log Send Queue KB','Redo Queue KB')

                 AND [instance_name]='TestDB'

 

For example, you can query this DMV every minute and save the values in CACTI.

In the Cacti you will see nice graphs:

This method is for long run monitoring, to recognize trends and etc.

If you have other way, please share.

One more thing that good to know and understand:

In asynchronous mirroring the transaction log can fill up as transactions
cannot be backed up until they have been applied on the mirror.

For more info:

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

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

http://technet.microsoft.com/en-us/library/cc966392.aspx

Is the % of an index fragmentation in Log shipped / Mirror database equal to the Primary/Source database?

Suppose there is Log shipping or Mirror between database X and database Y.
The mdf/ldf file(s) of the X DB and Y DB located in different storages.
I take some index in the primary db X and estimate its fragmentation by
using “dm_db_index_physical_stats” and do the estimation of that index
also in database Y.

SELECT  dm.object_id AS objectid,              
               dm.index_id AS indexid,
               QUOTENAME(i.name) AS name,
               dm.partition_number AS partitionnum,
               dm.avg_fragmentation_in_percent AS Fragmentation,
               i.fill_factor AS [Fillfactor],
               dm.page_count,
               CASE WHEN dm.alloc_unit_type_desc <> 'IN_ROW_DATA'
                        OR (t.lob_data_space_id = 1 AND dm.index_type_desc <> 'NONCLUSTERED INDEX')
                       THEN 'OFF' ELSE 'ON'
              END as [Online]
FROM    [sys].[dm_db_index_physical_stats] (DB_ID(), NULL, NULL , NULL, 'LIMITED') dm
              INNER JOIN [sys].[tables] t ON dm.object_id = t.object_id
              INNER JOIN [sys].[indexes] i ON dm.object_id = i.object_id AND dm.index_id = i.index_id
WHERE  dm.avg_fragmentation_in_percent > 0
              AND dm.page_count > 1000
              AND dm.index_id > 0

Let`s assume that the db X and Y are fully synchronized,
meaning all transactions from db X were transferred to db Y.

The question is: Is the % of the index fragmentation equal in db X and Y?

The answer is Yes.
Because every log record gets replayed on the secondary.
Every page split and every allocation is fully logged and then replayed
exactly the same on the secondary.

You may ask, what for you need to know the % of fragmentation
on secondary server. Well, first of all, estimation of the % of fragmentation
by using the “dm_db_index_physical_stats” in Production site may cause to
IO load. So we can estimate the fragmentation on the secondary server.
Secondary, it is always good to know and understand how it is working.

Here more info:
http://www.sqlsoldier.com/wp/sqlserver/whatdoesredonemean

Database Mirroring between a Server in Domain and Server outside domain (workgroup)

When you setup a Database Mirroring via the GUI in SSMS, you are
requested to specify the fully qualified domain names (FQDN) of the servers.
What about the cases when you need to configure the Database Mirroring

between two servers that not in a domain at all or the 2 servers on different

domains with no trust relationships or one server in domain and

the second is not. The setup of a DRP site whould be classic example.
You can enter IP addresses in the GUI wizard or

replace the IPs with some Server names that you put in the HOST file

it will work only if both servers in the same domain, in other cases you will

probably fail to setup the Mirroring, because SQL Server will try to use

Windows Authentication between the servers (By default the Configure

Database Mirroring Security Wizard always uses Windows Authentication)

You will get an error like this:

  

The server network address“TCP://SQLServer:5023″ cannot be reached or

does not exist.Check the network address name and that the ports for the local

and remote endpoints are operational.(Microsoft SQL Server, Error: 1418)

 

So how to setup the Database Mirroring when the two Servers not in the same

domain or the two servers are workgroups or one server in a domain and other not?

Well, SQL Server allows configuring database mirroring under mixed mode

authentication using SQL Server logins with the added security of using certificates. 

In English, you will have to create SQL Server Logins/Database Users and

the Mirroring EndPoint with authentication that use Certificate and not

Windows Authentication.

Note: The explanation below is dealing with mirroring configuration without
the witness server, please refer to links below if your configuration includes
witness server, in general all the steps for Principal/Mirror server should be
done also for the witness server.

Here is the highlight summary of the steps:
All the steps below should be done in each server instance(Principal/Mirror)
SQL scripts for each step you can find at the end,
and I suppose the you already restored the DB (and transaction logs, if needed)
in Mirror SQL Server with NORECOVEY of course.

1.In the master database, create a database master key.
2.In the master database, create an encrypted certificate.
3.Create a mirroring endpoint using the created certificate.
4.Back up the certificate to a file and copy it to the other server.

After you complete the steps above in both servers, do the following
also in both servers in master database:

5.Create a SQL Server login in each Server
6.Create a database user for created login.
7.Associate the certificate with the database user created in step 6.
8.Grant CONNECT permission on the SQL Server login for created
   mirroring endpoint.

Now let`s see the SQL Scripts,
note that the scripts have different numeration then general steps above.

-------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance */
-------------------------------------------------------------------------------------------------------
-- Step 1 :
--                    CREATE MASTER KEY,CREATE CERTIFICATE
--                   CREATE ENDPOINT,BACKUP CERTIFICATE to file
---------------------------------------------------------------------------------
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
 --Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE PrincipalServerCertificate
WITH SUBJECT = 'PrincipalServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
 --Create the database mirroring endpoint for the principal server using the certificate for authentication
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE PrincipalServerCertificate
,ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE PrincipalServerCertificate
TO FILE = 'c:\PrincipalServerCertificate.cer'
GO

 --------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Mirror Instance */
---------------------------------------------------------------------------------------------------------------------------
-- Step 2 :
--              CREATE MASTER KEY,CREATE CERTIFICATE,
--              CREATE ENDPOINT,BACKUP CERTIFICATE to file
----------------------------------------------------------------------------------
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
--Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE MirrorServerCertificate
WITH SUBJECT = 'MirrorServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE MirrorServerCertificate,
ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE MirrorServerCertificate TO FILE = 'c:\MirrorServerCertificate.cer';
GO

------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance.    
     The MirrorServerCertificate.cer needs to be copied on the Principal Server.
*/
-------------------------------------------------------------------------------------------------------------------------
-- Step 3 :
--               CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Mirror server)
--               and grant the created User to it.
------------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN MirrorLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER MirrorUser FOR LOGIN MirrorLogin
GO
CREATE CERTIFICATE MirrorServerCertificate AUTHORIZATION MirrorUser
FROM FILE ='c:\MirrorServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [MirrorLogin]
GO


/* Execute this against the Mirror Instance.  
    The PrincipalServerCertificate.cer needs to be copied on the Mirror Server.
*/
----------------------------------------------------------------------------------------------------------------------
-- Step 4 :
--                CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Principal server)
--                and grant the created User to it.
----------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN PrincipalLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER PrincipalUser FOR LOGIN PrincipalLogin
GO
CREATE CERTIFICATE PrincipalServerCertificate AUTHORIZATION PrincipalUser
FROM FILE = 'c:\PrincipalServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [PrincipalLogin]
GO

/* Execute this against the Mirror Instance.*/
------------------------------------------------------------------------------------------------
-- Step 5 :
--             Prepare the mirror server for the database mirroring session
------------------------------------------------------------------------------------------------
ALTER DATABASE TESTDB
SET PARTNER = 'TCP://PrincipalServerIP5022'
GO

/* Execute this against the Principal Instance.*/
-------------------------------------------------------------------------------------------------
-- Step 6 :
--            Prepare the Principal server for the database mirroring session
-------------------------------------------------------------------------------------------------
ALTER DATABASE TestDB
SET PARTNER = 'TCP://MirrorServerIP:5023'
GO
-- Set the Mirroring to Asynchronous mode (if needed)
ALTER DATABASE TestDB SET PARTNER SAFETY OFF
GO

That`s all after Step 6 the mirroring shoul be initialiazed.

Troubleshooting:

1)  Ensure the Principal and Mirror Server listening to defined mirroring endpoints.
     They should be listening after you define the endpoints and their state is Started.
      You can use the following command in Principal Server
       netstat -na | find "5022"
       and this in Mirror Server
       netstat -na | find "5023"

       Of course, you can set the port numbers to be different then in this example.

2) Make sure firewall is not blocking the port and the firewall is allowing traffic both directions

3) Check the SQL log for errors


Mirroring Monitoring problems:

In our case, which is, Principal Server in a Domain and the Mirror Server
is not, when launching the "Database Mirroring Monitor" we had the following error:
  














If the Monitor was executed in the Principal Server, then there was an error
saying that the Mirror Server not connected. And vice versa when the Monitor
was launched from Mirror Server.
Going to "Manage Server Instance Connections" option

and choosing SQL Authentication in the connection of Mirror Server not helped,
it gave the following errors

or
"SQL Server replication requires the actual server name to make a connection to
the server. Connections through a server alias, IP address, or any other alternate
name are not supported."

It seems that SQL Server tries to connect to the Mirror server by using
the Server Name and not IP.
Adding the mapping to HOST file solved the problem.

---------------------------------------------------------------------------------------
To see how to monitor the DB Mirroring from every angle,
please my post here.
---------------------------------------------------------------------------------------
Also, I want to share the Clean up scripts,
they are needed when the mirroring is not working and you want to start
over again or if you are not sure in each step you stopped and etc.

-----------------------------------------------------------------------
-- Clean up / check scrips for the PRINCIPAL/MIRROR SQL Server
-----------------------------------------------------------------------
/*
------------------------------------------------------------------------------------------------------------
Drop master key
----------------------------------------------------------------------------------------------------------

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

-- check if the database master key is encrypted by the service master key.
SELECT is_master_key_encrypted_by_server ,*
FROM sys.databases
WHERE name='master'

select name, principal_id, algorithm_desc, create_date
from master.sys.symmetric_keys
where name like '%databasemaster%'


DROP MASTER KEY

--------------------------------------------------------------------------------------------------------
-- DROP ENDPOINT
--------------------------------------------------------------------------------------------------------

DROP ENDPOINT MirroringEndPoint

SELECT name, role_desc, state_desc
FROM sys.database_mirroring_endpoints

--------------------------------------------------------------------------------------------------------
-- DROP CERTIFICATE
--------------------------------------------------------------------------------------------------------


DROP CERTIFICATE PrincipalServerCertificate
DROP CERTIFICATE MirrorServerCertificate

SELECT *
FROM sys.certificates

-------------------------------------------------------------------------------------------------------
-- DROP USER
-------------------------------------------------------------------------------------------------------

DROP USER MirrorUser

SELECT * FROM sys.sysusers;

--------------------------------------------------------------------------------------------------------
-- DROP LOGIN
-------------------------------------------------------------------------------------------------------

DROP LOGIN MirrorLogin

SELECT * FROM sys.server_principals 

*/

For more information see following links
http://technet.microsoft.com/en-us/library/ms191477.aspx

http://www.mssqltips.com/tip.asp?tip=1705

http://blogs.msdn.com/b/john_daskalakis/archive/2009/01/15/9320483.aspx

How to upgrade to SQL Server 2008 R2 with minimal downtime: 1-2 minutes

Here is real life example,
how to upgrade SQL Server 2005
to SQL Server 2008 R2 with
less than 2 minutes downtime
and without any connection
string changes.
Sounds interesting?
Then keep reading.





Site configuration:
Active/Passive failover cluster with SQL Server 2005
Enterprise Edition.

Generally, this is the way:
Break Cluster, leave one node in the cluster(Node X),
install new cluster on second node Y,
setup SQL 2008 in the second node Y,
setup mirroring between node X and node Y,
do mirroring failover,switch between the clusters
(assign IP of original cluster to the new cluster),
install SQL 2008 on Node X, join Node X to the new cluster.

Below is high level work plan step by step,
I will not elaborate on system related aspects,
I will just mention the steps:

Let suppose Node X is Active.

1) Break the cluster, remove passive node Y from the cluster

2) If you have Domain Controller installed on Node X/Y machine,
    you will have to remove it, because SQL 2008 cannot be
    installed on Domain Controller machine, you will need to
    install the DC on other machine.
    Remove the Node Y from old domain, Join it to new domain.

3) Define new Cluster on Node Y with different name.

4) UnInstall SQL Server 2005,Install SQL Server 2008 R2 on Node Y
   (Cluster installation type)
    Create Logins with Same SIDs,Jobs and etc.

5) Setup Mirroring between Node X and Node Y, in async or sync
    mode, yep mirroring between SQL 2005 and 2008, it works fine

6) Do manual Mirroring failover (recommended  changing to
    synchronized mode before the failover). Remove the Mirroring.
    2-5 seconds downtime.

7) Switch the Clusters, assign the IP of old cluster to the new
    Cluster, change the DNS records.
    1 minute downtime.

8)  That`s all, the SQL is up and ready to get connections.
     As you can see, there is no need to change any connection
     strings in the applications.

9) Now complete work on Node X, UnInstall SQL 2005,
    install SQL 2008, join it to new Cluster.

Again, this is in high level.

Important notes:

a. When you finish the manual Mirroring failover, there is no way
    back to SQL Server 2005.

b. Check if you can allow some time period without Cluster protection.

Thanks for reading