SQL Server High Availability comparison



Availability Groups Failover Clustering Log Shipping Database Mirroring (deprecated as of MSSQL2014)
"High Performance" "High Protection" "High Availability"
Asynchronous, no Witness Synchronous, no Witness Synchronous with Witness
Protects from: Node failure, Disk failure Node failure Node failure, Disk failure Node failure, Disk failure Node failure, Disk failure Node failure, Disk failure
Data modification - if you catch it in time
Doesn't protect from: Data modification Disk failure, Data modification Data modification Data modification Data modification
Hardware req: Cluster-aware hardware Cluster-aware hardware None special None special None special None special
Shared storage
Windows Server 2012 + Standard, Datacenter Standard, Datacenter None special None special None special None special
Windows Server 2008 & R2 Enterprise, Datacenter Enterprise, Datacenter None special None special None special None special
Windows Server 2003 — Enterprise, Datacenter None special None special None special None special
Windows Server 2000 — Advanced, Datacenter None special None special None special None special
SQL Server 2016 Standard, Enterprise ¹ Standard, Enterprise ² Not Express Enterprise Standard, Enterprise Standard, Enterprise
SQL Server 2014 Enterprise Standard, BI, Enterprise ² Not Express Enterprise Standard, BI, Enterprise Standard, BI, Enterprise
SQL Server 2012 Enterprise Standard, BI, Enterprise ² Not Express Enterprise Standard, BI, Enterprise Standard, BI, Enterprise
SQL Server 2008 — Standard, Enterprise Not Express Enterprise Standard, Enterprise Standard, Enterprise
SQL Server 2005 — Standard, Enterprise Not Express Enterprise (sp1) Standard, Enterprise (sp1) Standard, Enterprise (sp1)
Automatic failover? Yes, but only between two replica servers Yes No No No Yes
Automatic client
reconfiguration?		 Yes Yes No No No Yes - req SQL 2005 client
Downtime? Tens of seconds for failover Tens of seconds for failover Minutes at best Minutes at best Minutes at best Seconds
+ Automatic Recovery time + Automatic Recovery time
Scope: Per database Per SQL instance Per database Per database Per database Per database
Potential data loss? No No Yes, depends on schedule Yes, usually seconds No No
(Committed data)
Characteristics: Supports multiple standby servers. Is a single-copy cluster, so there is no "standby" concept. We can use the standby server for read-only querying. We can not use the mirror for querying. We can not use the mirror for querying. We can not use the mirror for querying.
We can optionally use some of the standby servers for read-only querying. Uses transaction log backups so interferes with DR. Witness must be licensed copy of SQL Server 2005 but can be Express edition.
Failure of standby servers does not affect primary, provided quorum is intact. Failure of standby does not affect primary. Failure of mirror does not affect principal. Failure of mirror does not affect principal. Failure of mirror does not affect principal (as long as the witness is available).
Must be Full recovery model Must be Full or Bulk-Logged recovery model Must be Full recovery model Must be Full recovery model Must be Full recovery model


Notes
¹ Standard edition does not support AlwaysOn Availability Groups but does support a feature called Basic Availability Groups.
² BI and Standard editions support a maximum of 2 nodes.

SQL Server course handouts









SQL Server Security Overview



SELECT *
FROM sys.fn_builtin_permissions( default ) 
ORDER BY class_Desc, covering_permission_name, permission_name ;

SQL Server Deadlock Demo

/* Setup.
*/
use tempdb ;
go
create table dbo.SavingsAccount ( AccountID int not null, Balance money not null ) ;
go
create table dbo.CheckingAccount ( AccountID int not null, Balance money not null ) ;
go
insert into dbo.SavingsAccount ( AccountID, Balance ) values ( 1001, 500.00 ) ;
insert into dbo.CheckingAccount ( AccountID, Balance ) values ( 1001, 100.00 ) ;

 /* Code 1.
*/
use tempdb ;
begin transaction ;
update dbo.CheckingAccount 
set Balance = Balance - 100
where AccountID = 1001 ;
waitfor delay '0:00:10' ;
update dbo.SavingsAccount 
set Balance = Balance + 100
where AccountID = 1001 ;
commit ;

 /* Code 2.
*/
use tempdb ;
begin transaction ;
update dbo.SavingsAccount 
set Balance = Balance - 100
where AccountID = 1001 ;
update dbo.CheckingAccount 
set Balance = Balance + 100
where AccountID = 1001 ;
commit ;

 /* Cleanup.
*/
use tempdb ;
go
if object_id( N'dbo.SavingsAccount', N'u' ) is not null
 drop table dbo.SavingsAccount ;
go
if object_id( N'dbo.CheckingAccount', N'u' ) is not null
 drop table dbo.CheckingAccount ;
