SQL Server Policy-Based Management the hidden configuration baseline.

Policy-Based Management

Policy-Based Management is a policy based system for managing one or more instances of SQL Server. This has been around in SQL Server since 2008 but is often overlooked for both its primary function in administration as well as its ability to store baselines.

If you open up SQL Server Management Studio (SSMS) and navigate to the Management/Policy Management node you can see that there are three sub-folders which are as follows.

  • Policies
  • Facets
  • Conditions

A facet is a logical grouping of properties. A condition is a question or check that we apply to our facet. Finally a policy contains a group of facets that have been selected to run checks against.

An example implementation could check that the Server Facet property backupdirectory is equal to ‘c:\backups’. This would then confirm that the default location for backups is the same all instances.

This policy is stored on the instance that we created on, however we could also export this to a file and run in straight from the file-system.

Now the important point to gain from running through the above is that Facets have access to configuration data and it is this that can be used to get a configuration baseline.

Baseline

Right click on the root node of the server, click facets. We can see that there are multiple facets and the values contained represent the current state. These can be exported as policies stored on an instance or as xml files providing a point in time snapshot baseline of the configuration. Also be aware that facets also exist at the database node level.

Automating that export process with PowerShell.

Import-Module –Name SqlServer;

<#
Get-Facets
#>
function Get-Facets {
[CmdletBinding()]
Param(
[Parameter(Position=0,
Mandatory=$true,
ValueFromPipeline,
ValueFromPipelineByPropertyName)]
[System.Data.SqlClient.SqlConnection]$SqlConnection,

[Parameter(Position=1,
Mandatory=$true,
ValueFromPipeline,
ValueFromPipelineByPropertyName)]
[string]$Directory,

[Parameter(Position=2,
Mandatory=$false,
ValueFromPipeline,
ValueFromPipelineByPropertyName)]
[string[]]$DatabaseList
)
begin {

}
process {
$SqlStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($SqlConnection);
$PolicyStore = New-Object Microsoft.SQLServer.Management.DMF.PolicyStore($SqlStoreConnection);

$XmlWriterSettings = New-Object System.Xml.XmlWriterSettings;
$XmlWriterSettings.Indent = [System.Xml.Formatting]::Indented;
$XmlWriterSettings.OmitXmlDeclaration = $true; #strip of declaration

#Server Facet
$ServerFacets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets |?{$_.TargetTypes -contains [Microsoft.SqlServer.Management.Smo.Server]};
$DataSource = $SqlConnection.DataSource;
$SfcQueryExpression = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SfcQueryExpression("Server");

If(($ServerFacets.Count -gt 0) -and ($SfcQueryExpression)){
$ServerFacets|%{
$Guid = [Guid]::NewGuid();
$Policy = $_.Name;
$Name = "$($SqlConnection.DataSource.Replace('\','$'))_$($Policy)_$($Guid).xml"
$FullName = $(Join-Path -Path $Directory -ChildPath $Name);
$XmlWriter = [System.Xml.XmlWriter]::Create($FullName,$XmlWriterSettings);

$PolicyStore.CreatePolicyFromFacet($SfcQueryExpression,$($_.Name),$($_.Name),$($_.Name),$XmlWriter);

$XmlWriter.Flush();
$XmlWriter.Close();
$XmlWriter.Dispose();
[Void]$SqlStoreConnection.Disconnect();
$SqlConnection.Close();
}
}

#Database Facet
if($DatabaseList.Count -ge 1){
$DatabaseFacets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets|?{$_.TargetTypes -contains [Microsoft.SqlServer.Management.Smo.Database]};
Foreach($Database in ($DatabaseList | select -uniq))
{
If($Database.Length -gt 0)
{
$SfcQueryExpression = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SfcQueryExpression("Server/Database[@Name='$Database']");
If(($DatabaseFacets.Count -gt 0) -and ($SfcQueryExpression)){
$DatabaseFacets|%{
$Guid = [Guid]::NewGuid();
$Policy = $_.Name;
$Name = "$($SqlConnection.DataSource.Replace('\','$'))_$($Policy)_$($Database)_$($Guid).xml"
$FullName = $(Join-Path -Path $Directory -ChildPath $Name);
$XmlWriter = [System.Xml.XmlWriter]::Create($FullName,$XmlWriterSettings);

$PolicyStore.CreatePolicyFromFacet($SfcQueryExpression,$($_.Name),$($_.Name),$($_.Name),$XmlWriter);

$XmlWriter.Flush();
$XmlWriter.Close();
$XmlWriter.Dispose();
}
}
}
}
}

}
}

