Archaic Commands

I was watching the San Diego Technology Immersion Group meeting in September, and found myself transported back into the past. This month was the first meeting of the Linux chapter, spending a few months working through The Linux Command Line.

Watching from home, I watched the hosts walking through the command line in Linux, showcasing various basic file comments. Seeing them explain and execute commands like ls, rm, alias, cat, tail, less, and other commands in a text session reminded me of learning Unix at university on SunOS. Slowly various bits of knowledge came back to me from my own experiments learning how to work with files, full of text data, interspaced with my regular visits to the “man” pages when something didn’t work. In fact, seeing the man pages come up on the YouTube session made me smile.

It’s funny to think how much the world has changed. Even now, I have an Ubuntu VM that I run at times, but it’s mostly a GUI shell, and my interactions with the command line are fairly minimal. That’s completely the opposite of my time at university and my first job where I pretty much lived in the command line. My code editors were vi and emacs that I ran across a dozen X Windows sessions open on various machines in the college computer lab. My interactions were really working with text and text commands. Even now, I might use SSMS and Visual Studio, but I’ll pop into a command line to check my git repo or spend time playing with PowerShell and executing commands in text sessions. While working with Python I’m as likely to use the REPL from a command window as I am to use an editor like VS or PyCharm. I really just like the interaction of the command line.

Not everyone feels that way, and that’s fine. I agree that GUI based systems are more convenient at times. In fact, I often use various GUI items in SSMS build up a task, such as an Extended Events session, and then script the result to give me code that I can execute. GUIs do some things extremely well, but allowing for repeatability is not one of them. They can also easily hide the actual commands and defaults being executed.

I think everyone working in technology needs to have some skill in working with a command line. Even if you want to use T-SQL to script all your tasks, you should be familiar enough with something like SQLCMD to execute a script in case you run into a situation where SSMS might not work. This might be especially true as you learn to automate more of the work your systems require and perhaps execute these scripts through other tools, like an Enterprise scheduler.

If nothing else, the command line teaches you how to work in a methodical, step by step process. However, I’d argue it develops new skills that might lead you to do solve a problem in more creative ways. Perhaps you’ll even grow to use a command line language like PowerShell to accomplish some repeatable task. You might find it works better than the old way you used to work.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment

Webinar: Database Deployment with Git, Jenkins, and Octopus Deploy

I’ve got a webinar later today that looks at how Redgate Software’s DLM Automation and other SQL Toolbelt tools can be used to smoothly move development changes from one database to another.

Sign up and spend an hour with me this morning.

This is part of a monthly series that Redgate is running, where we’re changing the platform out every month. In this case, I’m going to use a git as my VCS, storing changes in there as I make them to a database.

From there, I will push the changes to a remote git repo and have Jenkins automatically pick up the changes and build, test, and deploy the changes to another database. I’ll make table changes, data changes, proc changes, anything you want. Just ask.

However, that’s not all. From there, we’ll see Octopus Deploy take a package of changes and deploy them out to other environments, letting my manage and track the process with the click of a button.

I’ve been doing this demos for a few years, across multiple platforms, but never on Jenkins. However, I’m amazed that things work as smoothly here as they do with TeamCity or VSTS.

Building software is building software, including database software.

If you’ve got some time today, join me and see how easy this can be.

Sign up and see database changes made live.

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

Yet Another Database

The “yet another” moniker is used throughout technology. YAAF, YARN, YAML, and more ways that people talk about yet another way to solve some problem or build a platform. That’s one of the amazing things about technology is that we, as a group, are not satisfied with the way things are done. Someone is constantly looking to find a better solution and advance our technology choices in a new direction.

In the database world, we’ve seen changes as well. Relational databases were the obvious, or perhaps just dominant, choice for most of my career. At some point I saw object databases, XML databases, then streaming databases, none of which gained much popularity. The last few years have seen document databases, graph databases, key value databases, and more. We categorize databases as row-store or column-stores now. There are probably other types I’m forgetting.

