none
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.
    Wednesday, July 17, 2013 3:52 PM

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).
    • Marked as answer by koralage Thursday, July 18, 2013 2:07 PM
    Wednesday, July 17, 2013 9:48 PM
  • Thanks for the info. Mirroring seems like a good option for us. So far I haven't found any info about a mirror instance being free. Can you point me to any info regarding that. Thanks

    Take a look here, subtitle "Licencing".

    And here.

    Here also states about the same thing for SQL 2012:

    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.

    • Marked as answer by koralage Thursday, July 18, 2013 5:51 PM
    Thursday, July 18, 2013 2:53 PM

All replies

  • You first need to define what availability means to you. The fact that your database is running on a Windows 7 machine simply means that you do not require your hardware to be highly available.

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Wednesday, July 17, 2013 4:59 PM
    Moderator
  • 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.
    Wednesday, July 17, 2013 5:11 PM
  • 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.
    Wednesday, July 17, 2013 8:16 PM
  • Yes our budget is less than 10K. Can you set your clients to automatically switch in the case of peer-to-peer replication or mirroring? Thanks

    Wednesday, July 17, 2013 8:28 PM
  • 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).
    • Marked as answer by koralage Thursday, July 18, 2013 2:07 PM
    Wednesday, July 17, 2013 9:48 PM
  • And Syncronous Mirroring is supported in SQL Server Standard, so is Log Shipping.  SQL Standard is the highest edition supported on Windows 7.  Enterprise Edition requires Windows Server.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Shanky_621 Thursday, July 18, 2013 7:33 AM
    Wednesday, July 17, 2013 10:11 PM
  • Hi Vedran, Can you point me to some information on how to set this up with an express instance as a mirror. Thx
    Wednesday, July 17, 2013 10:12 PM
  • 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).

    Here is the link: http://msdn.microsoft.com/en-us/library/ms183671.aspx

    You could also find plenty of tutorials on other sites, even youtube videos, just put "sql mirroring tutorial" in the google.


    Wednesday, July 17, 2013 10:30 PM
  • 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

    Thursday, July 18, 2013 7:32 AM
  • Thanks for the info. Mirroring seems like a good option for us. So far I haven't found any info about a mirror instance being free. Can you point me to any info regarding that. Thanks
    Thursday, July 18, 2013 2:12 PM
  • Thanks for the info. Mirroring seems like a good option for us. So far I haven't found any info about a mirror instance being free. Can you point me to any info regarding that. Thanks

    Take a look here, subtitle "Licencing".

    And here.

    Here also states about the same thing for SQL 2012:

    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.

    • Marked as answer by koralage Thursday, July 18, 2013 5:51 PM
    Thursday, July 18, 2013 2:53 PM
  • Thanks
    Thursday, July 18, 2013 5:51 PM