Finding #dbatools #PowerShell commands

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

I was reading about Pester from Rob Sewell and saw he talked about a new command I hadn’t seen in dbatools: Find-DBACommand. This is a fantastic idea, and I think it’s my new favorite dbatools command, err, cmdlet.

This is the search cmdlet for dbatools. With this, I can pass in a parameter of something I’m looking at and get results. For example, if I run this with “attach”, I get:

2017-11-16 08_58_34-cmd - powershell (Admin)

If I use “security” I get:

2017-11-16 09_00_15-cmd - powershell (Admin)

These are the ways that I’d most often use the command, but I can certainly do other things. There are tags on some cmdlets, so I can do this: Find-DbaCommand –Tag job

2017-11-16 09_03_31-cmd - powershell (Admin)

This gets me less results than just a search for the string “job”, and these are items focused on jobs themselves.

I can loo for specific authors with the –Author parameter. I can also look for minimum or maximum versions of SQL Server, though I haven’t found anything that returns here in quick testing. In looking through some help and source code for other commands, I don’t see version tags, but hopefully that comes over time (Submit your PRs here).

This is a fantastic addition, and certainly better than trying to search all of PoSh help for something like Backup. I might get way more commands I don’t care about there.

If you haven’t tried dbatools, do it today. It’s a fantastic administration tool for your toolbelt.

Posted in Blog | Tagged , , | Leave a comment

The Pressure to Perform

I’ve worked as a technical person for most of career. I’ve often been responsible for building or managing systems, and ensuring they operated according to some set of requirements. If things didn’t work, I’d have to fix them, but often there wasn’t the threat of losing employment unless I was grossly incompetent. If I made a strong effort, I’d get other another chance, or maybe be shuffled to work on some other application or system.

Throughout my career, I have worked with various salespeople in different companies. Either they were my customers/clients or they were just co-workers. However, they operated under a different mindset. Most salespeople are expected to make a certain amount of sales in a time period, and if they don’t, they may be let go. In fact, in quite a few companies, a salesperson couldn’t miss their target more than two time periods in a row without finding themselves out of a job.

I realize that sales isn’t like the creative work that developers do, or the tedious work of managing systems, but what if we were treated like salespeople? Do you think you’d want to work in an environment where you had some quantifiable measurement, such as uptime for a quarter, plus tickets closed, and if you couldn’t meet the goals, you’d be let go from your DBA position?

Imagine if you were a developer. You might have the demand that you meet the expectations for delivery and you are held accountable for estimates within some amount of variance. Perhaps as a developer there would be a requirement to complete xx amount of stories in some time period. I don’t this would work well. I think we’d try to make longer estimates and more granular stories to better define out work, but I’d also expect the business might press back and put more pressure on us.

In plenty of organizations, there are these pressures. I certainly see developers working harder to get things done, not because of the threat of their job, but because many of us that choose technical work feel a sense of pride and responsibility in the work we do. We work long hours to meet deadlines, sometimes because we’re afraid not to, but usually we do want to complete our projects in the best way we can.

I think we’d all be worse if we were treated like salespeople, and likely many of us would have more job changes. I also think our organizations would suffer more, with higher turnover and a lack of detailed institutional knowledge among those that build systems. Certainly some high performers might thrive, especially if we received large bonuses, like salespeople often do, but I also think we’d find shoddier work overall, with applications even more cobbled together with patches and prayers, with the hopes that our workload doesn’t overwhelm our systems.

I know I wouldn’t like working in that type of environment, and why I’m not sure I’d ever want to go into any sales role.

Steve Jones


Posted in Editorial | Tagged | Leave a comment

Avoiding Development Taxes

When I first worked in an environment where multiple developers needed to release software, I found too many rules and constraints for smooth development. The process was developed by one person, or for one application, and somehow expanded to encompass all work being done in the organization. It was if we weren’t trying to think about our creative work as developers and DBAs, and instead viewed our systems composed of widgets built in a factory, each one a copy of the others, perhaps just a bit bigger or smaller. We used the same set of rules for the mission critical finance program as we did for the department vacation scheduling application.

While working inside an overreaching command-and-control mentality, the idea of moving faster or releasing software more often was seen as really dangerous. Two decades of Agile methodologies, and the rise of DevOps have started to change this for many companies. Each year the State of DevOps report seems to show more and more organizations finding ways to build better software, often by having their operations people embrace the concepts and processes used by software developers as a way of building and managing their environments.