There are also categorizations of OLTP, OLAP, operational, ODS, data warehouses (DW), etc. that people use. I ran across a piece called “Why you need another database“, whose opening line is “Do you really need yet another database?” The answer is, sure, you need an analytical database, but I think the piece is a bit behind the times. Lots of people have built an analytic database in the form of a relational DW, an SSAS cube, or perhaps some other type of NoSQL store (Hadoop is popular) that allows complex querying of data away from the operational, every day OLTP system.

I would guess many of you have some separate reporting copy of data that allows analysis separately from your online system. This could be a copy of your production database, a data warehouse receiving regular loads, a read-only AG replica, or something else. Some of you might perform analysis in your primary OLTP system, with enough spare capacity to handle the reporting load.

There are multiple ways in which you may decide to implement an analytical database, but I do agree that most businesses ought to have some way of analyzing the data they collect. The questions in the piece (structure, size, analytics), are good ones, and no matter what your platform, architecture, or technology, make sure that you build something that meets the needs of your organization as a whole, not just the technologists that prefer one platform over another.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment

Thursday Game Night at PASS Summit

On Thursday night, Oct 27, 2016, Andy Warren and I are hosting a game night event at the Washington State Convention Center. This is a low-key, alternative fun event for those of you that might not be attending another party elsewhere.

If you’re interested, please purchase a ticket. We have arranged for a bar, but this required a financial commitment. With your $10 ticket, we’ll provide you with a single adult beverage ticket or two soda tickets. You can purchase more with cash if you’d like. The tickets are more for planning purposes, as we have a limited space. However, if lots of you want to come, we’ll work on securing more space.

Andy and I will provide some board games (Monopoly, Scrabble, Chess, decks of cards), but please feel free to bring your own. Pass the word to friends, letting them know that they can come and enjoy their favorite tabletop game with others at the Summit. You can look to join a group and just play.

Our goal is more networking and fun in a safe, comfortable environment.

We are complying with the Anti-Harassment policy from PASS, so no Cards Against Humanity. Andy and I may ask you to leave if we feel your game is inappropriate. However, feel free to find another location and play there.

Again, please register today if you’d like to enjoy some gaming with fellow data professionals at the Summit. If we fill up, please join the waitlist and we’ll do our best to accommodate you.

Alternatively, come even if you’re on the wait list and perhaps you can find a few people that might want to migrate to a hotel lobby, restaurant, or other location for some gaming.

Hope to see you there, and please, pass the word to others.

Game Night – PASS Summit 2016

Posted in Blog | Tagged , | 2 Comments

Thursday Night Networking Dinner Meetup at Summit 2016

This year PASS announced that Thursday night would be an open night at the PASS Summit, with no events scheduled. Typically Microsoft has sponsored a party at Gameworks, but not this year. PASS has a list of events at the Summit, if you want to browse. Many of these events do require invitations, which means attendance will be limited.

Andy Warren and I decided to put together a couple of events for Thursday night, hoping to give many of you an alternative that’s both fun and good for your career. We came up with two items, and we hope that some of you will choose to attend. I’ll talk about one here, and the other in a new post.

Please pass the word on to others that you know might be attending the summit. Tweets, blogs, etc. are appreciated. We’re using the hashtag #sqldinner, so please feel free to add that to your promotion.

Thursday Night Dinner Meetup

For many years, Andy and I have run the Monday Night Networking Dinner. This year, we decided to expand that to Thursday night, but with a twist. We are looking to organize small groups of people for a quiet dinner. We are asking for anyone interested in meeting fellow database professionals to come to the Convention Center lobby on Thursday around 5:30. We’ll have signs to help guide you.

In the lobby we’ll have some facilitators to help you find/build a group of four with a shared interest, anything from technical to sports to politics. Once you have a group you’ll move over to the ‘restaurant board’ to look at a list of area restaurants. We’ll tag each with whether they are in walking distance (< 1/2 mile). Pick a restaurant and you’re off to dinner! Our goal is to facilitate, but self organizing is fine. Our hope is this is a chance for you to network with some new people and enjoy a nice dinner with some new friends.

