patternsqlMinor
Configuring physical storage for a Microsoft SQL Server installation
Viewed 0 times
serversqlstoragemicrosoftinstallationforphysicalconfiguring
Problem
I'm configuring a Microsoft SQL Server installation which will use a SAN for storage. For SAN storage I have:
My SAN supports storage tiering.
I'm on the fence about how to use the SSD disks. Here are the options that come to mind:
-
Option 1: Create tiered storage with the SSDs and 10K disks and use auto tiering to move highly accessed data to the SSDs. Then break that storage pool up into several different drives.
-
Option 2: Place key (I/O intensive) data on the SSDs and throw everything else on the 10K disks.
Questions
I don't have enough SSDs so that everything can reside on SSDs. So what's most important performance wise for SSD storage?
Secondarily, are there any best practices I should follow when setting up the drives?
Should I have separate drives for each
What about the system databases like
Server disk activiity
By the way, here is a screenshot of our existing database server disk activity. It seems the
Any advice would be appreciated.
- A small number of SSD disks
- A larger number of 10K disks
My SAN supports storage tiering.
I'm on the fence about how to use the SSD disks. Here are the options that come to mind:
-
Option 1: Create tiered storage with the SSDs and 10K disks and use auto tiering to move highly accessed data to the SSDs. Then break that storage pool up into several different drives.
-
Option 2: Place key (I/O intensive) data on the SSDs and throw everything else on the 10K disks.
Questions
I don't have enough SSDs so that everything can reside on SSDs. So what's most important performance wise for SSD storage?
tempdbs? LDF files? MDF files? My database is more write intensive than read intensive (if that makes a difference).Secondarily, are there any best practices I should follow when setting up the drives?
Should I have separate drives for each
tempdb? LDF files? MDF files?What about the system databases like
master, model etc., should those go on their own dedicated drives or maybe on the C: drive?Server disk activiity
By the way, here is a screenshot of our existing database server disk activity. It seems the
tempdbs are the most read/write intensive followed by our MDF file and surprisingly the LDF file seems to have the least I/O.Any advice would be appreciated.
Solution
First and foremost, look at your SAN vendor's documentation for recommendations regarding storage for SQL Server. This really should be your first step as the vendor hopefully has done a lot of this general analysis for you.
If the documentation doesn't mention hosting databases, do some more digging before you choose to go with Tiered Storage. Understand how data migration between tiers works on your SAN and how frequently it occurs. Beware of anything that will run a scheduled job that tracks how active sectors may be during a time frame (often a 24 hour period, but this if often configurable). I've found that in this scenario when it comes time to migrate the data, that data is often slow to access during the operation. You'll want to insure this process runs during a period of low db activity (which means don't run this during the same time you're backing up your database). If you have a database that's active all day, I'd recommend you NOT use tiered storage at all if this is how tiering works on your SAN. Another problem with migrating data between tiers when this type of analysis is performed is that often times data access patterns change and are not consistent throughout the day. This can result in data living on the faster tiers that really shouldn't be there. For instance, when you run Index Maintenance overnight the SAN may flag that data as hot and migrate it to a higher tier. If those indexes don't get accessed during the next migration analysis window, you're now wasting I/Os on idle data. Depending on your database usage patterns, this could happen quite often where your data is getting migrated to the faster tier only to sit there idle.
Again, look at your vendor documentation. I would hope they clearly outline the recommended approach needed for their hardware. Also, give SAN Storage Best Practices for SQL Server, from Brent Ozar a read. That goes into much more depth on some best practices and is well worth the read.
If the documentation doesn't mention hosting databases, do some more digging before you choose to go with Tiered Storage. Understand how data migration between tiers works on your SAN and how frequently it occurs. Beware of anything that will run a scheduled job that tracks how active sectors may be during a time frame (often a 24 hour period, but this if often configurable). I've found that in this scenario when it comes time to migrate the data, that data is often slow to access during the operation. You'll want to insure this process runs during a period of low db activity (which means don't run this during the same time you're backing up your database). If you have a database that's active all day, I'd recommend you NOT use tiered storage at all if this is how tiering works on your SAN. Another problem with migrating data between tiers when this type of analysis is performed is that often times data access patterns change and are not consistent throughout the day. This can result in data living on the faster tiers that really shouldn't be there. For instance, when you run Index Maintenance overnight the SAN may flag that data as hot and migrate it to a higher tier. If those indexes don't get accessed during the next migration analysis window, you're now wasting I/Os on idle data. Depending on your database usage patterns, this could happen quite often where your data is getting migrated to the faster tier only to sit there idle.
Again, look at your vendor documentation. I would hope they clearly outline the recommended approach needed for their hardware. Also, give SAN Storage Best Practices for SQL Server, from Brent Ozar a read. That goes into much more depth on some best practices and is well worth the read.
Context
StackExchange Database Administrators Q#184733, answer score: 4
Revisions (0)
No revisions yet.