Databases still lag behind, and as Donovan Brown says in this piece, if you’re not automating the back-end work of code deployments to the database, you’re still “faking” some of your DevOps process. Databases must maintain the state of data, even as transactions take place around code changes. Ensuring that we properly handle those data changes is a challenge. However, by treating the database changes as similarly as possible to application changes, we can minimize risk and learn database development techniques that help us push forward without being reckless.

Tools have gotten better for database development. Microsoft has some tools for SQL Server, and various other vendors such as Redgate have others. Good tools are essential, but each new one adds some complexity to the environment. That’s one reason why having a minimal number of tools and platforms reduces the friction of getting things done. I see this as a big reason SQL Server on Linux might take off. Plenty of companies don’t want Windows in their infrastructure when most of their systems are on Linux. Not that Windows is hard, but consistency makes everything easier for a staff.

This is the same reason why I’d say that it’s worth sticking with a database platform or two and not experimenting with each new type of system that comes out. If your staff knows SQL Server, then adding in MongoDB or Cassandra means there’s a learning curve, or a “tax” as the piece notes, to getting things done. This same tax gets paid with each new vendor, platform, language, or technology you take on.

Database work is hard, and once your staff gets good at building and deploying changes, you want to take advantage of their knowledge. For companies that have solid development and deployment practices, stick with the things that work well. However, if you have staff that turns over regularly and don’t have a mature process, you can reduce the “tax” you pay for database development. There are companies that have paid some of that integration tax, that learning effort, and they’ve got tooling to help you build code better and faster. Just as I wouldn’t want to code my own build server, I don’t want to code my own database deployment tools. I could, but I’d rather spend my time solving problems and use the tooling that someone else has built to make my job easier, and provide a consistent coding experience for my developers.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment

Data Bots

One of the challenges for many databases is getting the information out to our users. Early on many of us had to write queries in a customized fashion for our users. Eventually, reporting systems, such as Reporting Services and Crystal Reports, have been used extensively in many organizations to ease the burden or getting information to users, sometimes even allowing self-service where users write reports. Those are inefficient systems, where a lot of man-hours are spent to create something for users.

Certainly some efforts are made to allow users to query data in an easier fashion. English Query in SQL Server didn’t work well, but the version in Power BI seems to do some things well. Perhaps it’s because a user can see some of the data and the domain of data being queried is inherently limited. I expect this to improve as services such as Alexa, Siri, Cortana, and more become embedded into more applications.

The bots are coming. I used to wonder, but the more I see the advances made in machine learning and related technologies, the more I think that we will continue to see bots used as a way to access our data. There is plenty of hype, but certainly the technologies are being used more and more, and increasingly sophisticated ways. Plenty of businesses are experimenting with them, though it seems in many cases the technology is in an early stage.

I haven’t been sure where bots are useful, but I see them more and more in places like Slack where a workflow can be kicked off with a chat operation. This is somewhere between running a script with parameters and using a semi-intelligent agent, but certainly the ease of a bot in places like Slack is very interesting. Operations can be easily conducted, with logging and auditing, and feedback, in a way that a CLI doesn’t quite work as smoothly.

Perhaps it’s because the domain of actions is more limited, I think that ChatOps works better than general purpose bots. I expect that that will change as we find ways to both limit the scope of interactions, perhaps by passing more information along to the bot. I’m certain the context technology for bots will improve, as well the interaction design. In some ways it’s exciting to think about bots being more focused and responsive than today’s customer service representatives. The one thing that I do worry about is the ability to get an empathetic response from a human. A person is more likely to go the extra mile or do a favor than a bot.

Steve Jones

Posted in Editorial | Tagged , | Leave a comment

One More Trip and Down for the Count

The last month has been a rough one for me, with lots of travel. I was on trips for 4 out of the last 5 weeks, and the week I was home I had a remote presentation to do. Travel can be hard enough, but add in the need to continue to manage the day job as well as prepare and practice for presentations, and it’s a long stretch.

Fortunately, I have a month off. Or nearly so. I have one last trip in December, and then I’m done for the foreseeable future. I have nothing scheduled in Q1 for travel, and I’d like to keep it that way. Apart from trip to visit the Redgate offices and a few customers, I’ll likely take quite a bit of 208 off from speaking at conferences. I’ve done a lot the last couple years and I need a break.

Plus there are other projects with Redgate and SQLServerCentral I’d like to tackle, not the least of which is upgrading our infrastructure from SQL 2008/WS2003.

