sql server 2008 systems management n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2008 Systems Management PowerPoint Presentation
Download Presentation
SQL Server 2008 Systems Management

Loading in 2 Seconds...

  share
play fullscreen
1 / 16
rufina

SQL Server 2008 Systems Management - PowerPoint PPT Presentation

71 Views
Download Presentation
SQL Server 2008 Systems Management
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. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. 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]tonyrogerson@torver.net [Feel free to email me]

  2. 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.

  3. 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.

  4. 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

  5. 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

  6. 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

  7. Demo – Performance Studio • Set up from scratch • Centralisation • Reporting • Management Studio • Report Builder • TSQL

  8. 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)

  9. DEMO – Policy Based Management • Set up protection for our Production Environment

  10. 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

  11. DEMO – Powershell and SQLCmd

  12. 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..

  13. Demo – Extended Events / Event Notification

  14. 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.

  15. Demo – DMV’s

  16. 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)