W I S E. SQL Server 2012 Database Engine Technical Update WISE LTD.

(1)

SQL Server 2012 Database Engine

Technical Update

W I

S E

WISE LTD.

COURSE CODE: SQSDBE

COURSE TITLE: SQL Server 2012 Database Engine Technical Update

LEVEL: Beginner-to-intermediate

AUDIENCE: SQL Server DBAs and/or system administrators

PREREQUISITES: At least six (6) months DBA experience with SQL Server

2005/2008.

DURATION: 2 days

SUMMARY: Focusing on the SQL Server 2012 database engine, this course discusses the many fundamental changes in SQL Server 2012’s architecture and structure. The key improvement that enhances scalability, application, continuous availability and performance are central and illustrated via usage examples:

 Configuration  Memory management  Contained databases  Big data  Columnstore indexes  SSIS catalog  Deprecated features  DB checkpoints  T-SQL  Etc.

(2)

SQL Server 2012 Database Engine

Technical Update Core Topics

W I

S E

WISE LTD. 1. SQL SERVER CONFIGURAITON OPTIONS

 Via SSMS

 Via Stored procedures

 How to display configuration values

2. MEMORY MANAGEMENT CHANGES

 Prior to SQL 2012 vs. new  Max server memory  Relevant DMVs

 DBCC memory status output

 AWE

3. SQL DATA MAXIMUM CAPACITY

 Database objects  Utility objects  Application objects  Replication objects

4. NEW DYNAMIC MANAGEMENT VIEWS (DMVs)

 In SQL 2012; not in SQL 2008  In SQL 2008; not in SQL 2012

 DMV columns in 2012; not in SQL 2008  DMV columns in 2008; not in 2012  Operating system info:

SELECT * FROM sys.dm_os_windows_info

5. SCALABILITY & PERFORMANCE

 On-line index rebuild

 Increased number of partitions supported

 Metadata location

 TempDB performance enhancements  TempDB DMV enhancement

 Diagnostic information queries  New DMVs

 Automatic execution of queries at ‘start-up’  Online index rebuild (VARCHAR(MAX))

(3)

SQL Server 2012 Database Engine

Technical Update Core Topics

W I

S E

WISE LTD. 7. BIG DATA PROCESSING

 Hadoop technology defined

 Unconventional data types (e.g., non-relational data types: web logs, email, etc.)  Hadoop and business intelligence implications

 Data explorer tools

8. SQL SERVER 2012 VERSIONS

 Name

 Scope and limits

9. DATA COLLECTOR

 Purpose  Benefits  Terminology  Tasks

 Management Data Warehouse

10. COLUMNSTORE INDEXES

 Usage cases

 Compared to B-tree index  How to create

 Data types supported  Restrictions and limitations  Bitmap filter implications  Partition table considerations  Query optimization

 Best practices

11. SQL SERVER DATA TOOLS (SSDT)

 Purpose and scope  Architecture

 Installation requirements and system requirements  Auto script generation

 Debug stored procedures  Etc. 12. REPLICATION  Publisher failover  Extended events 13. SECURITY ENHANCEMENTS  New permissions  New role management  Default schema for groups  Audit enhancements

(4)

SQL Server 2012 Database Engine

Technical Update Core Topics

W I

S E

WISE LTD. 14. LINKED SERVERS  Usage cases  Security  How to create  Distributed queries

15. DATA TYPES, PER:

 Column  Local variable  Expression  Etc. 16. T-SQL RELATED  Sequences  First-value; last-value

 Sequences vs. identity columns  Offset/fetch

 Throw in error handling

 Business key vs. surrogate key

 Ad-hoc query paging

 Breakpoints and debugging

 LAG

 OVER  LEAD

17. MANAGEABILITY ENHANCEMENTS

 SQL Server management Studio: e.g., debugging  Start-up options

 Windows PowerShell

 Database Engine Tuning Advisor  SSMS shortcuts

 SSMS DB Engine Query Editor debugging  Indirect checkpoints

18. SSIS CATALOG (SSISDB)

 Purpose

 Object hierarchy  Folders

 Projects and packages  Parameters

 Environment, environment variables and environment references  Executions

 Validations  Permissions

(5)

SQL Server 2012 Database Engine

Technical Update Core Topics

W I

S E

WISE LTD. 19. PERFORMANCE MONITOR & TUNING TOOLS

 Traces

 Distributed replay  System monitor  SSMS

 Error logs

 System stored procedures  DBCC

 Built-in functions

 Trace logs  Execution plans  SQL Profiler

 Dynamic Management Views (DMVs)

20. DEPRECATED FEATURES & REPLACEMENT, IF APPLICABLE 21. BACKWARD COMPATIBLE FEATURES

22. HIGH AVAILABILITY & DISASTER RECOVERY

 Benefits

 Availability groups vs. mirrors  Failover modes  Replicas  Modes  Etc. 23. FILETABLE  Uses  Contents  Benefits  Maintenance 24. DB CHECKPOINTS  Definition  Automatic  Indirect  Manual  Interval

25. SQL SERVER 2012 HARDWARE/SOFTWARE REQUIREMENTS

 Components (e.g., hard drives)  Processor

 Memory

 Operating system

(6)

SQL Server 2012 Database Engine

Technical Update Core Topics

W I

S E

WISE LTD. 26. TEMPDB ENHANCEMENTS

 Collation of calling DB vs. collation of instance

 Contained DB implications

27. CLOUD COMPUTING

 Definition and scope  Layers

 Deployment models  Architecture

 Some typical uses