Home / server / Questions / 35088
Accepted
jishi
Asked: 2009-07-03 07:10:48 +0800 CST

SQL Server 2005 mirroring 2-1

  • 772

I want to setup a single instance of sql-server as a mirror-backup for 2 production-servers, and I need to use certificate authentication because they are not part of an AD-domain.

Reading the documentation, it claims this:

All mirroring connections on a server instance use a single database mirroring endpoint, and you must specify the authentication method of the server instance when you create the endpoint. Therefore, you can use only one form of authentication per server instance for database mirroring.

does this mean that it's impossible to use one instance, to backup databases from two other instances?

Since I need to create certificates on all three machines (if I understand it correctly), and I use certificates per endpoint. Shouldn't I be able to create two endpoints on my backup-machine that uses different certificates?

sql-server mirroring certificate

2 Answers

  1. Best Answer

    You can only create a single database mirroring endpoint on that instance.

    You should be able to simply create a certificate on your backup machine, and create the endpoint based on that cert. Then backup the cert and import it into both production servers. On each production server create a cert (each with different names) then backup and restore that cert to the backup server.

    At that point you should be ready to start database mirroring.

    • 1

  2. The certificates represent endpoint identities. An instance can have only one endpoint for mirroring, and that endpoint can be bound to a single certificate and private key. All peers the endpoint is connecting to must have a copy of the same certificate (just the public key) that is associated with a login that is granted CONNECT permission on the endpoint. This deployment is always symmetric.

    You can read on my blog exactly how mirroring endpoint authentication works.

    So in a case involving one instance (A) connecting to 2 other instances (B and C):

    1. All three instances have their own certificate A->Ca, B->Cb, C-Cc, with private key, that represents each endpoint's identity.
    2. All three instances export their respective certificates (public key only)
    3. B imports A's certificate Ca and grants CONNECT permission on B'e endpoint to Ca's owner
    4. A imports B's certificate Cb and grants CONNECT permission on A's endpoint to Cb's owner
    5. C imports A's certificate Ca and grants CONNECT permission on C's endpoint to Ca's owner
    6. A imports C's certificate Cc and grants CONNECT permission on A's endpoint to Cc's owner
    • 0