I’ll still aim to get to a few new events next year, and I do appreciate the invitations. You’re always welcome to ask, but understand I’ll do less. I’d like to visit some new places, and Pittsburgh is tops on my list for now.

It’s been a pleasure seeing and meeting so many of you the last few years. I hope you’ve enjoyed my talks, and I’ll certainly continue to do more in the future, just at a slower pace.

Posted in Blog | Tagged , , | 1 Comment

Keeping the Single User Connection

A reader was having issues with a script. They ran their code from PoSh (Invoke-Sqlcmd), setting single user and then trying to rename a database and its files. However, occasionally, they’d lose the single user connection to another user. Frustrating, but it wasn’t a PoSh issue.

The ALTER DATABASE commands don’t really specify how SINGLE_USER works. What happens is that if you set single user, the first user gets the connection, whether that’s sysadmin, dbo, or a regular user. This means that when you set a database to single user mode, you need to be one of the users in the database.

The change to single used is blocked, until all other connections disconnect. One way to ensure this happens quickly is to add the WITH ROLLBACK IMMEDIATE clause, which disconnects users.

The original poster’s issue was that while they used the clause, their connection was in the master database. This meant that another user could potentially connect to the database, grab a shared lock, and prevent renaming files.

If your connection is in the user database, then when you issue the ALTER DATABASE WITH ROLLBACK IMMEDIATE and disconnect users, then you have the only connection. This should prevent any issues.

Quick Demo

Let’s see this in a simple way. I’ve got a demo database that I use, and I’ll open three connections. The first will be as a sysadmin here:

2017-10-24 09_54_17-Connect to Database Engine

and then the other windows I’ll switch the connection to a non-privileged user.

2017-10-24 09_55_17-SQLQuery20.sql - Microsoft SQL Server Management Studio

At this point, I have a few connections to my database.

2017-10-24 10_00_48-SQLQuery19.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (56))_ - Microsoft

Let me now reset the database option.

2017-10-24 10_01_30-SQLQuery19.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (56))_ - Microsoft

Everything works here, let’s check the next session. If I switch to another tab, I’ll run a query. After a second or two, I get this:

2017-10-24 10_02_29-SQLQuery21.sql - (local)_SQL2016.EncryptionDemo (AEUser (61)) Executing..._ - Mi

SQL Server has disconnected my session. If I try to reconnect (this login has EncryptionDemo as the default), I get this:

2017-10-24 10_17_04-SQLQuery22.sql - Microsoft SQL Server Management Studio

If you need to perform maintenance, and want to ensure you have the connection, change context to the database before setting single user mode.

Posted in Blog | Tagged , | Leave a comment

Adding a Format SQL Button to the Redgate Toolbar

I’ve gotten used to CTRL+K,Y to format SQL with SQL Prompt, but a customer wanted a button on the toolbar. It’s fairly easy to do, but I thought I should document the process for others.

First, if you click the small area on the right of a toolbar in SSMS, you’ll get an “Add or Remove Buttons” menu, as shown here.

2017-11-14 16_29_31-~vs7FB.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft SQL

If you click that, you’ll see this dialog.

2017-11-14 16_29_39-~vs7FB.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft SQL

In  this case, I’m happy with the buttons, I want to customize my toolbar. Click that option. This opens up the dialog below, and I’ll want to click the “add Command” to add a menu item as a button.

2017-11-14 16_29_50-Customize

From here, I get a list of all SSMS menus. In this case, I’ve clicked the Dimension item, and I can see all the possible menu items on the right.

2017-11-14 16_30_02-Add Command

However, I want a SQL Prompt item, so I need to scroll down the left to SQL Prompt. Once I click that, I see the commands on the right,

2017-11-14 16_30_11-Add Command

Now scroll the right to find Format SQL.

2017-11-14 16_30_23-Add Command

Click OK and then your new button appears in the list.

2017-11-14 16_30_32-Customize

And on the toolbar.

2017-11-14 16_30_41-~vs7FB.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft SQL

Now you GUI clickers can reformat SQL quickly.

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

In a basement, a long, long time ago… #tsql2sday

It’s T-SQL Tuesday time and this is a good one. Read the invitation and proceed.

Who Influenced Me?

I’ve been working SQL Server for a long time. Since 1991, in fact, and had held 4 jobs as a DBA or developer by 1999. I was comfortable with the platform, and had actually published some articles in early 1999 for a small UK journal. My career was going well and I felt very comfortable with the platform (v6.5 in my company) at that time.

