August 23, 2017 at 7:43 am
I have a database application, that talks to microsoft sql, i want to create some redundancy, so if Server A fails i can still work from Server B, i been using mirroring, but the fact that it requires a witness for auto switching is a pain… my requirements are simple:
a- Data must be equal between the two servers all the time
b- in case one server goes down the other must be able to pick up right away with no data loss…
August 23, 2017 at 7:49 am
What version of SQL Server are you using? If it's 2012 or higher, you can use AlwaysOn, which uses mirroring as one of it's foundational bases, but also includes High Availability (HA) of Windows Clustering technology to allow the ability to have automatic failover without the need for a witness server.
We can offer more specific solutions to your problem if you provide more specific details about the problem you're trying to solve.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 23, 2017 at 7:58 am
SQL_Hacker - Wednesday, August 23, 2017 7:49 AMWhat version of SQL Server are you using? If it's 2012 or higher, you can use AlwaysOn, which uses mirroring as one of it's foundational bases, but also includes High Availability (HA) of Windows Clustering technology to allow the ability to have automatic failover without the need for a witness server.We can offer more specific solutions to your problem if you provide more specific details about the problem you're trying to solve.
Yes version will be 2012 or higher most likely it will be 2016...
So i have a data entry application, that just reads and write to the database, but it is very important for it to be up 24/7 so if the main server crashes, i need to be able to run that application on a backup server and keep on working, and after the primary comes backup, all the data and changes made on the backup must go to the primary, almost like with mirror.
Im going to read about this AlwaysOn
August 23, 2017 at 8:09 am
If you decide to use an availability group, you may need Enterprise edition. Standard edition only supports one database in an AG, so if you have more then one database that needs to be part of this, you will need enterprise.
And, if there are only 2 nodes, you will also need a witness as a "tie breaker". The witness does not need to be a third server. It can be a file share.
If you want to read, start here:
http://qa.sqlservercentral.com/stairway/112556/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 23, 2017 at 8:14 am
Michael L John - Wednesday, August 23, 2017 8:09 AMIf you decide to use an availability group, you may need Enterprise edition. Standard edition only supports one database in an AG, so if you have more then one database that needs to be part of this, you will need enterprise.And, if there are only 2 nodes, you will also need a witness as a "tie breaker". The witness does not need to be a third server. It can be a file share.
If you want to read, start here:
http://qa.sqlservercentral.com/stairway/112556/
Only one database, and correct 2 servers... So what difference this has in comparison with mirroring if i still need to have a witness server? one of the 1st things i read about alwayson, is that it can take up to 30 seconds for backup server to complete the failover?
August 23, 2017 at 8:54 am
miguelv06 - Wednesday, August 23, 2017 8:14 AMMichael L John - Wednesday, August 23, 2017 8:09 AMIf you decide to use an availability group, you may need Enterprise edition. Standard edition only supports one database in an AG, so if you have more then one database that needs to be part of this, you will need enterprise.And, if there are only 2 nodes, you will also need a witness as a "tie breaker". The witness does not need to be a third server. It can be a file share.
If you want to read, start here:
http://qa.sqlservercentral.com/stairway/112556/Only one database, and correct 2 servers... So what difference this has in comparison with mirroring if i still need to have a witness server? one of the 1st things i read about alwayson, is that it can take up to 30 seconds for backup server to complete the failover?
You do not need a witness SERVER. The are multiple ways a witness can be configured. In order for an automatic failover to occur, you need a quorum.
I suggest you read the Stairway article as a starting point.
Is 30 seconds not acceptable for a failover to occur? Mirroring will take that long, clustering will also take that long.
What are your specific requirements for high availability?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 23, 2017 at 9:23 am
Michael is completely correct...all of the Microsoft HA offerings will take about 30 seconds to failover when an event causes the primary to fail and the secondary becomes the new primary. If you need something that fails over faster than that, you'll have to investigate parallel (load balancing) processing (i.e. you will have to modify the application to write to two databases at the same time, and some sort of "monitor" to determine which node processed the write first. I work at a place where this is in practice over a WAN, and it does work very well, but I have no idea how they coded the application(s) to do this task).
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 23, 2017 at 9:30 am
On the test i have done mirroring takes 2-3 seconds... But i can live with 30 seconds... I will try this and see how it goes.
August 23, 2017 at 9:36 am
AlwaysOn Availability Groups are what mirroring + replication should have always been...and is the cross-breeding of HA and DR. With Enterprise Edition of SQL Server, you can have 2 synchronous secondaries and up to 4 asynchronous secondaries. They can be in the same data center or spread all over the world, which accomplishes the DR aspect of it. The only thing that is needed from Windows is the Failover Cluster components to perform failovers. As Michael has already provided the Stairway article, it will likely explain that it is a "sub-section" of clustering; for example, shared disks, network redundancy and other requirements of a failover cluster are not required for AlwaysOn. Pretty much, the only requirement is the installation of the specific components and creating a "cluster name" which is how the Listener in the Availability Group(s) work for seamless integration with your application. I'm certainly not a Microsoft employee, but I am certainly a HUGE fan of AlwaysOn...I have personally solved a myriad problems with this technology and I think it's one of the best features SQL Server has offered since SQL 2005 (redesigned database engine back then...).
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 23, 2017 at 9:45 am
miguelv06 - Wednesday, August 23, 2017 9:30 AMOn the test i have done mirroring takes 2-3 seconds... But i can live with 30 seconds... I will try this and see how it goes.
If you want SIMPLE, I would stick with mirroring honestly. Works fine and is pretty bullet-proof (Always On has had a LOT OF BUGS AND STILL MORE ARE GETTING FIXED WITH VIRTUALLY EVERY CU).
What is your concern over having a "witness server"? That can be a free version of SQL Express.
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-witness
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 23, 2017 at 9:59 am
TheSQLGuru - Wednesday, August 23, 2017 9:45 AMmiguelv06 - Wednesday, August 23, 2017 9:30 AMOn the test i have done mirroring takes 2-3 seconds... But i can live with 30 seconds... I will try this and see how it goes.If you want SIMPLE, I would stick with mirroring honestly. Works fine and is pretty bullet-proof (Always On has had a LOT OF BUGS AND STILL MORE ARE GETTING FIXED WITH VIRTUALLY EVERY CU).
What is your concern over having a "witness server"? That can be a free version of SQL Express.
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-witness
Yes all i want is simple but reliable
August 23, 2017 at 11:53 am
Michael L John - Wednesday, August 23, 2017 8:09 AMIf you decide to use an availability group, you may need Enterprise edition. Standard edition only supports one database in an AG, so if you have more then one database that needs to be part of this, you will need enterprise.And, if there are only 2 nodes, you will also need a witness as a "tie breaker". The witness does not need to be a third server. It can be a file share.
If you want to read, start here:
http://qa.sqlservercentral.com/stairway/112556/
Well... 2016 standard edition could still work. The setup is more complicated but it can be done. Standard (2016 sp1 to be clear here. Pre sp1 does not support basic availability groups) also supports automatic failover. We have a client that we are working through the setup of multiple BAGS with listeners on Standard edition.
Here is a method to do that advanced BAGs setup
https://sqljudo.wordpress.com/2016/07/25/a-group-of-basic-availability-groups/
The fileshare witness does need to be sitting on a server not acting as a node in the AG. I do recommend adding this additional "vote".
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
August 23, 2017 at 1:11 pm
So i went ahead and setup a alwayson, created a cluster between my two servers, and setup always on HA initially i did failover mode: manual and tested my application using the listeners, manually switch it over and worked fine.. Now how does the automatic failover works? I understand it uses windows clustering for it, does that mean that the entire server has to go down for it to automatically switch over? if the SQL server service gets shut down will it move over as well?
August 23, 2017 at 1:24 pm
miguelv06 - Wednesday, August 23, 2017 1:11 PMSo i went ahead and setup a alwayson, created a cluster between my two servers, and setup always on HA initially i did failover mode: manual and tested my application using the listeners, manually switch it over and worked fine.. Now how does the automatic failover works? I understand it uses windows clustering for it, does that mean that the entire server has to go down for it to automatically switch over? if the SQL server service gets shut down will it move over as well?
Broad strokes:
The sql instance on the primary can go down or become stable which would raise a condition causing the automatic failover. The listener will redirect traffic to the secondary node and the secondary node will switch to the primary node. The sql services are always running on both nodes in Availability Groups - much the same as mirroring.
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
August 23, 2017 at 2:12 pm
miguelv06 - Wednesday, August 23, 2017 9:59 AMTheSQLGuru - Wednesday, August 23, 2017 9:45 AMmiguelv06 - Wednesday, August 23, 2017 9:30 AMOn the test i have done mirroring takes 2-3 seconds... But i can live with 30 seconds... I will try this and see how it goes.If you want SIMPLE, I would stick with mirroring honestly. Works fine and is pretty bullet-proof (Always On has had a LOT OF BUGS AND STILL MORE ARE GETTING FIXED WITH VIRTUALLY EVERY CU).
What is your concern over having a "witness server"? That can be a free version of SQL Express.
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-witness
Yes all i want is simple but reliable
Why is it that you think a witness server for classic database mirroring isn't reliable?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply