RAID Storage and SQL Server

If you are a SQL Server Administrator, eventually you are going to need to request specific storage for your servers. Depending on the setup at your current company, that is all handled in the background by your Storage Administrators, but if you have the power or are asked for your opinion, knowing about RAID (Redundant Array of Independent Disks) technology is important. You can find full technical explanations on the web for this, but I’ll cover the basics from a SQL Server perspective.

RAID Overview

RAID uses multiple hard drives to improve availability and/or performance. RAID can overcome I/O bottlenecks that would result from using a single disk, provide resiliency from data loss through mirroring, and remove a single point of failure from a single drive being used.

To understand RAID, there are three terms we need to define first.

Mirroring is an idea you should understand intuitively, but perhaps not the exact details in relation to RAID. Disk Mirroring replicates data from one disk to another, providing a second copy for disaster recovery, and thus requiring at least two disks. RAID mirroring is performed synchronously and mirrored data can be read from either disk. More on Mirroring.

Striping means that the data is being separated onto multiple drives in a consecutive fashion. By separating the data onto different drives, the I/O load can be balanced across the disks and read times are faster. The more disks that the data is striped across, the faster the data throughput will be; however, if one device fails, since the data is spread evenly across all the disks involved in the striping, all the data will be corrupted and unable to be read. More on Striping.

Parity is probably the hardest term to understand or explain. In the most basic, mathematical sense, parity refers to whether an integer is even or odd. For computing, the definition is specifically whether the total value of 1’s occurring in a given binary number is even or odd. For RAID, parity data bits are a combination of existing data bits to provide redundancy. In the case of any single drive failure, the remaining data can be combined with the parity data to reconstruct the missing data. More on parity.

RAID Levels

Although there are more levels of RAID, for instance, 2, 3, 4…etc., they are rarely used, especially for SQL Server. I’ll just be explaining the four main types here.

 

Raid0RAID 0 (Striping) This basic form of RAID stripes data across multiple disks. Reads and writes occur simultaneously across all disks involved and thus provides faster reads and writes compared to a single disk. The more disks involved, the faster the performance. This creates multiple points of failure though, and is not really recommended for database use due to the increased vulnerability.

Pros: Improved Performance of Read and Writes
Cons: No Redundancy and any drive failure corrupts all data.

Raid1RAID 1 (Mirroring) This RAID level mirrors, or duplicates, data between a minimum of two disks. Mirroring requires 50% more storage since the mirror is an exact copy of the original data. Read speeds are faster since any disk can respond to a read request. Write speeds are reduced due to copying the data to multiple locations. Read times can be as fast as the fastest drive, while write times are often as slow as the slowest drive. If you need a relatively cheap method to protect your data, this is a good option. If one drive fails, you still have a perfect copy of the data on the other.

Pros: Redundancy, Faster Reads
Cons: 50% Extra Storage, Slower Writes

