Summit 2016–Setup

Tuesday was the first real day of the Summit for me, and a long, all day event. First, a lovely breakfast with Micky Steuwe, during which she presented me with a lovely glass art piece she made herself.

Photo Oct 26, 7 57 43 AM

I’ll treasure this, as personal, thoughtful gifts are touching. Thanks, Mickey.

Then it was the annual SQL Saturday organizers meeting. I had a short, 5 minute talk about trying to create slimmer, more affordable events. I indulged myself with a couple snaps from the stage.

Photo Oct 25, 10 56 21 AM

After that it was booth setup day. I’m not sure I did a lot, especially during the laptop setup since I don’t usually do the fine technical work here. I’m usually involved in carrying things, and with nothing to carry for awhile…

Photo Oct 25, 1 04 41 PM

It’s something to see the raw exhibition hall, which is really a construction zone. People are everywhere, working to get various booths assembled.

Photo Oct 25, 12 11 35 PM

When it comes together, things look really good and it’s a much different space. That’s good because I’ll spend most of Wednesday and Thursday in there.

Photo Oct 25, 12 36 45 PM

I also got to go prize shopping for the #RedgateRocks event on Thursday. It’s fun to try and be creative and find things people might enjoy. I also got to see our SQL Prompt playing cards, which are really cool.

Photo Oct 25, 12 38 41 PM

Then back to the convention center for the evening. Conversations with lots of old and new friends, and even a few selfies.

Photo Oct 25, 8 13 12 PM

A very long day, and off to bed relatively early for me. Then up again this morning, a short working, and now time to head back.

Posted in Blog | Tagged , | Leave a comment

Win a Year of Death Wish Coffee at the #sqlsummit

One of the promotions this week at the PASS Summit from Redgate Software is a chance to run a year’s supply of coffee. This isn’t just any coffee, but the Death Wish Coffee, full of extra caffeine. Grant Fritchey loves this stuff, and that’s what one lucky attendee will get.

If you want to win, come talk to me or one of the other Redgaters at our booth in the Exhibition Hall. Then you’ll know how to submit your entry.

And if you don’t want coffee, we’ve got plenty of other fun giveaways.

Don’t forget to also pick up your wristband for the #redgaterocks party.

Posted in Blog | Tagged , , | Leave a comment

Getting dbatools in Powershell

I had the chance to watch dbatools in action recently at SQL Saturday Cambridge. I watched a presentation from Chrissy LeMaire (@cl) that was impressive in how easy she made a number of DDL type changes between instances.

I decided to give some of these new cmdlets a try, since I was impressed with the work done. The first step in trying one is to get the dbatools module. If you go to the site and click the download link, you get great instructions.

The first thing I did was start a command line as an administrator, then run Powershell. From there, it’s a simple “Install-Module dbatools” command. As you can see below, you’re asked if you want to actually do the install. If you do, then things just work.

2016-10-17 17_31_00-cmd - powershell (Admin)

Let’s test.

First, I’ll type “copy-sql”, as shown here.

2016-10-17 17_31_50-cmd - powershell (Admin)

When I hit TAB, I get the first matching item. Hmm, I’ve never seen Copy-SQLAgentCategory. Not that I’d ever done this, but this is certainly going to be simpler than digging through DMVs or reading BOL.

2016-10-17 17_31_55-cmd - powershell (Admin)

Hit TAB again and get the next Copy-Sql command.

2016-10-17 17_32_01-cmd - powershell (Admin)

In fact, there are lots of Copy-, Export-, Test-, Get-, and more cmdlets. It’s a very handy list, and the types of things I’m stunned Microsoft just didn’t provide.

I’ll be digging in a bit more and looking at how some of these items work, as I suspect these will be invaluable for DBAs that need to manage and migrate various settings at any scale across systems.

Posted in Blog | Tagged , | 1 Comment

Using TRY..CATCH in SQL Prompt

I learned something new recently about SQL Prompt. I was working on documenting and experimenting with snippets and found a quicker way to write code.

One of the things I think is great is to use the TRY..CATCH structure. I don’t do it enough, but I want to do this more, and make it smooth. SQL Prompt includes a tc snippet, which gives me this code when I type t-c-TAB.

2016-09-23 12_13_39-SQLQuery4.sql - (local)_SQL2014.Sandbox (PLATO_Steve (63))_ - Microsoft SQL Serv

You’ll notice that my cursor is in the CATCH block. Why? Well, if you pause after t-c, you’ll see the snippet and code (unless you’ve turned off the window). Notice the $CURSOR$.

2016-09-23 12_13_17-SQLQuery4.sql - (local)_SQL2014.Sandbox (PLATO_Steve (63))_ - Microsoft SQL Serv

This means the cursor appears there. Not too handy. However, also notice the $SELECTEDTEXT$ snippet. This is handy.

Let’s change our code and include an update statement. For example, suppose as part of a procedure, I’m writing this code:

2016-09-23 12_15_44-SQLQuery4.sql - (local)_SQL2014.Sandbox (PLATO_Steve (63))_ - Microsoft SQL Serv

I’ve got a procedure, and I’ve forgotten to include TRY..CATCH. Certainly I can surround the highlighted code with the structure, but that’s cumbersome, even using Prompt’s intellisense.

There’s a better way.

Notice the little SQL Prompt icon in the side bar. Prompt is active, and if I click CTRL, I’ll get this:

2016-09-23 12_16_59-SQLQuery4.sql - (local)_SQL2014.Sandbox (PLATO_Steve (63))_ - Microsoft SQL Serv

I’ve activated SQL Prompt in the context of my highlighted text. Now I can type t-c and see the snippet.

2016-09-23 12_17_49-SQLQuery4.sql - (local)_SQL2014.Sandbox (PLATO_Steve (63))_ - Microsoft SQL Serv

If I hit TAB, I get this:

2016-09-23 12_18_23-SQLQuery4.sql - (local)_SQL2014.Sandbox (PLATO_Steve (63))_ - Microsoft SQL Serv

Pretty cool. My update is surrounded by the snippet code, and placed where the $SELECTEDTEXT$ placeholder was used.

Use this. Write code quicker and make it better.

Try a SQL Prompt evaluation today and then ask your boss to get you this productivity enhancing tool, or if you’re using the tool, practice using ii the next time you need to insert some data.

You can see a complete list of SQL Prompt tips at Redgate.

Posted in Blog | Tagged , , , | Leave a comment

Monday Night Networking Dinner Tonight

If you are attending the  PASS Summit, or even in the Seattle area, we have a SQL Server pro networking dinner tonight. This is at The Yardhouse on 4th Avenue. This is a buy your own event, but you’ll have the chance to meet with and network with other professionals.

We’ve split the dinner up into four times to lessen the load on the restaurant, and we’d like you to sign up so we can plan better.

The times are:

5:30 – Sign up

6:30 – Sign up

7:30 – Sign up

8:30 – Sign up

I’ll be there for awhile, along with Andy Warren and a few volunteers. Hope to see you there.

Posted in Blog | Tagged , , | Leave a comment

Your Technology Budget

After I wrote about a home lab recently, someone was curious about the amount of money that others invest in their technology lab, habit, or hobby. I thought that was fairly interesting, so I decided to ask others to get an idea of what amounts people spend and even on what.

How much do you spend on technology in a year?

Let’s skip cell phone and Internet service bills, but if you buy cell phones, tablets, computers, Kindles, or other gadgets, let us know. I’d be curious about your spending on your career, which might be travel and events, training, books, etc. versus the spending on technology for fun.

For me the amounts vary dramatically. The vast majority of my spending on technology is for replacement pieces and parts. I will buy hard drives or monitors or memory, but rarely anything more fun in most years. I don’t spend too much on learning as I get a Pluralsight subscription, and of course, I read SQLServerCentral. I rarely take training, but my generous employer usually covers that. In fact, most years, my total expense for technology is likely a few hundred dollars at the outside.

I have purchased a few Kindle devices for reading, and I’ve gotten cases and batteries for my cell phones. I do go through keyboards and mice every year or two, sot hose are expenses, but rarely anything more interesting. I did buy a Pebble watch a few years ago. This year was an exception as I spent a bit to rebuild a desktop that has been aging. I didn’t get everything I wanted, but I substantially improved the speed, which was the important thing. I’ll also likely add in a new tablet next year to replace my aging iPad Mini (non Retina) to help with coaching volleyball. I’d like a drone for some fun camera work with kids and sports, but I suspect that is not likely to happen in the next year. Those gadgets are expensive for a complete indulgence.

Let us know this week what you’re spending money on in technology and if any of those items have proven themselves really valuable, or just lots of fun.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | 2 Comments

Meet and Greet Data Pros at #sqlsummit

Next Monday night is the networking dinner that Andy Warren and I have put together for the last 6 years. Once again, we’ve arranged for everyone in Seattle to come join in.

If you don’t have plans for Monday night, pick a time, and join us at the Yardhouse. We’ve got free tickets that are just being used for planning purposes. There’s no charge, other than the food or drink that you want to order.

