Answered by:

Simple redundancy SQL Server 2012

Question

We have an application that will access a SQL Server 2012 DB installed on a Windows 7 box. The data access is pretty slow and we will have a max of less than 10 connections at any given time. The main requirement is that the DB must be available as much
as possible. I understand that SQL 2012 has AlwaysOn via windows fail over clustering. In our case we will not have a windows domain controler or a windows server. It will just be a Windows 7 box with a raid setup. Is there anyway to setup redundancy in this
configuration? Basically it is not an option to setup a Windows server and domain controller etc.

Answers

You can set synchronous mirroring with witness for automatic failover. Witness can be even free express instance. If mirror and principal are in different locations, put witness near the principal server to avoid split-brain scenario. In the client's connection
string you have to put special keyword pointing to mirror server, so both servers are in connection string. Also, in client application catch exception with error that is thrown during a failover process, and repeat the request after some pause (e.g. every
10 seconds) until it succeeds (failover finished and mirror is online, becoming new principal).

SQL Server Mirroring and HA Licensing

The rules change slightly when considering mirroring and High-Availability, but the rules for this are quite simple.

ACTIVE -> PASSIVE configurations, such as mirrored configurations or active-passive cluster/failover configurations, do not require licenses on the mirror. Note this doesn't apply if you are using a combination of mirrored and active
databases on your servers in the same instance.

ACTIVE -> ACTIVE configurations, such as clusters or HA Groups, do need licensing for every active node within the group, following the same licensing rules as described earlier in this article.

Only one passive node is waived for licensing purposes per active node. If multiple passive nodes are used, licensing on all but one of these passive nodes is required. When using active-passive configurations, the number of licenses bought must be suitable
for the server with the higher number of processors/cores, as in the event of failover the passive node must be adequately licensed.

For HA you need every hardware to be at least double. That means at least two servers. Hardware costs money, software also, and there is always a tradeoff between availability needed and the cost. You should ask how long service can be down not causing
substantial damage to the business and is it 7x24x365days or availability is measured only on workdays 9-17 etc. How many nines? What amount of data can be lost without major damage to business? What is the budget? If budget is 10 000 usd, forget about HA.
otherwise, look into peer-to-peer replication, mirroring, and even log shipping as simplest and cheapest.

So you mean SQL Server on Win 7 is a non-starter. Is this because of stability issues with regard to SQL Server and Win 7? Our simple application will have about four clients (separate win7 boxes) talking to a database and all we want is if the database
server fails to automatically point to a secondary server. The clients will get/set data about every 10 mins or so, so it is very slow access and little data. However, when the data is accessed it is critical to get/set the data.

You can set synchronous mirroring with witness for automatic failover. Witness can be even free express instance. If mirror and principal are in different locations, put witness near the principal server to avoid split-brain scenario. In the client's connection
string you have to put special keyword pointing to mirror server, so both servers are in connection string. Also, in client application catch exception with error that is thrown during a failover process, and repeat the request after some pause (e.g. every
10 seconds) until it succeeds (failover finished and mirror is online, becoming new principal).

Express instance is not allowed as a mirror, it can be used only as witness. But, correct me if I'm wrong, I think mirror instance is free if it is used just as the mirror, and not for anything else (not for reporting, for example).

First of all Please look at suggesstion given by David.Also FYI win7 is client OS and you are asking for HA feature on client OS.Windows server os is best for HA features and suports it very well .Ilike the option provided by david as it is providing u redundancy
at very low cost go for it.I have voted for his helpful post and knowledge it provided.

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

SQL Server Mirroring and HA Licensing

The rules change slightly when considering mirroring and High-Availability, but the rules for this are quite simple.

ACTIVE -> PASSIVE configurations, such as mirrored configurations or active-passive cluster/failover configurations, do not require licenses on the mirror. Note this doesn't apply if you are using a combination of mirrored and active
databases on your servers in the same instance.

ACTIVE -> ACTIVE configurations, such as clusters or HA Groups, do need licensing for every active node within the group, following the same licensing rules as described earlier in this article.

Only one passive node is waived for licensing purposes per active node. If multiple passive nodes are used, licensing on all but one of these passive nodes is required. When using active-passive configurations, the number of licenses bought must be suitable
for the server with the higher number of processors/cores, as in the event of failover the passive node must be adequately licensed.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.