5

I've planned to host many servers (50+) each will have more than 20K users stay connected. There is lot of shared data amongst users (e.g. a user needs to refer data prepared by another user).

If I host single database server (preferably Microsoft SQL server), all the (50*20000) queries will be queued to that single server which could be major performance bottleneck. Is it possible to have multiple servers sharing single database. So that it will enable me to distribute my queries across multiple database servers. If this is not possible, what are the options available?

Update: This is completely new game application being developed and I want the design to be highly scalable. As each user's score changes (increases or decreases by certain number) it will be updated to the server. In turn Server will reply with "list of nearest 50 users" to that score. This is planned to happen in not less than 1 minute per user.

Thus if DB table is indexed on score, we can say single write and 50 reads will happen per minute by each user. Now if 20K users are connected to each "connection server" and all those "connection servers" in turn querying single DB Server, it is effectively queuing all the million requests to single DB Server. Which obviously cannot complete all those requests in a minute.

Hence, I was looking if we can distribute those queries across multiple SQL servers which share single database.

migrated from stackoverflow.com Jan 20 '13 at 21:58

This question came from our site for professional and enthusiast programmers.

  • What proportion of the queries are reads vs. writes? What is the query rate, or do you just have estimates? Is this a relatively new application, or a legacy application that's getting bogged down due to increased demand? As it sits, there are way too many variables to give you a good answer for your situation. – Jon Seigel Jan 21 '13 at 4:38
  • Please check now I've updated this question with more details and overall idea about the application. – user1976551 Jan 21 '13 at 6:14
  • Your edit indicates 20k write tpm, coupled with 1M read tpm. On paper, that's in the zone for off the shelf run of the mill hardware. It's certainly nowhere near the scale-out vs scale-up decision unless the datasets are very large. Either way, given the low write/read ratio of the workload I concur with @AaronBertrand's suggestion for 2012. If the budget is challenging, federated servers. If millisecond accuracy isn't important, damn the database and cache in the app tier. Personally, my nose starts twitching (code smell) whenever replication is floated for this type of scenario. – Mark Storey-Smith Jan 22 '13 at 1:13
2

Based on the requirements that you've posted, I'd recommend using transactional replication to spread the workload to a farm of servers, then put the farm of read-only SQL Servers behind a hardware load balancer which can then balance the connections across the farm of SQL Servers.

The application will need to be written in such a way that the writes are all done to the main server (which you'll want to have clustered, or mirrored, or setup in an Availability Group for high availability) and have it connect via the load balancer for reading data.

This will give you "near real time" data sync across the farm of read only servers.

I've setup configs like this before for massive workload. It works quite well when done correctly, the downside being that if you don't do it right the first time, it's a mess to fix.

  • Great one to see from someone who has already done something similar :) Many thanks. – user1976551 Jan 21 '13 at 19:30
  • I wud like to make a plan based on your inputs: 1. Listening "application" and "read only sql server+db" will be on same box. All users will connect to these boxes via load balancer. 2. Transaction replication is set such that "main server" is publisher and these boxes are subscribers. 3. "application" will write straight to "main server" but will read from local sql server. I hope this arrangement will wrk for me ! Please lemme know yr view. – user1976551 Jan 21 '13 at 20:37
  • That should work. The problem that I see here is that if you need to reboot an application server, when that application comes back up the data on that machine won't be current until replication comes back up. I also see a licensing cost issue as you scale out the application server. Unless you are using SQL Express the licensing costs are going to get pretty expensive pretty fast. – mrdenny Jan 21 '13 at 21:45
  • Being it is free, are there any limitations will I face with SQL Express as long as my req concerned? (viz. I plan to cater to 20K users per server) – user1976551 Jan 22 '13 at 14:51
  • The SQL Express instance will only use 1 Gig of RAM, it will only use 1 processor and the database can't be any larger than 10 Gigs (if using SQL 2012 Express, smaller for older versions). There's no limit to the number of concurrent users other than the normal limits. – mrdenny Jan 22 '13 at 22:10
5

No, you can't have the same data from a single database shared across multiple servers, at least not in read/write mode. At the risk of ignoring that you might be prematurely optimizing here, there are some pretty common ways to scale SQL Server, but they aren't all as simple as you might expect, and they also might not meet all of your requirements (or budget, since for many of the options Enterprise Edition is required).

READ/WRITE

For read-write scaling you can "shard" data using federated servers and distributed partitioned views. But note that is not easy to set up. Another options is merge replication but it can also be a bear to set up and maintain.

READ ONLY

You can scale out reads only to multiple other servers by using Log Shipping (or simple backup / restore), transactional replication, Database Mirroring (though this feature is deprecated, so be careful about long-term projects) and Database Snapshots of the mirror, or in SQL Server 2012, AlwaysOn Availability Groups with readable secondaries. (Some good but admittedly biased comparisons with competing vendor solutions here.)

As an aside, I really don't like the term "active secondaries" as that implies to me that you can read/write to them, which you can't.

EDIT

Given the new requirements in the updated question, it seems the part that needs to scale is almost entirely read. One suggestion might be to have a small instance on each application server (could be Express so long as its resource limitations - most importantly 10GB data file - are sufficient). This instance would serve two main purposes: (1) queue updates to the primary server so that the application doesn't have to wait for them (and so that multiple updates from different users can be combined) and (2) serve as a data store for the read-only information so that this doesn't always have to ve retrieved from the primary server. With this idea there isn't really a simple "turn it on" feature that will help accomplish this, but the setup is pretty trivial in the grand scheme of things.

0

You may be able to consider peer-to-peer transactional replication where all the participating instances act as both publisher and subscriber of the replicated DB and its articles. It first became available with SQL Server 2005. If you want to pursue the option, please be sure to read up the requirements on how to synchronize schema changes and adding/removing peers (quiescing) as well as conflict resolution. Like merge replication that Aaron indicated above, the guidelines can be quite restrictive and if you encounter a replication conflict, it can be quite difficult to correct.

  • I've updated this question with more project specific details. Can peer-to-peer transactional replication be useful in this scenario? – user1976551 Jan 21 '13 at 6:16
  • @user1976551 Since 99% of the activity you're trying to scale is a read-only workload, I don't know how much scaling writes really helps you. I think you should focus on scaling reads. – Aaron Bertrand Jan 21 '13 at 13:56

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.