Search results matching tags 'Speaking', 'SMO', and 'SQL Server 2008'

PASS PowerShell/SQL Server PreCon

AllenMWhite — Thu, 19 Aug 2010 19:17:00 GMT

PowerShell is an amazing tool to help you automate your administrative processes. There are a lot of books and online sources to help you learn PowerShell, but how do you learn how to use it with SQL Server? Simple, just sign up for my PASS PreCon session. Here's the outline:

Use PowerShell to Get the Most out of SQL Server

Module 1: Introduction to PowerShell

Cmdlets
Aliases
The Pipeline
Variables
Objects
Control Flow
Functions
Modules
Error Handling
Command-line Arguments

Module 2: Introduction to SMO

Introduction to the SMO Library
SQLPS.exe - The SQL Server Mini-Shell
The SMO Object Model

Module 3: Backup and Recovery

Backup
Restore

Module 4: Automating & Maintaining Databases

Automating Index Maintenance
Automating DBCC

Module 5: Importing, Exporting and Transforming Data

ADO.NET
Invoke-SQLCMD
Importing and Exporting using CSV Files
Importing and Exporting using XML Files

Module 6: Manipulating Database Objects

Creating and Deleting Schemas
Creating, Modifying and Deleting Tables
Creating, Modifying and Deleting Triggers
Creating, Modifying and Deleting Indexes
Creating, Modifying and Deleting Views

Module 7: Managing the Enterprise

Registering Servers
Creating a Central Management Server
Connecting to a Central Management Server
Running Scripts Against Multiple Servers
Defining Policies for Policy-Based Management
Evaluating Policies for Policy-Based Management

Module 8: Profiling and Monitoring SQL Server

Gathering WMI Information
Gathering Performance Counters
Setting up a Server Side Trace
Monitor Log Growth

Module 9: Configuring and Tuning SQL Server

Setting Configuration Options
Setting Windows Mode or Mixed Mode

As you can see, we're going to spend the first part of the day learning the basics of PowerShell, but the focus is on how you can manage your own environment using this powerful language, so you can spend your time solving problems instead of the constant point-and-click drudgery of typical DBA tasks.

The all-day session is reasonably priced, and you'll come away with scripts to help you put this tool into use right away. Check out the session details at Use PowerShell to Get the Most out of SQL Server.

See you there!

Allen

PASS Demonstration Errors

AllenMWhite — Mon, 24 Nov 2008 16:37:00 GMT

Errors occur. (I'm sure that's someone's corollary to Stuff Happens.) Errors occur, and that's why we write error handling code in our applications.

My least favorite time for the occurrance of errors is when I'm doing demos, and that's what happened in a presentation I did at PASS last week. I try to test everything before giving the demos, but every so often something slips through the cracks, and that happened. :-(

Two things that I'd blogged about here (SQL 2008 SMO - Doing a Little Rearranging) came back and bit me, because I'd forgotten about them. In a nutshell, the Backup/Restore objects have been moved to Microsoft.SqlServer.SmoExtended.dll and the WMI objects have been moved to Microsoft.SqlServer.SqlWmiManagement.dll. The appropriate DLL has to be loaded in the PowerShell script before the objects contained in those DLLs can be referenced.

If you download my demo scripts from the PASS website, know that they're configured to be run from a workstation where SQL Server 2008 tools have been installed. They'll work against SQL Server 2000, 2005 and 2008. If you want to use them from a workstation with SQL Server 2005 tools, then delete the lines reference the aforementioned DLLs and they'll run fine.

One other problem occurred with my createdb.ps1 script, which is a condition that's occurred since I started working with SMO from PowerShell. In the Server object there's a collection called Settings. In that collection are properties for DefaultData and DefaultLog. These properties are very convenient for finding where your data and log files are supposed to go when you're adding files to databases.

For some reason, these properties are set to null, or an empty string, when the server is installed. The only way I've found to set them to their proper values is to use the Server Properties dialog in Management Studio, go to the Database Settings page and change them to something very different than what's there, then go back and set them back again. Once that's done the properties return the correct values in PowerShell and the createdb.ps1 script I use in my demos works just fine.

I've updated the slides and demos available for download from the PASS site, so grab those updated files and good luck with your testing.

Allen