How to configure SQL Server database mirroring between 2 instances on a standalone server
There are lots of troubleshooting articles about how to diagnose connectivity problems when setting up database mirroring, especially errors such as these:
Msg 1418, Level 16, State 1, Line 2
The server network address "tcp://mirrortest.com:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance
but here's something a little different.
Imagine that you have a server which is not connected to a domain. It might just be a standalone machine, it could be a virtual machine that you've just built. I've done this a few times in virtual environments to do demos on laptops and the like. It's a not a production scenario that you'd come across really, but it works really well in test.
The questions that usually come up are how can I configure the partners (and endpoints) without having a FQDN, since I'm not connected to a domain, I'm just in "WORKGROUP". SQL Server still wants you to use FQDN formatting for the partner addresses. When you're trying to configure it, you might have even encountered an error like this when trying to start mirroring (depending on exactly how you did the setup)
Database Mirroring login attempt failed with error: 'Connection handshake failed.
There is no compatible authentication protocol. State 21
What you need to do to be able to set this up is as follows:
1. adding "local" as the primary DNS suffix as in detailed this article here (ok it's about exchange server but it works the same)
(remember to reboot)
2. You can now specify FQDN formatted names for the local machine in the format:
For the principal:
For the mirror
(Or other port numbers of your choice, but remember that they'll need to be different)
3. You'll need these ports as exceptions in Windows Firewall if you're running it (I was even though it was a local machine!)
4. My endpoints are setup like this (but I just did them through the GUI) there's nothing exceptional here:
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)
You can now run some cool tests and demos without ever being connected to a network.