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. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
SQL Server 2008 Systems Management Tony Rogerson, SQL Server MVPhttp://sqlblogcasts.com/blogs/tonyrogerson [Feel free to learn something]http://www.sqlserverfaq.com [User Group - Feel free to join] http://www.sql-server.co.uk [Feel free to hire me]email@example.com [Feel free to email me]
Who am I? • Entered industry in 86 coding PL/1, DB2, CICS, Application System – mainframe beginnings. • More DBA centric from 1993 starting on 4.21a of SQL Server. • Freelance SQL Server specialist since 1999 • I started the UK SQL Server UG (sqlserverfaq.com) in 1999; been an SQL MVP since 1998.
History as a SQL Server DBA • Required Polling – lots of it • Extensive Development • Monitoring • Maintenance • Capacity Planning • Not much info available to DBA • Over use of xp_cmdshell • Afflicted with the business attitude you can stick the box in the corner and it will look after itself.
Agenda • Performance Studio • Policy Based Management • Powershell and SQLCmd • Event Notifications • Extended Events • DMVs (Dynamic Management Views) Goal: Build a working environment in 40 minutes
Feature usage • Protect Production using Policy Management • Backups and Maint using Powershell and SQLCmd • Example of FTP using Powershell • Monitor for problems using Event Notifications and Extended Events • Capacity Planning/Job Durations using Performance Studio • Adhoc system enquiries using DMV’s
Management Data Warehouse Overview DataCollector S2K8_TRADES DataCollector MDWDB • Reporting • Management Studio • Report Builder • T-SQL • Validation for spending more dosh on resources S2K8_SUPPORT DataCollector S2K8_REPORTING
Demo – Performance Studio • Set up from scratch • Centralisation • Reporting • Management Studio • Report Builder • TSQL
Policy Management Overview • Provide Standardised Management Framework • Enforce Standards (all tables have a clustered index) • Detect Changes (Change of Database Option) • Push across your enterprise (all SQL versions)
DEMO – Policy Based Management • Set up protection for our Production Environment
Powershell and SQLCmd Overview • PowerShell :: MS’s definitive scripting language – used in Windows OS, SQL Server, Exchange etc... • SQLCmd :: specific to SQL Server • Just use PowerShell – Invoke-Sqlcmd • Automation of tasks • OS related tasks too simple for SSIS
Event NotificationsExtended Events • Extended Events • Diagnostic mechanism – replaces profiler (for engine traces) • Event Notifications • On a specific event occurring the event details get placed on a Service Broker queue • Use to Email when faults happen – Mirroring block event, other blocking events, SQL error etc..
Dynamic Management Views (DMV’s) Overview • Gives an insight into SQL Server • Many categories – IO related, index related, execution related etc... • Very useful problem determination mechanism • Very useful information capture – basically what Data Collector uses in MDW.
Summary / Further Reading • http://tinyurl.com/yb5hysy takes you to http://sqlblogcasts.com/blogs/tonyrogerson/archive/2009/09/25/further-reading-references-for-my-system-management-presentation-on-20090929-ms-data-management-conferenc-in-london.aspx • http://sqlserverfaq.com – User Group LiveMeetings through User Group (http://sqlserverfaq.com) 20091022 : Tony Rogerson -> Management Data Warehouse 20091105 : Tony Rogerson -> Policy Based Management 20091112 : Allan Mitchell -> DMX 101 (Introduction to the basics of DMX, SELECT, FROM, Natural Prediction Join) 20091119 : Tony Rogerson -> Powershell and SQLCmd 20091126 : Allan Mitchell -> Using SSIS to connect to the cloud 20091203 : Tony Rogerson -> Event Notifications and Extended Events 20091210 : Allan Mitchell -> DMX 102 (A look at the Predict Function and its many guises)