Mirroring dedicated traffic

We are using the following configuration:

2 stand alone Windows 2003 servers 64 bit.

Each have two Gig Network cards configured with different IP addresses.

Each running multiple instances of SQL Server 64.

I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.

I am also trying to avoid Windows authentication.

Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...

Would someone please point me in the right direction?

Thank you.

Use certificate authentication. Look up "database mirroring [SQL Server], certificates" in Books Online.|||

Thank you very much for your response.

That is definetely a lot of information.

As about "database mirroring, certificates" - there is no such a topic in BOL. There is a "Setting Up database mirroring using certificates (Transact SQL)". That is a great topic! However, I thought that my question was specific? Sorry if not. I asked about dedicating NIC No.2 to a traffic generated by mirroring process. Unfortunately, topic mentioned by you does not have anything about TCP/IP; adding port numbers and/or netowrk cards to the mirror. (Actually, it is roughly covering even the main topic since witness installation totally skipped).

And if we will go here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx and go down to the networking section we will read a great article:

Q.

How do I specify a specific network adapter/network just for mirroring?

A.

Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.

I can' believe that nobody didn't get to the point where he will have several NICs on the server? Is it such an exclusive installation?

|||

Thank you for all your help.

Resolved.

|||Is another way to do this just to change the hosts file on the Servers to point to the other network address as opposed changing the name?|||

You can simply bind the mirroring endpoint to listen only on the dedicated NIC's IP. This will apply to outgoing traffic as well as incomming.

alter endpoint [endpointname] as TCP (listener_ip = (<4 part ip of dedicated NIC>));

|||

Mark,

simple 'on surface' type of decision is not always a correct one. What is the sense in certificate based security and ecryption if you are exposing your DB network structure ? Wouldn't it be a security risk?

|||

Sorry, Remus.

I marked your response as an answer. It is incorrect though. (I am novice here and do not know how to reverse it).

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic. Question was; how to separate the mirroring traffic through a separate NIC?

And solution is:

ALTER DATABASE [DB] SET PARTNER = 'TCP://<4part.IP.address>:<port_number>'

repeating this statement on both partners (principle and mirror) and then submitting

ALTER DATABASE SET [DB] WITNESS= 'TCP://<4part.IP.address>:<port_number>'

on principle or on mirror.

|||

I though you want to separate mirroring traffic from user connections traffic (TDS). Separting witness traffic from partner traffic is somehow unusual considering that connectivity with the witness is just as important as connectivity with the partner, otherwise why have a witness in the first place?

HTH,
~ Remus

|||

Glen Sidelnikov wrote:

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic.

There are many enpoints in an instance: user connection endpoints (shared memory, named pipes, TCP), mirroring endpoint, service broker endpoint, HTTP enpoints. The settings for mirroring endpoint and user endpoints are distinct, therefore you can set the user endpoints (TDS listenning port) on one NIC and the mirroring endpoint on another NIC.

Another way to achieve this is to listen on all NICs, but rely on underlying IP to choose the appropiate NIC. That is, set the partner/witness names to a name that is only reachable through the desired NIC.

HTH,
~ Remus