Allen White

Loading SMO Assemblies into PowerShell

2008-12-07T20:59:00Z

Microsoft included PowerShell with SQL Server 2008, and the new sqlps.exe program which includes the SQL PowerShell drivers including the PS Drives and new cmdlets for SQL Server. I prefer to work from vanilla PowerShell and load the assemblies I need, so I have code at the start of my script to load the appropriate assemblies.

When I started testing my SQL 2005 PowerShell scripts against a system with SQL 2008 loaded I ran into problems because many of the functions formerly included in the SMO DLL were moved to the SMOExtended and SQLWMIManagement DLLs. I was teaching a class this past week where a student asked if the code could be written so that the proper DLLs were loaded regardless of which version of SQL Server was installed.

I started playing with it and found some anomalies (or what seemed to me to be anomalies) in doing this. First, I expected the version information passed back when you load an assembly to be different between the two systems, but when I loaded the SMO assembly both reported back v2.0.50727. This is obviously of no help.

After playing with a few properties I found the release information I wanted buried in the property called FullName. I executed the following statement:

$v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

The $v variable contains a value of type System.Reflection.Assembly and the FullName property of this variable contains

2005:	Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
2008:	Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

So, by splitting out the string returned by the FullName property I can get the version. First I split by a comma (,), then by the equal sign (=), then finally by a period (.), and when I'm done I have the main version number of the DLL loaded. Here's the code:

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$p = $v.FullName.Split(',')
$p1 = $p[1].Split('=')
$p2 = $p1[1].Split('.')
if ($p2[0] -ne '9') {
	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended')  | out-null
	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement')  | out-null
	}

With this at the head of my PowerShell scripts I can be certain that whatever version of SQL Server I'm using the same scripts will load and run properly.

Allen

PASS Demonstration Errors

2008-11-24T16:37:00Z

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

2008 PASS Summit Presentation Schedule

2008-10-24T17:41:00Z

This week the PASS Program Committee asked me to add another session to my schedule, so now is as good a time as any to share my PASS schedule with you.

DBA-208 Using PowerShell to Get the Most out of SQL Server Wednesday, November 19, 2008 10:15 AM - 11:30 AM Room 602-603 (Theatre)

PowerShell is the new scripting environment from Microsoft for managing server environments. Because PowerShell supports the .Net Framework, accessing SQL Server in a scripting environment is easier than ever before. Whether you need to access data or manage SQL Server, PowerShell makes it possible. This session will give an introduction to PowerShell, explain the basic ADO.Net objects required to connect with SQL Server, and demonstrate accessing SQL Server from PowerShell scripts, both from a data and a management perspective.

DBA-331 Automating SQL Server Administrative tasks with PowerShell Wednesday, November 19, 2008 4:30 PM - 5:45 PM Room 615-616 (Theatre)

Most administrative functions consist of repetitive sequences of tasks which just take time. PowerShell is a powerful scripting environment which allows an administrator to automate those repetitive tasks, providing better results to the organization while saving the administrator time, and ensuring that the tasks get done properly every time. This session will give an introduction to PowerShell and demonstrate a number of scripts which help SQL Server administrators effectively manage their environments.

After the success we had presenting a similar panel discussion at Tech Ed 2008 in June, Kevin Kline asked me to moderate this session, sponsored by Quest Software:

Panel Discussion: Automating DBA Processes for SQL Server 2005/2008 When: Thursday, November 20, 2008 7:00 - 8:30 a.m. (breakfast served until 7:30) Where: Seattle Convention Center Room 613 (6th floor)

The general overview and registration page can be found here: http://www.quest.com/events/listdetails.aspx?contentid=8236

Panelists: Allen White (Moderator) Kevin Kline Thomas LaRock Charley Hanania Brent Ozar Buck Woody Dan Jones

Please join me for these fun events - more about the conference is available at the PASS site: http://summit2008.sqlpass.org/.

Allen

Personnel Development (aka Training)

2008-10-23T13:49:00Z

This is probably not the best time to discuss personnel development, given the current economic conditions, but then again, maybe it is.

How much does your company budget for training?

I was very fortunate when I was with Advanstar Communications for the last five years because my boss, Rich McCourt, believed strongly in training, both technical and soft skills. It was largely because of that that I got involved in PASS, my local SQL Server users group, blogging here, etc. Each year we were all expected to take two weeks of training, and some times I even had more than that.

Having, and using, a training budget accomplishes at least two things for employees - it gives them technical (or non-technical, in the case of soft skills) knowledge that they can use to make the systems they work on better, or improve their relationship with their user communities. It also tells them that they are valued employees and that their personal improvement is as much a priority to the company as the bottom line is. This value generally is returned back to the company at a far higher return than the cost of the training given.

Many companies send their people to classes just when they need to take on a new technology and come up to speed quickly. This is good, but sending people to classes when things aren't changing can help them get a broader perspective on the technology and maybe introduce some new ideas to make the technology work better.

There are a lot of sources for training, as many companies offer classes on specific technologies, some from Microsoft Learning and many others developed by experts in-house. Local colleges offer a wide array of courses that cover specific technical areas.

What I found most value in was technical conferences, like Tech Ed, and most appropriately, PASS. The wide variety of subjects covered, combined with the opportunity to network with others who did the same kind of work I did (since I was the only SQL Server DBA in my shop) proved invaluable.

Budgets are tight, that's a given, but investing a small amount in training can reap benefits significantly greater than the cost of that training.

Allen

What Is Your Change Management Process?

2008-10-21T01:07:00Z

Large(r) companies tend to have a defined process for implementing change in their data centers, but small(er) companies tend not to. (Your Mileage May Vary.) This makes sense, as large companies usually have more systems dependent on each other, and a change made to one system can dramatically impact the effectiveness of another system. Small(er) shops tend to feel that those implementing the changes know the impact of the change they're making.

What is change management? Well, it's a well-defined process to document changes made to your systems. The changes include hardware, software, network infrastructure, configuration settings, or anything that can disrupt the normal processing of your business applications. The process usually includes a description of what change will be made, who will make the change, when it will be made and (most importantly) what steps will be taken if the changes were unsuccessful. The changes are generally reviewed by people representing groups who will be impacted by the change. These people often have the responsibility to approve the proposed changes.

The documentation of the changes provides a great reference tool for those times when problems occur and are traced to some change event, because it's then known when the change was made, by whom, and the anticipated effects of the change.

I think the most important aspect of the change management process is the backout plan should the change not be successful. In too many cases the processes necessary to reverse changes aren't researched until after problems occur, when the change is disrupting business and there's significant pressure on those making the changes to "get it fixed". Sufficient time spent ahead of the change documenting what steps are required to back the change out can minimize the downtime and minimize that pressure.

(This is why flight training focuses so much on emergency procedures - keeping a level head when problems occur can truly save lives.)

If you haven't implemented a comprehensive change management process it's well worth your time.

Allen

SQL Server 2008 SMO Library Changes

2008-10-18T16:35:00Z

While preparing for a presentation at PASS next month I tested my PowerShell script to back up user databases on a server. This script has been running flawlessly against SQL Server 2005 for a couple of years, so I was really surprised when PowerShell returned a screen full of errors and no backups.

What slipped my mind was something I'd reported a year ago here, that a number of objects have been moved from the SMO dll to the SMOExtended dll.

The problem was easily resolved by adding the following line to the top of my PowerShell script:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null

Fortunately it's a minor gotcha, but one that can cause some problems after an upgrade if you're not prepared.

Allen

SQL Momentum Roadshow Events - Columbus and Detroit

2008-10-09T11:59:00Z

Now that SQL Server 2008 is out and available, Microsoft is hosting a series of roadshow events around the country to highlight the new features of the product. The sessions are four hours, with the first half focused on DBA/Dev topics and the second half focused on BI.

I'm based in Cleveland, and so I'll be doing the presentations for the Columbus, Ohio and Detroit, Michigan events. Here's the details on the events and links to the registration sites:

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032388722&Culture=en-US
Tuesday Oct 14, 2008 8:00 AM - 12:00 PM
AMC Cinemas - Easton Town Center 30 with IMAX,
275 Easton Town Center
Columbus, OH 43219

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032388726&Culture=en-US
Wednesday Oct 22, 2008 8:00 AM - 12:00 PM
Embassy Suites Hotel Detroit
19525 Victor Parkway,
Livonia, MI 48152

If you want to get a great overview of the capabilities of SQL Server 2008 it'll be well worth your time.

Allen

PASS Conference Stories

2008-10-09T01:47:00Z

My first PASS conference was in 2003 in Seattle, and PASS had as its SIG challenge a project. The people who showed up were divided up into teams, and were given a project that had a Dev, a DBA and a BI component, and had to have the project completed by Friday noon. We weren't allowed access to the project room during conference session times, but were allowed in from 6:30am to 11ish pm each day. My team consisted of four DBA's - myself, Johan Bijnens from Belgium, Morten Baden Rohde from Denmark, and James Loesch from California. We didn't win the competition, but Johan, Morten and I get together every year at the PASS conference. (Not sure whatever happened to James.)

The most entertaining stories came from the 2006 PASS conference, also in Seattle, where I had my first opportunity to speak at PASS. The dirty stories Joe Celko told in the Speaker's Lounge were hilarious - he truly has a gift in that sense. Gert Drapers also related to us the story behind xp_smtp_sendmail and how it was originally supposed to be included in an update to SQL Server 2000, but because it wasn't able to receive mail, it was axed, so Gert put it out on his own website - I truly made use of that tool.

Also in 2006 I'd brought a young developer (John Sobolewski) to the conference and introduced him to Johan and Morten, and we went out one night to the variety of vendor parties. Well, he and Morten ended up walking around downtown Seattle until 5am talking. I'd wondered where he was when he didn't show up for breakfast the next day. :-)

The 2004 PASS Conference in Orlando was the first where I'd paid for the pre-conference sessions. That was also the year that Hurricane Jeanne came through. (I remember the name because it was the same spelling as my mother-in-law's name.) Our flight from Cleveland was cancelled on Sunday, and they couldn't get us out until Tuesday, so another developer buddy (Joe Kuemerle) and I rented a car and drove. We got as far as Florence, South Carolina that first day and stopped for the night. On Monday morning we hit the road again and got hit by the hurricane as we drove through southern South Carolina and into Georgia. It was scary at times but we made it to Orlando on Monday afternoon. The Monday pre-conf sessions had been cancelled but we were there for the Tuesday sessions. (The other funny thing was that the Gaylord was hosting some nursing home residents because of the storm, so for the first couple of days every time I went in or out of my room, the guy across the hall would open his door and peek at me from behind the door, then close it again as soon as I'd look at him.)

The 2005 PASS conference in Dallas was uneventful except I found it amazing that I had to wear sweatshirts in the conference rooms because they had the air conditioning up so high, yet it was in the 90's outside.

In 2007 I was at the PASS conference for my first time as a SQL Server MVP, and that was an amazing experience, both from meeting and getting to know the people who'd helped me get to where I was through their writing in books, magazines and web sites. I helped Louis Davidson with the SIG Challenge Jeopardy game, which was a real hoot. The bowling party was crazy fun, and the final evening dinner where Itzik Ben-Gan stumped us all with his puzzles will always be a fond memory.

Tags: Kalen Delaney, Paul Nielsen, Louis Davidson and Lara Rubbelke!

Thanks for the tag and the kind words, Kevin. The next "fermented beverage" is on me.

Allen

[Ohio] A Day in the Life of the DBA 2008

2008-10-06T12:37:00Z

We are pleased to invite you to the October 9th 2008 meeting of the Ohio North SQL Server Users Group (ONSSUG). The meetings are held on the 2nd Thursday of every month at 5:45 in the Cleveland Microsoft office. The address of the office is below:

Microsoft Corporation
6050 Oak Tree Blvd. Suite 300
Cleveland, OH 44131

Agenda:

5:00 - 5:45 PM - Early Arrival/Discussions

