Buck Woody : Development, Administration

PowerShell PowerPack Download

BuckWoody — Wed, 07 Apr 2010 13:30:20 GMT

I read Jeffery Hicks’ article in this month’s Redmond Magazine on a new add-in for Windows PowerShell 2.0. It’s called the PowerShell Pack and it has a some great new features that I plan to put into place on my production systems as soon as I finished learning and testing them.

You can download the pack here if you have PowerShell 2.0. I’m having a lot of fun with it, and I’ll blog about what I’m learning here in the near future, but you should check it out. The only issue I have with it right now is that you have to load a module and then use get-help to find out what it does, because I haven’t found a lot of other documentation so far.

The most interesting modules for me are the ones that can run a command elevated (in PSUserTools), the task scheduling commands (in TaskScheduler) and the file system checks and tools (in FileSystem). There’s also a way to create simple Graphical User Interface panels (in ). I plan to string all these together to install a management set of tools on my SQL Server Express Instances, giving the user “task buttons” to backup or restore a database, add or delete users and so on. Yes, I’ll be careful, and yes, I’ll make sure the user is allowed to do that.

For now, I’m testing the download, but I thought I would share what I’m up to. If you have PowerShell 2.0 and you download the pack, let me know how you use it.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Using linked servers, OPENROWSET and OPENQUERY

BuckWoody — Tue, 16 Mar 2010 12:41:33 GMT

SQL Server has a few mechanisms to reach out to another server (even another server type) and query data from within a Transact-SQL statement. Among them are a set of stored credentials and information (called a Linked Server), a statement that uses a linked server called called OPENQUERY, another called OPENROWSET, and one called OPENDATASOURCE. This post isn’t about those particular functions or statements – hit the links for more if you’re new to those topics.

I’m actually more concerned about where I see these used than the particular method. In many cases, a Linked server isn’t another Relational Database Management System (RDMBS) like Oracle or DB2 (which is possible with a linked server), but another SQL Server. My concern is that linked servers are the new Data Transformation Services (DTS) from SQL Server 2000 – something that was designed for one purpose but which is being morphed into something much more.

In the case of DTS, most of us turned that feature into a full-fledged job system. What was designed as a simple data import and export system has been pressed into service doing logic, routing and timing. And of course we all know how painful it was to move off of a complex DTS system onto SQL Server Integration Services.

In the case of linked servers, what should be used as a method of running a simple query or two on another server where you have occasional connection or need a quick import of a small data set is morphing into a full federation strategy. In some cases I’ve seen a complex web of linked servers, and when credentials, names or anything else changes there are huge problems.

Now don’t get me wrong – linked servers and other forms of distributing queries is a fantastic set of tools that we have to move data around. I’m just saying that when you start having lots of workarounds and when things get really complicated, you might want to step back a little and ask if there’s a better way. Are you able to tolerate some latency? Perhaps you’re able to use Service Broker. Would you like to be platform-independent on the data source? Perhaps a middle-tier might make more sense, abstracting the queries there and sending them to the proper server. Designed properly, I’ve seen these systems scale further and be more resilient than loading up on linked servers.

Lessons from Volunteering

BuckWoody — Tue, 23 Feb 2010 14:31:01 GMT

Saturday I volunteered at a work-day at our church. Our building is one of the first buildings built in this area of Washington, so it’s a beautiful old structure, which of course means there’s lots of maintenance.

I’m fairly decent with tools, I’ve done some woodworking in my day, and I’ve even rebuilt a car or two. But working on a structure this old requires some special skills, skills that I don’t (or didn’t) have. And as I re-glazed a stained-glass window into an ancient window opening, I was not only humbled, I thought about how the entire project was handled and how I could learn about being a better DBA. I decided to share those lessons I learned here.

