T-SQL Tuesday #15: Automation in SQL Server

By Airborne Geek on February 8, 2011

Automation: every lazy DBA’s best friend; in some situations, a ticket to sanity.

T-SQL Tuesday #15

T-SQL Tuesday #15: Automation is the way to a DBA's heart

This month’s T-SQL Tuesday is brought to you by Pat Wright (blog | @SqlAsylum). The 15th topic for the monthly blog party is, as has been mentioned, Automation in SQL Server. I’m pretty excited to read this month’s posts to see what kind of crazy things everyone does. Most of these posts are probably going to be big on example scripts and code samples, as one would expect for such a topic. This one–for better or worse–won’t.

I have to admit that I haven’t done a lot of from-scratch automation in my day. Lots of things on the list at work right now, but implementation is still pending. As a result, I was afraid that I wouldn’t have anything to talk about this month, but I thought of a goofy direction that I can take this in.

Like many of us who do it for real now, I started my path to DBA-ness (uh… that’s unfortunate) as an Accidental DBA. Since I didn’t know any better at the time, I did a lot (OK, pretty much all) of administrative DBA tasks with the UI. Need to back up a database? Right-click | Tasks | Backup! Want to create an index? Fire up DTA! Use the GUI to pick the columns. Need to do that for more than one DB? Guess you’re going to be there for a while.

This technique obviously gets the job done (for the most part), but there is a lot of room for improvement.

“Automation” doesn’t have to be fancy

If you’re using the GUI for a lot of tasks, there’s an easy & cheap way to “automate” a lot of what you’re doing. Simply: Script stuff out. One doesn’t need to be a master of T-SQL syntax to start doing this, either. With SQL 2005 and above, making the transition from GUI to scripted tasks is pretty easy.

SSMS Backup Dialog's Script menu

The Backup Database dialog's "Script" menu

Just about every dialog box in SSMS has a “Script” button at the top. This control will script out whatever changes have been made in the dialog box. For example, if you bring up the Backup Database dialog, fill out the options & destination file location as desired, and then use the Script button to output that to a new Query window, you will wind up with a complete, functional BACKUP DATABASE command with all of the same settings that were selected in the GUI window. Mash F5 on that puppy and you’ll have your backup, just like you wanted it.

How does this classify as automation?

Spirit of the law, folks, spirit of the law 😉

Alright, I admit I might be stretching it a little bit here. I also know that just because two techniques solve the same set of problems doesn’t mean they can be classified the same way.

That said, consider some of the reasons that you automate big tasks:

  • Ease of consistent repeatability
  • Removal of the human element
  • Speed
  • Autonomy if you’re out of the office and someone is filling in

These same things make running T-SQL scripts instead of using the GUI for tasks a better idea:

  • As long as you don’t change the script before you re-run it, the same thing will happen repeatedly (unless of course the script does something like add a particular column to a table a second time). This is especially important when doing things such as migrating a new table through Dev, Test, Stage, and Production over the lifecycle of a project.
  • Setting options in a GUI window is prone to mis-clicks or flat-out forgetting to change a setting from the default.
  • The script is ready to go—running the action is as fast as opening the script file, checking it to make sure it is the one you’re expecting it to be, and mashing F5. This makes implementing the change a fast process, instead of having to click a bunch of radio buttons/checkboxes/whathaveyou, then verifying all of the settings before hitting OK.
  • If you’re out of the office, but something still needs to be deployed, it’s easy for the fill-in DBA (the boss?) to grab the scripts that have been prepared and run them. This is easier than walking through a list of checkboxes to check on a UI screen OR you try to remember everything from memory if the correct settings haven’t been written down.

Considering how I grew into a DBA, making this leap from pointing and clicking for just about everything to typing out ALTER TABLE instead, took some work. In the end, scripting everything is better in pretty much every conceivable way, even if it is hard at first.

If you’re a little GUI-heavy still and like the idea of automating the work that you do, letting go of the UI and embracing the big, blank T-SQL canvas is Step 1. The effort will be worth it, and you’ll feel like you’ve really automated tasks.

Posted in , , , Tagged , , 3 Comments

Repost: Voyager 2

By Airborne Geek on January 24, 2011

This post was originally written for my first blog, posted on 12/12/2007. It’s a goofy little post I wrote because the Voyager spacecraft are generally awesome and I’m a huge nerd. OK, that and there was a pretty major milestone passed. I’m reposting now because 25 years ago today, Voyager 2 made its 4.19 radii pass of Uranus (pffft). I know it’s pretty far from SQL Server, but, huge nerd and the Voyagers are awesome.

Images Courtesy NASA/JPL-Caltech

I sort of missed the boat on this when it happened, but back on August 20, Voyager 2 marked its 30th year in space. For 30 years, that little chunk (and its cohort, Voyager 1) has been hurtling through space, measuring plasma levels, temperatures, and taking pictures of crap, among other things.

The Voyager Spacecraft

Voyager

Thirty. Years.

