Geek Speak.

I have been working pretty extensively with SQL Server Mirroring in Microsoft SQL Server 2005. It seems to work pretty effectively, though setup outside a domain configuration is convoluted to say the least.

I have posted in the past about some problems I have had setting it up. My difficulties with the configuration aspect actually generated KB940254. The writeup for this particular KB seems kind of convoluted but the long and short of it is that you must use the actual machine name and not arbitrary DNS or Host file entries for mirroring configuration.

SQL Sever 2005 Mirroring Gotcha - Expiring Certificates

Well as we have plugged away with more of our clients using SQL Server Mirroring we have approached 2 more interesting issues. The first one is that, by default, when using SQL Server mirroring with certificates for authentication and encryption, SQL server certificates will expire after one year.

After your certificates expire SQL Server Mirroring will fail, rather ungracefully, if either the principal or mirror instance fails for any reason. And, if the server that fails happens to be the principal instance, your whole mirror instance dies and all your mirror databases go offline, the mirror does not recover even if you have configured a witness. Effectively defeating the whole purpose of having a mirror configured.

You can create certificates that don't expire at the default 1 year time period by altering your "Create Certificate" command in the following way:

CREATE CERTIFICATE db01_certificate WITH SUBJECT = 'db01 certificate', EXPIRY_DATE = '12/31/2028';
GO

SQL Server 2005 Mirroring Limitation - Maximum Mirrors?

The second thing to note about SQL Server Mirroring is, although Microsoft "recommends" a maximum of 10 concurrent SQL Mirroring sessions per SQL Server instance. They say there are no "hard limits" to the number of database instances you can configure.

What they really mean to say is that there are hard limits, they just don't feel like explaining them to you. I have asked Microsoft to issue a documentation update or a KB article describing these limitations and am awaiting an official response, but here is the information anyway while we wait for them.

Mirroring is only limited by the number of threads available to the SQL server process and by the hardware resources available to your machine. SQL Mirroring uses 5 process threads per mirroring instance.

So how do you know how many threads are available to your particular revision of SQL server? Well I got this handy formula from Bob Duffy's blog over at MSDN.

The following table shows how many threads are available in the default SQL 2005 Configurations

   CPUS      32bit     64bit
==============================
=   <=4   =   256   =   512  =

==============================

=    8    =   288   =   576  =

==============================

=   16    =   352   =   704  =

==============================

=   32    =   480   =   960  =

==============================

The formula to determine the max worker processes is as follows:

For x86 systems where total number of logical processors <=4
               # max worker threads = 256

Otherwise:
               # max worker threads = 256 + ((# Procs – 4) * 8)

For x64 systems where total number of logical processors  <= 4
               # max worker threads = 512
Otherwise
               # max worker threads = 512 + ((# Procs – 4) * 16)

On a client's 8 processor 64bit SQL Server instance our mirroring began to flake out and behave abnormally around in the default configuration around the 100th database mark. Changing the default SQL server configuration from 0, which is described as dynamic but isn't truely dynamic, to 750 threads has allowed us to continue to expand the available mirroring configuration without any appreciable negative impact to either SQL Mirroring node.

You can configure the max thread count by connecting to the SQL server instance with Management Studio, right clicking the SQL server and selecting "Properties". Then clicking "Processors" and changing the "Max worker threads" value to something other than 0.