3

I've been thinking about building a website which could potentially get a lot of traffic. It would be a research site of sorts with wiki capabilities. So users would be able to edit information on the site. However, 99% of users would probably never edit anything and would simply be searching / browsing the site. So they could theoretically do most things from a readonly copy of the database.

With that in mind, I was thinking that putting together something like this would work well... enter image description here

So there would be 3 physical servers in the same datacenter:

  • The "Primary SQL Server" would receive any UPDATE, INSERT, or DELETE operations.
  • The two web servers would have readonly copies of this database, which would be kept up to date with log shipping. These servers would be queried heavily as users browse the site and would also serve up ASP.NET pages through IIS.

I like that this model improves both scalability and reliability at the same time. If either web server goes down there'd be no interruption of service. If the Primary SQL Server goes down, certain functions of the site would be disabled but the site would still be up in a readonly state, so to speak. Then last of all, the load balancer would distribute load between the two web servers, which is nice.

But I have a couple questions about this:

  1. Is log shipping a good method to accomplish this?
  2. What is the minimum version of SQL Server required to do this? For example, would it be possible to run SQL Server Web edition on the Primary SQL Server and SQL Express on the web servers?

Thanks for your help.

Steve Wortham
  • 197
  • 1
  • 9

1 Answers1

2

What you have described would work.

However if its mostly read-only you will be dealing with often the easier answer is to look into in-memory or distributed caches. So for instance, when your web servers fire up they would load "x" amount of data either into the memory on your web server or a cache server.

All user requests would go to the in-memory cache (very fast). Any updates could then trigger the cache to go back to the DB and get the latest copy for future requests.

Take a look at the Windows Server App Fabric system. In you want true HA the same set of features is available with Windows Azure and would make things even more redundant.

Brent Pabst
  • 6,069
  • 2
  • 24
  • 36
  • Thanks Brent. I guess one thing I'd be afraid of with the distributed cache approach is what happens when the Primary SQL Server goes down? The web servers would need to have cached the entire database in order for the website to keep running. – Steve Wortham Aug 09 '12 at 18:11
  • Sure, but if thats a point of concern then you start talking about a SQL Server cluster and HA on the SQL side to make sure your server stays up. It just depends on how many levels you think you need HA and thats something only you will be able to answer. – Brent Pabst Aug 09 '12 at 18:35
  • Well, with my plan above, if the Primary SQL Server goes down, the site will still be up in readonly mode. This would be perfectly acceptable. And I think this would be considerably cheaper than building a SQL Server cluster since I wouldn't have to fork over the big money for SQL Server Enterprise edition. – Steve Wortham Aug 09 '12 at 18:53
  • You can run a two node SQL Cluster on standard and in 2008 since it uses an Active/Passive architecture you only have to buy one license for the currently active node and Windows... works out to be about the same if not cheaper since your web server SQL boxes would probably need more than the 4GB of RAM or CPU cycles depending on your load. – Brent Pabst Aug 09 '12 at 19:06
  • But like I said before you may be better off on Azure or AWS to make the design simple and redundant with the least amount of work. – Brent Pabst Aug 09 '12 at 19:08
  • I tried SQL Azure on a site for a few months earlier this year and was disappointed with its inconsistent performance. I'll have to research a bit more about SQL clusters. An active/passive architecture would work but then I don't get the benefits of scalability. My plan above still sounds more attractive to me, unless I'm missing something. For example, I don't know how efficient log shipping really is and how fast it can be. – Steve Wortham Aug 09 '12 at 19:15
  • @SteveWortham The model above was the typical solution 2 years ago. Now that distributed caches have come to fruition they are much faster and much easier to work with. Log shipping and even SQL replication can take time, especially if you get a lot of logs lined up and queued for replay back over the read-only copies. Other than that you'd have to study the MSFT architecture recommendation and best practices to compare your expected numbers versus system requirements, we won't be able to answer all of that here. – Brent Pabst Aug 09 '12 at 19:21