Or buy for someone you meet.

This is a great networking event, so please pass the word and attend if you can.

Posted in Blog | Tagged , , | Leave a comment

#RedgateRocks at the PASS Summit

If you are attending the PASS Summit next week in Seattle, and want to attend a fun party on Thursday night, come by the Redgate Software booth in the expo hall for your #RedgateRocks bracelet. This is your ticket to admission Thursday night.

Walk down to 1927 3rd Ave between 7:30-10:30 Thursday night and come in. We’ll buy the first drink and have some nibbles, as the Brits say.

Grant and I will be down there for at least part of the night along with plenty of Redgaters enjoying the night. We might not look quite the same, but we’ll be having some fun.


There’s plenty of other Redgate contests and fun for the week, so come see us at the Summit.

Posted in Blog | Tagged , | Leave a comment

Where Do You Run Your R Scripts?

I know most of you don’t work with the R language. In fact, plenty of you might not know anything about R other than a cursory understand of this as some sort of data analysis language. If you want to know more, here’s what the R Project is.

Microsoft wants you to use R Services in SQL Server, or the R Server product available as a standalone system. However, I saw someone ask the question why would someone run their R scripts inside SQL Server, because these are expensive CPU cycles to burn on analysis. Someone else noted that Microsoft loves your licensing dollars, so their push to use R Services is perhaps a little self serving.

Push the intellignce to the data makes sense. Isn’t that what we do with large data warehousing queries or SSAS cubes? We’re trying to get the analysis done at scale without having to move the data elsewhere, especially considering we’ve (usually) already moved the data in some sort of ETL (or ELT) process. Gaining insights from our ever increasing scales of data requires some computational cycles somewhere.

What’s the alternative? Large queries that pull data to some client? I think that’s fine, and that might be a better alternative since simple queries to pull data don’t burn as many CPU cycles as those that might perform analysis. I certainly understand that the licensed CPU cycles for a SQL Server instance are expensive, and we want to be careful how they are used. Adding complex R scripts might not be the best use of our licensing dollars. On the other hand, if I can perform analysis quicker, that is more useful, than perhaps I can eliminate other random queries analysts want to run on my database?

Ultimately I think that R Services make some sense in SQL Server, but not as some experiment. I would suggest that the R client is the way to experiment, preferably on a copy of data that allows someone to build scripts and determine if there is insight to be gained from a particular set of data. Build a Proof of Concept (POC), and only deploy it to a SQL Server if you find it provides value.

And if you do so, continue to experiment. That R script you run today might not be as useful in six months as your application, database, and business evolve. Data analysis isn’t a set-it-and-forget-it, but rather an ongoing, iterative process.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | 2 Comments

Creating a Logon Trigger–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Suppose you want to audit logins for your SQL Server instance. There are multiple ways to do this, but Logon Triggers have a few advantages. First, they get data into a table that most of us are familiar with, SQL Server. Second, they guarantee that the event is captured on the instance if the trigger is enabled.

There are plenty of other uses for these triggers, but beware that you can cause problems if your code doesn’t execute flawlessly.

I’ll show you how to create a basic logon trigger here. Note, these are server level items, and you’ll need to be able to create the trigger in the master database.

The structure of this code is similar to other triggers. We’ll use the CREATE TRIGGER DDL. Where this differs from DML triggers is that we use the ON ALL SERVER command. For auditing, I tend to set this as an EXECUTE AS ‘sa’, but you may choose a different type of account.

Here’s my basic code:

--ALTER TRIGGER LogonTrigger
    INSERT DBAAdmin.dbo.LoginAuditing
Note in this code  I’m specifying a specific table to store data in. This has to exist.

I also use the FOR LOGON event. You can scope triggers for other events, at the server or database levels, and read more about DDL triggers in BOL.

Once a user attempts to logon, the trigger fires and a simple insert takes place. If there are errors in inserting this data, the user may not be able to logon.

There is a lot to write about logon triggers, but for this short piece, I’m just showing hot to get started. Please, please, please, be careful with these and read the documentation carefully. Be sure you understand how they work and how to disable them. If you implement one, test it extensively.


I had a small issue building a logon trigger, and thought I’d get a few posts written on the topic. This was 5 minutes work since I had the code and just wanted to describe things, but I’ve got a few more posts sketched on this topics  as I’ve learned more.

Learning something, solving a problem, writing about it. This is a good way to show someone you are learning about a topic and developing some skill.

Posted in Blog | Tagged , , , | 2 Comments