The (Former) Complexity of PowerShell

When I first looked at PowerShell, it was v1.0, and I was in a TechEd presentation. The language seemed a mile past the VBScript I was using when T-SQL didn’t function well. The ability to access the .NET namespace, work with objects, and program with error handling was exciting. I played with the language a little, but didn’t find that many places to use it at the time.

Certainly file operations were much easier with PowerShell, and I built scripts to copy backup files around the network. AD operations were easy in PoSh. However, when I tried accessing SQL Server, I thought the code was complex. In fact, whether I was running a stored procedure, or performing a restore, the PowerShell code required was cumbersome. A good example is shown at the beginning of Aaron Nelson’s recently updated post on querying with PoSh. The complexity shown to just make a connection to PoSh made me think I should just write that kind of code in C#, with all the debugging and other software support available in Visual Studio. Building quick utilities with PoSh was something I’d like to avoid.

When I heard that SQL Server Powershell was being updated, I wasn’t too excited. I had visions of there being newer versions of cmdlets, but a similar level of effort to work with SQL Server. However, as I’ve played with the new cmdlets, along with the dbatools module, I see PoSh becoming easier to use than T-SQL in some cases. In fact, I’ve started to keep a command line window open (with ConEmu) and access that to perform lots of quick tasks that aren’t based on simple queries of data. Even a backup with Backup-SqlDatabase seems as simple as it might be in T-SQL, perhaps more so if I don’t have an SSMS connection open.

I know there can be a debate over whether you need PoSh as a DBA or you can get by with just T-SQL. I don’t want to take a side here, and no matter how you feel, if you can get work done, then you are successful. Your job depends on you being able to reliably write code to perform some task over and over. The language doesn’t matter.

My view is that I think there are cases where PoSh seems a better fit and places where T-SQL works really well. I want to improve my skills in both so that I can decide what works best in any particular situation, and feel comfortable in building a solution either way.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | 1 Comment

Using Out-GridView To Pick Parameters–#SQLNewBlogger

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

I was editing an article on PoSh and learned something cool. I can use Grid-View to surface an object in a pipeline and then change the items passed through to the next command.

Here’s how it works. Suppose I have a simple comment, like Get-SqlDatabase. I can run this and get a series of database. (command and output shown here).

Get-SqlDatabase –ServerInstance .\SQL2014

2017-01-03 12_50_20-powershell

I get a bunch of data, but I don’t want all the items. For example, maybe I just want the “SimpleTalk” databases.

Get-SqlDatabase -ServerInstance .\SQL2014 | Where-Object { $_.Name -like ‘SimpleTalk*’}

2017-01-03 12_51_42-powershell

I don’t want to edit this Where-Object clause all the time. I want something simple to easily fix this.

Enter Out-GridView

Instead of changing my filter, I can do this graphically. I can pipe my output to Out-Gridview, and I’ll get this.

2017-01-03 12_54_44-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView

All of my output data appears. No big deal, right? Is this useful? It sure is. Look at the filter item above. I can type in there, and my data is filtered.

2017-01-03 12_55_29-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView

How can I use this? Suppose my code is this:

PS SQLSERVER:\SQL\Plato\SQL2016\Databases> Get-SqlDatabase -ServerInstance .\SQL2014 | Out-GridView -PassThru | Backup-SqlDatabase -CompressionOption Default –Script

Here I’m getting a list of databases, passing them to Out-GridView, and then sending the results to Backup-SqlDatabase, which will return a script to back up all my databases.

I’ll run this, and then enter a filter in the grid.

2017-01-03 12_57_47-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView -PassThru _ Backup-SqlD

I can now highlight all these rows (if that’s what I want).

2017-01-03 12_59_11-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView -PassThru _ Backup-SqlD

This time I have an “OK” button in the lower right corner of the grid.

2017-01-03 12_58_23-Movies & TV

When I click “OK”, my filtered list is returned to the pipeline and send to the backup command.

2017-01-03 12_59_32-powershell

This is a quick way to work with the parameters in your pipeline in an ad hoc way. Use Out-GridView to filter and select the rows you want to return to the rest of your script.

Posted in Blog | Tagged , , | Leave a comment

Quick SQL CLone Support

I have been playing with SQL Clone for a few weeks now and liking the product. I’m looking forward to using this more and more, especially for testing ideas in development. However, there are times I have questions or am unsure of how to use things.

I work for Redgate Software, so I have an in. I can certainly email developers if I need to, but we also have this:

2017-01-16 14_29_03-sqlclone _ Redgate Slack

There’s a great Slack channel where I can quickly reach developers. This comes in handy as it seems various people at Redgate pay more attention to Slack than email or other communication methods.

No, this post won’t get you on our Slack channel.

However, when opening the SQL Clone beta, I see this dashboard.

2017-01-16 14_26_55-SQL Clone

Notice the little circle in the lower right? Click it and you get this:

2017-01-16 14_32_14-SQL Clone

Apparently there’s a semi-live chat system that our developers are using. Chris is actually one of the SQL Clone developers, and he helped me solve the registry issue a few weeks ago.

That’s pretty cool. If you type something in there, it will get sent to the developers, and if they’re around, they will respond. I sent a test, with a semi-legitimate question, and see this below my text:

2017-01-16 14_34_39-SQL Clone

Given I sent the note late in the afternoon, I’m sure all developers are gone for the day. I can hide the popup, and then will see when someone responds.

Update: in the am, I got an email from the system, at the same time, I got a reply in my SQL Clone dashboard. As you can see, my question was answered.

2017-01-17 08_29_47-SQL Clone

If I click the arrow in the upper left, I can see all my conversations, which is handy if I’m trying to report or work on a few issues.

2017-01-17 08_29_53-SQL Clone

That’s pretty cool, and it’s an neat way to get quick feedback. Assuming it doesn’t take too much time away from developers actually developing the product.

I suspect we’ll get support involved here, with escalations to developers. At least, that’s what I’d do, but for now, it’s good that our developers are responding to beta users and helping them use and test the product.

Posted in Blog | Tagged , | Leave a comment

Getting My Alias in #SQLPrompt

I got a tweet after my SQL Prompt formatting piece that said a user would be interested in upgrading from v5 if “as ‘Alias’” was transformed into “’Alias’ = “.

Well, in v7.3, this is in there, and I didn’t realize it. You can clearly see this in the formatting actions options, which is a place I didn’t think to look.


Does this work? It sure does. Let’s examine a query you might get from a VCS or a colleague. Suppose you’re working in the new WideWorldImporters database.


You prefer to see the actual columns returned in the result set at the front of the query, with an alias = format. That’s what I prefer as well, and so I want to have the column list shown as:

PrimaryContactFullName = p.FullName,
PrimaryContactPreferredName = p.PreferredName

A quick CTRL+K, Y and I see this.


My aliases have moved.

This is a great feature for those of you that need consistent formatting. There are other alias options if you prefer the alias at the end.

Update: I originally had this as single quoted aliases, but as pointed out, that format is deprecated, so I wouldn’t use it.

And if you want to change this back, you can have a second formatting preference that you choose to reformat code before sending it back to someone else.

Give SQL Prompt a try today.

Posted in Blog | Tagged , , | Leave a comment

Delaying Patches is Problematic

I was listening to a DevOps podcast from Josh Corman, of Rugged Software. Rugged Software aims to improve security by asking developers and sysadmins to adhere to their manifesto, which recognizes both the importance of software in the modern world, as well as the problems associated by not properly securing and patching software. These are duties that both developers and administrators should perform in their respective roles.

The goal of the Rugged Software is to promote best practices and constant vigilence to create and deploy secure code, and ensure that software installations remain that way. This isn’t a security state, but rather a process. A way to approach software development, deployment, administration, and maintenance to improve the security of our computer systems. This maps nicely to a DevOps approach to software development, with a focus on security. The ideas here include not just patching, but fixing underlying issues, learning from mistakes and from other organizations, improving the skills of their developers and administrators. This is a holistic approach to ensuring security.

This not only includes the software we write, but also the software products, platforms, libraries, etc. that we use. For many of us, this means the Windows host OS, the networking software and connections, http servers, vendor .NET libraries, and more. This also includes database software of all sorts, whether you use an RDBMS like SQL Server, perhaps a search service like ElasticSearch, a caching database like Redis, or some other NoSQL data store.

The idea is that we not only code securely, but also make sure we patch our software. That’s a problem in many cases. In fact, MongoDB has had a security issue for the past few years that was well publicized but still affects many installations. Far too few people have patched their MongoDB databases, and it’s a problem. Data is exposed to anyone on the Internet, and it’s likely someone is taking advantage of this to access the bits. In fact, many hackers exploit flaws in software for which patches are often available. The updates just haven’t been deployed.

Patching software is a challenge, and it seems that there is no shortage of ways in which companies deploy updates. Microsoft is pushing automatic patches. Some applications make these optional. Some apply changes in the background, some require downloads. If you use a service, then patches are seamless, but the changes may break functionality or force a new workflow on the user.

Ultimately I think that we, as an industry, need to mature with the ways in which we patch software. Customers and clients should demand better, and the impact of applying patches should go down. Quality should go up, and while that has happened with some companies, I think we need better patterns and practices that are taught and adopted by more developers. Perhaps more people sharing their techniques, templates, and practices will help others become better at building and managing our software updates.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | Leave a comment

Reminder: Extending DevOps Practices to SQL Server Databases with Redgate

I’m hosting a  new webinar on the DLM tools from Redgate Software and database DevOps. Tomorrow, Tues, Jan 17, at noon EST, Arneh Eskandari, a great sales engineer, will be demoing the tools and techniques to deploy changes.

You can register here and I’ll talk to you on tomorrow.

Posted in Blog | Tagged , , | Leave a comment

The Top 20 SQL Toolbelt Tips

Redgate produces some great software, and I’m glad that I get to use all of them as a developer and DBA. I use many of the tools each week, some daily, but I constantly find that there are plenty of features, tips, and tricks that I don’t know about.

At SQL in the City last year, I watched Tom Austin present a session where I learned a few things that will make me more productive.

The one that I need to start incorporating into my work is looking for all the references to an object with SQL Search. A quick check means that I won’t commit changes without considering all of the other objects impacting by development,

Watch all the tips below.

Posted in Blog | Tagged , , | Leave a comment

Writing the Correct Query is Important

There’s a saying in the data world: garbage in, garbage out. We use that when we can’t get good information from our database because the data we’ve stored isn’t as useful as we would like. That’s a problem, and it’s one reason why data professionals want to spend time thinking about the data we need to collect and how to store it. We want to be sure that we’ve at least made an effort to collect useful data that someone will use.

We sometimes have the data we need, but still struggle to use it effectively. I think this is an area where machine learning and similar technologies may help in the future, but there is a lot of work to be done to allow most of us to take advantage of those tools. In the meantime, many of us make do with basic T-SQL to perform data analysis, generate reports, and provide the answers to questions. When we do so, it’s important that our queries actually work correctly to answer the questions we need.

I don’t want this to be a political discussion, and I would appreciate that any comments be limited to the technical subject. I ran across a piece about a failure of the US government in determining the status of people being checked for immigration status. The interesting quote in this article was “… officials blamed computer code for the problem.” Leaving aside the implications in this case, the idea that computer code, likely some sort of query code, is not working as expected, querying the correct data, or isn’t being used properly is disturbing.

I’ve run across quite a few stories like this from various consultants that were called in to help organizations, only to find out the queries that had been used for long periods of time were incorrect. They didn’t filter appropriately, didn’t convert or aggregate data as intended, or didn’t even query the correct data.

We use databases and queries extensively in today’s world, and the growth is only going to increase. As much as I like the idea of DevOps and more frequent deployments, I also want higher quality for our software. This means that we need to ensure that our queries actually work as intended against databases. Code reviews, independent checks, using known data sets that evolve and include edge cases of data are all ways we can work to ensure we are actually writing the correct queries for our data. Above all, we need to be sure we are using test of some sort, preferably unit tests, that ensure the queries are actually the ones we want.

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 , , | Leave a comment

New Video Cards

When I want to upgrade hardware, I usually turn to Glenn Berry for advice. He’s the person I know that really watches, examines, and evaluates computer hardware. He gives good advice and upgrades often, so he actually uses the latest hardware.

One of the upsides of his hobby is that he is regularly replacing hardware. That means he regularly has older hardware. A few times I’ve asked for advice, I’ve actually ended up buying the previous generation from Glenn because he’s already upgraded. That works out well for me, since I don’t look to be on the latest and greatest.

Recently we had lunch and Glenn offered me his old Radeon R9 video card. It was ranked quite a bit higher than the GeForce GT 740 I bought last year. I didn’t need a replacement, but the price was right, and maybe I’d impress my kid a bit with the upgrade.

I went to lunch, and got a nice, geeky gift bag.

Photo Jan 13, 10 35 25 AM

Inside was not one, but two video cards. Apparently Glenn’s MicroCenter Outlet was having a special.

Photo Jan 13, 10 35 33 AM

The cards are on my desk as I need to some time to open up my case, replace cards, and then ensure everything comes back up correctly. I’ll need drivers, and no pressure to complete things quickly.

Hopefully I won’t need a new power supply, though I am tempted to get another monitor with all this horsepower and give Twitter and Spotify their own displays. Winking smile

Posted in Blog | Tagged , | 1 Comment

Do I have a Database Master Key in a database? #SQLNewBlogger

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

How can I tell if I have a Database Master Key in a database? It’s actually easy. I query the sys.symmetric_keys DMV for data. If I get a result that has a name of ##MS_DatabaseMasterKey##, then I have a database master key.

You can see this below. I’ve queried this DMV in my Sandbox database.

2017-01-11 12_21_55-SQLQuery1.sql - (local)_SQL2014.Sandbox (PLATO_Steve (59))_ - Microsoft SQL Serv

Now, what if I go to another database, say the Finances database. I see nothing.

2017-01-11 12_22_34-SQLQuery1.sql - (local)_SQL2014.Finances (PLATO_Steve (59))_ - Microsoft SQL Ser

Let’s add a master key here and then query. Note, I am not disclosing the real password here. Never do this, even in test systems.

2017-01-11 12_23_14-SQLQuery1.sql - (local)_SQL2014.Finances (PLATO_Steve (59))_ - Microsoft SQL Ser

This instance has been used with TDE, so if I go to master, I’ll get this:

2017-01-11 12_24_20-SQLQuery1.sql - (local)_SQL2014.master (PLATO_Steve (59))_ - Microsoft SQL Serve

You can see that I not only have a DMK, I have a Service Master Key (SMK), which protects the instance.

When I create my DMK, the only parameter I can provide is a password, after the optional “ENCRYPTION BY PASSWORD” keywords. I don’t name it, so I can count on the naming being fairly consistent. I don’t think that the name would change from version to version, but it could.

I’d prefer that MS not create magic numbers or names, and instead, add a column to the DMV that denotes this is a DMK.


sys.symmetric_keys –


Connect Item to add a flag –

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