omri bahat principal program manager microsoft session id: dat311
TRANSCRIPT
Microsoft SQL Server Automation, including PowerShell Support
Omri BahatPrincipal Program ManagerMicrosoftSession ID: DAT311
Everyone is being called on to administer more servers, databases, and applications
You’re looking for ways to cope with keeping your systems up and running
How do you go from what you know from SQL Server 2000 and 2005…
To taking control of your environment with the new features in SQL Server 2008 and from our partners?
Maximizing task automation
PowerShell By ExamplePowerShell SubsystemSQL Sentry
Execution
Using Resource GovernorMaintenance Plans Flow
Control
Agenda
Server ProvisioningDB Mail Review
Configure
Microsoft IThttp://technet.microsoft.com/en-us/library/bb687798.aspx
partner
MSIT Standard Server Provisioning
21 Day Backup PlanOne disk holds seven days of backups - one full, and six days of differentialsSwitch disks every seven daysTransaction logs go to a different diskPurge on drive rotation
On Demand JobsCleanup backup historyDBCC AllIndexDefragUpdateStatsIndexRebuild
Includes exception lists
For SharePoint Index defrag and maintenance, see: http://tinyurl.com/5snm3d
Configuring Database Mail for SQL Agentdemo
What Did We See?
Configuring DB Mail via the wizardUsing Hotmail as your SMTP serverExamples of sending mailHooking up SQL Agent to send mail
Maximizing task automation
PowerShell By ExamplePowerShell SubsystemSQL Sentry
Execution
Agenda
Using Resource GovernorMaintenance Plans Flow
Control
Server ProvisioningDB Mail Review
Configure
Using Resource Governor to Throttle Jobsdemo
Resource Governor Recap
Creating Pool and Workload Group for JobsUsing Classifier FunctionVerifying the results
Inside of Maintenance Plansdemo
What Did We See?
Creating Maintenance Plans in SSMSUsing sub plans for segmenting multi-server plansImporting plans into SSISDebugging plans in SSISExporting plans to another instance and cleaning up the result
Maintenance Plans Strategies
When to use themGreat for small shops – create/schedule/forgetEasy way to manage multiple instances with sub-plansEasy way to cover all user database objects with DB iteration in SSISCombined with SSIS additional functionality
When to avoid themWhen you want to share them – no scriptingWhen SQL Authentication is your only option
Maximizing task automation
PowerShell By ExamplePowerShell SubsystemSQL Sentry
Execution
Agenda
Using Resource GovernorMaintenance Plans Flow
Control
Server ProvisioningDB Mail Review
Configure
PowerShell Overviewdemo
What Did We See?
Shell profile customizationSQLPS launched from SSMS where server treated like a driveCommand piping, conditions, sorting, output columns, output to HTMLCalling WMI to list server instancesIntegration with .Net librariesPowerGUI.OrgChad Miller’s SQLPSX http://sqlpsx.codeplex.com/
Using the PowerShell Agent Subsystemdemo
What Did We See?
Review of Agent’s subsystemsUsing the PowerShell subsystemExecuting script fragments Executing script filesUsing a proxy account for script execution
Enterprise Policy Based Management on CodePlex scripts - http://www.codeplex.com/EPMFramework
NoteEach job step launching sqlps consumes ~20MB
SQLSentry Chaining, Eventing, and Queuing
Maximizing task automation
PowerShell By ExamplePowerShell SubsystemSQL Sentry
Execution
Recap
Using Resource GovernorMaintenance Plans Flow
Control
Server ProvisioningDB Mail Review
Configure
question & answer
SQL Server Community Resources
Become a FREE PASS Member: www.sqlpass.org/RegisterforSQLPASS.aspxLearn more about the PASS organization www.sqlpass.org/
Additional Community ResourcesSQL Server Community Center www.microsoft.com/sqlserver/2008/en/us/community-center.aspxTechNet Community for IT Professionals
http://technet.microsoft.com/en-us/sqlserver/bb671048.aspxDeveloper Center
http://msdn.microsoft.com/en-us/sqlserver/bb671064.aspxSQL Server 2008 Learning Portalhttp://www.microsoft.com/learning/sql/2008/default.mspx
• Connect: Local Chapters, Special Interest Groups, Online Community• Share: PASSPort Social Networking, Community Connection Event• Learn: PASS Summit Annual Conference, Technical Articles, Webcasts
• More about the PASS organization www.sqlpass.org/
The Professional Association for SQL Server (PASS) is an independent, not-for-profit association, dedicated to supporting, educating, and promoting the Microsoft SQL Server community.
SQL Server Word of the Day
DATA COMPRESSION
Wednesday, May 13
*Game cards may be picked up at the SQL Server booths in the TLC
Additional Resources
External Resourceshttp://www.powergui.org http://chadwickmiller.spaces.live.com/default.aspx http://www.codeplex.com
SQL Server 2008 Business Value Calculator: www.moresqlserver.com
• Speaker Blog: http://blogs.msdn.com/billramo/ • Team Forum:
http://social.msdn.microsoft.com/Forums/en-US/sqltools • Other: http://blogs.msdn.com/buckwoody
www.microsoft.com/teched
Sessions On-Demand & Community
http://microsoft.com/technet
Resources for IT Professionals
http://microsoft.com/msdn
Resources for Developers
www.microsoft.com/learningMicrosoft Certification and Training Resources
www.microsoft.com/learning
Microsoft Certification & Training Resources
Resources
Related ContentBreakout Sessions
BIN207 Upgrading DTS Packages to Microsoft SQL Server Integration ServicesBrian KnightFri 5/15 | 2:45 PM-4:00 PM | Petree Hall C
Hands-on Labs
DAT04-HOL Microsoft SQL Server 2008 Data Recovery and Preventative Techniques
DAT16-HOL Using Microsoft SQL Server 2008 Resource Governor for Predictable Performance
Complete an evaluation on CommNet and enter to win an Xbox 360 Elite!
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.