Transcript The Grand Tour Brian Garraty @NULLgarity  SQL Server DBA  Virginia Beach Public Schools  MCITP DBA  HRSSUG Leadership Team  Prior Life: C++/VB Developer.

The Grand Tour
Brian Garraty
@NULLgarity

SQL Server DBA

Virginia Beach Public Schools

MCITP DBA

HRSSUG Leadership Team

Prior Life: C++/VB Developer

Functional yet Elegant

The Workshop

Modern Luxuries

Home Security

Available Now

Bells & Whistles

Negotiations

The Small Print

I will aim to
 Refrain from any marketing babble
 Refrain from use of unneeded buzzwords
 Not use any of the following expressions:
▪ Mission Critical Confidence
▪ Breakthrough Insight
▪ Cloud on Your Terms





Not yet running 2012 in Production
High Level
No demos
We won’t cover everything
Very light on
 BI
 Cloud

7.5 (1998)
 Architecture Improvements
 Scalability

2000




Rewrite from Sybase
OLAP, ETL
Clustering
XML

2005
 Manageability (DMVs)
 Performance (CLR, Partitioning)
 High Availability (Mirroring)

2008 (continue prior momentum)
 Manageability (PBM)
 Performance (Compression)

2008 R2
 PowerPivot
 Report Builder 3.0
 Master Data Services

Azure (2010)
 SQL as a Service

2012 (My Take)
 Customer Feedback Driven
 Showstopper Breakthroughs
 Azure into the Fold
Your T-SQL can T-Sizzle...

CHOOSE
 ( index, val_1, val_2 [, val_n ] )

IIF
 ( boolean_expression, true_value, false_value )

CONCAT
 ( string_value1, string_value2 [, string_valueN ] )

FORMAT
 ( value, format [, culture ] )

PARSE
 ( string_value AS data_type [ USING culture ] )

TRY_CAST
 ( expression AS data_type [ ( length ) ] )

TRY_CONVERT
 ( data_type [ ( length ) ], expression [, style ] )

TRY_PARSE

DateFromParts
 ( year, month, day )

DateTime2FromParts
 ( year, month, day, hour, minute, seconds,
fractions, precision )

EOMonth
 ( start_date [, month_to_add ] )

UNDEFINED – any or none

NONE –abort if results returned

<result_sets_definition>
 Describes explicit result set(s)
 A step towards contract or interface
 Useful in SSIS OLEDB source
EXEC uspGetEmployeeManagers 16
WITH RESULT SETS
(
([Reporting Level] int NOT NULL,
[ID of Employee] int NOT NULL,
[Employee First Name] nvarchar(50 ) NOT NULL,
[Employee Last Name] nvarchar(50 ) NOT NULL,
[Employee ID of
Manager] nvarchar(50 ) NOT NULL,
[Manager First Name] nvarchar(50 ) NOT NULL,
[Manager Last Name] nvarchar(50 ) NOT NULL )
);

Similar to IDENTITY

Not tied to table

Controlled by application
 NEXT VALUE FOR

Default value for columns

Concatenated value identity

Cross statement ROW_NUMBER()

Raises exception, invokes CATCH block

If outside TRY…CATCH, ends session with
severity 16

Simpler than RAISEERROR

Useful inside CATCH blocks

SET FMTONLY Deprecated

sp_describe_first_result_set
 @tsql = N'Transact-SQL_batch
 @params = N'parameters'
 @browse_information_mode = <tinyint>

Also via similar DMFs
Everything you need to get ‘er done...

“Powered by” Visual Studio 2010

F5 is the new Ctrl-E (aargh!)

Result grid columns rearrangable

Editor windows are draggable

Restore GUI enhancements

Page Restore GUI

Database Engine Tuning Advisor
 Query Plan Cache Workload
 Top 1,000 events by default

No Support for Legacy File Extensions
 .PRC
 .TAB
 .UDF
 etc

Code Snippets
 Templates integrated with IntelliSense
 Customizable

Surround With
 BEGIN…END
 IF
 WHILE

Menu reads SQL Server Documentation

Defaults to Online help

Local installs separately

Same viewer as Visual Studio 2010 SP1

Dev-targeted Edition of Express

Runs in User Mode

“SQL Express-Lite”

Fast, zero-config install

Fewer pre-requisites

Public

Created and managed automatically

Usable by any app

Exists if LocalDB exists

Private

Created, owned, managed by app

Isolated, dedicated

Support multiple users

Created by administrator

Either automatic or named

“Largest investment in SSIS to date”

Project Concept
 Buildable
 Deployable
 Manageable

Best practice acknowledgement

Shared Connection Managers

Project scoped variables

Optional

Replace configurations

Project or Package Level

Package-scoped recognized by EPT

SQL Server Data Tools is the new BIDS

Undo

Consistency in XML
Granite countertops coated in PowerShell...



Turbo button for typical DW queries
Columnar (versus row based) data format
Speed from





Less data read, only columns
Columns are heavily compressed
Typical query uses few columns
Columns are processed in chunks
Limitations
 Read only
 One per table

Wizard-driven Creation

GUI-driven Editor

Customizable Data Viewer

Increasingly used for SQL Management
 AlwaysOn
 SSIS

No longer installed by SQL Setup

SQLPS Now Deprecated
 Stick with SQLPSX & straight up PowerShell
For that peaceful, easy feeling...

No auto provisioning to sysadmin
 BUILTIN\Administrators
 Local System

