The problem: One server sees the witness, one does not. If the dbs are active on the one that doesn't see the witness and I shut that server down everything works as it should. However, if the dbs are active on the server that sees the witness and I shut that one down the mirror does not take over because it can't see the witness and form a quorum. It throws Event ID 1438 every 20 seconds complaing "The server instance Witness rejected configure request...The reason 1451, and state3, can be used for diagnotics by Microsoft...."
The scenario: I finished setting up a mirror in a WORKGROUP, by going through the GUI and then at the choosing not to start the mirror. That allowed me to modify the NETBIOS names and change them to IP addresses in order to get around the FQDN requirement that I can't fulfill since the servers are in a workgroup. They are in the same subnet and can run SQL queries against each other.
When I query sys.database_mirroring from the database servers I get this on the good server:
mirroring_witness_name mirroring_witness_state_desc
----------------------------------------------------------
TCP://155.17.92.63:5022 CONNECTED
TCP://155.17.92.63:5022 CONNECTED
And this on the bad server:
mirroring_witness_name mirroring_witness_state_desc
-----------------------------------------------------
TCP://155.17.92.63:5022 DISCONNECTED
TCP://155.17.92.63:5022 DISCONNECTED
here's are the results from sys.database_mirroring_witnesses
database_name principal_server_name mirror_server_name safety_level safety_level_desc
-------------- -------------------------- ------------------------- ------------ ------------------
dbadmin TCP://pscdaltpscom20:5022 TCP://155.17.92.65:5022 2 FULL
dbtest TCP://pscdaltpscom20:5022 TCP://155.17.92.65:5022 2 FULL
Oddly, the server that I created the mirror from registered itself with it's NETBIOS name even though I told it to use an IP address. However, that's the one that is working properly. I think this might be a red herring.
Does anyone have any ideas why my second server can't communicate with the mirror? Any clarifications I can make? I have been completely unable to find a step-by-step guide to creating mirror databases outside of a domain...so if someone knows of one please point me that direction.
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
I am unable to add witness server to my mirror configuration. my error log is throwing error'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89'.
I am getting this error while trying to setup mirroring. "Neither the partner nor the witness server instance for database "shop" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"
I am just using principal and mirror server, there is no witness server. I have tried fully qualified name and ip for the servers also. I can connect from the principal server to the mirror server using the management studio and it also creates endpoints on prinicipal and witness. But then gives me an error that it cannot find the instance of the mirror server and if I try again it gives the error above. I can also telnet to port 5022.
I have used -T1400 as the startup parameter.
I did the backup and recovery based on this link http://msdn2.microsoft.com/en-us/library/ms189053.aspx and the mirror server is in recovery mode now.
I have two 64bit SQL Server Std servers that will be principal and mirror. I also need to stand up a witness. I already understand that the witness can be any version of SQL Server, but can the witness server be 32bit given the principal and mirror are 64bit?
the Mirror Server is not connected to the Witness Server.
Mirror Server and Wtiness Server are instances on one server.
I get this error:
" 2007-06-28 17:08:12.77 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry."
In the SQL Profiler for the Witness Server I get "An error occurred while receiving data" Error 64.
In the witness server logs is nothing depending on this.
The connection between the witness and principal and the mirror and principal are all right. I have created User logins with certificates on the same way on all instances.
We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.
See screenshot here
Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?
missing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event. Â
We have 2 data centers, site 1 and site 2. Â Site 1 is generally our primary, and site 2 is our Disaster Recovery (DR) site. Â I want to setup a SQL instance to have extremely high availability. Â Therefore I was looking at using DB mirroring, with synchronous data writing, high-safety and auto-failover. Â This requires the usage of a witness server.My problem with this setup, is that if the witness and principal are both at site 1, if site 1 goes away (power failure, asteroid impact, lol, anything else that would be a *true* DR scenario), then there is no failover to the mirror server at site 2. Â However, if you put the witness at site 2, anytime the WAN link between site 1 and site 2 has an issue, the DB will auto-failover to site 2. Â Is the reason for the recommendation of the witness being at the primary site because the thinking is that WAN link failure is likely more common than a *true* DR scenario that takes out all of site 1?
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
Does anyone know where I can get SQL Server 2005 witness hosting? Our primary server is in Vermont and our DR site is in New Hampshire. I would like to set up a witness server somewhere off the east coast. I don't want to have to purchase a new server and pay big prices for colo.
Is there such a thing as witness hosting? Would someone be willing to host my witness and I can host yours?
We are trying to set up database mirroring. The mirroring works for the principal and mirrored databases (set up via Management Studio), but when we try to set up the witness database, we are getting the error message €œThe ALTER DATABASE command could not be sent to the remote server instance 'TCP://€¦ (Error: 1456)€?. The same occurs if I try to do €œALTER DATABASE xxx SET WITNESS =€? in TSQL. We are using the Standard edition for all three. We have tried using several different servers for the witness. All same user, same domain. Any suggestions? Thanks.
I have set up a SQL Express sytem to act as a witness....all are under the same admin account, all can ping and see each other. When I attempt to start a mirror session, it fails to issue an "ALTER Database" comment and the mirror setup is set to "High Performance".
This is my first time setting this up and it took over a day to do this as it is a relatively new concept, I am not DBA, just a S/W developer set up with the task of communicating with two DB instances which are mirrored. A couple of questions:
1) Is it normal to see the mirror instance in the following mode: (Mirror, Synchronized/Restoring)?
2) Any special steps required on SQL express besides enabling TCP/IP within the Configuration manager? It doesn't need the same instance of the same databaes on it, does it?
I have managed to set up mirroring on SQl 2005 however i have a come across a problem that i dont know how to resolve.
Im running the mirroring with 'High Safety with automatic failover.'
If i instagate a failover on the Principal Database, the mirror becomes the Principal, and vice versa ... great .. how ever .. i tried the golden test by making the principal server dissapear by yanking out the network connection, expecting the mirror to become the principal.....nope it just stays there in a disconnected / restoring state which isnt any good to man nor beast . I thought that the Witness should have dectected that the principal database had 'dissapeared' and would have made the mirror the Principal.
Any pointers on where i might have gone wrong in the setting up process, i used the mirror wizard
I removed mirroring on a configuration that is currrently working. Next, I moved the witness server to another IP and changed the FQDN (I patched the machine to another newtork, added a FQDN to our DNS server and reconfigured the machines IP information. I can ping the witness at its new address from the principal and the mirror).
Would changing the Witness's IP and FQDN prevent Database Mirroring from being setup after the certificate has been created and distributed to the principal and mirror instances under the old IP and FQDN? Should the endpoint be reconfigured and a new certificate be generated and distributed after an IP and FQDN change?
I start with 3 servers, in High Safety mode with witness. I disconnect the network cable for the witness. Then I remove the witness from the mirroring session:
ALTER DATABASE db1 SET WITNESS OFF
I reconnect the witness network cable. Now I cannot add the witness back to the mirroring session (either with TSQL or thru the GUI). Here is the error message:
Msg 1433, Level 16, State 4, Server SERVERA, Line 1 All three server instances did not remain interconnected for the duration of the ALTER DATABASE SET WITNESS command. There may be no witness associated with the database. Verify the status and when necessary repeat the command.
I have to stop mirroring (SET PARTNER OFF) and then reconfigure a new mirroring session to be able to include the witness.
If I don't disconnect the network cable, I am able to remove and re-add the witness to the session, no problem.
Not sure where I am going wrong ... I am able to set-up the mirroring, but the witness set-up always fails. I am running all the 3 SQL servers (Enterprise edition) with same domain account, which is also a member of sysadmin locally within each SQL server.
When I try to add the witness on Principal using the following command:
ALTER DATABASE MyDB
SET WITNESS = 'TCP://WitnessServer:7022'
I have even tried the IP address with port number and fully qualifying domain (TCP://WitnessServer.xx.xx.com:7022) and I get the following message in error log.
2006-06-20 10:16:55.07 spid57 The ALTER DATABASE command could not be sent to the remote server instance 'TCP://WitnessServer:7022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
All the 3 servers are configured to use TCP and Names pipes. I can connect, ping and telenet the Mirror Server from Principal. Any suggestions?
I'm running into a problem where I had mirroring working correctly (on server A ->B with C being the witness) then I set another machine(s) up to also use mirroring (X->Y with C also the witness). When I checked the A->B configuration today the witness was disconnected so I removed mirroring all together and attempted to set it up and I'm now getting the error:
TITLE: Microsoft SQL Server Management Studio ------------------------------
Alter failed for Database 'A'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://C:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1456&LinkId=20476
One of my client asking mirroring between two server with witness.Is it possible witness and mirror in same server(with same instance)? OR Is it possible witness and primary in same server(with same instance) ? Best method to establishing mirroring with in two servers(with witness) ?
I'm devlving into 2005 and specifically mirroring.Having read the following article (http://www.microsoft.com/technet/pr...5/dbmirror.mspx ),is mirroring a sensible option as it indicates that it should not beused in a production environment, yet the SQL2005 seminars I've been toindicate that this is a worthy option.Do we also need a witness server, or can this be done (sensibly) withtwo SQL2005 servers ?Currently we have 2xSQL2000 (clustered) servers and a SAN for the data.If we go down the route of mirroring, it looks like we can get rid ofthe SAN and Cluster and use the mirroring instead.Any advice on the pro's / cons would be appreciated.ThanksRyan
I'm trying to configure mirroring with High Availability, Automatic Failover.
I know that all the jobs and maintenance plans need to be copied to the mirror server, and enabled if a mirror database takes over the principal role.
I wonder if it is a good solution to have all agent jobs on the Witness server (no jobs on principal and mirror). And all the jobs select the server where they should run (depending on current role).
One of the advantages of this approach would be that the jobs have to be created only once on the witness.
Will this solution work? What are the downsides of it?
Not sure if this is relevant here but I thought someone might have seen this.
I am trying to set up Express edition as a mirror witness and am running into a problem.
It seems the express instance thinks its "Host Name"[1] is foo-prod-witnes rather than it's real name foo-prod-witness (notice the additional "s")
When I try to create the security endpoints everything appearsd to work as far as connecting to the express instance but i cannot start the mirror because the address listed is
TCP://foo-prod-witnes:5022 (not a valid hostname)
My enterprise editions report foo-prod-sql01.fqdn:5022
Any ideas how to fix this?
1. As shown in the Host Name tab of the Configuration Manager
I can establish the mirror with the principal and mirror but cannot add the witness (error 1456) all machines are SQL Server 2005 standard edition service pack 2 principal and mirror are windows server 2003 and witness is windows xp
First of all, Great webcast today. My question is, I have everything up and running and would like to know what to do when the machine my primary is on quits or has a some type of disaster. Do I need to manually run recovery on each db that was mirrored? I'm not currently running a witness.
Hi guys , I would like to know what's the exactly results able to get in this following scenario.
Server A : Principal
Server M : Mirror
Server W : Witness
1) Firstly, disable server M, following by server W. So probably i will unable to connect to Server A eventhough it acts as a principal.
2) Next, if I enable either server M or server W , what will I able to get actually ? Is it the mirroring works like the article which written in Microsoft page ?(http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx#ETPAE)
At here, I get an error message and unable to continue the mirroring section. ( "Database EMS is enabled for Database Mirroring, but neither the partner nor witness server instances are available: the database cannot be opened." or "Database 'EMS' is being recovered. Waiting until recovery is finished." )
3) If I enable both server M and server W, the mirroring continue to work again.
Can I have any assistance on (2) issue? Thx for the assistance.
Is it alright to move the .bak and .trn backups which are automatically created in a File Share Witness when a DB is added to an Availability Group? I did that and there were error IDs 1069 occurring, and the AG was unable to be brought up whenever I run a load intensive batch job ...
Came across this scenario in AlwaysOn Availability Group (two node), file share witness times out and RHS terminate and cause the cluster node to reboot. File share witness is for continuous failover and if the resource is unavailable my expectation was that it should go offline and should not impact Server or Sql Server. But its rebooting the cluster node to rectify the issue.
Configuration Windows Server 2012 R2 (VMs) Sql Server 2012 SP2 Errors
A component on the server did not respond in a timely fashion. This caused the cluster resource 'File Share Witness' (resource type 'File Share Witness', DLL 'clusres2.dll') to exceed its time-out threshold. As part of cluster health detection, recovery actions will be taken. The cluster will try to automatically recover by terminating and restarting the Resource Hosting Subsystem (RHS) process that is running this resource. Verify that the underlying infrastructure (such as storage, networking, or services) that are associated with the resource are functioning correctly.
The cluster Resource Hosting Subsystem (RHS) process was terminated and will be restarted. This is typically associated with cluster health detection and recovery of a resource. Refer to the System event log to determine which resource and resource DLL is causing the issue.
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
