T-SQL Tuesday 85: Managing database snapshots with dbatools

For my second TSQL Tuesday post, I wanted to introduce a nifty feature in the dbatools lineup called Restore-SQLBackupFromDirectory, which used Ola Hallengren’s scripts as a baseline to restore a server from a backup folder, however during writing this I came across a newer command that Stuart Moore engineered (Restore-DBABackup) which made it entirely redundant, expect his post coming very soon.

Since there was a bit of a last minute pivot, we are going to do a whirlwind tour of two new PowerShell commands that just hit the lineup regarding database backups, Get and Remove-DbaDatabaseSnapshot.

These along with an additional function coming soon (New-DbaDatabaseSnapshot) are going to make it super simple to create, list, and remove database snapshots on your SQL Server instance.

Command Reference

Listing snapshots on a target SQL Server

Get-DbaDatabaseSnapshot -SqlServer <string[]> [-Credential ][-Databases <string[]>] [-Exclude <string[]>]

Returns a list of database snapshots on your SQL Server.

Accepts multiple SQL Servers, and can filter inclusively or exclusively on the base database name to find snapshots.

Example

Removing snapshots on a target SQL Server

Remove-DbaDatabaseSnapshot -SqlServer <string[]> [-Credential ] [-Snapshots <string[]>] [-Databases <string[]>] [-Exclude <string[]>]

Drops snapshots from your SQL Server.

This has an additional unique parameter of -Snapshots, which allows you to filter on exact snapshot name instead of using a base database name (in the case of multiple snapshots you want to remove.)

Example

Common Parameter Reference

As with many commands in the dbatools lineup there are several common parameters:

  • SqlServer – Indicates what server you would like to operate on.
  • Credential – Allows you to include an overriding SQL Login credential (PowerShell object),
  • Databases – Operates as an inclusion filter for database names.
  • Exclude – Operates as an exclusion filter for database names.

Final Notes:

Hopefully after reading today you have two commands ready to help you manage your database snapshots, and I have whet your appetite for the upcoming restore and snapshot commands coming soon!

  • If you don’t see the filtering/parameter populating from tab completion, this indicates an issue connecting to your SQL Server.
  • Both of these commands were written recently by one of the newest contributors to the project, niphlod great job!
  • If you want to learn more about dbatools (free PowerShell module with nearly 100 SQL Server administration, best practice and migration commands included) checkout https://dbatools.io we have a growing team and we want contributions from busy SQL DBAs like yourself.
  • If you are unsure where to start or just want to join the growing community, you can find us on the SQL Community Slack feel free to send me a message if you need some help!

TIL – Nov 25 2016, xp_dirtree appends null bytes to filenames in SQL 2000

TL;DR: In SQL 2000 the undocumented stored procedure xp_dirtree returns a null byte at the end the filenames it enumerates; this null byte can will likely cause issues if you don’t strip it when consuming it with .NET tools.

I was performing some final minor fixes on Find-DbaOrphanFile(helps cleanup files unattached to your server in default directories and additional folders you specify) and ran into an issue parsing the filenames returned.

In the process of changing the code to fix some other issues, IO.Path.GetFileName was added to properly enumerate the paths returned, and when testing it in the dbatools lab (Thanks @ctrlb!!!) it was failing on SQL 2000.

After a few attempts at parsing out slashes and various .trim commands I broke out the command in the PowerShell debugger (which I will detail in another post) and I was thinking of converting everything to numeric representations to double check their character values, but there was no need, the console had done the work for me:

The Find-DbaOrphanedFile (now with an additional ltrim and rtrim in SQL instead) command now works!

As a little aside, why even test SQL 2000?

One of the first things dbatools strove to do was migrate effectively between different versions of SQL Server, and we want to support SQL 2000 up to the most recent SQL version if possible, we do this because we want to offer the cool tools as far back as possible, we know that many people will be using old versions for many years to come.

It hurts sometimes and some of the code can get a bit hacky, but I think most of SQL 2000’s differences are interesting and long term forcing ourselves to manage different versions of SQL Server is reducing the into adding new and fundamentally different databases, such as all the interesting things that are coming with vNext.

And as always, this is one of the many dangers of using undocumented stored procedures 🙂

T-SQL Tuesday 84: Exploring DBA Tools or: How I Learned to Stop Worrying and Love PowerShell

@sqlbek (Andy Yun) posted a challenge for T-SQL Tuesday 84, and encouraged people thinking about becoming a speaker to write about their experience giving their first talk, a topic they wanted to present on in the future, or even on starting the road to creating their blog and establishing themselves in the SQL space.

I wanted to flesh out an idea I have had for a first talk, which is about my experience with dbatools.io and introducing a production DBA to the value of PowerShell.

@sqlbek (Andy Yun) posted a challenge for T-SQL Tuesday 84, where he encouraged people thinking about becoming a speaker to write about their experience giving their first talk, a topic they wanted to present on in the future, or even on starting the road to creating their blog and establishing themselves in the SQL space.

I wanted to flesh out an idea I had for a my first talk that would last under 15 minutes or so. I was thinking of targeting this at a user group (hopefully with a few production DBAs where are interested in learning more about PowerShell) to foment some excitement for and share my experience with dbatools.io

who (am I)

  • I am a production DBA who focuses on performance tuning for a few thousand users.
  • I haven’t written anything you’ve read (yet).
  • I have been poking around with PowerShell for more than a year, before this experience I had written only a few small things that made it to production. Definitely beginner skill level.

have you ever wanted to…

  • Migrate your SQL Server painlessly?
  • Double check that you have the best practices configuration for component or feature X which you are not an expert in?
  • Centralize the knowledge you find scattered around on various blogs and SQL wizard cabals?

what (do I want to tell you)

I am here to introduce your to dbatools, an open source project, to talk about my experience going from pure production DBA to PowerShell convert, and to talk to you about how augmenting your skills with some of these commands can mean very little effort expended for a significant payoff.

As a production DBA, I am responsible for a host of things, and I am often a little buried under the mountain of work that piles up, along with every day work, among other things I am responsible for:

  • Backups
  • Security
  • Managing performance
  • General Administration
  • Reporting on and managing configuration drift

My environment is growing rapidly, so I started looking for tools that I could use to help me do my job in a way that didn’t leave me in a cold sweat at night.

enter dbatools

dbatools was created help busy DBAs do their jobs, hopefully encoding best practices and reducing the cargo culting in the SQL Server community.

While dbatools was originally designed to migrate your SQL Server instance’s objects from one server to another it is currently expanding into a set of commands (cmdlet reference ) that I think many of my peers in the SQL Server would find very useful.

I first heard about dbatools in the SQL Community Slack and loved the demo on the front page showing how simple a migration is:

I was a bit gobsmacked as I had just finished my own instance migration, and it certainly took a significant amount of time and effort compared to this painless invoking of this cmdlet.

A bit of hanging around and even though I felt unfamiliar with PowerShell development, I was was roped into writing my own cmdlet .

To be frank, looking back at my first draft is painful.

I was worried about my code quality given my experience, and so I asked for some feedback from the head of the project Chrissy LeMaire.

What I got back was almost unrecognizable as my code, and it was one of the first times I was able to see my feral PowerShell (that did the job, badly) turned into something that you could consider idiomatic code.

I learned more in that code review than I had in the previous year of starts and stops in PowerShell land and spent the next week picking it apart and asking questions to cement my knowledge.

In one week I had expanded my PowerShell knowledge by at least 20 times, and felt way more comfortable in working with the team to build some command or troubleshoot an issue! In the end I had a fairly robust cmdlet and with minor bug fixes it made it into the release! I was super happy to have something published in an open source project, but even more, I was happy to have found a series of mentors who wanted to help me build my skills as a PowerShell and database developer.

A few pull requests and a few months later I am confident in my PowerShell abilities to figure stuff out, work through problems, and help newbies through their first steps to contributing to dbatools.

The small scope of PowerShell I have been able to learn and utilize has made things which at one time seemed daunting now seem possible, and even easy.

Now I want to help you make my story your story, contribute to dbatools.

when

Any time you want to.

dbatools is a community that lives in the #dbatools channel in the SQL Community Slack Any time day or night people are chatting, working on code, listening on feedback to improve the tools, and answering questions for people looking for help using it.

Come and express interest in contributing, we will help you through the rest!

how (do you contribute)

  • Join the #dbatools channel in the SQL Community Slack
  • If you are just learning PowerShell, that’s ok, many of us are still puzzling things out! All skill levels welcome!
  • We need help with not just with writing code, but with documentation and testing; your first contribution could be as simple as running a command.
  • You can contribute as much or as little as you have time for, many issues are as simple to fix as adjusting one line of code.

TIL – October 22 2016

Notes on automatic variables in Powershell.

Been a few days of learning since I last wrote one of these, but I have come back to the automatic variables page on the PowerShell documentation enough times that I think I should just blog the important parts for myself.

  • $? – TRUE/FALSE if the last thing you did succeeded.
  • $_ – Something everyone uses in posh, current pipeline object.
  • $Args – all the undeclared params passed to a function, try to avoid.
  • $Error – the array of error objects that represent a stack of the most recent errors. use $Errors[0] to get the most recent error.
  • $Home – full path of home directory of the current user.
  • $Host – methods to access information about the host we are running the PowerShell command on.
  • $Matches – used with the -(not)match operators, is populated with a hash table of the matched values.
  • $MyInvocation – name, params, values, and information about the current command. Super useful for weird introspection.
  • $OFS – Output File Separator, allows you to set the default file separator for array operations. Generally better to set variables to send to split, this can have downstream effects if you dont change it back.
  • $PID – PID of the process hosting the current PowerShell process.
  • $PSBoundParameters – Nice shortcut to refer to all the params to another script or function.
  • $PsCmdlet – mostly used in advanced functions to access the current running cmdlet’s properties.
  • $PSCommandPath – full path and file name of the script being run.
  • $PSDebugContext – if $null, debugger is not running.
  • $PsHome – path to PowerShell install dir.
  • $PSScriptRoot – directory where script is being run from, avoid if you want to support ps2.
  • $PsVersionTable – returns read only hash table about the current ps session.
  • $Pwd – a path object representing the current directory.
  • $StackTrace – shortcut for the stack trace of the most recent error.
  • $This – refers to an object that is being extended in a script property or method.
  • $true/$false/$null – use boolean login on all of these, nothing new here.

Read (slightly) more at about_Automatic_Variables

TIL – July 7 2016

I am starting a new mini-series called “Today I Learned” to start blogging a bit more about simpler topics, not so much about things that will definitely help you as much as things that helped me (and I will probably forget about in 3 months.)

Today’s topic is about how to get VS Code working with PowerShell 2.0.

Today I learned that VS Code (while awesome) has a few kinks to work out if you want to mix old and new powershell idioms, and its not 100% straightforward how to fix it.

To get to this point I already downloaded Windows Management Framework 5.0 (the announcement on the VS Code site points to a dead link), got VS Code, upgraded Git, and installed the Posh utility when I opened my first file and it suggested I download the extension (pretty cool that.)

I started off having a blast, but as soon as I used Invoke-SQLCommand things got a bit messy:

"Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information."

I have seen this error before and it usually involves modifying an app.config to say “I am totally ok with you mixing modes or whatever, just do what magic I need.”

I found a few related errors until I stumbled on https://github.com/adamdriscoll/poshtools/issues/192 which broke down where I should be looking (~/.vscode) and that I could create a file named

C:Users\%USERNAME%.vscodeextensionsms-vscode.PowerShell-0.4.1inMicrosoft.PowerShell.EditorServices.Host.exe