Around 5:45-6:30, we’ll try to organize people into groups of 4 that can go out for a dinner at a local restaurant. Please feel free to self-organize and leave if you have a group, but the idea is to network with some new people and enjoy a nice dinner with some new friends.

I’ve gotten a number of restaurant suggestions (still looking for more), at various price ranges, and will have information available that night. We will have the various restaurant names on cards, to help you choose, but also try to spread the load so people aren’t waiting too long. We will also have a whiteboard with various topics and ideas in the Community Zone during the day Thursday for you to come read and consider which items are interesting to you. Feel free to vote on existing topics or add your own.

This is an opportunity to try something new, and certainly a chance to get out of your hotel room if you don’t have other plans. If you have a group of 2 or 3, then feel free to come by and meet another new person or two that might want to join you.

Again, I hope this works out well and please help me and Andy pass the word. Even if you have other plans Thursday, I’m sure there are plenty that won’t, so encourage them to meet someone new on Thursday night.

Posted in Blog | Tagged , , , | 1 Comment

The 2016 Home Lab

Everyone needs a home lab. At least, everyone that works in technology and wants to grow their career or get better at their craft. I’d hope that’s most of you, but if not, that’s fine. If you’re happy with just cruising along at work, I wish you the best.

For many people, especially those wanting to learn, they need some lab. There are plenty of free software choices you can make to build a lab. SQL Server, SSMS, Visual Studio Code, Virtual Box, you can get these for free. The thing you’ll need to provide is the hardware. From my own experience, to learn about many features, you’ll want at least 2-3 VMs, or instances that can run different versions, provide some separation, and even a spare instance when you blow one up. If you want to experiment with the AlwaysOn technologies, you might want 4 or 5 spaces. You can do lots of this in Azure, but many people don’t want to commit, or have open ended costs. Or they don’t want to eat up bandwidth.

I’m not a hardware geek, and honestly don’t care what I run. It just needs to go fast and work well. I rebuilt my desktop, on advice from Glenn Berry, but I just followed advice. I didn’t try to determine if part A was slightly better or worse than part B. I just needed the SSD/MB/CPU/etc. to work.

I’m sure plenty of you might be considering a lab, or might need to upgrade. I ran across two great posts that give you a few ideas on what might work for you. Allan Hirt showcases a great portable lab, which I may adopt as a traveling companion. However, if you want another desktop that might run a slew of VMs for experimenting with SQL Server features, Glenn Berry from SQLskills has his own post that you can use for advice.

If you’ve got recommendations or ideas, or questions, please add them to the discussion. If you’re proud of your own setup, let us know. If you have found Azure (or AWS) to be a friendly, cost-effective place for a lab, I’m sure people would love to know that as well.

Steve Jones


Posted in Editorial | Tagged , | 2 Comments

Why I Practice Presentations

I had a new presentation to give this week at IT/DevConnections. This is a paid for conference, and I want to be well prepared and ready for the events. It’s the least I can do for people who have spent money to attend an event.

I enjoy IT/DevConnections, and it’s probably my favorite US event. Others are good, and SQL Bits is amazing, but I enjoy the variety of topics and speakers at this one, so if you want to try something different, think about this event next year. I hope I’ll be speaking again, and I’m planning on submitting. I believe the event is mid-October 2017, if you want to plan.

However, I digress. In this case, I was delivering a presentation that I had never publicly presented before. I began speaking, and as usual, I started walking around a bit. I was on slide 7 when the projector went out. I assumed there was an A/V issue, and since I’d already asked someone to get a tech for my microphone feedback, I covered by continuing on with the items on my slide from memory.

A few minutes later I walked back to the podium to see this:


The system was actually at 25% in my case, but moving slowly.

That’s a presenter’s nightmare. I had SSMS running, with multiple queries set up, a few Explorer windows, a VS session, and more. I knew it would take my system at least 5 minutes to restart and get ready.

The only think I can do is proceed. Fortunately, I’d practiced this talk a few times and could remember what was on the next few slides. I’d be stuck at getting to a demo until things were running, but I could talk.

I don’t like doing presenting new sessions at the PASS Summit, SQL Bits, or any paid event. I prefer to practice at a user group or SQL Saturday. Sorry, but user groups usually get unpolished, semi-rehearsed sessions. In this case, I hadn’t been able to get this one done before a user group session, so I was stuck practicing on my own.

Here was my prep, most of these sessions about 1-2 hours:

  • Thur (12  days to presentation) – Outline of talk done, most slides done, demos sketched but not all working. Practice the talk twice, fixing slides and flow.
  • Friday (11 days to presentation) – only working on demos. setup, run, correct items.
  • Saturday (10 days to presentation) – practice demos on laptop (first time on this machine). Fix some issues.
  • Sun – Wed – Speaking at another event, different talks, prep for these talks and practicing them.
  • Thur (5 days to presentation) – Walk through demos on plane flight home. Build up/tear down multiple times on a VM to test the code.
  • Friday (4 days to presentation) – Walk through entire presentation, including demos. Apart from interruptions, goes fairly well.
  • Sunday (2 days to presentation) – refine some demos and slides that don’t seem clear. The flow has been a little off, so I move a couple demos around to separate the concepts.

Monday (1 day to presentation) – Quick run through before leaving for the airport. Land in Vegas, check in, go through 2nd presentation as a quick reminder from 5-7. Hit the gym at 7. Dinner around 8:30. Final run-through of new talk from 9:30-11.

Tuesday (day of presentation) – Up early, flipping through entire slide deck, practice opening, but not running code. Double check that all items have been removed and cleaned from the instance. Realize I have 60, note 74 minutes. Make a couple mental notes about what to cut when I’m live.

A lot of prep for this talk, which I may or may not do again. I had some good feedback from people and a packed room. Quite a few people said they learned a lot and we had a lively discussion.

Note: my timing was poor for two reasons. One was that I had assumed all my talks were 75 minutes, but this one was 60. My fault, no excuse for not checking. Two, because I hadn’t delivered this before, I had no idea what questions to expect, and how the discussions might go. I let the session get slightly interrupted because I was answering questions. I was unsure I had explained things well, so I chose to answer items.

My apologies for those that might not have enjoyed the session, and certainly for a couple code items. I must not have cleaned something appropriately as I had a couple errors. Also, I didn’t run all samples because of time, so perhaps I broke something else.

In any case, next week will be a bit of review of the talk. I have some notes, but I’ll go back through code and try to ensure everything runs correctly and upload corrections.

Presenting is hard, and I work at it to ensure people enjoy the talks. I think it’s worth the time, but it is an effort. In this case, I was glad I had made that effort.

And, for the record, after the Meet the Speakers event on Tuesday, I went to the gym at 6:30 for an hour and then reviewed the presentation for Wednesday, despite the fact I’d delivered a similar talk the week before.

Be Prepared. That’s the motto I try to stick to.

Posted in Blog | Tagged , | 2 Comments

The Snippet Manager in SQL Prompt

I love SQL Prompt, since it makes T-SQL coding quicker and easier. One of the handiest things is the Snippet Manager. I’ll show you how this works.

When I type something, like “cl”, I get a list of things, as shown below..

2016-09-23 10_24_42-SQLQuery3.sql - (local)_SQL2014.Sandbox (PLATO_Steve (61))_ - Microsoft SQL Serv

I see CLOSE, which has “cl” in it, as well as a number of functions. However there are two items (cl and clrp) that have a description to the right. They also have a scroll to the left. These are snippets. If I hit Tab, the highlighted snippet, cl, will be replaced with code. This is shown below.

2016-09-23 10_19_22-SQLQuery3.sql - (local)_SQL2014.Sandbox (PLATO_Steve (61))_ - Microsoft SQL Serv

This is all custom code I put into my Snippet Manager. I can now replace these parameters with new values, or just execute this code as is to create a new login and user. Note I typed three things (c, l, TAB) to get lots of code here.

The Snippet Manager

To access the Snippet Manager, click the SQL Prompt menu item and look down towards the bottom.

2016-09-23 10_27_33-SQLQuery3.sql - (local)_SQL2014.Sandbox (PLATO_Steve (61))_ - Microsoft SQL Serv

This will bring up the Snippet Manager (part of SQL Prompt options in 7+). Note that I have a folder where I store snippets at the top, various snippets below that (with new/edit/delete buttons) and then the snippet code the the highlighted snippet at the bottom of this image.

2016-09-23 10_27_41-SQL Prompt – Options

The snippet is highlighted, and if I type, I get completion here. So, I’ll type C-D, and jump to the “cdb” snippet. Note I’ve skipped over cci.

2016-09-23 10_29_53-SQL Prompt – Options

I can then edit or delete this snippet. If I edit it, I’ll get a dialog with the snippet and the placeholder parameters listed. This is shown below.

Note: I’ve customized this code, so it isn’t the SQL Prompt default.

2016-09-23 10_31_02-SQL Prompt - Edit Snippet

I can also create new ones. For example, on one of our Redgate VMs, we keep some demo snippets. I’ll can create a new one by clicking “New…”

2016-09-23 10_32_05-SQL Prompt – Options

I’ll then get this dialog.

2016-09-23 10_32_12-SQL Prompt - Create New Snippet

I can give this a name, a description, and type any code into the code window. I’ve added a few items below.

2016-09-23 10_35_15-SQL Prompt - Create New Snippet

Now if I save this, close the Snippet Manager, and go back to a Query Window, I can type d-e-m, and see this:

2016-09-23 10_36_53-SalesDemo-2016-09-07-1117-export-i-fh727xw9 - VMware Workstation

My new demoxpcmdi appears before the other “demo” snippets. I can also see my description to the right.

Hopefully, you’ll see the value in SQL Prompt and start using snippets to improve your ability to code quickly and take the hassles and guesswork out of cleanly building SQL Code. You can also read a similar piece I wrote on the Redgate blog.

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.

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

Who’s Touching My Database


As databases become more critical to the operation of our organizations, we are being asked to audit and report on access to both data and the configuration of our systems. SQL Server has a number of features that can help you comprehensively audit your system, including SQL Audit, Extended Events and the default trace. Come learn how you can configure and incorporate detailed auditing into your reporting to management.

Level: 200 – Should have some basic knowledge of SQL Server, database security, and T-SQL.


  • Using Logon Triggers
  • Looking at the default trace
  • Using SQL Audit to track logins
  • Using Extended Events to track logins
  • Checking permissions at the server and database level
  • Watching database activity using DML triggers
  • Watching database activity using SQL Audit
  • Watching database activity using Extended Events


Posted in Presentations | Tagged , , | Leave a comment

Developer Deployment Frustrations

This editorial was originally published on Sept 7, 2012. It is being re-published as Steve is at DevConnections.

Why don’t developers like SQL Server? Probably a few reasons, but I’m sure this is one that really frustrates them. I found a Connect Item that was titled:  Why is Deploying SQL Server 2008 R2 sooooo FRUSTRATING?!!  There really is a question there, asking for guidance on  which versions of SQL Server are available and recommended for developers to include in their applications.

When SQL Server MSDE was released, it seemed that Microsoft was looking for it to be included in small applications that might then be upsized to a Standard or Enterprise edition of SQL Server. It seems to me that this is really the market for Express (the evolution of MSDE) and that it ought to be simple for a developer to not only deploy this with their application, but also setup basic maintenance easily.

I sometimes think that the software developers at Microsoft get lost in their own specialty and forget just how frustrating it can be for the rest of us trying to use their product in new ways. They forget that many of us want to deploy simple solutions easily, and not spend a lot of time working out the nuances of software setup.

I’d like to see Express not only have a very simple setup that works across multiple versions of Visual Studio, but also baic maintenance plans built in that allow full and log backups (if needed), along with index rebuilds with a simple switch set as a part of setup. A few registry keys or XML config changes could set paths or frequencies.

Making life simpler for developers is a worthwhile investment for the SQL Server team. It makes them more likely to include it in their applications. If you can add a one-switch replication to sync to a Standard or Enterprise SQL Server, they might think Express is required in every application.

Steve Jones


Posted in Editorial | Tagged , | Leave a comment