Press "Enter" to skip to content

Category: T-SQL Tuesday

Generating SQL With Biml

Published 2019-01-09 by Kevin Feasel

Cathrine Wilhelmsen shows us you can do a lot more with Biml than just generating SSIS packages:

This actually happened to me in a previous job. We had a fairly complex ETL solution for the most critical part of our Data Warehouse. Many SSIS packages, views, and stored procedures queried the tables that were replicas of the source system tables. One day, we found out that the source system would be deploying a new version of their database the following day. In every single table, some columns were removed, others added, and many changed data types.
Oh.
There was no way that we could manually update all our SSIS packages, views, and stored procedures in less than a day. Thousands of users depended on our solution. It was too late to pause the source system changes.
Oh.

That story ends up with a happy ending.

Comments closed

Automation With Powershell Desired State Configuration

Published 2019-01-09 by Kevin Feasel

Jess Pomfret takes us on a journey to a desired state:

PowerShell DSC is a platform to support the concept of Infrastructure as Code (IaC).  It uses declarative syntax instead of the usual imperative syntax of PowerShell.  This means that you describe your desired state rather than the specific steps needed to get there.  There are two modes for DSC, push and pull, although pull mode offers more features and scalability, we’ll look at writing our configuration and using push mode for this blog post to keep it simple.

This post covers initial installation and some of the initial configuration, so check it out if you’re new to DSC.

Comments closed

A Docker-Based Sandbox For dbatools

Published 2019-01-09 by Kevin Feasel

Chrissy LeMaire takes us through using Docker to build a playground for learning the functionality inside dbatools:

I’ve long wanted to do this to help dbatools users easily create a non-production environment to test commands and safely explore our toolset. I finally made it a priority because I needed to ensure some Availability Group commands I was creating worked on Docker, too, and having some clean images permanently available was required. Also, in general, Docker is a just a good thing to know for both automation and career opportunities

Probably a little bit better to work on cmdlets you don’t know about in a sandboxed container rather than on production. Just a little bit.

Comments closed

Invoke-DbaDiagnosticQuery In dbatools

Published 2019-01-09 by Kevin Feasel

Andre Kamman walks through a particularly useful cmdlet in the dbatools package:

My answer to that is simple, I’m a major contributor to the awesome Powershell library dbatools. What I’ve contributed to that library are commands that will help automate the running and processing of queries from the DMV library of Glenn Berry
At some point in the life of a DBA we’ve all come accross his scripts. For the longest time I would advise people to google “Glenn Berry DMV”, and it will be the top result. 
The scripts however, come in a single file per SQL Server version and you can’t run them all in one go. You would have to select a script, run it, and paste the result from Management Studio into an Excel sheet. Glenn provides an empty sheet with tabs ready to paste the various result sets in. I’ve automated this part, hope you like it!

Click through for a demonstration of this cmdlet and the useful output it generates.

Comments closed

Death Marches Roundup

Published 2018-10-17 by Kevin Feasel

Jeff Mlakar takes us on a tour of bad project planning:

This month we had 15 post submissions about this daunting topic. Two of the posts were from people who had never posted before. To them I say welcome and I hope you enjoyed the experience.

I think, in general, you were all brave to write about this sensitive topic. I know a lot of you are consultants (which is a great way to expose yourself to a death march project) and must be careful about telling stories that could be misconstrued by clients. Nonetheless, with enough obfuscation and redaction you have brought forth some truly horrifying posts! I am going to group the submissions by the most terrible themes that many posts shared.

It is best to read this post in Vincent Price’s voice.

Comments closed

Analysis Of A Failed Project

Published 2018-10-11 by Kevin Feasel

Eugene Meidinger looks back at a big project which fell apart:

So the first issue was that the software was built in-house by another company in the same industry. Imagine, for example, if a large bakery had created an ERP system and another large bakery wanted to move to that system. Sounds great, right? Well, you run into two issues in that scenario.

First, a bakery is not an independent software vendor. Programming, by definition, is not their core competency. Which means that you may run into fragility or issues that you wouldn’t run into with a commercial piece of software. It also means that there isn’t going to be any documentation on migrating to the software or implementing it. Why would there be. If you built software for one company, why would you create scaffolding to move other companies onto it?

Second, not every business is the same. A lot of the fundamentals are the same, but you will run into many edge cases. We do invoices this way. They do workorders this way. We handle purchase orders this way. They handle inventory that way.

The way that I think about it is like a sea shell. It’s this intricate curve that’s grown over time, organically, to fit that creature. If you just try to fit a different snail or mollusk in that shell, it may not work out.

Read the whole thing.

Comments closed

Trigger Spirals

Published 2018-09-13 by Kevin Feasel

David Fowler tells a story of woe, one which is totally not his fault:

To do this, a trigger was created which would send all the details via a Service Broker message to another SQL Server, this SQL Server was used to hold details of the AD accounts and from there, changes were automatically propagated out to AD.

This was working well until one day when it was realised that any changes to account permissions in AD weren’t reflected in the personnel database.  To solve this, another trigger was created to send a Service Broker message back to the personnel database with details of the change.

This was where I came in, it was noticed that the system had started to run slower and slower, not only that but permissions seemed to be constantly changing for no obvious reason.  Were the machines finally waking up and taking over?

There’s a reasonable explanation here, for some definition of reasonable.

Comments closed

Triggers: Good, Bad, Mostly Ugly

Published 2018-09-12 by Kevin Feasel

Bob Pusateri walks us through a poorly-written DDL trigger:

First, the scope. While the application that deployed this trigger has its own database, AppDB, this trigger is firing for events on the entire server, which is what the ON ALL SERVER line means. Any qualifying event on this server, even if it pertains to another application with a separate database, will be written into this database. And what is a “qualifying event”? Literally any DDL statement. The line AFTER DDL_EVENTS specifies the very top of the event hierarchy used by DDL triggers.

So to recap on scope, this application is capturing all DDL statements on the entire server and saving a copy for itself. This application is seeing (and recording) plenty of events that it has no need to see. If this were a healthcare application or a system that dealt with PII it would be a legal nightmare, but fortunately it isn’t.

However, scope isn’t the only issue.

Worth the read.  If you use DDL triggers on the instance level, make sure you know what you’re looking for and limit yourself as much as possible.

Comments closed

Tracking Database Logins: 5 Methods

Published 2018-09-12 by Kevin Feasel

Eugene Meidinger has a medley of options for tracking server logins:

I once had to some auditing for a customer and it was a complicated, multi-stage process. We had to be able to demonstrate who had admin access and what kind of activity was going on, on the server. But before we could do any of that, we first had to identify who was actually logging on.

We get a brief walkthrough of each, and an important warning.

Comments closed

Server-Level Triggers

Published 2018-09-12 by Kevin Feasel

Shane O’Neill makes me wish Policy-Based Management ever got the love it needed:

Now, my normal attitude with regard to triggers tends to run to the negative. Which is horrible, because triggers are just like any other tool; neutral by themselves and only good or bad based on how we use them.

So, with that being said, I’ve forced myself to think of a positive use for them. So here is a time when I’ve used triggers for a “good” cause and used them to get some visibility on when new databases are created.

DDL triggers can be useful things, as Shane shows us.

Comments closed