次の方法で共有

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.

or

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)

https://technet.microsoft.com/en-us/library/aa997119.aspx

(remember to reboot)

2. You can now specify FQDN formatted names for the local machine in the format:

For the principal:

TCP://MIRRORTEST.LOCAL:5022

For the mirror

TCP://MIRRORTEST.LOCAL:5023

(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]

AUTHORIZATION [MIRRORTEST\Administrator]

STATE=STARTED

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.

Comments

  • Anonymous
    December 08, 2008
    I've been posting in the MSDN and TECHNET mirroring forums in the last few days and I've noticed a lot