1 / 16

SQL Server 2008 Systems Management

Learn about SQL Server system management techniques, monitoring, policies, backups using PowerShell, email notifications, and event tracking to optimize performance effectively. Explore dynamic views, management studio features, and standardization strategies.

rufina
Télécharger la présentation

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

More Related