To me, the Voyagers are some of the most memorable and recognizable pieces of equipment that NASA has ever thrown up in the air. I remember 2’s flyby of Neptune in 1989 (map of the planetary flybys is below), and it was one of the targets of my early obsessions with all things aerospace. Sure, the space shuttle was a little more visible, but there was always something special about Voyager.

And here it is, a spacecraft built to run for five years, still going strong after six times that. I guess they don’t make them like they used to, but then again, the Mars Rovers are still going after, what, almost four years now, and they were supposed to go for 30 days? I joke a lot about how we sent men to the moon with chalkboards and slide rules, but man, those Engineers can do anything with just about anything. I mean, NASA did put a square peg in a round hole, once…

Voyager Journey

Voyagers' path within the Solar System

Anyway, Voyager 2 has now reached the edge of the Solar Wind. That’s way out there. Even though I haven’t a clue what most of what you learn from that is about or how it is useful (that’s more of my sister’s area), it’s still pretty cool. I’ve never really thought about the dynamics of the SW on the interstellar gas; I suppose that could be due to how I just thought of the gas as just there, not necessarily an entity that would interact with things within it. This is why I’m in IT and not working at the JPL or something 😉

I can’t help but think about how I zip around far greater patches of space in my little Internet Spaceships in seconds and it’s taken these guys 30 years to get to where they’re at now. I know it’s just a stupid game, but I still believe that someday our technology will have us there, too. I don’t expect to be around to see it of course, but there will be some dreamer like me there to take advantage of it.

Posted in , Tagged , 1 Comment

Un-SQL Friday #2: Tech Giants

By Airborne Geek on January 23, 2011

A non-Friday, depressed-that-the-Bears-can’t-get-a-decent-franchise-quarterback-to-save-their-lives edition.

UnSQL

Un-SQL: When SQL People get less SQL-y, while still talking about SQL.

Un-SQL Friday: The T-SQL Tuesday foil blog party created by Jen McCown (blog | @MidnightDBA) where we all talk about something related to SQL Server instead of something about SQL Server. It’s a less-technical topic, which is better for me (because I’m terrible at this) and gives us all something a bit more relaxing to focus our brains on (in case we need that sort of thing).

This round’s topic is “Tech Giants.” Jen says: Who are the IT giants you talk to/read that make you feel like a technical poser? No fair saying “everyone”. Oh. I can’t say “everyone”? Really? Uhh, OK, that’s going to make this a bit more difficult than I had initially thought…

That “not everyone” bit does make this pretty difficult, because I don’t know who to pick individually. I really do feel like all of the SQL people that I talk to on Twitter are giants in the field compared to me. I appreciate: everything that the SQLskills crew does for the rest of us; Thomas LaRock (blog | @SQLRockstar) for putting up with us if we act a little like wide-eyed fanbois the first time we meet (ahem); Jorge Segarra (blog | @SQLChicken) for nagging me about applying to work for Pragmatic Works, because, I’m pretty sure that’s what I should be doing; and Steve Jones (blog | @way0utwest) for generally being awesome, even if he does hate planes 😀 . Oh, and Jen & Sean (site | @MidnightDBA) for always being willing to be at least somewhat “inappropriate” in what seems some days like a sea of over-the-top political correctness Professionalism (this isn’t to say that they’re R-rated all the time—they know when to be serious and when to come back with “That’s what she said”). Those are just some people; there are so many more that I’m leaving out. We’d be here for a while if I went on.

As nice as all of this is, both from a technical knowledge and networking perspectives, it is a two-edged sword. One day at lunch with our Senior DBA, I asked him where my worst shortcomings are & what I need to work on to continue to move ahead in my career current job. Me being me, this question partially took the form of “where/why do I suck?” His response was basically, “you don’t, but you spend too much time on Twitter.” The reference being that I spend too much time comparing myself to all of these great people who have awesome jobs (and could get whatever other job they wanted at the drop of a hat) and can answer really gnarly questions about SQL’s Storage Engine off the top of their heads (potentially because they wrote it!), and some days I let it get me down. He’s right… some days I do do that. Alright, more days than is really healthy do I do that. This is a little bit of a problem, and I’m trying to get over that.

Tammy and I were talking about this a few days ago, and she reminded me that one isn’t going to be at a comparable level to Brent or Tom overnight—it takes work to get there. These guys should be aspirations, not thought of as peers. Plus, like Jen mentioned in her invitation post, all of these guys have giants of their own, too, and that is, indeed, comforting. As long as we all have someone that we think we suck compared to, then I can tell myself that means I suck a little less than I think I do 😉

Posted in , , Tagged 4 Comments

T-SQL Tuesday #14: Resolutions

By Airborne Geek on January 11, 2011

T-SQL Tuesday 14

T-SQL Tuesday 14, hosted by Jen McCown

Resolutions… For a long time, I thought 1600 x 1200 to be the ideal, but with the proliferation of widescreen—oh…wait…not that kind of Resolution? Sorry. My bad.

It’s that time again: the monthly blog party known as T-SQL Tuesday. This month is #14, hosted by the deservedly newly-minted Microsoft MVP Jen McCown (blog | @MidnightDBA). The topic this time around is “Resolutions,” originally, “techie resolutions have you been pondering, and why.”

