1 / 83

Ultimate Guide to SQL Server 2012: Functional Elegance and New Features

Discover the extensive upgrades and elegant functionalities of SQL Server 2012 in this comprehensive guide. Authored by Brian Garraty, a seasoned SQL Server DBA with a rich background in C++ and VB development, this resource highlights essential topics such as T-SQL enhancements, metadata discovery, SSMS changes, and performance improvements. Engaging the reader with practical examples and insights, this guide aims to elevate your SQL skills by focusing on modern luxuries and functional capabilities without unnecessary jargon. Available now, this is the workshop you need to take your SQL Server expertise to the next level!

nirav
Télécharger la présentation

Ultimate Guide to SQL Server 2012: Functional Elegance and New Features

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Grand Tour SQL Server 2012 Brian Garraty @NULLgarity

  2. Who I am • SQL Server DBA • Virginia Beach Public Schools • MCITP DBA • HRSSUG Leadership Team • Prior Life: C++/VB Developer

  3. Itinerary – Part 1 • Functional yet Elegant • The Workshop • Modern Luxuries • Home Security

  4. Itinerary – Part 2 • Available Now • Bells & Whistles • Negotiations • The Small Print

  5. My Promise • 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

  6. Disclaimers • Not yet running 2012 in Production • High Level • No demos • We won’t cover everything • Very light on • BI • Cloud

  7. Historical Perspective • 7.5 (1998) • Architecture Improvements • Scalability • 2000 • Rewrite from Sybase • OLAP, ETL • Clustering • XML

  8. Historical Perspective • 2005 • Manageability (DMVs) • Performance (CLR, Partitioning) • High Availability (Mirroring) • 2008 (continue prior momentum) • Manageability (PBM) • Performance (Compression)

  9. Historical Perspective • 2008 R2 • PowerPivot • Report Builder 3.0 • Master Data Services • Azure (2010) • SQL as a Service

  10. Historical Perspective • 2012 (My Take) • Customer Feedback Driven • Showstopper Breakthroughs • Azure into the Fold

  11. Functional Elegance Your T-SQL can T-Sizzle...

  12. Logical Functions • CHOOSE • ( index, val_1, val_2 [, val_n ] ) • IIF • ( boolean_expression, true_value, false_value )

  13. String Functions • CONCAT • ( string_value1, string_value2 [, string_valueN ] ) • FORMAT • ( value, format [, culture ] )

  14. Conversion Functions • PARSE • ( string_value AS data_type [ USING culture ] ) • TRY_CAST • ( expression AS data_type [ ( length ) ] ) • TRY_CONVERT • ( data_type [ ( length ) ], expression [, style ] ) • TRY_PARSE

  15. Date and Time Functions • DateFromParts • ( year, month, day ) • DateTime2FromParts • ( year, month, day, hour, minute, seconds, fractions, precision ) • EOMonth • ( start_date [, month_to_add ] )

  16. EXECUTE … WITH RESULTS • 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

  17. EXECUTE … WITH RESULTS EXECuspGetEmployeeManagers16 WITHRESULTSETS ( ([Reporting Level]intNOTNULL, [ID of Employee]intNOTNULL, [Employee First Name]nvarchar(50)NOTNULL, [Employee Last Name]nvarchar(50)NOTNULL, [Employee ID of Manager]nvarchar(50)NOTNULL, [Manager First Name]nvarchar(50)NOTNULL, [Manager Last Name]nvarchar(50)NOTNULL) );

  18. Sequence Numbers • Similar to IDENTITY • Not tied to table • Controlled by application • NEXT VALUE FOR

  19. Sequence Use Cases • Default value for columns • Concatenated value identity • Cross statement ROW_NUMBER()

  20. THROW • Raises exception, invokes CATCH block • If outside TRY…CATCH, ends session with severity 16 • Simpler than RAISEERROR • Useful inside CATCH blocks

  21. Metadata Discovery • SET FMTONLY Deprecated • sp_describe_first_result_set • @tsql= N'Transact-SQL_batch • @params = N'parameters' • @browse_information_mode = <tinyint> • Also via similar DMFs

  22. The Workshop Everything you need to get ‘er done...

  23. SSMS Changes • “Powered by” Visual Studio 2010 • F5 is the new Ctrl-E (aargh!) • Result grid columns rearrangable • Editor windows are draggable

  24. More SSMS Changes • Restore GUI enhancements • Page Restore GUI • Database Engine Tuning Advisor • Query Plan Cache Workload • Top 1,000 events by default

  25. Most Unsettling SSMS Change • No Support for Legacy File Extensions • .PRC • .TAB • .UDF • etc

  26. IntelliSense Improvements • Code Snippets • Templates integrated with IntelliSense • Customizable • Surround With • BEGIN…END • IF • WHILE

  27. Documentation Changes • Menu reads SQL Server Documentation • Defaults to Online help • Local installs separately • Same viewer as Visual Studio 2010 SP1

  28. LocalDB • Dev-targeted Edition of Express • Runs in User Mode • “SQL Express-Lite” • Fast, zero-config install • Fewer pre-requisites

  29. Automatic LocalDB Instances • Public • Created and managed automatically • Usable by any app • Exists if LocalDB exists

  30. Named LocalDB Instances • Private • Created, owned, managed by app • Isolated, dedicated

  31. Shared LocalDB Instances • Support multiple users • Created by administrator • Either automatic or named

  32. SSIS Changes • “Largest investment in SSIS to date”

  33. SSIS Paradigm Shift 2.0 • Project Concept • Buildable • Deployable • Manageable • Best practice acknowledgement

  34. SSIS Projects • Shared Connection Managers • Project scoped variables • Optional

  35. SSIS Parameters • Replace configurations • Project or Package Level • Package-scoped recognized by EPT

  36. SSIS Dev Changes • SQL Server Data Tools is the new BIDS • Undo • Consistency in XML

  37. Modern Luxuries Granite countertops coated in PowerShell...

  38. Columnstore Index • 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

  39. Extended Events • Wizard-driven Creation • GUI-driven Editor • Customizable Data Viewer

  40. PowerShell • Increasingly used for SQL Management • AlwaysOn • SSIS • No longer installed by SQL Setup • SQLPS Now Deprecated • Stick with SQLPSX & straight up PowerShell

  41. Home Security For that peaceful, easy feeling...

  42. More Secure by Default • No auto provisioning to sysadmin • BUILTIN\Administrators • Local System

  43. Managed Service Accounts • Created & managed by Domain Controller • Passwords • SPNs • Domain\Accountname$ • Windows Server 2008 R2 only

  44. Virtual Accounts • Automatically managed local accounts • No Passwords • NT SERVICE\<SERVICENAME> • Access to network via computer account • <domain_name>\<computer_name>$ • Windows Server 2008 R2 only

  45. Contained Databases • Little or no server dependencies • Contained vs. uncontained objects • Useful for • Failover • SQL Azure • Non sysadmin Administration

  46. Audit Improvements • Server level supported in all editions (database level still Enterprise-only) • Recoverable audit log failures • User defined audits • Audit log filters (WHERE clause)

  47. Other Security • 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)

  48. Available Now Move in ready!!!

  49. High Availability • AlwaysOn – umbrella term • Failover Cluster Instances • Availability Groups • Online Operations • Indexes with LOBs • Adding Columns with Default Values

  50. Failover Cluster Instances • Multi-subnet Failover Clusters • Inter-datacenter • No single, shared storage • Data replication • Disaster recovery + HA

More Related