Im setting up a hardware raid 5 solution for one of our db servers. The data files will reside on the stripe. We dont realy want to raid more drives for the Transaction log if its not nessesary. If the drive with the log crashes is the data file for the database useless ?
View 1 RepliesI am configuring a new database server, without SAN access, and want to know what is the best practice for SCSI RAID configuration. Do most folks prefer RAID 5 or RAID 10 configurations where their databases will reside?
I've always heard that RAID 5 (or better, RAID 10) is preferred for the actual database (mdf), but RAID 1 for logging.
If I have a dedicated physical volume for each, what's the performance hit for selecting RAID 1 for the MDF files? 3%, 20%, 200%?
Doing so (all RAID1) will allow me to have a separate physical volume for the TEMP database - that is heavily used by my app.
RAID 5 beats RAID 10Can I get some feedback on these results? We were having some seriousIO issues according to PerfMon so I really pushed for RAID 10. Theresults are not what I expected.I have 2 identical servers.Hardware:PowerEdge 28502 dual core dual core Xeon 2800 MHz4GB RAMController Cards: Perc4/DC (2 arrays), Perc4e/Di (1 array)PowerVault 220SEach Array consisted of 6-300 GB drives.Server 1 = Raid 103, 6-disk arraysServer 2 = Raid 5 (~838 GB each)3, 6-disk arrays (~1360 GB each)TestWinner% FasterSQL Server - UpdateRAID 513Heavy ETLRAID 516SQLIO - Rand WriteRAID 1040SQLIO - Rand ReadRAID 1030SQLIO - Seq WriteRAID 515SQLIO - Seq ReadRAID 5MixedDisktt - Seq WriteRAID 518Disktt - Seq ReadRAID 52000Disktt - Rand ReadRAID 562Pass Mark - mixedRAID 10VariesPass Mark -Simulate SQL ServerRAID 51%I have much more detail than this if anyone is interested.
View 13 Replies View RelatedMy SQL 7 is on RAID 5. Sometimes on non-peak hours, on RAID disks first
two lights ( from left ) are constantly on for hours. NT Task manager, nothing
unusual, SQL current activity - no running user processes. Isn't second light
on RAID comes on if any disk activity ( Read/Write ).
Suggestions are appreciated.
Thanks,
Ivan
I`ve tried implementing NT Software Raid / Stripping with Parity
and am unable to stripe disc that are more than 2g and
use SQL. I have not found any info in technet. Any ideas! Thanks.
Should one install RAID 5 for SQL Server or just use separate hard drives, one for the data and one for the transaction log?
View 4 Replies View Relatedcould any one tell me about the difference between RAID and SHARED DISK ARRAY
View 3 Replies View RelatedHi,I was going to buy a server with Raid 1 as I thought that it meant that ifone of the two mirrored drives fail, you simply take it out and put a newone in. At which point presumably the hardware takes over and copies theother drive over to mirror it again.However, my sql server admin book, says raid 1 is bad, as it means you havelots of downtime, when recovering from a broken drive.Can anyone give me some advice on this? What is the best Raid to use whenyou are running SQL server on the server.ThanksJJ
View 1 Replies View RelatedHi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
Hi guys,
On a new server with 4 disks, what level of raid is best to apply. In terms of what's important, I'd say speed is at the top of the list.
BJ
Hello,
I run a small homw office. I am planning to purchase a dell powerdge 1750 server to install SQL server on that.
I am confused here about which RAID should I install on this server RAID 1 or RAID 5.
The dell customer rep could not tell me the advantages of installing only RAID 1 or only RAID 5 or installing both RAID 1 and RAID 5
HI All,
I'm going to buy 1 IBM DS3400 SAN. I got 2 x SQL Servers
one for our ERP System other for Web
Physical Server are like this ATM
SQL1 (ERP) ( 3 RAID arrays)
-----
OS: 36GB (RAID 1)
DATA: 120GB (RAID 5)
Log : 15GB (RAID 5)
Backup:270GB (RAID 5)
SQL 2 (WEB) ( 2 RAID Arrays)
-----
OS: 120GB (RAID1)
Data/log/bak 569GB (RAID10) disk like this
DATA: 218gb
LOG:15gb
Backup: rest
So how do i raid the SAN and what sort of HDD i should buy?
I am recommending that we change our Raid Configuration on some of ourServers from Raid 5 to Raid 0+1; we are experiencing severe IObottlenecks.Our hardware guys are pushing back a bit. They claim that Dell has aweird implementation of 0+1 and told me something about one drivefilling up before it begins to write to the next. They claimed thatthis gets rid of most of the benefits of 0+1.I know that 0+1 is not as good as 10 for availability, fault tolerance,and rebuilding, but shouldn't the write throughput be about the same?Setup:Poweredge 2850Powervault 220SPerc 4/DC Controller 1Perc 4e/DI Controller 0
View 5 Replies View RelatedI have always used RAID 5 for my database apps. I am wondering though is this the best solution. I am purchasing a new server and deciding whether to go with RAID 1 or RAID 5. This server will support all our .NET Apps, SQL Server 2005, Reporting Services, and Integration Services.
Any Input?
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
Server: Msg 3132, Level 16, State 1, Line 1
The RAID set for database 'xxxxx' is missing member number 2
This happens when I try to run a restore?? is it because my server
is not set up as RAID 5, I had took a dump from one server and try
to apply it to another? Suggestion please.
I have a server setup with the standard recommended RAIS(10-5-10 setup (10 for the OS, 5 for the data, and 10 for the trans logs). Running out of space on my RAID 5. Have lots of extra space on my RAID 10 where my trans logs are. I currently dump my files to disk and then use tape to back them up. I have been putting these files on my RAID 5 array, but was going to move them to my RAID 10 array. Anyone seen any downside to doing this?
View 2 Replies View RelatedDear All,
if i have a SQL Server running on a machine with RAID 5 array and i will be performing transactional replication on it, what are the implications?
is there any issue with it since it is a transactional replication with many writes?
there will be a lot of writing n reading to the database.
Can someone advise on this?
Thanks in advance.
Cheers,
white
I heard that is said that MSQ SQL 2000 doesn't work correctly with raid (for example raid 5)
I have my application in 2 companies. The first one with not a raid controller (even though the computer has a raid controller) executes a backup when no users are online in more than 8 minuter and the file of the database backup is 3,5 GB. The memory of the computer is 2GB.
In the other company when more than 20 users work in the database with a raid controller the backup is occured in 2,6 minutes and the database backup file is more than 6 GB. The memory of this computer is 4 GB.
What is your opinion?
Is this rumor true?
Do you suggest I should apply raid 5 to the first server?
Regards,
Manolis
Hi experts,
We have a huge table with around 250 million records and have implemented SQL server 2005's new table partitioning feature. Now the data seems to be evenly spread across 20 different filegroups ( each 5 GB approx ) for the same table that was occupying 100 GB itself in the PRIMARY filegroup earlier.
Still the query response times have not come down drastically but we could see a good improvement in the execution plans now.
WE ARE USING RAID 5 IN OUR PRODUCTION ENVIRONMENT. ANY IDEA / THOUGHT ON HOW TO PLACE THE PARTITIONED FILEGROUPS AND THE LOG FILES IN THE RAID 5 (BTW , I'm very new to RAID concepts , any detailed instruction would be helpful ).
Any help would be greatly appreciated.
Thanks,
Hariarul
Hi
Background:
My company is in the process of upgrading our sql 2000 server to 2005 on new server hardware. In this process I'm charged with the actual server and database configuration on the new server so currently I'm investigating how to take advantage of new 2005 features and improve the use of common features on both platforms. We've decided to perform this upgrade in three stages (won't bother you with why) and the first stage is to install the new server with a 2000 installation with a different configuration regarding files/filegroups on the new hardware configuration that has a different raid configuration. The old server configuration was lacking in just about every department so I'm serious about getting it right this time. Its an OLTP system btw.
The question(s):
The 2780A course book states that sql 2005 can perform the following scans: "Multiple parallell scans for a single table if the filegroup of the table contains multiple files." Does this imply that the files must be on separate disks/raid channels? Ie, if I put all files in the filegroup on the same RAID 10 channel will I gain nothing over having just one file?
Can 2000 take advantage somehow of several files in a filegroup to achieve greater parallellism or is there no (end user) performance oriented reason to have more files in a filegroup? (Disregarding any impact of different backup strategies for several files).
I'm intending to place all nonclustered indexes in a separate filegroup with all files assigned to a dedicated RAID 0 channel for striping. Due to the lack of fault tolerance here I'm wondering if this is wise. But since nonclustered indexes are basically secondary metadatastructures and doesn't contain any data I'm wondering wether the server can disregard using an index when executing a query if the index resides on disk which have failed and the index is inaccessible. Is there a fallback option available to the server to perform a table scan or use another index instead?
Basically the same question can be asked for tempdb, use a RAID 10 with four disks or dare I use a RAID 0 with four disks (since 2005 use tempdb more heavily than 2000).
Also considering to put the OS on a dedicated RAID 1 and a dedicated RAID 0 for the pagefile, same concerns apply.
I appreciate any input, also examples (or links) or your experiences of good RAIDing practices for OLTP systems.
Thanks for your time.
PS. Sorry for posting this in both 2000 and 2005 forums but I don't wanna miss out on any good answers. ^^
/Timo
Afternoon Folk ...
Here is the question.
I have a RAID 10 container consisting of 4 146GB, SAS, 3.5-inch 15K RPM Hard Drives.
We have the OS on its own RAID so we are strictly dealing with setting up SQL Server 2005 on the RAID 10.
SO ... is it better to create seperate volumes for the Data, TLog and TempDB file placements (i,e, C:, F: and G or is it alright to create just plain-jane folders for the file placements?
Does it make a difference?
Is there a better way to carve this RAID 10 up?
Thanks ALL!
Ok - dont' throw rotten food at me for asking this question...
Is there any advantage (faster I/O) to creating multiple mdb files on the same RAID 5 for the same database?
In other words, database ABC is 100 gb in size and has one primary file on H:. If I create another (or 3 or 6) secondary files for the mdb, would there be an appreciable performance gain? Same with log file?
Thanks in advance.
DataGeek
Hi there,
How to choose the right RAID level for a database server based on disk performance counters?
Lets say I have the collected performance counters data for Disk Transfers/sec, Disk Writes/sec and Disk Reads/sec.
Or is there any other best method to decide the RAID level?
Please let me know.
Regards,
mkarumuru
Hi fellas,
I have to spec out a new server, and I have the option of using RAID-5 or RAID-1 drive sets. I am limited to 24 drives in groups of 6,
and I have to have one hotspare per group, so up to 5 usable drives per channel.
I need 80-100GB of space total.
Okay, you're waiting for the question....I have heard many differing opinions on which is better, RAID 1 or RAID 5. If I have 2 Large disks (say 36GB)
on a Raid-1, I assume having 4 smaller 9GB drives on a RAID-5 will be faster, but I am not sure due to overhead and the like.
Does anyone know where I can get more information on RAID performance and how it is going to affect me? The database is going to be Read-Write, with
a ton of small transactions, and the occasional (usually on a weekend) aggregation.
Any help would be much appreciated,
Joe
can anyone help me? I just installed WinNT 4 SP3 and now trying to install SQL 6.5 on an IBM Netfinity 5500 with RAID 5. There is one 500mb partition for the system and then one 12 gig partition and SQL only seems to see ~117mb of the 12 gig partition. Is this a known prob? Thanks for any input
Josh
Hello! I hope that I have posted in the right spot.
We are looking for advise. I know that there is no right or wrong answer. "It depends."
We currently have a server with the OS as Windows Server 2003 SP2 and SQL Server 2000 SP3. This Server has 6 physical drives; only 3 of these physical drives are being used. These 3 physical drives are 1 container with Raid 5. This 1 container is divided into 3 logical drives.
We would like to fill the other 3 physical drive slots and create another container. We were thinking of making this Raid 1.
I should put in my disclaimer that Raid 10 is out of the question and so is SAN.
Is Raid 1 the best choice? This is my first question.
Next is how should we split up the files among the containers.
For example, OS, log and swap file on container 1 with Raid 1 and datafiles on container 2 with Raid 5?
What are most people doing? Is there a standard? Can people provide examples of what they are doing or provide suggestions?
Hello -
I have an SQL Server 2000 DB running on a 5 year old server. It has 5 drives SCSI 10KRPM drives on IBM ServeRAID 4Lx card. I'm maxing it out to 9 on the same backplane (all 10KRPM).
Not sure the best way to make them count. Here's the particulars:
1. Current config is:
Vol1 = RAID1 for OS, swap, and Logging files.
Vol2 = RAID5 (3 disks) for DB.
2. The app does heavy writes and use of Temp DB.
I don't have by-volume stats. This stat excludes backup (taken 3 hours after a daytime reboot). Windows Task Manager shows SQL task and SERVICES.EXE both have physical reads about 15% higher than physical writes. SERVICES.EXE has about 3x the IO count as the SQL task. I assume that's mainly SQL activity.
Note: Symantec Antivirus (10.1.6) excludes .mdf, .ldf, .bak, and .trn files.
My question for you: How best to configure the 4 new drives.
Redundancy is critical, so any non-RAIDed volume is out.
Option 1:
Vol1 = RAID1 for OS.
Vol2 = RAID5 (3 disk) for app DB.
Vol3 = RAID1 for Sys DBs (Master etc) plus Temp DB. Also OS Swap. Also .BAK scheduled backup files.
Vol4 = RAID1 for all .ldf files.
Option 2: Abandon RAID5 due to write penalty (same division of files)
Vol1 = RAID1
Vol2 = RAID1
Vol3 = RAID1
Vol4 = RAID1
9th drive = hot swap.
Option 3:
Vol1 = RAID1. for OS and .BAK files.
Vol2 = RAID10 (4 disk). for all .mdf files
Vol3 = RAID1 for all .ldf files.
9th drive = hot swap.
I'm wondering if RAID1 read penalty will outweigh RAID5 write penalty (for 3 stripe RAID5). Will RAID10 advantages outweigh separation of tempDB + System DB on RAID 1 volumes (or RAID5 + RAID1).
Thank you very much for your esteemed advice. :)
I was informed (by Microsoft) that for all SQL Servers prior 2008 recommended (or must) stripe size is 64k
Does anyone knows full set of settings that must be applied when
setting up raid 5 for sql server box
Thanks