This code then be run via a command such as the following. I have chosen to export master database facets as well as the server level facets in the example.

$Server = "$env:COMPUTERNAME";
[System.Data.SqlClient.SqlConnection]$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server=$Server;Integrated Security=SSPI;Application Name=Discovery");

$SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlConnection;
$DatabaseList = $SmoServer.Databases.Name;
#$DatabaseList = @("master","model","tempdb");
$DatabaseList = @("master");
$Directory = 'C:\temp'

Get-Facets $SqlConnection $Directory $DatabaseList;

Regain access to SQL Server via inject service

One way to regain access to SQL Server is to use a simple inject technique which overrides the current Image Path for the SQL Writer service.

#http://jongurgul.com/blog/regain-access-sql-server-inject-service
If(([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")){
$You = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name;
$ImagePath = $(Get-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath).ImagePath;
#"C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe";
$SQLCMDPaths = $(Get-ChildItem -Path "C:\Program Files\Microsoft SQL Server\" -include SQLCMD.exe -Recurse | Select-Object FullName,Directory,@{Name="Version";Expression={$_.Directory.ToString().Split("\")[-3]}} | Sort-Object Version -Descending);
$SQLCMDPath = $SQLCMDPaths[0].FullName;
$SQLCMDPath;

If(Test-Path $SQLCMDPath){
$InjectedImagePath = "$SQLCMDPath -S . -E -Q `"CREATE LOGIN [$You] FROM WINDOWS;EXECUTE sp_addsrvrolemember @loginame = '$You', @rolename = 'sysadmin'`"";

#Stop SQLWriter
Get-Service -Name SQLWriter | Stop-Service -ea SilentlyContinue;

#Inject
Set-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath -Value $InjectedImagePath;
Write-Host $(Get-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath).ImagePath;
Get-Service -Name SQLWriter | Start-Service -ea SilentlyContinue;

#Restore
Set-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath -Value $ImagePath;
Write-Host $(Get-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath).ImagePath;

#Restart SQLWriter
Get-Service -Name SQLWriter | Start-Service -ea SilentlyContinue;
}Else{"Check SQLCMDPath";}

}Else{"Not Administrator"};

 

PsExec can alternatively be used to access SQL Server as shown in the post below. This relies on the NT AUTHORITY\SYSTEM account having been granted system administrator on the instance. This will be true for anything before SQL Server 2012.

Running as local system to allow administrators in to SQL Server

Additional notes:

 

“In SQL Server 2008 and later, the local Windows Group BUILTIN\Administrator is no longer provisioned as a login in the SQL Server sysadmin fixed server role by default at SQL Server setup install.
As a result, box administrators cannot login to the new SQL Server 2008 and SQL Server 2008 R2 instance by default.”

https://support.microsoft.com/en-gb/help/2184138/guidelines-on-granting-sql-server-sysadmin-privileges

 

 

In SQL Server 2012: “BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role.”

https://technet.microsoft.com/en-us/library/bb500459(v=sql.110).aspx

 

Microsoft SQL Server 2016 build

Here is a quick post for the recommended patches to deploy for Microsoft SQL Server 2016.

After deploying Microsoft® SQL Server 2016 I would recommend immediately patching to 13.00.1708 by applying the MSVCRT patch. (Unless planning to apply a later CU or SP)

This ODBC patch is needed to stop SQL Agent connection issues and “Failed to open loopback connection” issues.

Then deploy the most up to date patch:

 

Backup History

If you want to see more than just when the last backup occurred. Here is a script for backup history with two bonus features.

  • Backup compression ratio – CompressionRatio
  • Backup speed – Backup_MiB_S
--http://jongurgul.com/blog/backup-history/
SELECT
 bus.[database_name] [DatabaseName]
,bus.[type] [Type]
,bus.[backup_start_date] [BackupStartDate]
,bus.[backup_finish_date] [BackupFinishDate]

,CONVERT(DECIMAL(15,3),(bus.[backup_size]/1048576)) [Size_MiB]
,CONVERT(DECIMAL(15,3),(bus.[compressed_backup_size]/1048576)) [CompressedSize_MiB] --SQL2008
,LTRIM(STR((bus.[backup_size])/(bus.[compressed_backup_size]),38,3))+':1' [CompressionRatio] --SQL2008

,DATEDIFF(SECOND,bus.[backup_start_date],bus.[backup_finish_date]) [Duration_S]
,CONVERT(DECIMAL(15,3),(bus.[backup_size]/COALESCE(NULLIF(DATEDIFF(SECOND,bus.[backup_start_date],bus.[backup_finish_date]),0),1))/1048576) [Backup_MiB_S]
,bumf.[physical_device_name]

,bus.[first_lsn] [FirstLSN]
,bus.[last_lsn] [LastLSN]
,bus.[checkpoint_lsn] [CheckpointLSN]
,bus.[database_backup_lsn] [DatabaseBackupLSN]
,bus.[is_copy_only] [IsCopyOnly]
,bus.[differential_base_guid] [DifferentialBaseGUID]
,bus.[differential_base_lsn] [DifferentialBaseLSN]
,bus.[first_recovery_fork_guid] [FirstRecoveryForkID]
,bus.[last_recovery_fork_guid] [LastRecoveryForkID]
,bus.[fork_point_lsn] [ForkPointLSN]

,bus.[user_name] [UserName]
,bus.[compatibility_level] [CompatibilityLevel]
,bus.[database_version] [DatabaseVersion]
,bus.[collation_name] [CollationName]
--SELECT *
FROM [msdb].[dbo].[backupset] bus
INNER JOIN [msdb].[dbo].[backupmediafamily] bumf 
ON bus.[media_set_id] = bumf .[media_set_id]
ORDER BY bus.[backup_start_date] DESC

SQL Server and PowerShell (SQLPS) Starter

The following two snippets of code are two ways to achieve the same outcome, which is the $Server object containing the default instance.

$Server = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)";

Or when opening a SQL PS (Powershell) prompt at the default location. e.g. PS SQLSERVER:\SQL\SB01\DEFAULT>

$Server = (Get-Item .)

I mention this because I was asked what the simplist entry point to PowerShell for SQL person was. I can think of nothing simpler than opening the SQL PS shell from SSMS and typing the folllowing:

(gi .)

Note that gi is shorthand for Get-Item.
For example now that we have a reference to out instance we can then start to explore.

(gi .).Databases.FileGroups.Files|Select @{Name="UsedSpace_MiB";Expression={($_.UsedSpace/1KB)}},Name

 

(gi .).Databases|Select Name,LastBackupDate

 

(gi .).Databases

 

(gi SQLSERVER:\SQL\$ENV:ComputerName\DEFAULT).Databases

Using the default system_health Extended Event

The default extended event system_health appeared in SQL 2008 and provides several key monitors, which can prove useful when investigating issues. In the past I have mentioned this in the capturing deadlocks post as it is one of the monitors automatically in place and it is an easy way to grab the deadlock xml.

However deadlocks are not the only thing captured. An overview of what is inside system_health  can be found here:

  • The sql_text and session_id for any sessions that encounter an error with severity >=20
  • the sql_text and session_id for any sessions that encounter a “memory” type of error such as 17803, 701, etc (we added this because not all memory errors are severity >=20)
  • A record of any “non-yielding” problems (you have sometimes seen these in the ERRORLOG as Msg 17883)
  • Any deadlocks that are detected
  • The callstack, sql_text, and session_id for any sessions who have waited on latches (or other interesting resources) for > 15 seconds
  • The callstack, sql_text, and session_id for any sessions who have waited on locks for > 30 seconds
  • The callstack, sql_text, and session_id for any session that have waited for an extended period of time for “external” waits or “pre-emptive waits”.

I going to single out errors (error_reported) and provide a query to parse the data. Note I have commented out the additional where filters, but have left them in to show how this can be done.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
DECLARE @target_data XML
SELECT @target_data = CAST([target_data] AS XML)
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'

SELECT
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM (SELECT @target_data) [target_data]([target_data])
CROSS APPLY [target_data].nodes('/RingBufferTarget/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1
--AND x.y.exist('.//data[@name="severity"]/value/text()[. = "20"]') = 1
--AND x.y.value('(@timestamp)[1]','DATETIME') = '2015-10-21 16:29:00.000'

The above query I have used a variable to store @target_data, this could obviously be removed and the queries combined. When I am writing these xml queries I tend to keep a static copy of target_data so as not too impact the instance more than needed.

DECLARE @target_data XML
SELECT
CAST([target_data] AS XML) [target_data] INTO #target_data
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'
SELECT @target_data = [target_data] FROM #target_data

Finally for completeness, as well as the ring_buffer there is also another target for system_health and that is to file.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
SELECT 
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM
(
	SELECT 
	CAST([event_data] AS XML) as [target_data],*
	FROM sys.fn_xe_file_target_read_file('*system_health*.xel',NULL,NULL,NULL)
) e CROSS APPLY [target_data].nodes('/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1

Creating a Deadlock

Previously we have looked at how to capture a deadlock, but how do we go about creating a deadlock?

I think the following is the simplest method:

1. Create two tables with a single row in each and update a row in the first table (note transaction is started with BEGIN TRAN, but not committed.)

USE [tempdb]
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J1')
DROP TABLE [J1];
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J2')
DROP TABLE [J2];
CREATE TABLE [J1]([ID] INT);
CREATE TABLE [J2]([ID] INT);
INSERT INTO [J1]VALUES (1);
INSERT INTO [J2]VALUES(1);
GO
BEGIN TRAN
UPDATE [J1] SET [ID] = 1
--UPDATE [J2] SET [ID] = 1

2. Open a query in a new window and run:

USE [tempdb]
BEGIN TRAN
UPDATE [J2] SET [ID] = 1
UPDATE [J1] SET [ID] = 1

3. Finally in the query window opened in step 1. run the commented code by highlighting everything after the comment marks then pressing F5.

--UPDATE [J2] SET [ID] = 1

This will result in our first window being the victim of a deadlock:

Msg 1205, Level 13, State 45, Line 13
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Note that the second query will have an open transaction, which you can either ROLLBACK or COMMIT.

In step 1 (spid 53) a lock was taken on the row of table J1
In step 2 (spid 54) a lock was taken on the row in table J2, and then it tries to take a lock on J1 but has to wait as the first query already has it locked.
In step 3 (spid 53) the first query attempts to lock the row that the second query has already locked, while holding a lock on the row that the second query wants.

ExampleDeadlock.xdl save this file to your desktop stripping off the .txt extension to leave .xdl which will allow opening in SSMS.

Samsung Gear VR2

Samsung VR for S6 Edge Plus in the UK is here

***** https://shop.samsung.com/uk/samsung-gear-vr-lite-286321/ *****

Update 2 new link
http://www.samsung.com/uk/consumer/mobile-devices/wearables/gear/SM-R322NZWABTU

Original Moan below:

There may be a few of us geeks in the UK looking for the latest VR offering from Samsung, which appears to be pre-releasing everywhere but here.

vrfocus are running updates as and when they occur. Their post linking to Amazon UK looks to be what we are waiting for although annoyingly no product details. http://www.amazon.co.uk/gp/product/B017WA5VI6

From what I can gather it the newer VR units are referenced as SM-R322NZWAxxx and then a 3 character country code. (The older headsets are SM-32x). Therefore the reference SM-R322NZWABTU looks good for the UK based on BTU being used before. I am not holding my breath for a release this year tho. Amazon quoting 1-3 Months… ho ho ho. Not exactly the best product launch from Samsung…

The new headset is “Compatible with Galaxy Note 5, S6 edge+, S6 and S6 edge.”

Note that the older headsets are prefixed as follows:
Gear VR Innovator Edition for S6 SM-R321.
Gear VR Innovator Edition for Samsung’s Galaxy Note 4 SM-R320.

Links to vrfocus articles:
http://vrfocus.com/archives/25783/amazon-launch-uk-gear-vr-pre-orders/
http://vrfocus.com/archives/25467/gear-vr-now-listed-australia-new-zealand-sweden-korea/

Links to Samsung:

Sweden
http://www.samsung.com/se/consumer/mobile-devices/wearables/gear/SM-R322NZWANEE

Germany
http://www.samsung.com/de/consumer/mobile-devices/wearables/gear/SM-R322NZWADBT

New Zealand
http://www.samsung.com/nz/consumer/mobile-devices/wearables/gear/SM-R322NZWAXNZ

Australia
http://www.samsung.com/au/consumer/mobile-phone/wearables/gear/SM-R322NZWAXSA

South Korea
http://www.samsung.com/sec/consumer/mobile-tablet/gear/gear-series/SM-R322NZWAKOO

US
http://www.samsung.com/us/mobile/wearable-tech/SM-R322NZWAXAR

Bits, Bytes and the Binary Decimal confusion

Quick Binary Bit background

In computing we use a unit of information with two values 0 or 1, which is called a bit. Now with either a 0 or 1, how do we go about representing the number 6 or 9? We could write 111111 and 111111111111, but you will see that this will require a lot of space to show larger number. If we use Binary (base-2), then the position of the 0/1 will relate to a corresponding value which in binary are 1,2,4,8,16,32,64,128 etc. Note that this is 2 to the power n where n is the position of the value starting from 0. The positions would be read from right to left, so Pos 7, Pos 6, Pos 5,Pos 4, Pos 3, Pos 2, Pos 1 and finally Pos 0. Perhaps therefore the decimal number values would be more easily understood if written as 128,64,32,16,8,4,2,1.

2 to Power 0 = 1
2 to Power 1 = 2
2 to Power 2 = 4 (2×2)
2 to Power 3 = 8 (2x2x2)
2 to Power 4 = 16(2x2x2x2)
2 to Power 5 = 32(2x2x2x2x2)
2 to Power 6 = 64(2x2x2x2x2x2)
2 to Power 7 = 128(2x2x2x2x2x2x2)

The number 6 is therefore represented by setting 1 in position 1, and position 2. Which would look like this 0 0 0 0 0 1 1 0. (Position zero is set to 0 so even number)
The number 9 is therefore represented by setting 1 in position 0, and position 3. Which would look like this 0 0 0 0 1 0 0 1. (Position zero is set to 1 so odd number)

Time for a quick Byte?

Now I have not chosen to show 8 bit positions randomly, as this group of 8 is referred to as a Byte. Where this gets complicated is that from this point on there are two ways to group the data units, which are either binary (base-2) or decimal (base-10).

As numbers get larger we assign a prefix denoting that it is to be multiplied by X to get the actual value. In decimal (base-10) a prefix is added for multiplies of 1000, in binary (base-2) however this is multiples of 1024.

So how much is a kilo byte? Well do we mean a binary or decimal?

1024 bytes = 1 binary kilo byte
1000 bytes = 1 decimal kilo byte

Confused? The easiest way to relieve this confusion is to use an appropriate prefix which denotes this difference. A multiple of 1000 in decimal is referred as k or kilo, where as in binary a multiple of 1024 is stated as Ki or kibi.

A more comprehensive list can be found online with a quick search but below is the first few. https://en.wikipedia.org/wiki/Binary_prefix

k kilo Ki kibi
M mega Mi mebi
G giga Gi gibi
T tera Ti tebi

So RAM in a computer is referred to as 4 GB or 4 Gigabytes, it would be more accurately described as 4 GiB or 4 GibiBytes as it is denoting a binary value. A 1000 BASE Ethernet card refers to decimal value, therefore the card is 1 GiBits. Storage is often labelled by hardware vendors as decimal multiples.

Useful Specifications

Here is a useful link for data transfers: https://en.wikipedia.org/wiki/Data_rate_units

USB https://en.wikipedia.org/wiki/USB
Mode Gross data rate Introduced in
Low Speed 1.5 Mbit/s USB 1.0
Full Speed 12 Mbit/s USB 1.0
High Speed 480 Mbit/s USB 2.0 (60 MB/s)
SuperSpeed 5 Gbit/s USB 3.0 (625 MB/s)
SuperSpeed+ 10 Gbit/s

PCI Express https://en.wikipedia.org/wiki/PCI_Express

v1.x: 250 MB/s (2.5 GT/s)
v2.x: 500 MB/s (5 GT/s)
v3.0: 985 MB/s (8 GT/s)
v4.0: 1969 MB/s (16 GT/s)

SATA Decimal Prefix Units https://en.wikipedia.org/wiki/Serial_ATA
SATA 1.0 – 1500 Mbit/s – 150 MB/s
SATA 2.0 – 3000 Mbit/s – 300 MB/s
SATA 3.0 – 6000 Mbit/s – 600 MB/s
SATA 3.2 – 16 Gbit/s – 1969 MB/s

Appendix: Throwing new hardware at an old server?

Is it a good idea to fully populate an R710 with 8 x expensive consumer grade SATA SSD? (This is an 11 Generation Dell PowerEdge Server from 2009.)

Well the server itself supports SATA 2.0 so that means each connected drive could potential push 300 MB/s, and most SSD will do more than that. So bottleneck one is that a modern SSD will want to go faster than SATA 2.0. So buy cheap SSD, as max we are going to start with is 300 * 8 = 2400 MB/s.

Next thing we hit is the controller card. A PCI Express x8 card with only 4 lanes wired Gen 1. 250 MB/s * 4 = 1000 MB/s.

 

Mmm, it’s really not going to take much to saturate this server using modern storage. Good idea to add SSD? No, unless you have them laying about.

Dell Documentation for the R710:

http://www.dell.com/downloads/global/products/pedge/en/server-poweredge-r710-tech-guidebook.pdf

11.1 Overview
The PowerEdge R710 has two PCI Express risers: Riser 1 and Riser 2. Each riser connects to the planar through a x16 PCI Express connector.
* Riser 1 consists of two x4 slots and a third x4 slot dedicated for internal SAS storage through the PERC 6i or SAS 6/iR.
* The default Riser 2 consists of two x8 PCI Express connectors.
* There is also an optional x16 Riser 2 that supports one x16 PCI Express card.

11.2 PCI Express Risers
The two PCI Express risers provide up to four expansion slots and one slot dedicated for the
integrated storage controller card. The slots meet the following requirements:
* Two x8 and two x4 PCI Express Gen2 slots, each connected to the IOH
* One x4 PCI Express Gen1 slot for internal storage connected to the IOH

12.4.1 SAS 6/iR
The R710 internal SAS 6/iR HBA is an expansion card that plugs into a dedicated PCI Express x8 slot (four lanes wired). It incorporates two four-channel SAS IOCs for connection to SAS or SATA hard disk drives. It is designed in a form factor that allows the same card to be used in the PowerEdge R610 and PowerEdge T610.