Transcript here
DBA Delta Force SQL Server 2012 features for the DBA MAKING BUSINESS INTELLIGENT www.pragmaticworks.com Agenda About me Discontinued and Deprecated features in SQL Server 2012 New features to be aware of ColumnStore Indexes Thanks MAKING BUSINESS INTELLIGENT www.pragmaticworks.com Gareth Swanepoel From South Africa, originally Been in IT for 20+ years Was first a sysadmin, then a DBA DBA Consultant with Pragmatic Works Currently authoring 3 chapters in the upcoming SQL Server 2012 Pro Admin book eMail : [email protected] Twitter : @GarethSwan Website : www.MyGareth.com MAKING BUSINESS INTELLIGENT www.pragmaticworks.com Discontinued & Deprecated features MAKING BUSINESS INTELLIGENT www.pragmaticworks.com Discontinued features Features not available in SQL Server 2012 BACKUP DATABASE WITH [MEDIA]PASSWORD Compatibility version 80 (SQL 2000) VIA connection protocol SQL Mail, Net Send & pager notifications All 32-bit support (AWE, etc.) SQL Server Distributed Management Objects (SQL-DMO) Reference: http://technet.microsoft.com/enus/library/ms144262(v=SQL.110).aspx MAKING BUSINESS INTELLIGENT www.pragmaticworks.com Deprecated features Features not available in the next and future versions of SQL Server Compatibility version 90 (SQL 2005) Remote servers Ability to return result sets from triggers BACKUP {DATABASE | LOG} TO TAPE SQL Server Profiler Reference: http://technet.microsoft.com/enus/library/ms143729(v=sql.110).aspx MAKING BUSINESS INTELLIGENT www.pragmaticworks.com New & improved features Features that DBA's care about that will kick butt in SQL Server 2012 Extended Events – New Session Wizard & Session UI Contained database authentication Custom (user-defined) database roles Auditing enhancements AlwaysOn FileTable and Full-text Semantic Search T-SQL enhancements MAKING BUSINESS INTELLIGENT www.pragmaticworks.com ColumnStore Indexes The Fangorn Forest: Home of the Ents ColumnStore Indexes – the basics Row Store (Heap / B-Tree) data page 1000 ProductID OrderDate Cost 310 20010701 2171.29 311 20010701 1912.15 312 20010702 2171.29 313 20010702 413.14 MAKING BUSINESS INTELLIGENT Column Store (values compressed) data page 2000 310 311 312 313 Cost OrderDate ProductID data page 2010 20010701 20010701 20010702 20010702 data page 2040 2171.29 1912.15 2171.29 413.14 www.pragmaticworks.com ColumnStore Indexes The Fangorn Forest: Home of the Ents Key Characteristics of CSI's Columnar data format Faster query results Query processing Table cannot be updated MAKING BUSINESS INTELLIGENT www.pragmaticworks.com ColumnStore Indexes The Fangorn Forest: Home of the Ents DEMO MAKING BUSINESS INTELLIGENT www.pragmaticworks.com ColumnStore Indexes The Fangorn Forest: Home of the Ents Best Practices Choosing Columns DO … put CSI's on large tables only DO … structure queries as star-joins DON'T … use OUTER JOIN DON'T … use UNION ALL DON'T … use string filters, etc DON'T … use NOT IN (<subquery>) MAKING BUSINESS INTELLIGENT www.pragmaticworks.com eMail : [email protected] Twitter : @GarethSwan Website : www.MyGareth.com Sales : [email protected] Products BI products to convert to a Microsoft BI platform and simplify development on the platform. MAKING BUSINESS INTELLIGENT Services Speed development through training and rapid development services from Pragmatic Works. Foundation Helping those who do not have the means to get into information technology and to achieve their dreams. www.pragmaticworks.com