none
Read-only database

    질문

  • I have the following high-level requirements:

    - All data is collected in the central database (billions of records)
    - There is a number of 24x7 edge databases from which data is read
    - The only way for data to get into edge database is from the central database -- the edge databases are "ready-only" for all other purposes
    - There is extremely high volume of reads against edge databases
    - Transactions are not important -- meaning that if one edge database is ahead of the other one on updates, that's Ok

    What technology would you recommend for this scenario? Log shipping, read-only databases, replication, etc...

    Thank you.

    2012년 4월 20일 금요일 오후 5:02

답변

  • With mirroring you'd be able to have only 1 edge database.

    What version of SQL Server do you use (2008 or 2012)? How data is changing in the master database? How many changes do you have between "refreshes" of edge databases?

    And most importantly, what is the purpose of this architecture? By any means it would not be cheap solution - you'll need to have licenses for all servers that host "edge" database, develop "load balance service", etc. At the end it could be easier and cheaper to architect and optimize solution that would use the single server.


    Thank you!

    My blog: http://aboutsqlserver.com

    2012년 4월 21일 토요일 오후 1:46
  •  I am not sure that partition view is the good way. Each time you re-define the structure (Am I right?) SQL Server obtains schema locking which may affect performance.... But ,sure I do not know your business rules

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

    2012년 5월 1일 화요일 오전 6:41
    중재자

모든 응답

  • Correct me if I misunderstood , But one option I would suggest to check database snapshot

    http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

    good simple acticle.


    Ali Maqbool | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

    2012년 4월 21일 토요일 오전 7:17
  • Interesting. It seems that Mirroring together with Snapshot may be the answer for the edge databases. Thanks.
    2012년 4월 21일 토요일 오전 10:25
  • With mirroring you'd be able to have only 1 edge database.

    What version of SQL Server do you use (2008 or 2012)? How data is changing in the master database? How many changes do you have between "refreshes" of edge databases?

    And most importantly, what is the purpose of this architecture? By any means it would not be cheap solution - you'll need to have licenses for all servers that host "edge" database, develop "load balance service", etc. At the end it could be easier and cheaper to architect and optimize solution that would use the single server.


    Thank you!

    My blog: http://aboutsqlserver.com

    2012년 4월 21일 토요일 오후 1:46
  • It's a new project, so we can go with SQL Server 2012. 

    The data changes throughout the day. It's financial market data, and we would like to push the deltas to edge databases every 15 minutes.

    My reasons for considering this:

    - The edge databases serve "read-only data" as fast as possible -- everything else is secondary. I don't want readers of the data to be contending with the process that creates the data.

    - I want to have the flexibility of having multiple edge database in different geographies. 

    What about Change Data Capture? Would that be something to consider?

    Thanks.


    2012년 4월 21일 토요일 오후 8:13
  • as far as I know, the 2012 edition allows for Read Only Mirrors, which should be sufficient to get a good live DB version, this was a feature people were craving for in 2008 R2 as a mirror DB would only be mounted in restore mode when acting at the failover partner, and inaccessible.

    Otherwise it would probably have to be snapshot/merge replication to get that quiet time when the read speed is important.

    2012년 4월 27일 금요일 오전 10:11
  • Do you mean to copy all the data or just specific objects? How often do you plan to copy the data? As others have already mentioned SNAPSHOT replication would be probably the best choice.

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

    2012년 4월 30일 월요일 오전 2:35
    중재자
  • It's market pricing data, so during exchange open hours, the pricing data for the past 15 minutes need to be applied to edge databases. There virtually no updates to the existing data.

    I will certainly look into read-only mirrors.

    I also have this idea of doing something custom in the edge databases, such as having a partitioned view over a set of tables, then having another table receive the new data for the latest 15 minutes, and then each 15 minutes redo the definition of the partitioned view to include the table with the latest updates. In other words, having a partitioned view that's redefined each 15 minutes to include the table that contains latest data. Do you think this is a good way to split readers and writers?

    Thanks.

    2012년 4월 30일 월요일 오후 4:20
  •  I am not sure that partition view is the good way. Each time you re-define the structure (Am I right?) SQL Server obtains schema locking which may affect performance.... But ,sure I do not know your business rules

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

    2012년 5월 1일 화요일 오전 6:41
    중재자