Networking handouts

SQL Querying (courses 20461, 20462, 20761, 20762, 10985)

https://learnsql.com/blog/sql-basics-cheat-sheet/

Books I've Read and Recommended

T-SQL Fundamentals (https://www.mightyape.co.nz/product/t-sql-fundamentals/24946232) (https://www.barnesandnoble.com/w/t-sql-fundamentals-itzik-ben-gan/1123454797?ean=9781509302000)
T-SQL Querying (the next book after Fundamentals) (https://www.mightyape.co.nz/product/t-sql-querying/22612292) (https://www.barnesandnoble.com/w/t-sql-querying-itzik-ben-gan/1119958056?ean=9780735685048)
T-SQL Window Functions (https://www.mightyape.co.nz/product/t-sql-window-functions/31121942)
Joe Celko's SQL Programming Style (https://www.mightyape.co.nz/product/joe-celkos-sql-programming-style/2007807)
Any book by Joe Celko. :-)
Pro SQL Server Design by Louis Davidson and Jessica Moss (https://www.barnesandnoble.com/w/pro-sql-server-2012-relational-database-design-and-implementation-louis-davidson/1106997676?ean=9781430236962)
Database Design for Mere Mortals by Michael Hernandez (https://www.barnesandnoble.com/w/database-design-for-mere-mortals-michael-j-hernandez/1136968169?ean=9780136788041)

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

TechNet: about_Hash_Tables

https://technet.microsoft.com/en-us/library/Hh847780.aspx