Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: SQL Server Option

    Hello - I'm beginning development on a software application which will be expected to grow very large and require performance suitable for a modern web application.

    Previously Ive only created SQL server databases running on a single machine.

    Based on my reading it appears I will have to design the architecture (Translate) to suit the application - from here: Scaling Out SQL Server

    Are there any Tips that could be useful? I.e. I Know with my application cache I can build it to be easily updated to run using a Distributed Cache when the time comes.

    Should I start with a SQL Business instance on Azure and construct the database in a particular way from start?

    Any tips, information would be appreciated

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you doing this project on your own?
    If it is going to be as big as you say it is, surely you have a team that included a DBA?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Quote Originally Posted by blindman View Post
    Are you doing this project on your own?
    If it is going to be as big as you say it is, surely you have a team that included a DBA?

    Hello - at this stage no we don't have a DB Admin - can't afford it yet, so would like to move towards having a good platform - so we don't give whoever gets hired long term a Heart attack.

    Ive made some decisions:
    We have reference data - that will be stored separately to the main functions.
    Id also like to implement Horizontal partitioning - perhaps with a replicated schema (So there are nice fast little units all over the place - Customer 1 - Server 1 - Customer 10000 - Server 2 Perhaps) - Ill need to get a schema defined to help with determining the partition size.

    I was thinking replication of the login table (LoginID,Email/Username, Password, CustomerID) might be useful rather than trying to split the table by Email (As that will change) - Good/Bad idea? Least say 10 million logins (Possibly alot more - but that could be the DB Admins job).

    Customer record - Per Partition.
    Associated Child Tables - Could be up to 10 million transaction records (I would expect this to take longer than a year to tally up and would be on the larger scale of the business units involved) though I expect this would take a while (Logs would be elsewhere)

    Is there anything I would need to look out for when moving the Customer records between partitions (Should I simply start with smaller partitions - perhaps split on the same physical server - as a scale up was needed simply move those elements)

    With Horizontal Partitioning would it be a good idea to replicate the partition.

    For the most part these developments will be token to start with but will allow the code to be in a very scalable structure for later on - when it will be needed.

  4. #4
    Join Date
    Aug 2008
    Posts
    147
    If you're planning splitting on the same physical server, you'll need to consider - how you will distribute the partitioned data across different IO channels - to maximise performance
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  5. #5
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Quote Originally Posted by JackVamvas View Post
    If you're planning splitting on the same physical server, you'll need to consider - how you will distribute the partitioned data across different IO channels - to maximise performance
    I think initially the performance lost by such a design would be worth it to save on future development hassles. Also if designed well - thinking Lookup system: Then partitioned Locations each partitioned location would be queried separately, also the lookup query could have a copy of the relevant data (Search Field, Display field, something else interesting) to remove the need for queries to the partitions on lookups accross locations. Then selection of a particular record would be distinct to the connection of the partitioned server (Say edit or View detail)

    Public view for transactions, would probably be a series of constructed views/tables - containing current available numbers (Based on last processing schedule) - which would combine particular information into much more available sets with only the information needed for those business processes.

    Please correct me if this is dumb for some reason?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The database is the lowest layer, the foundation, of your application.
    You need the assistance of a DBA at the start.
    You will also need DBA assistance at the end.
    Typically it is in the middle of a project when your need for DBA assistance ebbs.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •