Managing SQL Server 2008 Systems Efficiently
Discover insights on SQL Server 2008 systems management, DBA history, agenda, feature usage, and data warehouse overview. Learn about performance studio, policy-based management, PowerShell, event notifications, DMVs, and more.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
SQL Server 2008 Systems Management Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson http://www.sqlserverfaq.com http://www.sql-server.co.uk tonyrogerson@torver.net [Feel free to learn something] [User Group - Feel free to join] [Feel free to hire me] [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 Goal: Build a working environment in 40 minutes Performance Studio Policy Based Management Powershell and SQLCmd Event Notifications Extended Events DMVs (Dynamic Management Views)
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 Data Collector S2K8_TRADES Data Collector MDW DB Reporting - Management Studio - Report Builder - T-SQL S2K8_SUPPORT Validation for spending more dosh on resources Data Collector 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 Notifications Extended 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..
Demo Extended Events / Event Notification
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/2 009/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)