As a developer, I knew T-SQL well, and was still learning things from reading articles, but there weren’t many SQL problems I couldn’t come up with some solution for. As a DBA, I knew the tricks to keeping a large, busy instance running, including the DR tricks to ensure my restores worked well on new hardware.

I was lucky enough to get my company to fund a trip to the initial PASS Summit. I went, along with my wife and infant son, to visit Chicago. We went to Comiskey Park on the last ,day of the baseball season, braving chilly weather along with about 1,500 people to watch the White Sox. I’m not sure my wife has been to a game since…

In any case, my main memory of that event was being the basement of some hotel in downtown Chicago. There were a few rooms in use, and we packed into see sessions on various topics. One was delivered by Kalen Delaney, @sqlqueen, who was perhaps the person I saw as an amazing speaker and teacher. I’d read her Inside SQL Server 7.0 book, anticipating an upgrade. I was delighted to see her speak, and perhaps more thrilled to shake her hand and get an answer to a question.

That was quite a highlight in my career.

Since then I’ve seen Kalen speak many time, and we’ve become friends. I’m always glad to get a hug and a few words with her, but each time, I remember that early, semi-awestruck time when I met her.

She’s inspired me, both as a data professional and a teacher.

If you ever get the chance to meet Kalen, take a moment or two with her. You won’t regret making the effort to introduce yourself and ask a question. I know I haven’t.

Posted in Blog | Tagged , , | 1 Comment

Changing the Default DB for a Login–#SQLNewBlogger

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

Recently I got into a bit of a pickle. I was detaching some databases for a demo, which is something I do periodically to make it easier for someone to see what’s doing on. The database detached fine, and I ran my demo.

Then, as a sysadmin, I right clicked to attach a database back.

2017-10-23 15_59_17-SQLQuery6.sql - (local)_SQL2014.SimpleTalk_1_Development (PLATO_Steve (57))_ - M

and all of a sudden I get this error:

2017-10-23 15_59_08-Microsoft SQL Server Management Studio

Interesting. I could have attached the database back from the command line, or with dbatools, but I thought this was interesting. As I go to the attach dialog, a new connection is made. However, in this case, the default database for my sysadmin account was the one I’d detached.

Fortunately, this is easy to fix. First, I opened a query window with master as the specific database:

2017-10-23 16_01_20-Connect to Database Engine

Then, I have a couple options to reset my default database. For old SQL Servers, many of you might have used sp_defaultdb. That’s marked as a deprecated procedure, so ALTER LOGIN is the new way. The syntax uses the WITH to include various options. In my case, I needed the DEFAULT_DATABASE item. This was my code:


If you are on an older version, something like this will work:

exec sp_defaultdb @login = ‘Steve’, @defaultdb = ‘master’

Once that was done, the GUI dialog worked. A quick and easy fix in this case.


As soon as I found the error, I knew what was happening. Resetting the default database took less than a minute, but I decided to spend 10  grabbing a few screenshots and putting this post together.

You could do the same thing. Show that you can recover from errors.

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

How Long Before You Upgrade?

This editorial was originally published on Apr 11, 2014. It is being re-published as Steve is out of town, with a few minor changes to dates and values.

It’s 2017. SQL Server 2000 is 17 years old, but there are still quite of you managing instances. SQL Server 2005 is 12 years old, and I’m sure more of you still deal with that version. I know because I work for a software vendor and I’m constantly asked if our software will run against those two versions of SQL Server. Most of our software is no longer supported on those versions, as they’re too far out of date.

For many of you, however, if you’re managing a SQL Server 2000 instance, it might only be 10 or 12 years old. Your company might still have been installing SQL Server 2000 in the year 2005. The same is true for SQL Server 2005. I wouldn’t be surprised to find companies still installing 2005 instances in 2008 or even 2009.

Companies don’t care much about versions. They tend to mostly care about databases getting the job done, and sometimes, support. Many organizations don’t see value in upgrading too often because of the overhead. I suspect many managers would prefer to get many years usage out of a platform before they change in order to minimize work that doesn’t add value to their business.

The question this week asks you about the longevity of your database instances. Think about the average instance, or even the majority of your applications and how long they will remain on a particular version.

How many years will you run a platform before you upgrade it?

Years ago I heard someone at a large Fortune 100 company say their stated policy was to get 10 years of service out of a database server. At the time I thought that was a long time, but the more I think about it, the more I think that might be a minimum amount of time I’d want from a platform.

Let us know this week what you experience, and perhaps what you’d prefer.

Steve Jones

Posted in Editorial | Tagged | Leave a comment