SQL 2012 :: Mirroring Failover And Failback
Aug 26, 2015
I have the following mirroing setup for DR.
Prinical(ServerA) and Mirror(ServerB)
When we do a DR test we do manual failover from Principal(ServerA) to Mirror(ServerB)
Now
ServerB is Prinicpal
ServerA is Mirror
Applications do their testing against the new principal which is ServerB but dont want to take those changes or sync to ServerA(Mirror).
Is there any way to do this without breaking the mirror?
Finally It will go back to original setup after the test which Server A is Principal and Server B will be its mirror.
View 9 Replies
ADVERTISEMENT
Oct 12, 2006
Hello all,
I'm about to setup Log Shipping in our SQL Server 2000 environment. I will have the primary server, one standby server, and the witness server in this setup. I understand the process of failover and the stored procedures needed to make this happen, but my main question is about failback. Once the standby server has assumed the primary role, will log shipping be automatically configured to start flowing from the new primary server to the old primary server, or will I have to go back and setup log shipping from the new primary server to the old primary server?
Thanks for any help or insight you can provide on this subject!
Eric
View 7 Replies
View Related
Aug 25, 2015
I am running SQL 2014 2-node AlwaysON Availability groups, Enterprise Edition in our environment and 5 databases are part of AG.
Question is, sometimes AG is getting failed over to node2 but always our preferred node is node1 due to some business needs otherwise some of our jobs will fail.
So, what I looking for is, a sql script which can handle a situation wherein, for some reason, AG is failed over to node2, it should be able to detect if node1 is back online or not and if so, it should fail back to node1. How to do this using tsql query or stored proc or sql agent job ?
View 4 Replies
View Related
Jun 8, 2006
Hi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
View 5 Replies
View Related
Nov 8, 2006
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).
Thanks
Larry
View 1 Replies
View Related
Feb 16, 2006
We are planning to update our two node, active/passive clustered system to SQL 2005 from SQL2K. Would we be better off to use database mirroring rather than clustering with failover? I believe mirroring takes 3 systems rather than two (one for each mirror and one for monitoring), but can the monitor be a rather low powered PC?
Our current cluster uses two 4-processor Dells and an external PowerVault RAID array with fiber channel connections. Each server has two NICs, one for general network and one for heartbeat and a fiber channel card to connect to the external PowerVault.
Here are my questions:
1 - If I understand it correctly, mirroring uses just separate servers without the need for the rather expensive and complicated shared fiber channel array that must go between them. Do I still need a shared array or can I just use internal RAID arrays in each box?
2 - Are there any advantages to keeping our current system? Our current system has worked well, but the failover takes a couple of minutes. Is mirroring faster?
3 - Is there still a virtual IP address and network name that everyone connects to?
4 - What are the drawbacks?
5 - Can I run non-mirrored DBs on the same servers or are they really locked together tightly?
6 - How much different in configuration can the two boxes be? With the cluster, I really need duplicates in every way.
7 - Do I need a separate license for the SQL Server on each box in the mirror or is one enough, since they work in lock step?
Thanks for any of your experiences on this.
View 7 Replies
View Related
Mar 15, 2006
Hi there.
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 ?
View 8 Replies
View Related
Jun 30, 2015
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.
View 5 Replies
View Related
Jun 14, 2007
Hello.
I am confuse and cant decide on how to setup high availability on our SQL 2005. Here's what on my mind and on resources list:
I plan to have mirroring on my SQL1 to SQL2 with the help of SQL3 as witness. So this would be automatic failover. My idea on mirroring is when SQL1 goes down, SQL3 would tell SQL2 to run and be the primary. It will automatically failover to SQL2. Right? My questions are:
1) How can I revert back to SQL1 once it is ready?
2) I read in one of the post that it is impossible to write in a mirrored DB, is this true? I mean, what's the use of failing over to the next node when it's not possible to write and update data/records?
3) If number 2 is false (i hope so), how would the data be synchronize from SQL2 back to SQL1. Those transaction that were made while SQL1 is down.
4) How about the connection string from the web applications? Would it be automatically point to SQL2? We have load balancing setup in place, would this help web application connection to automatically point to SQL2?
Another setup:
We have SAN in place (not yet used, but is planning to use for this SQL thing), EMC to be specific. My question would be:
1) For SAN setup, the data storage would be centralize. So would that mean that SQL1 and SQL2 services will use the same data and log file from the SAN storage?
2) How would you call this setup then? Can this be clustering type of high availability? Will clustering work under load balancing setup? I believe mirroring is not possible here? Right?
3) How can I setup my 3 SQL servers with the same theory in mind: when SQL1 goes down, SQL2 will take over. Data will be synchronize when SQL1 is up and running again. With automatic failover and reverting back to primary.
I read so much topics about this, but the more I research, the more I get confuse.
Any suggestions, comments, advice is greatly appreciated!
View 6 Replies
View Related
Jun 22, 2015
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
View 3 Replies
View Related
Oct 16, 2007
Hello everybody,
I'am currently testing a MSSQL 2005 mirroring implementation with automatically failover.
1 principal server ====> Server A
1 mirror server ====> Server B
1 witness server ====> Server C
When server A fails Server B becomes Principal. ( which is the general idea ) This is working perfectly.
The problem is when i try to connect to the mirror Server B ( actually the new principal )
I use the .NET connection string with the failover parameter ( even without the parameter ) the connection fails.
( the general error : unable to connect, or connection property has not been initialized )
Data Source=ServerA; Failover Partner=ServerB;Initial Catalog=DBMTest;uid=dbuser;pwd=dbpassword;
This connection property works fine when server A (original principal) is online.
When i run the SQL profiler with the mirror db as profile, i dont see the traffic.
So you think the mirror server has a network problem or was not connfigured for remote access.
i thought that also, but when i create a 'stand alone - non mirrored' database the same application with the same connetion string ( well.....different db) works fine.
The problem must be in the whole mirroring implementation....... ( my idea )
Does anyone had the same problem, or better, doed anyone know how to solve it?
Hope to hear from you soon!
Thank you in advance!
Regards,
W.schmitt
The netherlands
View 6 Replies
View Related
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?
View 3 Replies
View Related
Sep 24, 2015
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
View 3 Replies
View Related
Mar 10, 2014
If I have multiple servers in my AG group and set two of the secondaries to synchronous with auto failover, how do I control which one one of the two secondaries will become primary if the original primary goes down?We need to maintain HA even during patching so need a first choice auto failover and a second choice auto failover.
View 9 Replies
View Related
Mar 29, 2014
I have virtualbox installed on my notebook. For testing purpose, is it possible to setup SQL Server Fail-Over cluster using virtual box? I don't have a shared storage. How can I simulate that?
View 1 Replies
View Related
May 5, 2014
How to create a SQL 2012 Failover cluster using VMs?
View 9 Replies
View Related
Dec 10, 2014
I have setup SQL AlwaysOn between primary and DR data centers. Here is the setup:
Primary data center: Server1 (Primary), Server2 (Sync Commit Secondary), Server3 (ASync Commit Secondary)
DR data center: Server4 (ASync Commit Secondary)
Data synchronization and manual failover works fine. But, sometimes, the AlwaysOn cluster automatically fails over to Sync Commit Secondary on Primary data center. Here is the error message from Failover Cluster Manager->Cluster Events:
"Cluster has missed two consecutive heartbeats for the local endpoint xx.xx.xx.yy:~3343~ connected to remote endpoint xx.xx.xx.zz:~3343~"
"Cluster has lost the UDP connection from local endpoint xx.xx.xx.yy:~3343~ connected to remote endpoint xx.xx.xx.zz:~3343~"
I had our network engineer check all connections multiple times and he confirmed everything is fine. But he was also able to confirm (using monitoring tools) that right at the time of a failover, there is almost 2GB worth of traffic going from Primary Server to DR server. That happens every time. I had checked the times of all failovers and there is no job or process occuring that will produce 2GB worth of data. Also, this happens regardless of which server is primary.
Even though the failover works fine, this unexpected automatic failover due to missed heartbeats are occurring often (2-3 times a month).
Here is the list of errors from the Cluster Validation Report:
Under Network Section, I see the following error messages in Red:
Validate Network Communication
Network interfaces Server4 (DR) - SAN_Team and Server1 (Primary) - SAN_Team - VLAN 20 are on the same cluster network, yet address xx.xx.xx.pp is not reachable from xx.xx.xx.yy using UDP on port 3343.
Network interfaces Server4 (DR) - SAN_Team and Server2 (Secondary) - SAN_Team - VLAN 20 are on the same cluster network, yet address xx.xx.xx.qq is not reachable from xx.xx.xx.yy using UDP on port 3343.
[Code] ....
View 7 Replies
View Related
Jan 21, 2015
I am trying to create a failover cluster without the log shipping in 2012 as i've done it with a static instance with some database.Is the "AlwaysON" feature the solution when an application creates random and numerous databases within the instance and we need a failover scenario ?
View 9 Replies
View Related
Jan 27, 2015
So I have 2 servers S1 & S2.
Database Group 1 = L1 with Primary S1 and Secondary S2
Database Group 2 = L2 with Primary S2 and Secondary S1
For 99% of the time the 2 groups of databases are not related. For the 1 procedure that does move data from L1 to L2 something like
Update L2.DB.Owner.Table
set flag = 1
Whare a = 0
On S1 I have a linkedServer with connection to L2.
If I have a failover I cannot have L2 on S2 as they are essentially the same server.
How to I use the 2 groups hand in hand.
View 0 Replies
View Related
Jun 26, 2015
what type of replication is best and easiest to startup on the DR site in event of a failover for an SQL server with very high transaction occurring on the database.I normally handle Oracle database and we use SAN to SAN replication to the DR site, all w have to do is mount the replicated disks at the DR site and Startup the database.How do i achieve this on SQL database including having the using logging details work at the DR site.
View 2 Replies
View Related
Sep 30, 2015
Today a vendor bluntly stated that VMWare provides the same failover and redundancy for SQL that would render "AlwaysOn" high availability unnecessary.
Essentially that VMWare would detect a problem and failover and have .9999 uptime .
View 2 Replies
View Related
Mar 21, 2014
I'm reading up on Always on and I am confused what is the difference between AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances.
View 3 Replies
View Related
Apr 3, 2014
I'm looking for a solution to have cross data center automatic failover in the event of a data center loss for highly critical databases. I would like to have local HA and also automatic failover to the DR site. This does not seem possible with AlwaysOn.
Is my only option for automatic cross data center failover to build a node in one data center and a node in the other data center with a node/FS at a third data center in order to maintain quorum? I'd like to have local HA in the mix but that doesn't seem possible.What pattern for the highest data security and also availability?
View 5 Replies
View Related
Apr 17, 2014
I had a cluster running on 3 nodes (windows failover cluster) with server 2012 and SQL server 2012 running in the cluster. Well, I thought I'd just go ahead and rename all 3 of the hosts with new hostnames and it apparently broke the cluster pretty bad. Now when I open failover cluster manager I cannot see anything nor can I connect to the cluster object in AD. I re-named all of the hosts back to their original names but that didn't work.
View 5 Replies
View Related
Jun 16, 2014
If there is a history kept somewhere of failover events of a database in an AO group? I have 2 replicas with automatic failover and I'm looking for a history of failovers.
View 0 Replies
View Related
Jul 16, 2014
I have a cluster using Always on on 2012 and i have set up a Witness file share which losts connectivity the other day and a failover was invoked.
Is there a threshold i can change so that the cluster can try a couple of more times to connect before it fails over ?
View 3 Replies
View Related
Jul 21, 2014
I have 2 servers in a SQL Server Fail-Over Cluster. IOW I use always-on availability groups. I run backups - full, diff and log - regularly via SQL Agent on one server only depending on which is primary. If there is a fail-over, then backups will continue on the other server. If I have to restore a database in an availability group I probably would need some combination of full, diff, and log backups from each server. Would that actually work? I test the backups weekly however I just realized that I never tested that scenario.
View 1 Replies
View Related
Oct 6, 2014
We are implementing a multi-site (Windows Server Failover Cluster) WSFC to enable Always On between our primary and DR site. We are not going to use SQL clustered instances. We are not planning to use shared disks. Each node is running a standalone instance of SQL 2012.
I have successfully configured a 3 node multi-site Windows failover cluster with no shared storage. For quorum, I have defined a File Share Witness (FSW). The FSW has voting rights and is in the DR site. The setup looks like this –
WSFC –
•Node A – Site #1 (voting right = 1)
•Node B – Site #1 (voting right = 1)
•Node C – Site #2 (voting right = 0)
•FSW – Site #2 (voting right = 1)
Again - There are no shared disks in our setup. We are not going to use SQL clustered instance. We are going to use Always On with these 3 nodes.
SQL Always On –
•Node A – Site #1 (Primary Replica)
•Node B – Site #1 (Readable Secondary)
•Node C – Site #2 (Readable Secondary)
All the setup including the “availability group” works properly under this setup. However, a failover to site #2 under DR situation is not working and I know why but don’t know what needs to be done to fix the problem.
The following works fine –
•Automatic failover between nodes A and B (same site – site #1)
•Forced failover to node C in site #2 provided at least one of the nodes in site #1 is up (non – DR situation) - this will ensure the cluster is up
The following is not working –
•Forced failover to node C in site #3 when both nodes in site #1 are lost (true DR situation) – This is because the cluster is not up at this point.
I know I have to bring the cluster up somehow and I have not been able to do so by restarting the cluster service.
I tried to run the command to start cluster service.
Question –
How can I FORCE the cluster to come up in Site #2 on node C when it has no voting rights?
I have always worked with even number of nodes and shared disks with traditional clustering. I am not sure what needs to be done in this scenario with 3 nodes and a FSW.
View 3 Replies
View Related
Oct 22, 2014
Approach 1:
Prod - shared storage between server 1 and 2
Server1: clustered SQL instance with availability group as primary
Server2: Passive server for clustered instance of PROD
DR - shared storage between server 1 and 2
Server1: Clustered SQL instance with availability group as replica
Server2: Passive server for clustered instance of DR
Approach 2: Using replicated SAN
Prod -
Server 1: Standalone instance with availability group as Primary
Server 2:Standalone instance with availability group as replica
DR -
Server 1: Offline until Disk group 1 (Prod server 1) has been broken and brought online at DR
Server 2: Offline until Disk group 2 (Prod server 2) has been broken and brought online at DR
Both these approaches will work wont they? I have only built and played with normal availability groups across servers, not mixing it with clustered instance replicated SAN
View 4 Replies
View Related
Dec 19, 2014
how to resolve and trouble shoot the availability group is not ready for automatic failover in sql2012
View 4 Replies
View Related
May 29, 2015
Let's say I have a two node AG, Server A and Server B. Server A is normally the primary and Server B is the replica. Whenever the primary fails over from A to B or from B to A, I'd like to automatically run a script that will restart the SQL Agent service on the new replica.
View 3 Replies
View Related
Sep 30, 2015
How can we find the cluster failover count in always on ?
As my AG is configured as synchronous mode , AG went offline and we manually restarted the AG service when we check the properties on AG role they r in default setting ?
View 0 Replies
View Related
Oct 27, 2015
An automatic failover set exists. This set consists of a primary replica and a secondary replica (the automatic failover target) that are both configured for synchronous-commit mode and set to AUTOMATIC failover.Configured the both AG Group database automatic failover and synchronous-commit mode.But automatic Failover failed also Cluster service not started automatically at Node2. It got connected through AO Listerner after starting Node1. As below SQL Error log during shutdown Node1
Date,Source,Severity,Message
10/27/2015 10:44:20,spid37s,Unknown,AlwaysOn Availability Groups: Waiting for local Windows Server Failover Clustering node to come online. This is an informational message only. No user action is required.
10/27/2015 10:44:20,spid37s,Unknown,AlwaysOn Availability Groups: Local Windows Server Failover Clustering node started.
[code]....
what need to be change existing AO configuration?
View 9 Replies
View Related