Deepak Puri offered to present information that he found interesting from the 2nd Annual Microsoft BI Conference. Deepak has presented to the group following SQL Pass and other conferences and does an outstanding job of synthesizing the most important highlights from the conference.

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM - A Day in the Life of the DBA 2008 by Buck Woody, Microsoft

This presentation details a typical DBA's day, demonstrating the new improvements of the Management Platform made for Microsoft SQL Server 2008. These improvements include more efficient system management, multi-server management, performance tracking, policy creation, state detection and enforcement, and using both graphical and command-line tools. We will cover everything from the new Management Interfaces to Policy Based Management, including a practical demonstration of PowerShell integration in SQL Server administration tasks.

About Buck

As a program manager for Microsoft SQL Server, Buck Woody provides leadership on feature sets within SQL Server, as well as leads team of developers and testers. Buck is also responsible for the documentation, user-experience, and the legal and marketing professionals for the feature sets. Find out more at http://buckwoody.com/BResume.html.

7:00 - 7:30 PM - Wrap up / Ongoing discussions

Registration:

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen

SQL 2008 Data Compression - Linchi Shea

2008-09-26T14:07:00Z

Last night at the NYC SQL Server Users Group meeting Linchi Shea presented a session on Data Compression (not Backup Compression) in SQL Server 2008.

By understanding how the compression algorithm works, and tuning your clustered indexes based on that understanding, you can achieve some pretty incredible space savings on disk, and performance benefits due to the increased amount of data loaded into cache. His analysis is available from his blog post SQL Server 2008 Data Compression: Impact of Data Distribution.

His explanation was clear and I learned a lot from his session. (I am in New York to do some performance testing at the Microsoft Technical Center, where the meetings are held every Fourth Thursday of the month.)

If you happen to be in New York and can spare the time, I highly recommend this group. It was well worth my time.

Allen

[OT] Here today, Gone to Maui

2008-09-10T12:40:00Z

It's been three years since I've had a real vacation, so we're leaving tomorrow to head to the island of Maui, where I'll be running the Maui Marathon on Sunday (Sept. 14).

Aloha!

Allen

Introduction to the New Data Types in SQL 2008

2008-09-09T17:57:00Z

We are pleased to invite you to the September 11th 2008 meeting of the Ohio North SQL Server Users Group (ONSSUG). The meetings are held on the 2nd Thursday of every month at 5:45 in the Cleveland Microsoft office. The address of the office is below:

Microsoft Corporation
6050 Oak Tree Blvd. Suite 300
Cleveland, OH 44131

Agenda:

5:00 - 5:45 PM - Early Arrival/Discussions

Due to technical difficulties we did not get to see the Demonstrations of Excel 2007 BI. These technical difficulties will be remedied and Deepak will show the Excel Demos from the August Meeting.

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM - Introduction to the New Data Types in SQL 2008 by Sarah Dutkiewicz

SQL 2008 introduces 7 more data types. Come see Sarah Dutkiewicz as she walks us through the new datetime, hierarchical, and geospatial data types and see how they can be used.

About Sarah

Sarah Dutkiewicz has been developing database-driven applications over the past few years. When she isn't working as a developer for a Cleveland-based company, she's looking into new technologies and staying involved in the .NET community. Her blog is at http://www.codinggeekette.com.

7:00 - 7:30 PM - Wrap up / Ongoing discussions

Registration:

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen

The Business Intelligence (BI) features of Excel 2007

2008-08-13T12:33:00Z

We are pleased to invite you to the August 14th 2008 meeting of the Ohio North SQL Server Users Group (ONSSUG). The meetings are held on the 2nd Thursday of every month at 5:45 in the Cleveland Microsoft office. The address of the office is below:

Microsoft Corporation
6050 Oak Tree Blvd. Suite 300
Cleveland, OH 44131

Agenda:

5:00 - 5:45 PM - Early Arrival/Discussions

Join Bob Coppedge As they discuss the current state of Microsoft certifications for database professionals

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM - The Business Intelligence (BI) features of Excel 2007 by Deepak Puri

