March 31, 2010 at 5:08 am
hi
we will soon be purchasing a new SAN to store all our SQL databases - approx 10 in use.
what i want to know is how do i distribute these databases over the SAN?
the largest and most intensly used i thought would be best saved on its own raid array as we seem to get alot of pagelatches etc
would it make any difference?
how would you recommend i distribute them?
March 31, 2010 at 3:49 pm
Like most SQL Server questions, the answer is "It depends" 🙂
To plan something like that, there are a number of questions about the config and usage I would take into account:
For the SAN:
1. What type of SAN? Is it one that virtualizes the storage volumes/containers/whatever that brand calls it, or must you commit whole disks to any defined volume? (As in, can different SQL Server instances share disks by placing multiple independant LUNs across the same set of disks?)
2. How many disks, and their speeds and types (15k rpm FATA, 7.2k rpm SATA, SSD, etc.), the average rated small-block IOPS for them?
For each database to placed on the SAN:
1. What are the peak and average reads/sec and writes/sec for each? Answering that is one of the primary drivers in storage planning, as it determines the number of disks and RAID types for the data and log files for each database. This includes TempDB. These values can be determined by capturing the file I/O for each file in each database using the sys.dm_io_virtual_file_stats DMV repeatedly throughout the day. (We capture the values every 15 minutes into a table so we can keep an eye on performance) This is the way to determine how effective your SAN setup and the way to you files on it is. There are a number of good articles out there on measuring disk usage and performance using that DMV, both for examine the raw I/O usage, and the service times (time spent waiting for the disk system to complete requested work) on the files in your databases.
2. How much space do you need for the data, logs, and backups for each database?
-Eddie
Eddie Wuerch
MCM: SQL
April 1, 2010 at 4:49 am
Hi Eddie
the SAN must be divided into whole physical disks.
it will have 12 x 300gb SAS 15k discs
we have approx 10 database's not counting logs or system db's
the most important database (db1) has at the moment the highest reads at 14959134 - this will be priority as we are suffering quite bad slow downs, locks, pagelatch etc at the moment with it
highest writes is the tempdb! at 2157731 (it also has 2nd highest reads), the next highest writes is db1 at 672966 - should/is it ok, for the tempdb be so high?
strangely tho, our sql monitoring tools db is pretty high as well in regards to reads and is the 3rd highest sometimes 2nd!? we have some pretty intensive database that i would expect to be higher than this - it also has the 2nd highest IO stalls at 82406135. db1 is highest again with 723362332 IO stalls
Storage needed for DB's would be approx 150-200gb for current db sizes, and then 80-100gb for logs,
all backups are done to external discs/tape so no storage needed here.
from what i am reading raid 10 for the primary database's is the way forward? and maybe a 2nd array for less important db's?
hope to hear back from you!:-)
April 2, 2010 at 9:21 am
Any advice anyone? 🙂
would love to be able to get an expert in and get a bit of tutoring but its simply not an option at the moment, tho i am waiting on a SQL Admin book to get a better idea!
so if anyone can even give advice on there config of a similar setup it would be greatly appreciated !
mal
April 2, 2010 at 10:37 am
Have you asked your SAN vendor about their recommendations for SQL Server?
April 2, 2010 at 4:06 pm
Hi David
i would, only i see how its installed at the moment based on what i can tell there advice, (iv just started with the company a few weeks back and recommended the upgrade as being essential)
eg server 2003 std with sql 2005 ent, 3GB of RAM, 2 logical drives , c has windows and sql logs:crazy:, d has all the databases, and we are talking about a server which can have 300+ connections, performance is awful at times.
to be honest iv been doing alot of research myself and can understand alot of what im reading, i just like a bit of extra input when i can get it and appreciate there is no substitute for experience which i just dont really have - yet!:-D!
April 25, 2010 at 11:32 pm
Are all your servers clustered? There is also the option of using an SSD or a local storage solution like Fusion IO which is basically a PCI-E storage device. HP resells it as a data accelerators or something like that. They are perfect for tempdb. Incredible throughput.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply