Voice of the DBA

Tag: T-SQL Tuesday

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

  • T-SQL Tuesday #48–Cloud Atlas

    tsqltuesdayI never saw the movie, though it’s on my list to watch sometime. I love the Wachowski’s as directors.

    This month Jorge Segarra is hosting. He is better known as the SQLChicken, and his theme this month is Cloud Atlas. He’s asking us to write about the cloud and what we think of it. What’s our take on the cloud. He wants to know if we’ve used it, what we think, probably how we even define it. It’s a great question with all the media and hype about the cloud, especially for data professionals.

    This is a monthly blog party, where bloggers write on a specific topic every month, on the second Tuesday. I’ve got a complete list of topics on this blog, and to participate, do this:

    • write a blog
    • publish it on the Tuesday, November 12, 2013 (for this month)
    • be sure a trackback or comment is on Jorge’s blog invitation
    • include the T-SQL Tuesday logo, linked to the invitation.

    That’s it. Write, and enjoy.

    The Cloud Atlas

    What is the cloud? It’s been hyped in commercials and numerous articles, and to some extent I think we don’t have a good definition. We have PaaS, IaaS, and SaaS, all of which are legitimately the “cloud” for different people.

    As far as I’m concerned, the cloud really is any computing service that is provided by a vendor. This means you don’t own the hardware and rent out either a machine (physical or virtual), a service, a set of APIs, etc. This exclude co-location, but anything else from renting a VM to using IFTTT, is cloud computing.

    With that in mind, what do I think? There are certainly problems with the cloud in terms of security and recovery of data. However I think the promise of renting out computing resources (machines/platform/software/services) in a scalable fashion is amazing to me.

    Do I use the cloud? Sure. I have Evernote, Skydrive and Dropbox to keep my data in sync. My family uses OurGroceries to manage a shopping list. We daughter is hooked on Spotify, which alleviates the need to keep much music on her device. I even have backups of data on Glacier.

    On the career side, we (SQLServerCentral and Red Gate) offer AdventureWorks on Azure for users to play with. I’ve done minor work in Azure, and spun up a SQL instance on AWS. I haven’t actually done anything more than test, personally, but certainly the cloud is useful for SQLServerCentral. We host our database server and web server on virtual machines we rent from Rackspace. We use a Content Delivery Network for some videos and YouTube for others. We’ve dramatically lowered costs and increased scale with the cloud.

    The cloud works.

    Sometimes.

    For some applications.

  • T-SQL Tuesday #47 – SWAG

    tsqltuesday.jpgIt’s T-SQL Tuesday time, the second Tuesday of the month when we all coordinate our blog posts on a particular topic. This month Kendal Van Dyke has invited us to take a break from technical topics and have a bit more fun. This month’s topic is asking about the swag that you’ve gotten from an event. This seems appropriate given that there are so many events this time of year. In addition to the tremendous number of SQL Saturdays, we had DevConnections last week, SQL in the City this week, the PASS Summit next week, and SQL Intersection next week.

    This happens every month, and if you want to participate, look for the invitation on twitter with the #tsql2sday hashtag. If you want to host, contact Adam Machanic.

    The Best SWAG

    I’ve been fortunate to attend many events in my career. I’ve been to TechEd, the PDC, SQL Connections, SQL Intersection, the PASS Summit, various TechFests, lots of SQL Saturdays, and, of course, Red Gate’s SQL in the City events. I don’t often get swag these days, but earlier in my career I would grab various gifts and goodies from the events and vendors. I’d often bring back items for co-workers and my kids, some of which I actually still have.

    These days I don’t get much swag, though I do get speaker gifts from some places, which feel like swag to me. What are the best items? I’m not sure that I can pick just one, but I have a few things that stand out.

    Multi-tools

    20131004_102054

    During the WIT panel at SQL Saturday #222, SIOS sponsored a gift to take back to kids, boys or girls. It was a multi-tool and I grabbed two, one for each of my kids. I even checked my bag (which I never do) so I could bring the small knives to my son and daughter. They loved them, putting them in bags to use when they might need them. I saw my son actually break this out a week later to use the pliers.

    Wine

    20130917_12371420130917_123704

    The speaker gift from SQL Saturday #222 in Sacramento this year was a bottle of wine. I thought that was a great gift, and one that’s still sitting in our wine rack, but probably not for too much longer 🙂

    Beer Mugs

    20131004_102125

    A few years ago the crew from SQL Sentry gave away silicone beer mugs at the SQL Connections conference. I loved the insulated mug for cold beverages, and so did my kids. I found after a week that I never got to drink from them because my kids were filling them with smoothies, juices, and more. The mugs were always in the dishwasher. I mentioned this to Peter Shire and he graciously gave me 4 more at the next event. That hasn’t stopped my kids from using them at every opportunity, but with 5, I do get to use a few of them now and then.

    The Keychain

    I’ve gotten a few, and some of them are very nice, but the one that stands out for me was from the SQL Saturday #169 in Denver. The local user group made their own parachute cord keychains that I still have on a backpack. It’s similar to a survival bracelet, the kind of things that Boy Scouts carry, just in case. I love the keychain and while I hope I never have to use it, I have it if I need it.

    Lots of Gifts

    There are plenty of other things I’ve gotten that have survived. I have bags and shirts from many events. I have pens, USB drives, a couple keychains, and even a few bottle openers. I’m sure I’ve forgotten some of the other items, though to be fair, there were lots of forgettable items.

    It’s hard to give out good swag. At SQLServerCentral, we’ve struggled to think of what things people would like. I’ve given away lots of shirts and books since I’ve felt people will keep those around and hopefully have fond memories of the events. I know I do when I use some of the items that I’ve gotten.

  • T-SQL Tuesday #46–The Rube Goldberg Machine

    tsqltuesdayIt’s T-SQL Tuesday time again. This is the monthly blog party where we all write on a topic. This month the topic is the Rube Goldberg Machine, as it might relate to SQL Server. You can read the invitation linked above from Rick Krueger and write your own post if you choose.

    Just be sure you post it on Tuesday, Sept 6, 2013.

    Catching the Cheaters

    A long, long time ago, in a county just to the west of here, I worked for a small company as a DBA. I had to support our production operations, as well as a team of 10-12 developers that were building our main company application. Since we were an ecommerce type company, this was an important application for the company.

    We were working in an high incremental way, probably similar to an Agile methodology, and deploying changes every week. However when I started the deployments weren’t smooth. Our developers were handling them, often bumbling them a bit, but able to fix things in an hour since they had done the development.

    Not a good separation of duties and it was becoming more of an issue as our boss wanted minutes of downtime, preferably single digits. I tracked down the main cause as a haphazard development style where changes were made to databases, instances, and IIS without much tracking going on. The IIS changes were easy: the developers had their rights removed from production and even test machines for configuration changes. However the database required a bit more work.

    As this was SQL Server 2000, we had limited ability to audit and manage rights. We also required our developers to have rights to the development machines in order to create test databases and work on importing data. We also wanted to ensure that developers moved quickly, but I needed a way to keep control of the instance.

    The first contraption I used was a simple one. I loaded the output of sp_configure into a table on the instance. I had created an administrative database that I used to track backups, and I added this information in there. I then created a job that would run sp_configure every day, compare the values to my table, and then alert me to changes. It would also update the table with the new, current values.

    This allowed me to catch various changes that developers were making when they tinkered with the server to “make it run faster”. I didn’t prevent changes, and if their changes worked, we’d deploy them to test (and eventually production), but this allowed us to document them and be aware.

    This worked well enough that I build a few more mousetraps. Catching schema changes is hard, as there isn’t good auditing in SQL Server 2000. However there is a “version” number for each object that is incremented when it’s changed. I built a similar audit/job system for our sysobjects table, but ran this every hour, catching changes. When I was alerted, which was more days than not, I’d email the team, track down the person changing things, and make a note for our current branch of work.

    This worked great in that development wasn’t slowed, but I was able to account for all development changes and slot them into the current, or future, development deployments. In a few months we reduced our deployment time from about an hour every Wed night to less than 5 minutes.

    Sometimes the mousetraps actually help the mice work better.