Hi all,
Need Some Advice…
Current Setup of the Databases:
All “system databases” + “reportserver & reportservertempdb”are on “ D drive” Raid 1 ( Mirrored)and their location is : D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
Free Space left on "D" Drive is # 18 Gb.
“User Databases” are on - “E Drive” Raid 5 and their location is E:\Data\db\
Now , we got new storage device with much larger capacity. So, we are planning to transfer our databases on this new drive which is “RAID 5”.
My question is:
(1) Should I transfer all the databases ( (i.e.) “system databases”+ “reportserver & ReportServerTempdb” + “User databases”) on that new drive ( RAID 5).
(OR)
(2) Keep the “system databases” + “ReportServer& ReportServerTempdb” on the old
drive (i.e.) RAID 1andTransfer only the “USER Databases” on that new drive (i.e.) RAID 5.
(OR)
(3) Transter only Tempdb to the new drive (i.e.) on Raid 5, so as to avoid any I/O congestion. and keep all other system & ReportServer & ReportServerTempdb on the old "D"drive.
(OR)
(4) Is there anything I should be careful when doing this& Is there any better way of doing this….
Thanks for all your help….
Hi, this is a big topic area, so I can only give generic advice based on what you have said in your posting.
The things you want to split up (get onto seperate drives) are:
1) TempDb
2) Your user mdb files
3) your user ldf files
Usually the best performance drives are raid 1+0 for these, but in practice for your setup:
Put your ldf (log files) on your RAID 1 (mirrored) drives and your mdf (database) files on the RAID 5 drive. Depending on how you are using your tempdb and the size of it, it could be worth placing this on the system (i'm assuming C
drive.
Hope this helps.
|||Here's what I'd do although there are a lot of variables.
I'd leave the system databases and the ReportServer db's on the RAID 1. I'd move the user database log files only and TempDB to the new RAID array. (I assume you have one TempDB file per cpu.)
This way, you're separating your log I/O from your data I/O. Ideally, you'd have your TempDB on it's own RAID-5 (although RAID 10 is better since you get striping and mirroring) array if you had another device. The only qualifier might be if your databases are using the FULL recovery model and they are update intensive. If that were the case I would probably recommend putting the log file of the database that is most heavily updated on it's own device. Log files are written sequentially so the READ/WRITE heads would not move between updates and you'd get a little performance gain that way.
Just fyi, the more spindles the better. Because you really only have three logical devices available to you your optimization options are fairly limited. The fact that you're using RAID-5 indicates that there are multiple devices striped together but the parity overhead is a killer for write performance. I always recommend RAID-10 to my clients. You'll get far better overall performance but the tradeoff is that it's more expensive. Sometimes I lose that battle but they do it with their eyes open.
|||
Yes, i agree that temdb and log files should be there on RAID 1, but here its already Raid 5, we cant help , and both log files and data files are stored together on the same Raid 5.
Its not OLTP, its batch processing .
So, with the given requirement, what will be the best place for "system+ user + Reportserver & Reportservertempdb", Databases, in the new environment (i.e.) on the new drive.
So, what the suggestion now
Thanks again for all your help...
|||Define "batch" processing. Are the batches update intensive? Do they do a lot of sorting? Are there a lot of temp tables or table variables involved? Are the transactions short and sweet or do they involve thousands of rows before a commit occurs?
I would NOT put the tempdb or the log files on RAID 1 because you would lose the benefit of striping which distributes the I/O across multiple spindles.
You can re-distribute data or log files at any time using the ALTER DATABASE statement. At least you can in SQL 2005. I'm not sure what the procedure is in SQL 2000 but I'm sure it's possible.
