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.
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:
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:
-- OR, forget the darn GUI and use the below script :)
Check auto page repair history (again, SQL 2008 and 2008 R2 only)
-- if you get no rows, that’s a good thing J
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:
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.