February 27, 2014 at 7:33 am
Hi!
How do I create endpoint for withess server if it reside on the sane physical server as mirror?
February 28, 2014 at 3:55 am
Hi,
I don't think there is any restriction on running the Witness & Mirror on the same server (although it is not a recommended configuration).
Follow the guidance here:
"How to: Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)"
http://technet.microsoft.com/en-us/library/ms190430%28v=sql.100%29.aspx
February 28, 2014 at 8:24 am
You can put the witness on the mirrored server but that's not generally a good idea. I believe, and someone else can probably confirm, that you can use any version of sql server 2008, such as express (which is free) and put it on a separate server just for witnessing.
February 28, 2014 at 8:41 am
I created named instance. So I am going to have mirror server on default SQL server and witness on named instance. But I think they should be listening to different ports. Right?
March 3, 2014 at 5:23 am
Just in case you are not aware: each Instance will listen on a TCP port (either fixed or dynamic) for standard database connections.
Separate to this, you need to create a Mirroring Endpoint for each Instance. Each Mirroring Endpoint also listens on a fixed TCP port. If two Instances are running on the same server, then obviously different TCP Ports will be required for each of the Mirroring Endpoints.
March 4, 2014 at 7:48 am
Here is my problem. I have 3 instances of SQL Server. Two of them reside on the same physical server:
Server1 – principal database server, TCP port 1430
Server2 – mirror database server, TCP port 1440
Server3 – witness database server, TCP port 1600,
Where Server2 and Server3 are on the same physical server. The problem is it is not allowing me to create separate endpoints for those two servers.
This is what I run on Server2:
CREATE ENDPOINT endpoint4mirror
STATE = STARTED
AS TCP ( LISTENER_PORT = 1440,LISTENER_IP=ALL )
FOR DATA_MIRRORING (ROLE=PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
--VERIFY IF IT WAS CREATED CORRECTLY
SELECT NAME,TYPE_DESC, PORT,IP_ADDRESS FROM SYS.TCP_ENDPOINTS
This is result:
NAMETYPE_DESCPORTIP
Dedicated Admin ConnectionTSQL0NULL
TSQL Default TCP TSQL0NULL
endpoint4mirror DATABASE_MIRRORING1440NULL
This is what I run on Server3:
CREATE ENDPOINT endpoint4mirror
STATE = STARTED
AS TCP ( LISTENER_PORT = 1600,LISTENER_IP=ALL )
FOR DATA_MIRRORING (ROLE=WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
--VERIFY IF IT WAS CREATED CORRECTLY
SELECT NAME,TYPE_DESC, PORT,IP_ADDRESS FROM SYS.TCP_ENDPOINTS
This is result:
NAMETYPE_DESCPORTIP
Dedicated Admin ConnectionTSQL0NULL
TSQL Default TCP TSQL0NULL
endpoint4mirror DATABASE_MIRRORING1600NULL
Now, if I go back to Server2 to check endpoint, I will find that endpoint that I created before had been replaced with one on Server3:
SELECT NAME,TYPE_DESC, PORT,IP_ADDRESS FROM SYS.TCP_ENDPOINTS
This is result:
NAMETYPE_DESCPORTIP
Dedicated Admin ConnectionTSQL0NULL
TSQL Default TCP TSQL0NULL
endpoint4mirror DATABASE_MIRRORING1600NULL
March 5, 2014 at 2:57 am
I think maybe you are confusing yourself with the terminology of "server" to mean the physical hardware, the operating system and the MSSQL Instance.
Maybe if you ran through your procedure again, clearly labelling each CREATE script for a specific INSTANCE, the situation would become clearer.
Each Instance can only have one Mirroring Endpoint. This single Endpoint can be used for multiple databases (i.e. multiple mirrors). I cannot see how creating an endpoint on Instance 3 would affect an endpoint on Instance 2 (assuming different TCP ports are used).
I advise you make 2 changes to the endpoint config:
FOR DATABASE_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS KERBEROS, ENCRYPTION = REQUIRED ALGORITHM AES)
Assuming you are in an Active Directory environment, Kerberos is preferred; also AES encryption is preferred over RC4.
Let us know how you get on....
March 5, 2014 at 9:32 am
See attached file with code and error msg.
Serv1M and Serv2W are two instances of SQL Server 2005 Standard edition on the same box.
May be I missed something during the installation of second instance?
March 6, 2014 at 4:08 am
Small point - you've raised your post in the SQL Server 2008 forum!
Database Mirroring was brand new in 2005; in fact, it wasn't even ready for the RTM release.
I assume you are running SQL Server 2005 SP1, at a minimum? You may also find that my advice for AES encryption is wrong (I don't know which version of SQL Server started supporting AES; certainly ok in 2008, not sure about 2005).
The error message in the Word document, simply tells me that the Witness Server already has a Mirroring Endpoint. Can you verify this fact?
March 6, 2014 at 7:43 am
yes, we are running SQL Server 2005 SP1. And yes, the error msg tells that Witness sever already has a Mirror endpoint. Also if I will create endpoints in opposite order (first witness and then mirror), the error msg will say that Mirror server already has Witness endpoint. Seems like it is configuration problem of two instances of SQL Server -Serv1M and Serv2W, that resides on the same box.
Also could you confirm that patches should be istalled per each instanse, not per server box?
March 6, 2014 at 8:51 am
Each Instance needs to have the service pack applied. I don't remember, if the Service Pack Installation allows you to choose more than one instance during setup. Some components are only installed once per server, but most components are Instance-aware.
I cannot fathom how creating a new Endpoint in one Instance affects the Endpoint in another Instance. Something weird...
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply