Recovery :: Where To Place Witness Server For Mirroring Using High-safety And Auto-failover
Apr 9, 2015
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'm reading about database mirroring in High Safety Mode (Synchronous with no witness). I'm a little confused about what happens if the mirror server disappears.
From Books Online "If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data)." http://msdn2.microsoft.com/en-us/library/ms179344.aspx
I was to the course [Upgrading your Database Administration Skills to SQL Server 2005] and the course material does not say the same."Scenario: Synchronous mirroring without a witness server. If the mirror server becomes unavailable, the principal server will take the database offline. This action attempts to maximize the protection of the database and ensure the integrity of its contents"
I've made tests that shows that Books Online is right, but I would like to know if this is a behaviour that has changed.
I also would like to know if it's possible to run in a more secure mode, where exposed operations is not allowed.
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
I realise this is a stupid quesiton but i cannot really find any confirmation of this in BOL.
If you are running High Safety with automatic failover, when failover occurs does this automatically change to High Performance mode. SInce for failover to occur something has happen with the primary , it will be impossible to commit transactions on the new primary and mirror asyncronously since 1 of them is no longer available.
So am i correct in assuming that automatic failover also automatically changes the mode to High Performacne for that session?
we have to build high availability SQL 2012 cluster for VDI and we have two options. One option is to build a server cluster with combination of failover and mirroring and other option is to build failover cluster with AlwaysOn.We are not sure which option to chose. We have contacted Microsoft support to provide us some documents and instructions for failovermirroring combination but they have send us instructions for AlwaysOn option.
What would be best way to build high availability cluster for VDI? Also, since first option is very complicated.
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'.
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. Â
Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.
If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?
1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. After fail over done to 2nd secondary node what mode in production(readonly or read write).
4. How to rollback to production primary ,will change data in secondary will get updated in primary.
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
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.
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 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.
I´m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.
The tests i´m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.
My question here is: What if i use Sql Server failover mechanism?
I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?
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.
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
I have SQL Server 2008 R2 database mirroring setup with witness. Below database connection string is used by the application  jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER  During some maintenance activity at the OS side , I tried to manually failover database from Server_A to Server_B. It took long time for failover to Server_B. While failing over the Mirrored_DB database goes in In Recovery status and application is not able to connect to database. I waited for long time more than 40 mins for database to come online on Server_B but it did not work.So I have recovered database on Server_B using RESTORE DATABASE Mirrored_DB WITH RECOVERY.  After database was recovered on Server_B application was not able to connect to database with error as "The database <Mirrored_DB> on server Server_BMSSQLSERVER is not configured for database mirroring".  My Question is
Why manual failover did not get back database on secondary server upwhy the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring
 jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER
I'm looking at a setup where they have server1 and server2 in a mirroring relationship with automatic failover.Â
Server1 is the principalThey are using transactional replication to replicate asingle databse to server3 is AWS.Distribution database is on Server1All Agents (log reader, snapshot, distributor) run on Server1Server2 has not been set up for replication...My understanding is that in this set up you would normallly place the distribution database on a separate server and enable publication on the mirror, Server2.
What happens if they failover? Replication would stop, and presumably records added while the mirror is the active database would not be marked for replication?How would they recover? Failback and reinintialize
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.
Hello, Does any one know, any software out there that can provide a solid failover / cluster / high availability solution for SQL Server 2000 Databases. I have tried Incepto but it requires an extra column in every single table that involves in Replication and its not gonna work
I am experiencing a problem while performing failover testing on 2005 Standard. I have an application which uses an ODBC connection SQL Native Client with a failover partner.
The following is the series of events that cause the problem. I am using a domain admin account whilst testing which has a server login on both servers with the same privileges.
Server A (Principle) Server B (Mirror) **Application can connect**
Manual failover
Server A (Mirror) Server B (Principle) **Application can connect following a restart**
Server A is switched off
Server B (Principle, disconnected) **Application continues working**
***restarting the application the following error occurs***
€œConnection Failed SQLState 08001 SQL Server Error 53 [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connectionto SQL Server [53]. Connection failed: SQLState: '08001' SQL Server Error: 53 [Microsoft][SQL Native Client]An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Connection failed: SQLState: 'S1T00' SQL Server Error: 0 [Microsoft][SQL Native Client]Login timeout expired€œ
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?
Here is the situation we are trying to resolve. The client has 2 locations, each has local appliations running on a database. db schema on both locations are the same, data is different and won't overlap.
The requirements are: 1. at each location the application can read and write 2. near zero down time for applications on each site 3. db on one site also has the data from the other site for DR purpose
The client is running on SQL 2005 STD SP2
We looked at the approach of setting up db mirroring on each location + 2-way transactional replication between both locations. The mirroring was fine, and I was able to set up transactional replication from mirrored publisher to a non-mirrored subscriber. But, from what I experienced and from reading, there is no way to have the subscriber db to be mirrored, since Distribution Agents simply doesn't have the option to specify Failover Partnr for mirroring, so I guess it is not supported. Any comments on that?
Assuming that's correct, then the only way of using SQL out-of-box technique seems to be using Clustering on each location instead of mirroring, then the 2-way transactional replication works on clustered subscribers I think (although I haven't tested it). Peer-2-Peer replication would have been a good candidate between sites in this case, but STD version of sql 2005 ruled that out.
I would like implementing a database mirroring architecture with SQL server 2005 but i have questions.
If i don't use a cluster architecture, i would like knowing if there's any solution to move the alias SQL server from the primary to the secondary by script.
I'm testing mirroring in a high availability mode and getting some odd results. If I kill the SQL Service (or reboot the machine) fail-over works quickly and correctly. If I unplug the Network cable though I will get some of the databases failed over and some of the databases will either be Mirroring / In recovery or Principal / In recovery and never go live. Right clicking on them and going to properties lists there error that the database is unable to communicate with the Partner or Witness. The databases that this will occur on seem to move around (i.e. it isn't always the same databases, but will often be many of the same databases). If I reboot the databases that will not fail over tend to change fairly dramatically. I've seen instances where only 3 databases would fail over (I'm testing 17 databases) and other instances where all but 3 databases would fail over. I've had exactly one test where all databases failed over correctly. Again if I reboot a principal server, fail over works every time.
Has anyone else experienced only partial failovers for network issues or have a work around?
I have temporarily created a script that will force the databases to be the principal, but it's not great for high availability (probably will work for disaster recovery though).
Could not able to connect secondary replica below is the error message I am getting when I tried to failover. Cannot connect to VLDBATEAM.
The secondary replica that you selected to become the new primary replica does not belong to the specified availability group. A possible explanation is that the replica has not been joined the availability group. Â (Microsoft.SqlServer.Management.HadrTasks)
I have been looking over the forum, and also other sites for information about my problem but cant seem to find what im looking for so I have decided to make a quick post.
Presently, I have 3 computers setup for mirroring. One is the principal, another the mirror and the third is the witness.
Im using SQL 2005 Enterprise Edition on all three, and creation of the mirror using SQL Studio works without problems. Manual failover (using the button in SQL Studio) also works fine.
When I start the "Mirror Monitor" application, and connect to the two DB servers the status is all green, they are connected to each other and the witness server can be contacted.
Now here is my issue; when its time for an automatic failover situation (pulling the plug from the current principal for example) it detects the fault and changes the status of the mirror to "principal" BUT keeps the other status to "Disconnected" (Principal, Disconnected) so no active connections to the failed over database will work.
When running the mirror monitor during the failed fail-over attempt the still online database reports that the connection to the witness is still present but the mirror is offline.
There are a few error notices in the logs, but from what I can tell they are normal for whats happening. But the codes would be 1479 (cant talk to the database; this would be the one we took offline) and 1474 (network name is no longer available; once again as it was taken offline). Note that these errors are also in the witness server logs, as I believe one should expect.
Any help or assistance with this problem would be appreciated.