and place the following content within it:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup useLegacyV2RuntimeActivationPolicy="true">
      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

This did not work.

After a second scratching my head I found that it looks like VS Code’s new version of powershell is using a different folder name within the hidden vscode folder, so if you check

C:Users\%appdata%.vscodeextensions

and navigate to the bin folders within it before you make your file you will be fine.

Also, make sure the app.config file matches the name of the executable being invoked (plus the extension .config) in this case I had both Microsoft.PowerShell.EditorServices.Host.exe and Microsoft.PowerShell.EditorServices.Host.x86.exe, made both files, and now I can run everything without issue.

Using Flamegraph.pl to create Flame Graphs for SQL Server Profiler, Extended Events, and more

A friend of mine was recently discussing using Flame Graphs in a project of his, and it inspired me to see how I could abuse the format to present a visual model of SQL execution.

Originally I had thought of building my own interface, but apparently the author of the above site also maintains a great repo which allows you to skip some of the more difficult steps: https://github.com/brendangregg/FlameGraph.

There is a lot of detail there about profiling software with specific tools and how to convert their output to something the tool understands, but at its core the “folded” format is fairly easy to target and if you are fine with some text manipulation you can produce your own FGs and re-use the SVG generation, search, and zoom components of this tool.

What’s the format?
It doesnt take too much effort to suss out from flamegraph’s source:
From https://github.com/brendangregg/FlameGraph/blob/master/flamegraph.pl
# frame in the stack is semicolon separated, with a space and count at the end
# of the line.

Data analysis on a list of active domain names

A friend of mine had an idea for a project based on new and upcoming domain names and wanted some help transforming the Zone files into something a little more intelligible.

While we wait for confirmation, I used an existing scans.io dataset to get him his first results, feel free to download the data and run some queries with my simple domain queries github project.

Script for non-production restores

This is some code came together as a result of straightforward requirements; a team needed to spend a significant amount of time doing manual restores, and were tired of the GUI nonsense from SSMS. But also wanted some basic flexibility due to the fact that the servers configuration may change significantly (but not during the restore.)

The code makes some assumptions:
* Assumes your SQL service account has access to the file location for the backup
* You have the permission to create new databases and select from sys.master_files (CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.)

@mobileck Are there any certs or books you’d recommend to aide in my quest to become a DBA?

It depends on if you are more of a production, developer, or devops DBA, but I would definitely read everything below:

Dirty tricks in SSRS authentication

Configuring SSRS authentication to IIS can be a pain, here are some things I have done in a pinch, and almost all of them are terrible, but if you repeatedly get a 401 unauthorized with SSRS, these might be able to help.

SSRS native mode on one domain, IIS on another – DMZ

In this mode, you are normally advised to implement basic authentication, and just pass a username and password in… in some instances you may be unable to use basic, and are forced to use NTLM.

In this situation a simple method to bypass this is to create a local user on both boxes with the same password, and have that user be the one the report server grants access to, and which user is running the web server process.

Make sure this password is strong, and doesn’t change.

SSRS native mode, same domain, IIS running as local system

In some cases you may find that the system is configured to run the IIS process as local system (generally not recommended). If this is the case, you can actually grant the entire computer name in the report server permissions via its hostname and a dollar sign.

Example:

MachineHostname$

Additional Troubleshooting for Native Mode

  • Enable the HTTP log for the report server , it can help enormously to isolate what’s being accessed, where, when, and sometimes who.
  • Double check each report folder in the hierarchy’s permissions for your user, while you may think you have permission, its quite possible you don’t and that someone overrode the parent permissions.
  • If you get a FORBIDDEN message instead of an unauthorized, the user you are trying to run the report as may need escalated permissions on the SSRS application directory itself (on the SQL Server).
  • When all else fails you can grant Browser permission to “Everybody”, but be aware that means anyone can request information from your reports, and is a really really really bad idea. (If it doesn’t work, that means you are not even making the request from the same domain)