Voice of the DBA

Tag: Redgate

  • Turning 40

    I turned 40 a few years back, and it wasn’t that big a deal for me. In some ways my wife thinks I’m a little old for my age, and when I hit 40, it wasn’t the milestone it is for some people. I think the night I turned 40 I was at a Cub Scout meeting with my son, delaying a sedate, quiet family dinner for a day or two because of that commitment.

    Last week I was in Cambridge, at the Red Gate offices.

    English phone boxes
    Not in Kansas (or Colorado) anymore.

    Every year I go over once for twice for meetings with my team at Red Gate where we brainstorm, do some evaluation on where we are, and look forward to the future. Most of my time is away from the office, but I do spend a little time at our building (Red Gate has a building!!) and say hi to a few friends.

    On Thursday morning, I was there for a surprise, and a rare event. The entire company, some 200 odd people gathered in the first floor (floor zero for you non-American, counting-building-floor-challenged people), with everyone in a Red Gate shirt.

    Red Gaters wearing red shirts
    A sea of red

    The two founders of Red Gate, our co-CEOs, Neil Davidson and Simon Galbraith, both were turning 40 within a few weeks of each other. A few people in the company had organized a celebration with sweets

    This was less than half of the treats available.

    cards

    Large 40th Birthday cards signed by everyone.

    and a large rendition of Happy Birthday from the employees.

    It was quite a celebration, with a funny “roast” speech given by one employee, a video Happy Birthday tribute, and even a parody of “Never Gonna Give You Up” from the Red Gate Chorus group.

    A nice break in the day, and quite a celebration. Simon and Neil have built something rather amazing at Red Gate. It’s the kind of company I’d want to build if I every started another venture. Successful, hard working, respectful, fun, and very supporting of employees.

  • Use Your Tools

    I used this tool for the first time and found it very handy.

    Someone asked me recently if I’ve ever exported a table using SQL Packager, a tool from my employer, Red Gate Software. I hadn’t, and in fact, hadn’t ever even opened the tool. So I started it up and exported a table. I was surprised how easy it was, and I wrote up a short blog on it.

    That reminded me of a common issue that many of us have: we get into a rut. It’s easy to stick to doing things the “old way” we’ve learned, and not updating our skills to take advantage of newer features.  It’s also easy to get used to going through a process one way and never trying, or experimenting with different techniques or applications of the tools.

    I have been guilty of this, and am trying to rectify it, working with new features, and trying out new SQL Server 2008 R2 or SQL Server 2012 T-SQL changes where I can. I noticed Grant Fritchey recently talking about Extended Events and the advantages of using them with deadlocks, and also using them for performance tuning. Learning Extended Events is an area I need to spend some time with in the future, and I’d recommend most other SQL Server professionals do the same.

    However it’s not just the changes in SQL Server. There are changes in tools from vendors that can help us do our jobs better, or even tools we don’t know we have. I have spoken with lots of Red Gate customers that weren’t even aware of the capabilities of the tools on their machines. In some cases they weren’t even aware they had the tools because they were purchased in a bundle. Not every tool is useful, and not always appropriate, but it’s worth a little time investment to learn how to use the tools and gain some awareness of their capabilities.

    We publish some information on the Red Gate tools at SQLServerCentral, and I constantly find great blogs written by people that actually use the tools in their daily work. I suspect that’s the case for other tools as well. Take some time and read about the tools you own, and even the ones you don’t. You might find one that will actually save you time, make your job easier, and provide a nice ROI for your company.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • A Quick Export with SQL Packager

    Disclosure: I work for Red Gate Software

    Someone asked me the other day if I’d ever used SQL Packager to export a table to send to another person. I hadn’t, and in fact hadn’t even ever run the tool, but this individual said it worked great.

    Since Red Gate tools are designed to be simple and intuitive, I thought I should give it a try and see what happens. I went through the Start Menu and found SQL Packager in my toolbelt installation:

    packager0

    I documented this as I went, shooting this images as I went through the process for the first time. As soon as Packager started, it began the packing wizard.

    packager1

    SQL Packager is designed to help you bundle up a database, or part of a database, as a part of an installation in your application. It can produce an .exe, a C# project, or a set of scripts that you can include as a distributable item in your application installation (or upgrade). The information can be compressed, so you reduce the requirements for your customers.

    In my case, I decided to just package up a table. I first signed into my local instance, and chose the AdventureWorks database.

    packager2

    Next, I chose just one table, the Customers table. The Red Gate tools tend to follow a similar, intuitive design, and try to do the most common things for most customers. In this case, the entire database was selected (this is a database packaging tool), so I deselected all, and then chose the Customer table.

    packager3

    Once I choose the table and click next, and confirm the selection, the packaging begins. I get some options as to how I might choose to build my package.

    packager6

    The options are shown, and in this case I choose to save the script. Once I clicked next, I had a change to see the final script. First there was the schema tab:

    packager4

    On this tab, all the DDL for my table is there, including a couple dependent tables, and some functions needed for defaults or computer columns. Keys and indexes were included.

    On the data tab, I had the DML for the actual data.

    packager5

    The comment says “Add 1000 rows”, which seems like a default. However I went back and checked in SSMS, and sure enough, my table had 1000 rows.

    I clicked next, and had the chance to specify a save location.

    packager7

    After saving, I opened the script in SSMS, just to check. Sure enough, the DDL was at the top:

    packager8

    and the data at the bottom:

    packager9

    Simple, and easy.

    If you are looking for a way to move certain sections of your database for a deployment, like all the lookup tables, give SQL Packager a try.

    If you need to send some stuff to a client or friend, it might be a simple way as well to export the DDL and DML into one package.

  • T-SQL Tuesday #25 – T-SQL Tricks

    TSQL2sDay150x150It’s time for T-SQL Tuesday again, and this time Allen White (@SQLRunr | blog) is asking for your tricks. If you want to participate, read Allen’s post and learn how.

    The question this month is: What T-SQL tricks do you use today to make your job easier?

    My Tricks

    I don’t have any great whiz bang tricks in T-SQL, and I’m sure there are more than a few people that can out-code me with their. However I do like to make my job easier, and so I have a couple of administrative tricks for use with your T-SQL environments. These are the ways that I save time, and work more efficiently.

    The thing that has helped me most often in my career is to keep little snippets of code handy to that I make few mistakes and save time. These days I do that quite often with SQL Prompt, a third party tool from my employer. It basically implements intellisense for SSMS, but more importantly, it gives me shortcuts.

    However the biggest advantage to me is the Snippets in Prompt. There’s a feature that allows you to type a shortcut and then press “Tab” and have that shortcut replaced with a longer section of text. For example. I have this shortcut:

    prompt1

    If I type “zqd” in SSMS, and then hit tab, the T-SQL in the “code” box above appears. There are a few very frequently used slices of code that I can insert like this, without taking my hands off the keyboard, which is very handy. There’s even a whole snippet manager in Prompt that has pre-defined, and custom, snippets.

    prompt2

    The most often one I use is “ssf”, which inserts this:

    SELECT TOP 10 * FROM

    So do you need to buy SQL Prompt? No, but if you do, tell them I recommended it so my boss with be happy and maybe send me a nice bonus next Christmas.

    A very similar functionality is in SSMS. I actually used to heavily use templates in the old Query Analyzer days of SQL Server 7/2000 and this has continued in Management Studio with the Template Explorer

    template1

    I can drag a template from the explorer on the right into the code window and the code appears. I’ve pulled in the backup template. You can even add your own:

    template2

    It’s easy to do, and you can read more about Template Explorer in BOL.

    However if you’re like me, you move around, you use VMs for coding, and you want to be sure that your tools are on all these machines. There are a few ways to do this:

    • portable drives
    • cloud sevices

    I guess these are both the same thing, just implemented differently. I’ve used both ways, and while I do carry some flash drives, and hard drives, with various items on there, I find that I can never quite keep these up to date, and they’re really emergency drives for me in the event I don’t have connectivity.

    The primary way that I manage mode snippets, templates, etc. is by putting all my code in centralized places. For Prompt and SSMS, these locations are known, and while configurable, I stick with the defaults. For me this means I have three folders to track:

    • SQL Prompt default snippet folder
    • SSMS Templates folder
    • \SQL in my Documents folder in Windows

    All three of these folders are the same on all my machines, and I use a cloud service to keep them in sync. For me, I have two difference services in play, mostly for testing, and I see little difference between them. I have Live Mesh, a Microsoft service, for some folders, and DropBox for others. From what I’ve seen, they both work essentially the same, though DropBox is a little smoother for me with the Apple integration of some apps. That probably doesn’t matter for most of you, but it’s a difference. Live Mesh works on my Macbook, but not on the iPhone.

    There are other cloud services, and you can choose the one that works well for you, but I highly recommend you have a script library, as well as a snippet/template library, and you use a cloud service to be sure you can access those files if you are away from your primary machine. You might be surprised how handy this is when working on a server or remote machine.

    That’s my T-SQL Tuesday trick for T-SQL, better script management to make your work easier.