Voice of the DBA

Category: Blog

  • 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.

  • 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.

  • Powershell in a Month Day 8 – Objects: data by another name

    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.

    What are objects? I’ve always found that to be a hard question to answer if I’m talking to someone that doesn’t understand how to program. The explanation here is simple, relating each row of results to an object, and the entire result as a collection. It’ s a little simplistic, but it works for Powershell. The whole idea, really, is to understand that these objects have types (each field or column) and those types matter because they affect what we can do with the output of each part of a PoSh process.

    Get-Member is the big introduction here, to help you find out what the properties and methods are for these objects. I have the feeling this is handy because when things don’t work right and you are debugging, Get-Member (or gm) is probably very handy. The other commands that are introduced here and are handy are the SortObject (or Sort) and Select-Object (or Select). These are easy concepts for me, because the way they work, with multiple columns and ascending or descending (-desc) are very similar to SQL Server.

    Overall, it’s a short chapter, but I feel like it’s starting to be more useful. I can pick and choose, easily, which columns I want, sort them, and find the various ways I can interact with an object. The lab was easy, feeling more like writing simple T-SQL queries than anything else.

  • Lemonade and Life

    I’ve got a torn ACL. I found out yesterday at a visit with a surgeon that gave me the results of his exam and an MRI I had last week.

    Don’t feel too bad for me. I’ve had this for (I’m guessing) eleven years. In that time I’ve

    • run every day for over 4 years,
    • studied karate for 5 years and earned a black belt
    • snowboarded over 150 days
    • played 6 seasons of baseball at all positions outside of pitcher/catcher
    • played volleyball
    • practiced yoga and Pilates
    • picked up and carried my kids plenty of times
    • played weekly volleyball for over a year.

    I’ve done plenty more and felt fairly normal. The knee has been slightly unstable at times, but I’ve just limited, not eliminated, activity.

    I need to get this fixed. It’s becoming a problem, and it aches fairly constantly. It also limits my ability to engage in some activities that I think are important for my long term health. Not having an ACL also potentially means that I am hastening future issues as I age. It’s a fairly routine and simple surgery, and I should be walking on my own inside a week. The downside is that I can’t fly for 4 weeks afterwards because of potential blood clot issues.

    This means I need a 4 week hole in my schedule, which can be hard to find. I may cancel or miss some events, and I’ll apologize in advance now. I’ll give as much notice as possible, but something will have to give, and it will likely be in the Apr/May/June time frame.

    Let’s be clear. I’m not complaining here. My life is amazing and I couldn’t ask for things to be going better. This is, at worst, a minor hassle in my life. Surgery is dangerous, and there could be complications, and I’ll lose a few weeks of activity, but that’s a minor inconvenience in the grand scheme of things. I’ll get this fixed and move on.

    Life does throw issues at you. Many of you have had much worse situations than this minor injury. I’ve had worse things for sure, and I try to remember that the trials and troubles I face are a part of life. I can let them get me down, and sometimes they do, but I can also look past them and remember all the good in life I’ve had, and look forward to the good things I’ll find later.