The Excel pivot table has long been one of the most popular BI tools for data analysts, and the most frequently used client tool for SQL Server OLAP cubes. Now, with Excel 2007, its integration with Analysis Services has been revamped to comprehensively support SQL Server 2005-specific features. We will explore how this integration helps to improve the user experience.

Many other enhanced Excel 2007 features, such as charting, filtering and formatting, bring significant benefits to BI usage scenarios as well. In addition, the new Excel Services feature of SharePoint 2007 allows spreadsheets to be published to a SharePoint server, and be viewed by users via web browsers.

We will examine how this new functionality makes Excel 2007 a powerful and flexible BI client tool.

About Deepak

Deepak is a Business Intelligence Consultant at a large insurance company, and has been working with SQL Server Analysis Services since 2000. His interest in OLAP technology arose from working with large volumes of call center telecom data. In addition, Deepak also works with performance data and Key Performance Indicators (KPI's) for new business processes. Recent project work includes SSAS cube design, and dashboard and reporting front-end design for cube data, using Reporting Services, SharePoint and third-party OLAP-aware Web Parts Deepak is also an SQL Server Analysis Services MVP.

7:00 - 7:30 PM - Wrap up / Ongoing discussions

Registration:

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen

Oh, What a Tangled Web

2008-07-31T13:17:00Z

At Tech Ed 2008 I made a mental note when Paul Randal mentioned that you can't use Database Mirroring when you've got the Filestream feature turned on. I like Database Mirroring and so that was useful information.

In preparing a bunch of demos for SQL Server 2008, I'm using the AdventureWorks2008 database currently available on the Codeplex site. Installing this database requires the Filestream feature, so I turned it on.

Some of the demos I'm working on are existing demos written for the AdventureWorks database on SQL Server 2005, and I've found many of them don't work. It turns out that the structure of the database has been modified, eliminating some tables commonly used in demos (Person.Contact, for example), and changing the column names of primary keys as well as other columns.

I also discovered that you can't set the READ_COMMITTED_SNAPSHOT and the ALLOW_SNAPSHOT_ISOLATION options on if the Filestream feature is enabled, so to demo those options I'll have to load the old AdventureWorks database as well as the new AdventureWorks2008 database.

Fun!

Allen

Partition Wizard Makes it Simple

2008-07-28T12:44:00Z

The partitioned table was one of the great new features of SQL Server 2005. We could now manage data based on some range data, usually a date value, and keep the different ranges in separate physical files, and use rolling range scripts to move old data out of the table and create new partitions for new data and incorporate those new ranges into the table, and didn't have to change the application to keep it working properly.

The feature is cool, setting it up turned out to be a pretty large task, and when setting up a partitioned table over two years history with millions of rows of data, it took hours to plan and set up.

SQL Server 2008 introduces a wizard that makes the creation of the partition function and partition wizard fairly easy to understand and very simple to implement.

By right-clicking on the target table, a menu option called Storage appears, and you can then select Create Partition. Select the column to be used as the partitioning column, specify names for your partition function and scheme, then the Map Partition dialog is displayed. You can specify RANGE RIGHT or RANGE LEFT using the right boundary or left boundary radio buttons at the top of the dialog. (Boundary makes much more sense than RANGE, doesn't it?) Also, when the left boundary button is selected, the header of the second column in the filegroups grid indicates "<=Boundary", and when the right boundary button is selected, the header indicates "<Boundary", so it helps you understand the decisions you're making.

Now, let's say you've got two years of data you want to partition, with a partition for each month. Just click the Set Boundaries button, enter the start and end dates and the range you want to partition on (Monthly, Quarterly, Yearly, etc.) and the wizard creates those ranges for you automatically. Now, all you have to do is assign each of the date ranges calculated to the partitions you'll have to have already set up in the database. (You do have to set up the filegroups and files before starting the wizard - it can't do that for you.) You have to also include a filegroup to catch the data falling outside your defined range, so if you've set up 24 ranges for two years of data, you'll need 25 filegroups defined.

The partitioned table is a great feature you should be using to manage data more effectively. With SQL Server 2008 it'll be even easier to implement.

Have fun!

Allen