In my 2011 “Goals” post, I talked about one of the things I need to do this year: something about my career direction. I’ll take this opportunity to elaborate a little bit on what that means for me this year. The part about needing to pick one of two directions still applies, but the work that I need to do for each of those is pretty different.

If I stay a DBA…

If I stay a DBA, what I need to work on the most can be summed up fairly simply: Catch up.

Since I work with SQL 2000 almost exclusively these days, there are a lot of current bits of technology that I need to spend time working on and learning:

  • DMVs
  • Policy-Based Management
  • T-SQL advancements (Actually using Try/Catch, TVPs, etc)
  • PowerShell

That’s just a few things that I can pull off the top of my head; I know there are more that I should know and be able to use at this point. Not having the opportunity to use these features day-to-day doesn’t exactly help me learn and keep these skills sharp 😉

If I want to be one of the cool kid DBAs on Twitter, 2011 is a year in which I have to do a lot of work on these topics.

Less BI-Curious and more BI-…uhh…Pro?

The other choice I have is to do awesome Business Intelligence work. I’m still turned on by a lot of these technologies and the power that they can put into the hands of business users at all levels of an organization. A change in direction at my current job to a more BI-focused role has yet to fully materialize, and as for right now I’m still somewhat impatiently watching that carrot out there.

Whether I do it in my current position or choose to take something new, changing direction to full-time BI work comes with its own set of topics that will need lots of attention from me:

  • Improve dimensional modeling skills (I do some of this already)
  • Actually learn how to do work with SSAS
  • PowerPivot
  • Get better at talking to non-geeks!

There are lots more topics than those three here, too. However, BI in and of itself is a pretty wide field, so the specific topics that would need attention would be dictated by the flavor of BI work that I was doing and my involvement on such a team.

Actually making this stuff happen

Like all resolutions, this is, of course, the hard part. Making this happen isn’t going to be easy for me, either, but the rewards should be very well worth the effort required. Unfortunately, since knowing is half the battle, and I don’t have that taken care of yet, the near-term has the potential to be pretty rough.

At the absolute bare minimum, I resolve to do my best to take care of “knowing” in the first quarter of the year.

(Also, I’m sorry about that screen resolution thing at the beginning; couldn’t help myself)

Posted in , Tagged , , 5 Comments

File System Rights Needed to Attach a Downgrade DB

By Airborne Geek on January 4, 2011

This falls into the category of things I probably should have already known, but for some number of reasons (not all my fault!), I didn’t.

Last Thursday night was an honorary Friday night at The Hideaway, since we took last Friday off (I got screwed at work on a day of for New Year’s this year), so I was trying to catch up on some blog posts in my reader. One of them included some code I wanted to run, but I found out that I didn’t have AdventureWorks attached to SQL after the last rebuild of the main server at home. The first step was going to be fixing that, so I moved the files from their old location to my new standard folder and set off to Attach…

Before this story gets too long…

The DB wouldn’t attach to the SQL 2008 instance. I was using my old SQL 2005 version of AdventureWorks, because it was already on the machine and at least for now, I want to keep it around. It was complaining about the files or the DB being set to Read-Only, and it can’t do an upgrade on a Read-Only DB. I needed to fix the DB or the file system permissions.

The DB wasn’t read-only when I killed the server before the rebuild, so that wasn’t it. Had to be NTFS permissions.

Checked those and the group that the SQL Service Account had Modify permissions on the folder where the files were. Checked this a few times to make sure I wasn’t crazy. I tried to search out some help on this, but I was having a hard time getting the search terms right to find anything useful. I finally wound up breaking out ProcessMonitor to help figure out what was going on.

Turns out, SQL Server was using (impersonating?) the Windows account I had connected to the instance with when it was reading the files. That account doesn’t have write access to the folder(s) in question, so any writes it was trying to do were failing. That’ll do it.

I don’t understand why it does this. Nothing is mentioned in BOL about this, and some real quick Googling didn’t bring up anything, either. Since I pretty much work only with SQL 2000 and SQL auth all day right now, I don’t know if this is new in 2008. At my last job where I had 2005, whenever I would do an operation like this, my Windows account would have been Local Admin on the server, so I wouldn’t have run into it.

I did some quick testing and although it of course still impersonates the Windows Auth user when you attach a DB that doesn’t need upgrading, as long as the user you’re logged on with has read rights, you’ll still be in business.

Workaround(s)

Obviously giving write rights to the Windows account you’re logging on to SQL with will fix this, but that doesn’t strike me as a good idea if you don’t have those rights in the first place. I mean, you don’t have those rights for a reason, right (principle of least privilege, etc)? But, if it’s a Windows Auth-only situation, that’s the only way to do it with the account in question.

Another way around it, is to use SQL Auth. This is what I wound up doing, mainly because I wanted to test to see if that works. As mentioned, if the Instance isn’t in Mixed Mode Auth, this isn’t an option. Also if, for whatever reason, creating a new account for this purpose isn’t allowed, then this option also doesn’t help you.

I’m sure this is old news to most everyone else, but it caught me by surprise. Lesson learned!

Posted in , , , Tagged , Leave a Comment