Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: Database File Configurations

    Hi,

    We have been given a new Database server with SAN drives attached for storage. We will see the SAN as 4 separate drives on the server (D,E,F,G). Drives D and E will be a 7 disk array using RAID 5. F and G will be s sperate 7 disk array using RAID 5 also.

    My question is how best to configure our databases on these drives. I was thinking of splitting the systems db's, user db's, indexes and logs like this:

    D: System DBs
    E: User DBs
    F: Indexes
    G: Logs

    but i'm not sure if that is the best solution. I wanted to keep data and indexes seperate as well as data and logs seperate. I'm not sure if it makes sense to keep the system dbs on there own filegroup or not.

    Any ideas or best practices?

    thanks
    Scott

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Depending on how much I/O you are expecting that should be a good start. One thing to remember is to make sure the disks are formatted with a 64KB allocation unit size. Will you have all your backups going to tape?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you have system databases that use any significant percentage of a seven drive array, you already have some serious problems.

    Your SAN manager and architecture will determine a lot of things in terms of what is possible and what is optimal for database performance. What works well on an EMC may not perform well on a Pillar, and if a configuration works for them it will probably not work for a NetApp and vice versa.

    You need someone to sit down and understand either your database or your SAN, then engineer a solution that works well for both. If you already own your SAN, the only choice you have is to work backward from the SAN architecture to get to a good arrangement for the SQL Server. In order to work backward from the SAN architecture, I would have to understand the SAN architecture.

    The only person/group I would trust to "shoot from the hip" for SAN to SQL architectures would be Brent Ozar, and he doesn't do that for free. Spend some time to study up on what your SAN offers, come back here and ask questions, be prepeared to try several configurations before you settle on one for long term use.

    The task of matching SAN to SQL is a big one, but with a bit of learning, work, and patience you can find or make a good fit!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Sep 2009
    Posts
    62
    I guess my SAN knowledge is not up to par, but according to our SAN Admin what we have is a DELL PowerVault MD1000 SAN that is split into 2 7-drive RAID 5 arrays. They are also serial-attached (SAS) drives.

    the database server contains about 15 user databases ranging from 100MB to about 75GB in size. I know the system databases are small and probably don't need to sit on a drive by themselves, which is why i was thinking this might not be the best solution.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    tempdb is getting to be a lot bigger in a lot of applications these days. I don't find it too unreasonable to see a sizeable tempdb, if there are say database snapshots in use.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a pure SWAG, I'd start with:

    D Data
    E Log
    F Backups
    G TempDb

    You'll need to look at the read/write percentage settings, and the cache settings too. TempDb needs a lot of cache, Logs need relatively little. Logs and backups are very write biased, TempDb and data tend to be about even. All of the drives ought to be set for 64 Kb paging, and it is critically important to align the data and log files on 64Kb boundaries (to take advantage of the hardware paging).

    Spend some time reading Brent Ozar's website for more insight into the geek details of setting up a SAN for optimum performance with SQL Server.

    -PatP

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2009
    Posts
    62
    Pat,

    thanks for the info. I will need to get more familiar with the SAN world as it pertains to SQL Server...

    Scott

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One other thing to keep in mind... There's a pretty wide gap between, awful, Ok, and fabulous in terms of performance. You need to hit Ok, but you probably don't need to make it all the way to fabulous. Be sure to make good decisions, and when things don't perform well enough find measurements that will help you fix things.

    I can't begin to stress this enough. I've seen people deploy SQL on a SAN and have it work Ok out of the box. I've seen atrocious first efforts that literally took minutes or hours to tune to get performance that was better than the users had ever seen before. I've also seen people drop man-years into tuning a SAN and get no material gain from their effort. Decide on what end user measurements will mean "good enough" for them, and use that as your prime critereon for setup/tuning/management. Once you reach that "good enough", think long and hard before you put more time/money/etc into the quest for better tuning... It is very rarely worthwhile.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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