Not everybody knows what you do
This seems obvious – but I’m so often on the “knowing” end of a project that it’s easy to forget. Several of the other people at the workday knew what went where, how things worked, and what to do to take something out or put it back in. Even with my construction skills, I felt a little overwhelmed. Happily, the people that were there were VERY gracious and helped me do what I could. The nice thing is that they did not talk down to me or treat me like an idiot – they found out what I did know, and then mapped that to what I could do. They helped me learn, and by doing that I became useful to them.
This is also true in our career field. Are you having trouble between developers and data professionals? Buy a pizza, have a lunch and each team should take a turn at learning what the other knows, and what they would like them to know.

If you’re good at something, lead
One of the people at the workday was an older gentleman who was amazing in his knowledge. He’s a contractor, who’s dad trained him in construction starting at 5 years old! And in fact, his dad (and him) actually helped build this church. He’s an amazing guy. But he was running from task to task, doing all of the “hard” things. So I pulled him aside, and told him that we couldn’t do what he could do – but we were all willing hands and heads. I explained that if he would be willing, he could lead us. I asked him to group us into skillsets, and then come help each of us as needed. He agreed, and soon we were all productive.
You should do the same. If your kid’s school needs a “teacher for a day”, step up. Teach the kids what you know about databases. You’ll be surprised! Don’t be afraid to take a leadership role where you are.

Break things down into simple tasks
To do the two things above, you need to plan a little. The gentleman that helped us at first gave us things like “re-glaze that window.” After we stared at him blankly, he broke down the work into the steps of heating the old glaze with a torch, gently removing the old glaze, replacing the section of glass, and re-applying the glaze, all the while showing us the little parts of those tasks that keep the glass from shattering.
One of the easiest things to do when you’re a technical professional is to “glaze over” all of the little steps into a large one. You’ll need to learn to break things down into the simplest components, or at least the simple components that are needed for your workers.

I hope these tips are useful – I’ve been applying them each day.

Know Your Product Specifications

BuckWoody — Wed, 13 Jan 2010 14:57:01 GMT

As the Data Professional in your organization, the rest of the org looks to you to ensure that the system can handle what the business requires. To do that, you need to know two things: what the business requires, and what SQL Server can do.

But of course there’s a bit more to it than that. Knowing the business side of the requirements – well, I teach an entire course on that. But knowing what SQL Server can do is something you can find out on your own.

SQL Server comes in versions, which are released based on date, and editions, which are based on features and capabilities. It’s that last part that I want to focus on today.

As Microsoft SQL Server matures, you’re going to see even more separation between what each edition of SQL Server can do and where it should be used. In the past, most folks have only focused on three editions – Express (the “free” one), Standard, and Enterprise. The rule of thumb was that if Standard was good enough at the moment, put it in. And it is true (and a good thing) that you can upgrade from one edition to another fairly easily.

But as time goes on, we should spend a little more time understanding what each edition does, what it’s features and capabilities are, and where and when we should put them in. As I study this information, I’ll throw in my 2 cents and you can as well based on what you see. One thing I’ve found so far is that once I have the business requirements, there’s a mix of what I can write in code and what might already be included in a different edition. It’s important to look long and hard at that choice – writing a feature on my own is certainly cheaper in the short term than moving to a “higher” edition, but in some cases it makes sense to let Microsoft handle that lifting.

These links are ones that you should bookmark and take a peek at periodically. They are the “header” links for more information on those features and capabilities:

SQL Server 2008: http://msdn.microsoft.com/en-us/library/ms143287.aspx

SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms143287(SQL.105).aspx

In addition, you might start learning a little more about SQL Azure. I’ll talk more about that later.

SQL Server Best Practices: Protect CmdExec

BuckWoody — Thu, 03 Dec 2009 15:49:34 GMT

In SQL Server, there are times that you need to do things in the operating system, and to allow that there is a feature called CmdExec. This is not always a good thing –whenever you leave the confines of SQL Server and go out to the operating system, you can cause issues, not the least of which are security-related.

This best practice is primarily aimed at SQL Server 2000 – in SQL Server 2005 and higher, you’ll have these as job step types in SQL Server Agent (or ActiveX). What you should to do is ensure that only the sysadmins role can run CmdExec job steps.

In SQL Server 2005 and higher, you should use other methods to work with the operating system, such as SQL CLR or PowerShell to handle that with better safety and security.