| Author |
Topic |
|
maxi_ng
Starting Member
6 Posts |
 Posted - 2009-09-02 : 06:07:17
|
| I am very very new to large system and not a DBA, I have done some research on scale out and not sure which approach is better, so I come here for help.any advice is welcome, thank you so much.1. Acceptable down-time is about 1hr tops, not a "must not fail" kind of application.2. two groups of users, one will write data to database and another just read data from it.3. A 'data writer' may perform 150000 insert query per 15 mins.4. A 'data reader' may perform 750000 select query all at once. I don't think they are willing to wait over 10s to 'see' their result.5. I can buy many servers and sql server licenses to scale things out.As far as I know, their are a few options. (maybe misunderstood by me, correct me if I am wrong, thank you)1. Service Broker - can redirect query to different database, and cross instance. With this approach I think I can handle a certain number (let's say 100) users by one instance of database which run on its dedicated server. I think this would good for the 'insert' and fairly good for 'select'. But the bad things are the programming will be compilcated and that service broker server will be very busy. (scale out the broker? will be even more complicated... I am very scared)2. Publisher and subscribers - all insert are done on the publisher database server and publish to all subscribers. All select are handle by subscriber databases. This should be simpler then the first approach. Does it suits my requirements?3. Mirroring/Transaction replication - I think its advantages are auto fail over and synchronized data. But if I am doing so much 'insert', I think the replication will just make this scale out useless, is that so?please tell me which one is better or even better approaches are welcome!Thank you very much for any reply again. Cheers |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ScottWhigham
Starting Member
49 Posts |
Posted - 2009-09-03 : 10:28:52
|
quote:
Originally posted by maxi_ng
4. A 'data reader' may perform 750000 select query all at once. I don't think they are willing to wait over 10s to 'see' their result.
Couple of things:1) What size database are we talking about?2) How "up to date" does the 'data reader' have to be? In other words: is there an acceptable lag between what was loaded by the 'data writer' and when it can be read by the 'data reader'?quote:
Originally posted by tkizer
You can't use mirroring to scale out, since the mirror database is not available for queries until you failover to it.Have you looked into the READ_COMMITTED_SNAPSHOT isolation level? That's what we implemented to prevent reads and writes from contending with each other.
I was thinking that a database snapshot (refreshed as needed) could help out with the data readers but I'm concerned that with how much of a latency the 'data readers' will allow.========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ScottWhigham
Starting Member
49 Posts |
Posted - 2009-09-04 : 13:19:52
|
| I wasn't trying to imply that they were in any way related; I was just considering that, if he could have a delay of 12-24 hours between what the writers write and the readers need to read, a database snapshot would be a nice way to segment the use and provide a point-in-time view of the data.========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx |
|
|
|
maxi_ng
Starting Member
6 Posts |
Posted - 2009-09-06 : 21:34:09
|
quote:
Originally posted by ScottWhigham
quote:
Originally posted by maxi_ng
4. A 'data reader' may perform 750000 select query all at once. I don't think they are willing to wait over 10s to 'see' their result.
Couple of things:1) What size database are we talking about?2) How "up to date" does the 'data reader' have to be? In other words: is there an acceptable lag between what was loaded by the 'data writer' and when it can be read by the 'data reader'?quote:
Originally posted by tkizer
You can't use mirroring to scale out, since the mirror database is not available for queries until you failover to it.Have you looked into the READ_COMMITTED_SNAPSHOT isolation level? That's what we implemented to prevent reads and writes from contending with each other.
I was thinking that a database snapshot (refreshed as needed) could help out with the data readers but I'm concerned that with how much of a latency the 'data readers' will allow.========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx
Data should be writen every 15 mins (or less). The reader prefer reading 'real-time' data, that is I think no more than 15 mins lag.I have been suggested to use publisher/subscriber approach.Is that okay? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
maxi_ng
Starting Member
6 Posts |
|
|
maxi_ng
Starting Member
6 Posts |
Posted - 2009-09-08 : 22:00:07
|
I have checked that out. It is a feature that allow advance locking mechanism that speed up read/write transactions of a database.This sure help scaling up the database. Thank you.Do you have any suggestion on scaling out?I need that information too.quote:
Originally posted by maxi_ng
quote:
Originally posted by tkizer
I wouldn't bother replicating this data just yet. Check out READ_COMMITTED_SNAPSHOT first.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
okay, let me check that out
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-09-09 : 13:15:30
|
| Honestly I don't think that scaling out is needed if the system is properly designed and if you have suitable hardware. But to answer your question, we have used transactional replication to scale out some systems. We put reports on the subscriber system as well as read-only type queries. I don't have experienced with the service broker, so I'm unable to comment on that much. I did go to a MySpace presentation that described their architecture (they have thounsands of SQL Server 2005 databases) and they make heavy use of service broker. They almost entirely got rid of replication by instead using service broker and saw huge gains with that.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
maxi_ng
Starting Member
6 Posts |
Posted - 2009-09-09 : 22:13:50
|
This is great, tkizer.I am having the exact same plan as yours.I mean it is good to know that scaling out is not that necessary.I am just afraid by not knowing how much data a sql server can support. I feel better now after reading your opinion/experience.I will be using a single sql database and scale out to subscribers which act as read-only queries servers.I have read a little about service broker. It is complicated and not needed by my system for the time being (I do not have even hundreds of sql server...).quote:
Originally posted by tkizer
Honestly I don't think that scaling out is needed if the system is properly designed and if you have suitable hardware. But to answer your question, we have used transactional replication to scale out some systems. We put reports on the subscriber system as well as read-only type queries. I don't have experienced with the service broker, so I'm unable to comment on that much. I did go to a MySpace presentation that described their architecture (they have thounsands of SQL Server 2005 databases) and they make heavy use of service broker. They almost entirely got rid of replication by instead using service broker and saw huge gains with that.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
|
|
|
|
|
Scale out my database, which approach should I use