go

Using PowerShell Hash Tables with -Property and -GroupBy


When learning these, I struggled to find authoritative lists for what cmdlets supported what hashtable keys. The man pages for cmdlets sometimes listed them, but not often. Sometimes the man pages were wrong (for example, the nonexistent depth= key).

Note that the name of the key can be abbreviated to any length. For example, alignment="right" and align="right" and al="right" and a="right" are all allowed. Usually, we abbreviate to just the first letter.

The name= and label= keys are synonyms. Use whichever you prefer.

Summary

Cmdlet name expression ascending,
descending		 width formatstring alignment
Select-Object
   -Property ✓ ✓ ✗ ✗ ✗ ✗
Sort-Object
   -Property ✗ ✓ ✓ ¹ ✗ ✗ ✗
Format-Table
   -Property ✓ ✓ ✗ ✓ ✓ ✓
   -GroupBy ✓ ✓ ✗ ✗ ✓ ² ✗
Format-List
   -Property ✓ ✓ ✗ ✗ ✓ ✗
   -GroupBy ✓ ✓ ✗ ✗ ✓ ² ✗
Format-Wide
   -Property ✗ ✓ ✗ ✗ ✓ ✗
   -GroupBy ✓ ✓ ✗ ✗ ✓ ² ✗

Notes

¹ If you use both ascending= and descending= then the descending value takes precedence.
² The display of the grouping does not, however, seem to honour the format string.

Select-Object

The –Property parameter supports: name/label = "string"; expression = {script block}

 Get-Process `
| Select-Object `
    -Property `
        @{name="Taskname";expression={$PSItem.Name}}, `
        @{n="PID";e={$PSItem.Id}}

Sort-Object

The –Property parameter supports: expression = {script block}; ascending = Boolean; descending = Boolean 

Get-Process `
| Sort-Object `
    -Property `
        @{expression={$PSItem.Name};descending=$true}, `
        @{e={$PSItem.Handles};ascending=$true}

Format-Table

The –Property parameter supports: name/label = "string"; expression = {script block}; width = Int; formatstring = ".NET format string"; alignment = "left" | "center" | "right”

The –GroupBy parameter supports: name/label = "string"; expression = {script block}; formatstring = ".NET format string"

Get-Process `
| Format-Table `
    -Property `
        @{n="Process Name";e={$PSItem.ProcessName};width=50}, `
        @{n="WS (MB)";e={$PSItem.ws/1MB};alignment="left";formatstring="n2";width=12}, `
        @{n="VM (MB)";e={$PSItem.vm/1MB};a="left";f="n2";w=12} 

Get-Process | Sort-Object -Property @{e={$PSItem.Threads.count}} `
| Format-Table `
    -GroupBy `
        @{n="Threads";e={$PSItem.Threads.count};f="n1"} `
    -Property `
        @{n="Process Name";e={$PSItem.ProcessName};w=50}, `
        @{n="WS (MB)";e={$PSItem.ws/1MB};a="left";f="n2";w=12}, `
        @{n="VM (MB)";e={$PSItem.vm/1MB};a="left";f="n2";w=12} 

Format-List

The –Property and –GroupBy parameters support: name/label = "string"; expression = {script block}; formatstring = ".NET format string"

Get-Process `
| Format-List `
    -Property `
        @{n="Process Name";e={$PSItem.processname}},
        @{n="WS (MB)";e={$PSItem.ws/1MB};formatstring="n2"} ,
        @{n="VM (MB)";e={$PSItem.vm/1MB};f="n2"} 

Get-Process | Sort-Object -Property @{e={$PSItem.Threads.count}} `
| Format-List `
    -GroupBy `
        @{n="Threads";e={$PSItem.Threads.count};f="n1"} `
    -Property `
        @{n="Process Name";e={$PSItem.processname}},
        @{n="WS (MB)";e={$PSItem.ws/1MB};f="n2"} ,
        @{n="VM (MB)";e={$PSItem.vm/1MB};f="n2"} 

Format-Wide

The –Property parameter supports: expression = {script block}; formatstring = ".NET format string"

The –GroupBy parameter supports: name/label = "string"; expression = {script block}; formatstring = ".NET format string"

Get-Process `
| Format-Wide `
    -AutoSize `
    -Property `
        @{e={$PSItem.ws/1MB};formatstring="n2"} 

Get-Process | Sort-Object -Property @{e={$PSItem.Threads.count}} `
| Format-Wide `
    -AutoSize `
    -GroupBy `
        @{n="Threads";e={$PSItem.Threads.count};f="n1"} `
    -Property `
        @{e={$PSItem.ws/1mb};f="n2"} 

Links


Subscribe to: Posts (Atom)