2

We currently have a asp.net application running on a Win 2012 dedicated server, with a local copy of SQL 2014 Express.

We are looking to add a second web server and have them in a NLB cluster. But what to do with SQL... The website relies heavily on the SQL databases (read & writes)

I am vaguely familiar with the forms of SQL replication, availability groups, log shipping, etc and have read through quite a few posts on StackExchange regarding the same topic, but I still have a few questions.

The site has only moderate traffic, but HA and RPO is still very important. I would rather have some downtime, vs loose 1 row of SQL data, although the whole point in migrating to this setup is to decrease the chance of downtime.

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.

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. B) Are there any new features coming out in SQL 2016 that will help with this?

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? (Kinda like Windows NLB uses the same IP for all servers, posting a update/insert to the SQL IP would write it to all of them, but a query would simply use the result from the first server that answers?

Thank you all!

8

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.