Microsoft SQL Server 2008 Administration with Windows PowerShell (Wrox Programmer to Programmer)

Introduction. 1. What Is Windows Powershell? 2. Windows Powershell Command Types, Snapins, And Cmdlets. 3. Windows Powershell Programming, Scripting, Error Handling And Debugging. 4. Windows Powershell Functions, Parameters, Sourcing, Scopes And User Profiles. 5. Working With File System, Registry, And Variables. 6. Working With Event Logs. 7. Working With Windows Services And Processes. 8. Working With Wmi. 9. Wmi Provider For Configuration Management. 10. Wmi Provider For Server Events. 11. Windows Powershell In Sql Server 2008 Environment, Sql Server Powershell Provider. 12. Managing Policies Through Sqlserver:\sqlpolicy. 13. Windows Powershell And Smo. 14. Building Sql Server Standards And Powershell Coding Standard. 15. Building Sql Server Inventory. 16. Database Administration And Sql Server Installation. 17. Collecting Sql Server Performance And Host Performance Data. 18. Monitoring Sql Server. 19. Monitoring Disk Space Usage, Database Files And Backups. 20. Defining Policies. 21. Generating Database Scripts. Appendix A: Cmdlets Related To Core Snapins. Index. Muthusamy Anantha Kumar, Yan Pan. Includes Index. Wrox Programmer To Programmer--cover. Microsoft SQL Server 2008 Administration with Windows PowerShell 1 About the Authors 9 Credits 11 Acknowledgments 13 Contents 15 Introduction 23 Who This Book Is For 23 What This Book Covers 24 How This Book Is Structured 24 What You Need to Use This Book 25 Conventions 26 Source Code 26 Errata 26 Chapter 1: What Is Windows PowerShell? 29 Command-Line Interfaces versus Graphical User Interfaces 29 Prerequisites for Installing Windows PowerShell 2.0 30 Installing Windows PowerShell 35 Launching Windows PowerShell 38 Summary 41 Chapter 2: Windows PowerShell Command Types, Snap-ins, and cmdlets 43 PowerShell Command Types 44 PowerShell Snap-ins 51 PowerShell cmdlet Syntax 56 Getting Help 59 Sorting 66 Formatting 67 Summary 70 Chapter 3: Windows PowerShell Programming, Scripting, Error Handling, and Debugging 71 PowerShell Scripts 72 PowerShell Variables 72 Arrays 76 Expressions 77 Conditional Expressions 80 Loop Construct 82 Input 86 PowerShell Scripting 87 Text File As Input 90 Output 92 Error Handling 95 Debugging 99 Summary 103 Chapter 4: Windows PowerShell Functions, Parameters, Sourcing, Scopes, and User Profiles 105 Functions 105 Script Parameters 109 Sourcing 112 Variable Scope 113 Transcripts 118 Summary 119 Chapter 5: Working with the File System, Registry, and Variables 121 Using Get-PSDrive and Get-PSProvider 121 Working with File System 123 Working with the Registry 136 Working with Variables 139 Working with Environment Variables 149 Summary 150 Chapter 6: Working with Event Logs 151 Event Log Service 152 Event Viewer 153 Event Logs 153 Log Entry Types 156 Exporting the event logs 162 Summary 168 Chapter 7: Working with Windows Services and Processes 169 What Is a Windows Service? 169 Windows PowerShell and Windows Services 171 Working with Windows Processes 179 Get-Process 181 Stop-Process 183 Summary 185 Chapter 8: Working with WMI 187 Permission Issues and WMI 187 The WMI Model 189 Working with Event Log 192 Working with Services 198 Working with Processes 207 Working with Environment Variables 214 Working with the Registry 218 Summary 223 Chapter 9: WMI Provider for Configuration Management 225 Managing SQL Server Services 226 Managing Client Network Protocols 231 Managing SQL Server Client Aliases 235 Managing Server Network Protocols 239 Changing FILESTREAM Settings 242 Changing SQL Server Advanced Properties 244 Summary 246 Chapter 10: WMI Provider for Server Events 247 WMI Provider for Server Events 247 WMI Query Language (WQL) 248 Event Handling with Windows PowerShell 2.0 249 Monitoring Errors from the SQL Server Error Log 253 Monitoring Deadlocks 254 Monitoring Blockings 257 Monitoring Login Changes and Failed Login Attempts 261 Monitoring Databases 263 Monitoring Database Objects 264 Summary 267 Chapter 11: Windows PowerShell in SQL Server 2008 Environment, SQL Server PowerShell Provider 269 sqlps Utility 269 SQLSERVER: Drive and Invoke-Sqlcmd cmdlet 273 SQL Snap-ins 273 Encoding and Decoding Uniform Resource Name (URN) 284 Summary 286 Chapter 12: Managing Policies through SQLSERVER:\SQLPolicy 287 SQLSERVER:\SQLPolicy Folder 287 Conditions 291 Policies 296 Summary 309 Chapter 13: Windows PowerShell and SMO 311 PowerShell and the SQLConnection .NET Class 311 Working with SQL Server using SMO 318 Working with Databases using SMO 325 Working with Tables using SMO 332 Backup and Restore with SMO 336 Summary 344 Chapter 14: Building SQL Server Standards and PowerShell Coding Standards 345 SQL Server Standards 346 SQL Server Development Standards 346 Stored Procedure Standards 349 Database Design Standards and Best Practices 352 Data Protection Standards and Best Practices 355 SQL Server Production Standards 355 PowerShell Coding Standard 357 Summary 359 Chapter 15: Building SQL Server Inventory 361 SQL Server Inventory 361 Hosts 363 Clusters 374 ClusterNodes 378 Servers 383 Databases 396 Supplementary Tables 401 Summary 404 Chapter 16: SQL Server Installation 405 Installation Procedure and Template 405 Standalone Installation Example 417 Cluster Installation Example 423 Summary 427 Chapter 17: Collecting SQL Server Performance and Host Performance Data 429 SQL Server Performance Data Collection 429 SQL Server Host Performance Data Collection 436 Summary 445 Chapter 18: Monitoring SQL Server 447 Pinging SQL Server Hosts 448 Checking SQL Server鈥搑elated Services on SQL Server Hosts 451 Checking Uptime of SQL Server Hosts 455 Monitoring Windows Event Logs 457 Monitoring SQL Server Error Log 461 Monitoring Blockings 467 Monitoring Deadlocks 472 Summary 477 Chapter 19: Monitoring Disk Space Usage, Database Files, and Backups 479 Monitoring Disk Space Usage 479 Monitoring Database Files 489 Monitoring Backups 496 Summary 501 Chapter 20: Defining Policies 503 Stored Procedure Naming Convention Policy 503 Auto_Close and Auto_Shrink Off Policy 508 Summary 513 Chapter 21: Generating Database Scripts 515 Scripting Databases 515 Scripting Schemas 517 Scripting User-Defined Data Types 519 Scripting Tables 522 Scripting User Views 524 Scripting Stored Procedures 526 Scripting XML Schemas 530 Scripting Users 531 Summary 534 Appendix A: cmdlets 535 cmdlets Related to Core Snap-ins 535 cmdlets Related to the PowerShell Management Snap-in 540 cmdlets Related to the Security Snap-in 547 cmdlets Related to the Utility Snap-in 548 Index 559 The definitive guide to automating SQL Server admnistration with Powershell scripts If you're a SQL Server database administrator, this book will make your life easier. Windows PowerShell is an administrative scripting tool that allows you to automate many tasks you're probably currently doing by hand. This nuts-and-bolts guide shows you how to create Windows PowerShell scripts to administer almost every aspect of SQL Server. You'll get a thorough introduction to PowerShell basics and all the PowerShell components that have been integrated into SQL Server 2008, as well as actual administration tasks. The book provides complete scripts that have been tested and proven, saving you hours of effort. The new release of Microsoft SQL Server 2008 now includes Windows PowerShell, allowing administrators of large-scale enterprise databases to automate many tasks SQL Server 2008 Administration with Windows PowerShell introduces you to PowerShell, covering its components in detail as well as providing basic instruction in using scripts to administer the database The book includes numerous examples of SQL tasks that have been automated and also provides completed scripts that you can put to use immediately The Companion Web site includes complete code scripts If you're a database administrator, the scripts and techniques in SQL Server 2008 Administration with Windows PowerShell will save you hours of effort.