Raid5RAID 5 (Striping & Parity) This is the likely the most common type of RAID used, but requires at least three disks. Data and parity calculations are striped across all the disks. Since data is not mirrored, less storage is ‘wasted’, resulting in only a minimum of 1/3rd (1 / total # of drives) of the storage space used for redundancy. In the event of a drive failure, the data can be reconstructed using the parity data, but at a cost. There is a significant impact when one disk fails due to the parity data reconstruction overhead. Losing the 2nd drive in a three disk RAID 5 configuration will result in the entire array going offline and data being lost. Always replace after the first failure as soon as possible! Since write speeds are slower with RAID 5, it is not the best choice for Transaction Logs or Data Files. Backup drives are a prime candidate for this RAID level though since write speed is not as important.

RAID 6 is growing in popularity; it’s identical to RAID 5, except it adds an extra disk with another set of parity data. This RAID level requires a minimum of four disks and can handle up to two drive failures.

Pros: Fault Tolerance, Fast Reads (until a drive fails)
Cons: Slower Write Speeds, More Expensive than RAID 1 or 0

RAID 0+1 (Mirroring & Striping) Data is striped and subsequently mirrored in this RAID level. This incurs the 50% storage redundancy loss. There is fault tolerance for a single drive failure, but this reduces the RAID to essentially RAID 0 with no redundancy.

Raid10RAID 10 (Striping & Mirroring) Data is mirrored first and then striped with this method. As long as one drive from each side of the mirror remains, no outage will occur. With more fault tolerance, this is the preferred high-end method over RAID 0+1.

Pros: Fault Tolerance, Best Read/Write Speeds
Cons: Lots of Disks and Lots of Money

Both RAID 0+1 and 10 are hybrid RAID levels and provide the best read and write performance, but double the required storage. This is what everyone wants but not everyone can afford. It combines the best of the previous configurations but requires many disks and therefore a big budget. These RAID levels are best suited for high traffic, like your Data and Log files. If you cannot afford RAID 10, RAID 5 is a decent alternative though.

Advertisements

String Manipulation in T-SQL or PowerShell

I don’t often have to do string manipulation in T-SQL, but when I do, it’s rare enough that I don’t remember the syntax. I’ve done it in the past, so I keep a script handy with a working example though. I recently added to that example after wanting to know not only the file names, but also the folder location.  This article will cover getting a string after a specific character. This can be used to get a folder path or just a file name.

The scripts are cumbersome, to say the least, in SQL. Some people use this problem as an interview question, but I feel like the only way you’d be able to answer this off the top of your head is if you did it on a daily basis. While I remember the general idea of switching the order and checking the text after a specific character, I couldn’t write this from scratch if my life depended on it. Even with intellisense helping me out, it’s hard for me to remember how CHARINDEX can be manipulated to get the correct answer here, since I use it so rarely. I can pull it from my script library quickly though, and now I’ll be able to refer to my own blog as well. (I do that every time I set up Transparent Data Encryption).

I’ve commented into the code a quick explanation of what each command does in order to find the correct answer. Then I found out WordPress refused to color code the comments correctly. So I removed them. Now I’ll just explain it instead.

SQL can do it

The below script will find the File Name of each master file by reversing the direction of the text and using CHARINDEX to get the length of the string until it reaches a specific character, in this case, the \. The RIGHT command then returns the number of characters at the end of the original string based on the inner index command. The -1 removes the \ from the returned results.

To get just the folder path, perform the same command again, but this time issue a REPLACE command for the FileName, replacing it with an empty string.

In order to get a single folder, you combine the steps above to get just the FolderPath, and then once again get the last value after the \. This returns the final folder in the list. This may not be the optimal way to find the results, but it uses the same methods in order to keep it relatively simple.


SELECT
mf.name
,mf.physical_name
,[FileName] = RIGHT(physical_name,CHARINDEX( '\', REVERSE(physical_name)) -1)
,[FolderPath] = REPLACE(physical_name, '\' +RIGHT(physical_name,CHARINDEX( '\' ,REVERSE(physical_name)) -1), '')
,[FileFolder] = RIGHT(REPLACE(physical_name, '\' +RIGHT(physical_name,CHARINDEX( '\' ,REVERSE(physical_name)) -1),''),CHARINDEX( '\' ,REVERSE(REPLACE(physical_name, '\' +RIGHT(physical_name,CHARINDEX( '\' ,REVERSE(physical_name)) -1), ''))) -1)
FROM sys.master_files mf

PowerShell just does it better

On the other hand, I feel that the syntax to perform the same string manipulation is much more intuitive in PowerShell. It’s definitely less code, although I will concede that I assigned variables here, while with the SQL approach, I chose to do it all in a single statement. No variable assignments makes it much harder to read.

Leveraging the SPLIT command and LENGTH is about all you need for PowerShell. SPLIT creates an array from the string, with each new value separated by the character supplied in the parenthesis, that \ again. Returning the array value based on the length of the newly split variable returns any folder level or the file name alone, it just depends on the number you subtract from the length of the split.


$FullPath = "C:\Users\Test\Desktop\Text.txt"

$Split = $FullPath.Split('\')
$File = $Split[$Split.Length-1]
$LastFolder = $Split[$Split.Length-2]
$FolderPath = $FullPath.Replace($File,'')

$FullPath
$FolderPath
$LastFolder
$File

 

Getting Jobs by Run Time

New month, so I have to make sure I write something about SQL again, instead of babbling about PowerShell all the time! I manage a lot of servers, so scripting out something that I can run on all the servers rather than pulling up the GUI on one server at a time is always important.

Checking to see when jobs ran on a single server is a pretty quick task. You just have to pull up the Job Monitor, and you’ll have times and even more information easily accessible. Doing that on a dozen servers is tedious though. Doing it on a hundred would be a nightmare. Beyond that, checking specific times through the Job Monitor can be a bit of a hassle, as it displays a summary of the last execution times. What if you want to know what happened 5 hours ago, regardless of whether jobs have ran since then?

You can query SQL for job history, and with a lot of time conversions,  you can get a list of jobs that ran within a specific time period. I use this to narrow down what was happening on a box while troubleshooting. If you need to review long running jobs over a period of time, compare run times of jobs across servers, or just see what jobs might have been slowing performance at 3:03 am, this script can help you out.

The script takes a start and end time and checks for any jobs that started or started earlier but were still running by your supplied start time.

Calculating the Start and End times from job history is ugly. You’ll see that the datetime conversions below are disgustingly complicated. The short and sweet explanation is that date, time, and duration are all integer values. Because Microsoft thinks everything should be a number. To properly calculate date and time, then display it in a readable format, you have to stuff in colons and and use modulus division to get the hours, minutes, and seconds displaying correctly. If you really like math (I do!), that’s a technical explanation of modulo.

The run status will tell you if the job succeeded or not. Now, according to TechNet SQL Server 2000, there is a run_status of 4 meaning the job is still In Progress. Newer versions don’t mention a run_status of 4 and I’ve never seen run_status of 4 in a result set, even though I had jobs running at the time. Will it work for you? I really don’t know. I don’t trust it though. For newer versions of SQL, I’d suggest running a system stored procedure instead to get currently executing jobs, the code for which I’ve provided after my script.


DECLARE  @StartTime INT = '195500' --HHMMSS
,@EndTime INT = '195600' --HHMMSS
,@Date  INT = CONVERT(INT,CONVERT(VARCHAR,GETDATE(),112))
,@MinTime INT = 10  --Job Run Time Minimum in Seconds

SELECT
[JobName] = j.name
,[StartDate] = CONVERT(VARCHAR,CONVERT(DATETIME,RTRIM(jh.run_date)),101)
,[StartTime] = CONVERT(TIME(0),STUFF(STUFF(REPLACE(STR(RTRIM(jh.run_time),6,0),' ','0'),3,0,':'),6,0,':'))
,[EndTime] = DATEADD(second,run_duration%100,DATEADD(minute,run_duration / 100%100,DATEADD(hour,run_duration / 10000,CONVERT(TIME(0),STUFF(STUFF(REPLACE(STR(RTRIM(jh.run_time),6,0),' ','0'),3,0,':'),6,0,':')))))
,[Duration] = STUFF(STUFF(REPLACE(STR(jh.run_duration,7,0),' ','0'),4,0,':'),7,0,':')
,[RunStatus] =
CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'InProgess'
ELSE 'Unknown'
END
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id
WHERE run_date = @Date
AND step_id = 0 -- Entire Job
AND run_duration > @MinTime
AND
(
( run_time >= @StartTime AND run_time <= @EndTime )
OR
( Run_Time < @StartTime
AND
DATEADD(second,run_duration%100,DATEADD(minute,run_duration / 100%100,DATEADD(hour,run_duration / 10000,CONVERT(TIME,STUFF(STUFF(REPLACE(STR(RTRIM(jh.run_time),6,0),' ','0'),3,0,':'),6,0,':'))))) > CONVERT(TIME,STUFF(STUFF(REPLACE(STR(RTRIM(@StartTime),6,0),' ','0'),3,0,':'),6,0,':'))
)
)
ORDER BY Run_Date,Run_Time

Note that jobs that are still executing do not have an entry in job history. So combining this script with the executing jobs system stored procedure is a good idea if you are dealing with a recent time window especially. You can add the parameter @Category_Name = ‘jobs from msx’ to return only the jobs managed by your Central Management Server.


EXEC msdb.dbo.sp_help_job @Execution_Status = 1