Voice of the DBA

Author: way0utwest

  • T-SQL Tuesday #50–Automation

    tsqltuesdayI missed last month and I was a bit sad, but I was traveling the week before and speaking, and then was on vacation. No time to write a post, and it was a tough topic from Robert Davis. However I’m back this month, and I’m thrilled to participate in topic #50.

    This month’s topic comes from @sqlchow who gives us the topic of automation. How would we do it? What would we do? What tools? It’s a great topic.

    This is part of the monthly blog party, started by Adam Machanic. To participate, grab the logo above, write a post on the 2nd Tuesday of the month, and link to the invitation (from @sqlchow this week). If you want to host, contact Adam (blog|twitter).

    Automation

    I’m going to tackle the topic of tools, since I’ve been working on the Powershell Challenge.

    I’ve used lots of tools for automation in my career. I started with DOS and batch files professionally, which were easy for me after a few years of Korn shell and C shell working on Sun OS in college. The idea of scripting in a batch file made perfect sense, so much so that I had alias batch files for various Unix commands, like ls, ps, etc. that I used often. We even had 411.bat on our network, that would grep a text file of our internal phonebook to return extensions.

    As I moved to SQL Server, I found myself frustrated with some of the limitations that SQL had, especially while working with files. Batch files weren’t great, and a full C++ or VB program was too heavyweight. I eventually moved to VBScript, and learned to love the FileSystemObject. I had tried Perl briefly, but abandoned it because getting ActivePerl installed on all machines was too much of a hassle.

    However I’ve started to move to PowerShell (PoSh) and I am starting to really like the idea of quick modules and cmdlets written in the language. It has a lot of the piping and simplicity of Perl, but the power of working with objects. It’s also quick to write scripts. While I find it cumbersome for now, that’s me, not the language.

    These days, if I needed to automate something in SQL Server, I think I’d really look hard at PoSh if I needed to work outside of an instance. Meaning access the host system, work with files, or connect to multiple instances.

    Inside once instance, I still love T-SQL scripting where I can use it.

    One further note, scheduling is an important part of automation. While there are some good enterprise level schedulers, they aren’t always available. That’s why I tend to use the SQL Agent to do lots of scheduling for me. It’s more reliable than the Windows scheduler built into all Windows hosts, and provides some notification capabilities.

    More importantly, I can query the status of my automated tasks.

  • No Bosses

    Zappos has been a successful company in a number of ways. In addition to tremendous profits, they’ve shown that they can provide amazing customer service by empowering individual employees. Now they’re taking another step forward as they move the company to holacracy and eliminate the traditional managers from managing people.

    The idea in this new structure is that leaders are responsible for circles of people, and employees may be part of numerous circles. It sounds to me as though this is close to the early stages of startup companies where each person has to wear many hats and be responsible for a variety of tasks. I’ve had to manage databases, back up system administrators and Exchange administrators, and even work with telecommunications providers and ISPs at some small companies. All while taking direction from people I didn’t report to.

    It sounds like it’s a structure that many IT people would enjoy. Be responsible for getting work done on different projects or areas, but managing your own efforts, schedule, and priorities. Many of the technology professionals I’ve known like to be assigned work, given a deadline, and then left alone, working at their own pace and schedule to get things done.

    There have been some smaller companies that have embraced this idea, but Zappos, at 1,500 people, is the largest. It will be interesting to see if they can make it work. Time will tell, and I am hoping that Tony Hsieh will update us, perhaps with another talk or lecture that describes the culture inside the company. I rooting for this to work because I do subscribe to the ideas that we are driven by autonomy, mastery, and purpose. I see structures that free people up while requiring responsibility as the best way to move a company forward.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( MB) podcast or subscribe to the feed at iTunes and Mevio . feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • The Powershell Challenge Day 9 – The Pipelines, deeper

    This is part of my Powershell Challenge, to learn more about PowerShell (PoSh) using the Learn Windows Powershell 3 in a Month of Lunches book by Don Jones.

    The authors note this is an optional chapter, one that isn’t necessarily one you need to read. After going through it, I’m not sure I agree, especially as I’m not completely sure I grasp all the contents, though I do think they will be handy.

    When you pipe output from one command to another in Unix, it’s really a text stream. Just as it would be written to stdout, it’s sent to the next command. That means your commands need to handle text as inputs.

    In PoSh, if I understand this, that’s not the case. The inputs into commands are through parameters for the most part. That means that the output from one command, which is a collection of objects, needs to be accepted in the next. For many commands, that doesn’t quite work.

    There are two methods of sending in parameters. One is input ByValue, which is a parameter binding method. In this method, PoSh looks at the type of parameter being sent in, in essence the object type (or data type) and matches to a parameter if the parameter accepts pipeline input. Not all do, and you need to read the HELP for a command to see which parameters accept pipeline input. That seem mis-named to me. It’s ByType, not ByValue in my mind, but I’m certainly not a PoSh expert.

    The other way is ByPropertyName, which is as it implies, by name. The object coming in has its name matched with the parameters of the command on the other side of the pipeline.  That makes more sense, though there are times that the names don’t match.

    That’s where you can then create custom properties with the Select-Object syntax that creates name/value pairs from your input. It looks like handy, not too complex, but cumbersome syntax. I’ll have to play with this more as I had to work through some exercises and carefully watch my parens and braces (opening and closing) as I typed in some commands.

    One thing that frustrated me slightly with these exercises is that a bunch of them looked at multiple machines, and I don’t have a domain at the present time with matching credentials. I couldn’t figure out (easily) how to connect to another machine with custom credentials for Get-Process and Get-Service, but I also was running short of time this lunch, so I skipped.

    The lab was analysis without using PoSh, since many people reading the book might not be admins on a domain. I got most of the answers right, but not always the explanation. I certainly think I need to play with this more, and that means I’ll stand up a domain here soon that I can use. I need to do that anyway.

    The custom properties, along with the introduction of parens for order of operations, make this a must read chapter for me. I think the authors shouldn’t caveat this one at all.

  • The Desktop Setup

    Most of us find ourselves to be creatures of habit. We want to have our chair set at a certain height or we can’t easily type. We prefer certain colors on our monitors, and we want our software to be configured in a certain way. For those of us that work with software, we typically desire a particular set of applications installed on all our systems, or we struggle to be productive.

    Whether you have a laptop or desktop, or even tablet, there are probably certain things you expect to be set up in your environment. These could be hardware or software items that you find necessary to do your job. This week I wanted to ask you what those items are.

    What do you consider to be valuable for your desktop environment?

    It could be hardware or software. Are there tools that you find useful for development? For administration? For ergonomic comfort or efficiency? Perhaps even more importantly, do you have a procedure to easily rebuild your next machine, or this one if it has issues?

    I don’t have a procedure, but I do tend to work in VMs for SQL and development work. This gives me an easy backup, and if something breaks, I restore a snapshot in the VM. More importantly, I still have a machine that is working if some piece of software causes issues in my environment. Aside from SSMS and VS, I do find that I want the Red Gate toolbelt, an image capture tool (I can’t decide which one), Edit Plus, Live Writer, Github, and Evernote in my working environment.

    In terms of hardware, I’m fairly flexible, though I do like my standing desk for most work. I find I prefer it to sitting at a desk or table. Of course, the one constant no matter where I work is I need a good supply of water and coffee.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and Mevio . feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.