A solution that will work with SQL Express would be ideal, SQL
Standard is fine, but I dont want to have to upgrade to SQL
Enterprise.
If you want something straight out of the box, that's going to be difficult to find. However, 2016 does bring Standard Edition Availability Groups. Standard Edition before 2016 does have Mirroring. Additionally if you're using asp.net and using the session state databases, there may be additional considerations (like not mirroring or putting it into an AG as the data is volatile and not worth/needed kept).
A) What options do I have/suggestions do you have regarding keeping
SQL on the IIS servers. I would rather span SQL over the 2+ web
servers then have to add additional servers just for SQL.
The best practice for scale would be to put SQL on its' own server. If that's too pricey, it would be possible to put it on the same server as your web services... this brings up a few risks (security, performance, uptime).
B) Are there any new features coming out in SQL 2016 that will help
with this?
Yes and No. Yes, Basic Availability Groups are available but I honestly don't believe it's the right fit for your situation. I actually don't believe Availability Groups in general is a good fit for what you've described.
Although I know this is not exactly a DBA question, but I'll ask
anyway. Is there a way to force a asp.net to write to all available
SQL databases at the same time, but can read from any of them?
Unfortunately there is nothing inside of SQL Server out of the box except for Replication. In this case you could have Peer-To-Peer, but again that's an Enterprise Edition feature so we have to throw that away. It would be possible with Merge Replication but to be honest that's a pretty terrible beast I wouldn't wish on anyone.
Taking into account your given resources and in house abilities (not meant to be rude) I would say these are the items to investigate further to see which fits your situation the best (as you're the best person to make that decision as you know the environment).
1. Hosting in Azure
I want to be completely transparent, I work for Microsoft. Having said that, it could be the most cost effective and RPO/RTO effective to transition the application to Azure.
Why? There are a few key resource constraints in your current environment that could make it rough to achieve the uptime you're looking for balanced with cost and people resources. In Azure, there can be multiple copies of the database and you can spin up/spin down (elasticity) web hosts. This gives you the RPO/RTO you would like without having to know any of the SQL Server parts and configure it through a clickable GUI. This can be scaled up/down per cost control and usage.
The biggest win is you get the RPO/RTO and don't have to buy hardware or software.
2. SQL Server Mirroring
Mirroring doesn't require a Windows Cluster or a host of other pre-requisites like Availability Groups do. It is generally much easier to setup and is straight forward. It does require at least Standard Edition and there would most likely be licensing costs per instance of SQL Server, depending on a few things.
Mirroring allows for single database failover and a witness server can be used with SQL Server Express Edition. Additionally .NET applications have the ability to set a failover partner server in the connection string to help facilitate a more transparent failover scenario. Mirroring does not require a listener or client access point like Availability Groups do, thus it's very easy to use an IP load balancing solution to do health checks and automatically point to the proper host. This does not allow for multi-master scenarios, there will still only be a single "master" server which will then update the single partner server.
This is much less flexible of a design, but meets the RPO/RTO targets and requires the least amount of administrative upkeep (other than Azure). It is a deprecated (read, removed) technology but should get you through until 2014 is out of mainstream support and there may be other solutions.
3. SQL Server Clustered Instances
This should meet the RPO/RTO requirements as well as be one of the easier implementations if you're already familiar with Windows Clustering. If you are not familiar with it, then I'd probably stay away from SQL FCIs or have a contractor come in and set it up (if you choose this route).
FCIs only hold one copy of the database and do requires shared storage to implement. There are also the Windows Clustering requirements. It does, however, provide transparent failover and can be used with Standard Edition. There needs to be at least two servers to provide high availability.
4. Log Shipping
This is not a good fit but does most of what you'd like, doesn't deliver the RPO/RTO but does meet the cost requirements. It's also extremely easy to setup and does require at least Standard Edition.
This basically works by taking log backups through SQL Server agent, copying them to a location that all instances can see, and then the secondary servers check the location periodically and restore the latest log backups. Everything after that is completely manual. Though, again, it could be used with a CNAME or IP load balancer to make things a little more seamless. It would still require a ton of administrative work to do a failover and then setup reverse log shipping to keep all the other secondary servers in sync.
Aaron Bertrand has a good post of doing this on a budget while having "readable secondary servers"
Best Answer
If you want it set to 1, then you should configure your backup preferences properly.
The reason it is not returning a
1
is because it shouldn't be based on your current preferences. Right now, all of your replicas have the exact same backup priority which means we're going to next look at replica names. SinceUK-DR-SQL001\WSS
sorts first, that should be the secondary (given thatUK-SB-SQL01\WSS
is your primary) that the function returns a1
on.If you want the backups to happen on a different server, then set the priority appropriately.
Here is an extremely simple repro showing that given your current settings,
UK-DR-SQL001\WSS
should be the replica which a1
is returned.It seems to be that this isn't setup or running on the DR server, or a backup would at least be attempted.