July 12, 2010 at 4:40 pm
We had a test of the generator today (not informed by the way), and now we have something interesting going on here.
We have five servers participating in a mirror environment; HR1, HR2, FIN1, FIN2, UPG1 (not their actual names). UPG1 is used as the Witness Server for both HR1/2 and FIN1/2.
Here is the weird part, when using the Mirror Monitor HR1/2 are connected to each other and the witness. FIN1 and FIN2 are connected and FIN1 is connected to UPG1, however, FIN2 is disconnected.
Logging into each server (FIN2 and UPG1) you can ping the other server. We have also tried rebooting both FIN2 and UPG1, but to no effect.
I have not yet tried dropping and recreating the witness, but will look at that option next.
I'm curious if anyone else has seen this behaviour in a mirrored environment.
July 12, 2010 at 4:52 pm
You can ping each of the servers, but can you connect to each server in SSMS (from each of the participating servers)? Open a fileshare? Is there a firewall, managed switch or router in between the accessible and inaccessible servers?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2010 at 6:27 am
you should restart FIN1.
July 13, 2010 at 8:31 am
Geoff A (7/13/2010)
you should restart FIN1.
Not currently an option. That would have to wait until our scheduled maintenance period on Thursday night. As long as the mirror is working between principal and mirror, need to keep the principal database up. Since the mirror database cannot see the witness, failover currently is not an option.
July 13, 2010 at 8:46 am
hopefully, your principal server has enough disk space to log then 😛
does your witness have 2 instances?
July 13, 2010 at 9:08 am
We have a future production server on FIN1/FIN2. It has the same problem as the production server. On this database, we dropped the witness and re-established it on the Primary server, the mirror server still did not connect. Manually failed to the mirror database on the future production database, dropped the witness, but was unable to re-establish the witness:
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://UPG1:7022'. The database mirroring
configuration was not changed. Verify that the server is connected, and try again.
Note: Server name and port changed to protect the guilty.
July 13, 2010 at 9:24 am
so you are running multiple instances of mirroring using a single witness on different ports....
i never ever had any luck with that setup. I always have separate instances for each mirroring instance in my shop.
is the browser service started and running on the current witness?
July 13, 2010 at 10:44 am
Looks like Network Services found the problem. Lesson learned on thier side, don't put servers on DHCP.
(Uh, that's a duh in my book and I'm not a network person. :-P)
July 13, 2010 at 10:46 am
Geoff A (7/13/2010)
so you are running multiple instances of mirroring using a single witness on different ports....i never ever had any luck with that setup. I always have separate instances for each mirroring instance in my shop.
is the browser service started and running on the current witness?
Nope, same witness server using the same instance, so same witness port. Each server has only one instance. Only the witness server is shared between Finance and HR mirrors.
July 13, 2010 at 10:58 am
i am not sure how you got 2 separate pairs of servers to use the same witness on the same port... that is something i could never do in my lab.
spin off another instance on your witness and you'll be all set to re-establish the session.
http://technet.microsoft.com/en-us/library/cc917681.aspx
Connection management for database mirroring is based on endpoints. An endpoint is a SQL Server object that enables SQL Server to communicate over the network. For database mirroring, a server instance requires a dedicated database mirroring endpoint to receive database mirroring connections from other server instances. An endpoint that is used for a database mirroring connection cannot be used for any other purpose. Moreover, each SQL Server instance can have only one database mirroring endpoint. All databases in that instance that are involved in database mirroring must use the same endpoint.
Database mirroring endpoints use Transmission Control Protocol (TCP) to send and receive messages between the server instances in database mirroring sessions. The database mirroring endpoint of a server instance is associated with the port on which the instance listens for database mirroring messages. Each database mirroring endpoint listens on a unique TCP port number.
By default, a database mirroring endpoint requires the encryption of data that is sent over mirroring connections. In this case, the endpoint can connect only to endpoints that also use encryption. Unless you can guarantee that your network is secure, we recommend that you require encryption for your database mirroring connections. However, if you have a guaranteed secured network, you can turn off encryption, and gain a bit of performance.
July 13, 2010 at 11:46 am
Geoff A (7/13/2010)
i am not sure how you got 2 separate pairs of servers to use the same witness on the same port... that is something i could never do in my lab.spin off another instance on your witness and you'll be all set to re-establish the session.
http://technet.microsoft.com/en-us/library/cc917681.aspx
Connection management for database mirroring is based on endpoints. An endpoint is a SQL Server object that enables SQL Server to communicate over the network. For database mirroring, a server instance requires a dedicated database mirroring endpoint to receive database mirroring connections from other server instances. An endpoint that is used for a database mirroring connection cannot be used for any other purpose. Moreover, each SQL Server instance can have only one database mirroring endpoint. All databases in that instance that are involved in database mirroring must use the same endpoint.
Database mirroring endpoints use Transmission Control Protocol (TCP) to send and receive messages between the server instances in database mirroring sessions. The database mirroring endpoint of a server instance is associated with the port on which the instance listens for database mirroring messages. Each database mirroring endpoint listens on a unique TCP port number.
By default, a database mirroring endpoint requires the encryption of data that is sent over mirroring connections. In this case, the endpoint can connect only to endpoints that also use encryption. Unless you can guarantee that your network is secure, we recommend that you require encryption for your database mirroring connections. However, if you have a guaranteed secured network, you can turn off encryption, and gain a bit of performance.
I understand everything you have said above regarding each instance of SQL Server can have only one database mirroring endpoint, that the database mirroring endpoints use TCP to send and receive messages between server instances in database mirroring sessions, that each database participating in a mirroring session has to use the same endpoint as other databases that are mirrored on the same server instance, that by default connections between endpoints are encrypted (mine are), that each mirroring endpoint listens on a unique TCP port number on each server (different physical servers can use the same port number, two instances on the same server must use different port numbers).
With all that, the problem was with DHCP and DNS and how and when servers restarted during our generator test and servers went down (and really shouldn't have, but that is another issue).
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply