none
Single DB shared across multiple servers RRS feed

  • Question

  • 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, lets assume I've 50 "connection servers" and around 20K users are connected to each connection server.  All these connection servers in turn querying single DB Server. So it is effectively queuing all the 50*20000 (means millions of) requests to single DB Server. Which obviously cannot complete all those requests in a minute.

    Hence, I am looking if 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, how can this be achieved?

    Monday, January 21, 2013 12:43 PM

Answers

  • >I am looking if is it possible to have multiple servers sharingsingle database

    That is not a good idea.

    Look into caching query results by webservers and reporting server (SSRS).

    Also, check into data warehousing & OLAP cube architecture.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/

    For additional assistance, provide full details with query & DDL.


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Monday, January 21, 2013 3:10 PM
    Moderator
  • Caching is an interesting thought, but in the end, it really depends on how "true" you need your data to be, and how much data you have. It seems to me that perhaps you need to split the data into transactional (needs to be right) and non-transaction buckets (nice to be right). I might cache locally in SQL Server. The query to find the 25 greater and 25 lesser scores would be fairly easy, but it could be 10s or 100s of thousands of rows, depending on the number of players running concurently.

    Starting with the user's game score, that is transactional. If I get 100 points, I by golly had better get 100 points. If I notice I lose 100 points that I expect I got, then I am not going to be happy with your service.  But if the 50 scores nearest my score is missing some other scores that are happening concurrently... eh. Will anyone really know.

    I wouldn't go ahead and expect the need to have > 1 database servers, but planning ahead, you could have a table with final scores and user names that you build regularly from new scores coming in (probably an insert SSIS type package to insert new scores, and a delete package to remove old scores (perhaps scores where the game has ended?)), and that table you might replicate to the other servers. Users would be sticky to the DB server they are storing their data along the way, but you would maintain the score list on all servers and replicate the data using some method (replication, ssis, whatever works :))

    Louis


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, January 22, 2013 7:41 PM
    Moderator

All replies

  • >>>his is planned to happen in not less than 1 minute per user.

    What is amount of the data users to return? 100 rows, 100K rows? You can take a look at SNAPSHOT isolation level which prevents readers from locking writers......

    A disadvantage of multiple servers is a maintenance. 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, January 21, 2013 12:51 PM
    Moderator
  • >>>his is planned to happen in not less than 1 minute per user.

    What is amount of the data users to return? 100 rows, 100K rows? You can take a look at SNAPSHOT isolation level which prevents readers from locking writers......

    A disadvantage of multiple servers is a maintenance. 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/


    In the DB, table columns would be "user_id" and "score" and 50 rows of this table will be returned to user.
    Monday, January 21, 2013 1:09 PM
  • I see no performance problem, have a CI on userid and score columns (should be testing)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, January 21, 2013 2:07 PM
    Moderator
  • >I am looking if is it possible to have multiple servers sharingsingle database

    That is not a good idea.

    Look into caching query results by webservers and reporting server (SSRS).

    Also, check into data warehousing & OLAP cube architecture.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/

    For additional assistance, provide full details with query & DDL.


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Monday, January 21, 2013 3:10 PM
    Moderator
  • Caching is an interesting thought, but in the end, it really depends on how "true" you need your data to be, and how much data you have. It seems to me that perhaps you need to split the data into transactional (needs to be right) and non-transaction buckets (nice to be right). I might cache locally in SQL Server. The query to find the 25 greater and 25 lesser scores would be fairly easy, but it could be 10s or 100s of thousands of rows, depending on the number of players running concurently.

    Starting with the user's game score, that is transactional. If I get 100 points, I by golly had better get 100 points. If I notice I lose 100 points that I expect I got, then I am not going to be happy with your service.  But if the 50 scores nearest my score is missing some other scores that are happening concurrently... eh. Will anyone really know.

    I wouldn't go ahead and expect the need to have > 1 database servers, but planning ahead, you could have a table with final scores and user names that you build regularly from new scores coming in (probably an insert SSIS type package to insert new scores, and a delete package to remove old scores (perhaps scores where the game has ended?)), and that table you might replicate to the other servers. Users would be sticky to the DB server they are storing their data along the way, but you would maintain the score list on all servers and replicate the data using some method (replication, ssis, whatever works :))

    Louis


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, January 22, 2013 7:41 PM
    Moderator