Created & managed by Domain Controller
 Passwords
 SPNs


Domain\Accountname$
Windows Server 2008 R2 only




Automatically managed local accounts
No Passwords
NT SERVICE\<SERVICENAME>
Access to network via computer account
 <domain_name>\<computer_name>$

Windows Server 2008 R2 only

Little or no server dependencies

Contained vs. uncontained objects

Useful for
 Failover
 SQL Azure
 Non sysadmin Administration

Server level supported in all editions
(database level still Enterprise-only)

Recoverable audit log failures

User defined audits

Audit log filters (WHERE clause)

User-defined server roles

Default schema for Windows Groups

New encryption algorithm support
 SHA2_256
 SHA2_512

Stronger Server & Database Master Key
Encryption (3DES to AES)
Move in ready!!!

AlwaysOn – umbrella term
 Failover Cluster Instances
 Availability Groups

Online Operations
 Indexes with LOBs
 Adding Columns with Default Values

Multi-subnet Failover Clusters
 Inter-datacenter
 No single, shared storage
 Data replication
 Disaster recovery + HA

Upgrade from “SELECT @@SERVERNAME”

Windows uses dedicated connection

Health monitored via sp_server_diagnostics

Configurable Health Check Timeout

Configurable Failover Condition Level






0 – No automatic failover or restart
1 – On server down
2 – On server unresp0nsive
3 – On critical server errors
4 – On moderate server errors
5 – On qualified failure conditions, e.g.
 SQL Service is down
 SQL Instance unresponsive
 etc

Checkpoints flush mods to disk

Impact recovery time on unexpected
shutdown or failover

Normally target is server configuration

Database specific target

Move tempdb to SSD

People have been doing it for years

Now fully supported

Layman’s definition:
 DB mirroring for groups of databases

Components:
 Availability Groups
 Primary Replicas
 Multiple Secondary Replicas

Secondary replicas
 Read only access
 Backup ops
 Automatic page repair
 Resource Utilization

Flexible Commit Modes

Flexible Failover Modes
Ding dong. Whoot-hoot.

Built on FILESTREAM
 SQL storing unstructured data on file system

File data exposed via Windows API

Simplified transition from file server

Supports files and directories

Access is non-transactional

“Knowledge driven data quality product”

Consists of
 Knowledge Bases
 Data Quality Projects

Knowledge of your data

Types
 Out of the box
 System Generated
 User Specified

Building Block of Knowledge Base

Data Field Specific Knowledge

Tracks
 Valid/Invalid Values
 Synonym Associations
 Business Rules
 Matching Policies

Facilitates use of Knowledge Base

Cleansing Projects
 DQS analyzes data, suggests what to do
 Human actually decides what to do
 Done by SSIS

Matching Projects
 Feeds on Cleansed Data
 Implements Matching Policy

Implemented by
 SQL Server DBs
 Stand-alone Client
 SSIS Cleansing Component
 Master Data Services Data Quality Components
Motivated Seller!!!

Enterprise

Business Intelligence

Standard

Developer, Express, & Compact

Datacenter

Workgroup

Small Business

Options
 Core-based
 Server+CAL

Enterprise Edition –
 Core-based only
 Minimum Four

BI Edition –
 Server + CAL Only

Standard
 Either Model

HW Purchasing Decisions Impacted

Virtual Machine Level
 Server + CAL
 Virtual Core Licensing

Host Server Level
 Physical Server Cores
 Unlimited VMs with EE & SA
 Without SA, limits on VM Moves

High Availability Scenarios

Online Operation Scenario

New Data Quality Project

New Installs (when supported)

Others???

In-place
 Pros – no copying, same name
 Cons – all at once, error = disaster recovery

Side-by-side
 Pros – piecemeal
 Cons – more work copying, configuring

Can analyze
 Data
 Objects
 T-SQL Scripts
 Workload Traces

Reports on Issues

Links to More Info

Replay 2005/2008/R2 activity on test server

Requires 4 Servers
 Production
 Baseline – same version as Production, isolates
activity
 Test – SQL Server 2012
 Report – Saves Comparison Results
Please initial the bottom of each page...

SQL Trace/SQL Profiler in favor of:
 xEvents

Not ending T-SQL statements with semicolon

Slipstreaming in favor of:
 Product Updates

SET FMTONLY
 See Metadata Discovery Slide

Osql.exe in favor of:
 sqlcmd.exe

90 Compatibility

SET ROWCOUNT
 TOP

sysobjects, etc
 Compatibility views

text, ntext, image
 varchar(max), nvarchar(max), varbinary(max)

8.0 Compatibilty

*= and =*

DTS

32-bit Address Windowing Extensions (AWE)

Data Quality Services Blog

Mike Walsh on User-defined Server Roles

Denny Cherry on Sequence Objects

James Rowland-Jones on EXECUTE WITH
RESULT SETS

Aaron Bertrand on LocalDB

Aaron Bertrand on Columnstore Index

Jamie Thomson on SSIS

The Baker’s Dozen, 26 New Features in SQL
Server Integration Services 2012, Kevin Goff,
Code Magazine

Ashish Kumar Mehta on Data Type
Conversion Functions

MSDN Blog on IntelliSense Improvements

History of SQL Server on YouTube

Pinal Dave on sp_describe_first_result_set

Conor Cunningham on Contained DBs

Michael Otey on Licensing

Grant Fritchey on Extended Events
NULLgarity.wordpress.com