Category: Tools

New dbatools Release

Published 2018-09-07 by Kevin Feasel

Chrissy LeMaire announces new changes to dbatools:

Marching onward to dbatools 1.0, a ton of commands have been renamed to align with our now mature naming scheme. These changes were made in today’s release, version 0.9.410 aka regularlegs, now available on GitHub and the PowerShell Gallery.

Here’s the general idea:

DbaDatabase has mostly been renamed to DbaDb with a couple exceptions
DbaSql has been changed to just Dba, no exceptions. Think of Dba as “Sql”.
DbaConfig has been renamed to DbatoolsConfig
TempDbConfiguration has been renamed to TempdbConfig
All Configuration commands are Config except SpConfigure
DbaDacpac has been renamed to DbaDacPackage. Dac is the prefix for our data-tier application commands.
DbaDbQueryStoreOptions has been renamed to DbaDbQueryStoreOption

Some of this is in preparation for breaking changes in dbatools 1.0. There’s a lot of stuff in this release, so check it out.

Comments closed

Getting Anatomical With SSIS Catalog Compare

Published 2018-09-04 by Kevin Feasel

Andy Leonard shows off parts of SSIS Catalog Compare. First up is the catalog reference script:

As you may glean from this analysis of one script generated for SSIS Catalog References management, the Transact-SQL for scripting SSIS Catalog artifacts in SSIS Catalog Compare is rigorous and includes several existence and error-condition checks prior to performing any updates. The script is designed to be idempotent, as well, meaning the script will succeed and the results will be repeatable and predictable each time the script is executed – and that the script itself is re-executable.

Then an environment script:

The final portion of the script checks for the existence of the Environment Variables and responds accordingly. This is a three-step process:

Drop the Environment Variable if it exists.

Create the Environment Variable.

Set the Environment Variable value.

If the Environment Variable exists the script drops it. Why? SSIS Catalog Compare wants to be sure the environment variable is created with the proper data type and initial values.

And connection literals:

These messages are intended to be copied and stored in the Notes field of a ticketing system in a DevOps enterprise. Note the detail contained herein:

Script Name – the path to the file used to perform the operation.
Generated From – the SQL Server instance of the SSIS Catalog host from which the script was generated.
Catalog Name – redundant at present because all SSIS Catalogs are named “SSISDB.”
Folder Name – the name of the SSIS Catalog Folder that contains the scripted artifact.
Project Name – the name of the SSIS Project that contains the scripted artifact.
Project Connection Name – the name of the SSIS Project Connection.
Generated By – the name of the enterprise account used to generate the artifact’s script.
- Note: SSIS Catalog Compare respects the security model of the SSIS Catalog. Windows Authentication is required to perform many SSIS Catalog operations.
Generated Date – the date and time the script was generated.
Generated From – the version of CatalogBase used in the generation of the artifact script.
- Executing On – the name of the machine on which CatalogBase was running.
Deployed to Instance – the SQL Server instance hosting the target SSIS Catalog.
Deploy Date – the date and time the deployment script was executed.
Deploy By – the enterprise account used to deploy the artifact script.

Andy has put a lot of thought into SSIS Catalog Compare, so go check it out.

1 Comment

The Best Of The Underground Toolbox

Published 2018-07-10 by Kevin Feasel

Adrian Buckman shares some of his favorite creations:

sp_AGreconfigure

This is a great goto proc for an alternative to the Always on availability group GUI for changing Failover mode, Synchronous mode or even Readable options.

When you manage multiple servers with multiple Availability groups this stored procedure can save you alot of time, sometimes I find the GUI can take a long time to open but equally it can take some time to execute the command.

sp_AGreconfigure can speed this process up for you, we tend to use this as our goto for switching synchronous settings when patching/rebooting replicas but also I tend to use it in @Checkonly = 1 mode for giving the Availability group settings a once over.

Click through for this and several other useful tools.

Comments closed

Using FlashText Instead Of RegEx

Published 2018-06-21 by Kevin Feasel

Leona Zhang compares the FlashText Python library to using regular expressions:

If you have done any text/data analysis, you might already be familiar with Regular Expressions (RegEx). RegEx evolved as a necessary tool for text editing. If you are still using RegEx to deal with text processing, then you may have some problems to deal with. Why? When it comes to large-sized texts, the low efficiency of RegEx can make data analysis unacceptably slow.

In this article, we will discuss how you can use FlashText, a Python library that is 100 times faster than RegEx to perform data analysis.

Learn more on the GitHub repo. I haven’t used this before but I could see it being handy.

Comments closed

Building Observability Tools At Scale

Published 2018-06-05 by Kevin Feasel

Kevin Lew and Sangeeta Narayanan give us some lessons learned from building logging and monitoring solutions at Netflix:

We started our tooling efforts with providing visibility into device and server logs, so that our users can go to one tool instead of having to use separate data-specific tools or logging into servers. Providing visibility into logs is valuable because log messages include important contextual information, especially when errors occur.

However, at some point in our business growth, storing device and server logs didn’t scale because the increasing volume of log data caused our storage cost to balloon and query times to increase. Besides reducing our storage retention time period, we addressed scalability by implementing a real-time stream processing platform called Mantis. Instead of saving all logs to persistent storage, Mantis enables our users to stream logs into memory, and keep only those logs that match SQL-like query criteria. Users also have the choice to transform and save matching logs to persistent storage. A query that retrieves a sample of playback start events for the Apple iPad is shown in the following screenshot:

It’s an interesting post.

Comments closed

Using mssql-cli

Published 2018-05-16 by Kevin Feasel

Prashanth Jayaram shows how to install and use the mssql-cli client:

Switching to the editor mode is pretty simple and straight-forward. At the bottom of the screen, we can see the help bar which guides us through the switching process between the available editor modes. The options available for instant switching are the multiline mode, activated by pressing F3, and the Emacs mode, activated by pressing the F4 button.

To run the multi-line query in the multi-line mode, append the query with a semicolon and then press the enter key to execute it.

Use the same keys as mentioned above to turn on and turn off the editor modes—F3 for the multi-line query mode and F4 for the EMACS mode.

If you’re big on command-line interfaces, you’ll probably enjoy this client.

Comments closed

mssql-cli Update

Published 2018-05-15 by Kevin Feasel

Alan Yu announces an update to mssql-cli:

GDPR compliance

As many of us are familiar with, GDPR is approaching and we made some updates. In the past, file history stored entire T-SQL queries. However, if the query contained any secrets or passwords, it wasn’t smart enough to scrub those out. This is no longer the case, and now file history has been updated to no longer store secrets or passwords.

In addition, we have added 24-hour rotation of UserID when we collect telemetry.

Read on for other improvements.

Comments closed

Essential SQL Server Tools

Published 2018-04-30 by Kevin Feasel

Jens Vestergaard shares the most recent T-SQL Tuesday round-up:

Tuesday 3rd of this Month I invited people in the SQL Server community to share which tools are essential to their daily work. I was really overwhelmed by the number of stories that the topic triggered. 22 in total took the time to write down and share which tools they use for their work chores.
Going through 22 posts and aggregating them has been taking more time than I had hoped for, since my trusted laptop broke down – blinking codes are well and alive I tell you!

Click through for the 22 submissions as well as Jens’s set of links to the tools people mentioned.

Comments closed

Online Tools For Data Professionals

Published 2018-04-11 by Kevin Feasel

Cathrine Wilhelmsen lists five interesting online tools:

When I need to quickly create smaller sets of test data or dummy data, I use Mockaroo. It is highly configurable with over 140 built-in field types for locations, personal information, product information, technical information and much more. Every field type can be customized, and you can also use your own regular expression to generate data. The data can then be exported to CSV, JSON, SQL, and Excel formats. The interface is simple to use and understand, and you can save your schemas and data sets for later reuse.

I’m fond of Coblis and was aware of the last two, but the first two were new to me.

Comments closed

Tools For Various SQL Server Stacks

Published 2018-04-11 by Kevin Feasel

Warren Estes breaks out some tooling recommendations by stacks—that is, common use cases:

The Admin stack is probably the most important stack here. You still using maintenance tasks via SSMS? stop doing that. Rebuilding indexes every night? Maybe rethink that.

How you keep track of, monitor and do basics DBA tasks?

CMS server
Ok so this can involve SSMS, but a feature not a lot of people may not use. We use it to keep track of all of our instances and push things..oh baby baaaby! It also allows me to combine PoSh to do work against instances, gather data (historical, dmv…etc) and do a boat load of admin stuff without pointing and clicking. Heck I don’t even have to open SSMS to use my CMS server at all.

SentryOne SQLSentry
SQLSentry can automatically defrag indexes for you and update stats. You could use this instead of the below choices for this aspect if desired. Although not free, it’s an option we have in our environment and I love me some options.

Ola hallengren/Minionware
Both amazing options for backup, reindexing and checkdb. Although most places i’ve worked use Ola’s scripts by default. HOWEVER…. Minion has some pretty nice options that are FAR more configurable than Ola’s. We have mitigated some large DB issues by rolling our own code on top of Ola’s scripts. We could avoid this by simply using Minionware!

I’m a huge fan of the Minionware suite. And several other things Warren mentions.

Comments closed