Press "Enter" to skip to content

Month: March 2017

Invoke-SqlCmd

Published 2017-03-16 by Kevin Feasel

Mike Fal defends Invoke-SqlCmd’s honor:

Why do people gripe so much about Invoke-SqlCmd then? Well, to understand this is to understand the history of SQLPS. For a long while, the SQL Server module for PowerShell was klunky and buggy. There were a lot of challenges with loading it and using it, such that many scripters decided to throw it out and write their own functions. In many cases, PowerShell folks would skip Invoke-SqlCmd not because it was bad, but because it came packaged with the rest of SQLPS and they wanted to avoid the entire module.

Now that the SQL Tools team has been reworking the module as SqlServer, this has become less of a concern. The module is less of a burden to load and the other components do not get in the way. There are also improvements and updates to the code to make it work better and serve more needs.

Mike makes good points, like how you can pretty much guarantee that Invoke-SqlCmd will be available, whereas you can’t always guarantee that third-party libraries (even if better) will be available on all systems at all times.

Comments closed

Local Filesystem Source Control

Published 2017-03-16 by Kevin Feasel

Steve Jones shares a tale of woe related to source control neglect:

One of the first things I found was that all our stored procedures in the production server were encrypted. I wasn’t sure why, since we hosted our machines, but that wasn’t a big deal.

Until it was.

One day we had an issue on one of our SQL Server 2000 servers (we had two, supposedly identical). In troubleshooting and putting some sample data in both systems for a fake customer, we got different results. Hmmm, not what I wanted to see.

I checked the VCS (SourceSafe at the time) and checked out the code. I then loaded my test data and … got a third, different result. Now I was concerned as this was a production bug that was delaying work for a customer.

I’ve become convinced over the past few years that having all of your code in source control (including database scripts!) is a key differentiator between a good work situation and a bad work situation.

Comments closed

Indexing Woes

Published 2017-03-16 by Kevin Feasel

Shane O’Neill relates a tale of trying to create an index with a SQL Agent job.  Easy, right?

Now I’m angry too since I count these failures as personal and I don’t like failing, so I get cracking on the investigation.
Straight away, that error message doesn’t help my mood.
I’m not indexing a view!
I’m not including computed columns!
It’s not a filtered index!
The columns are not xml data types, or spatial operations!
And nowhere, nowhere am I using double quotes to justify needing to set QUOTED_IDENTIFIER on!

SO WTF SQL SERVER, WHY ARE YOU GIVING ME THESE ERRORS???

Read the whole thing.

Comments closed

Coalescing In DocumentDB

Published 2017-03-16 by Kevin Feasel

Melissa Coates shows how to use the null coalesce operator in DocumentDB to provide default values for missing attributes:

This is a quick post to share how we can use the coalesce operator in Azure DocumentDB (which is a schema-free, NoSQL database) to handle situations when the data structure varies from file to file. Varying data structure is a common issue in big data and analytics projects. A schema-free database like DocumentDB allows us to ingest and store the data with varying structures without a lot of upfront effort. However, accommodating these varying data structures is challenging later when we want to analyze the data. When querying the data (think Schema on Read here), I do need to impose a consistent structure on the data to perform analytics.

Read the whole thing.

Comments closed

Disk Space Shenanigans

Published 2017-03-16 by Kevin Feasel

Meagan Longoria writes about an outage due to improper file layout:

One day, a manager asked me if I could help on an urgent matter: the application suddenly could no longer execute transactions on the production database and the database connection was intermittently failing. The system admin was busy with other duties, so I was the closest thing they had to a DBA.  All they could tell me was the production database had crashed and they got an error message about insufficient disk space.

Click through for the rest of the story.

Comments closed

Replication And TDE

Published 2017-03-16 by Kevin Feasel

Drew Furgiuele looks at how replication interacts with Transparent Data Encryption:

But what happens if we set up a transactional replication publication on this database and do a snapshot? Remember that when you create a publication, your distributor and subscriber(s) need to know which network share (or FTP server) to drop all the data and schema definitions to so they can be read in by the distribution agent and recreated. In my example, I’m dropping them to a network share. Once the snapshot completes, let’s go check out our subscriber database…

Uh oh. The same query returned zero results at the subscriber. Which means no encryption! Replication won’t replicate encryption, at all. So if you have a requirement to encrypt your data at the source, you’ll need to do it on your subscribers too.

Drew points out a couple important gotchas which might lead to you exposing information you didn’t intend to make available.

Comments closed

NULL Parameters

Published 2017-03-16 by Kevin Feasel

Riley Major turns this T-SQL Tuesday into thoughts on procedure parameterization:

But what if the caller wanted the date to be “empty” (i.e. 1900-01-01)? And what if a NULL is passed?

In our environment, we’ve disallowed NULLs from our table fields. We understand that NULL is actually information– it says that the data is unknown– but we believe that for most data fields, there are non-NULL values which just as effectively represent unknown. Typically, 0’s and empty strings (and the “blank” date 1900-01-01) serve that purpose. And those values are more forgiving during coding (they equal things; they don’t make everything else “unknown”), and we accept the risk of paying little attention to which parts of our logic touched “unknown” values.

It’s an interesting look at dealing with optional and default parameters within procedures.

Comments closed

Supersized Tables

Published 2017-03-16 by Kevin Feasel

Deborah Melkin tells a story of a design battle she lost:

The programmers came to me and said we need to add a large number of columns to this table for one piece of functionality. It would more than double the total number of columns on the table. Oh, and all of the new columns would be NULL since we would only need to populate them if they were using that functionality and even then, not all of them would require data. The final result would be that 65-75% of the table would end up having nullable fields with the majority of those having NULL for the value.

I said what I think any sane DBA would say to this request: No.

Click through for the rest of the tale.

Comments closed

The Guru

Published 2017-03-16 by Kevin Feasel

Adam Machanic wins this T-SQL Tuesday:

Back in my basement hideout, I spent the next couple of hours exploring the network and figuring out which server to connect to. The CTO was right; I did have enough access. I was sysadmin on the production SQL Server and had full admin access to the app server. I logged in to the app and with the help of a Profiler trace managed to figure out one of the main slow stored procedure calls that occurred any time someone saved a change via the user interface.

Pasting the procedure call into SSMS, I turned on Actual Execution Plan, hit F5, and got ready to see indications of a few missing indexes. I was ready to walk back upstairs, gloat to the CTO, and ask for a better workspace so I could continue to help. What I didn’t expect was what actually came back: Not one execution plan, or two, or three, but hundreds and hundreds. The scroll bar become progressively smaller as time clicked by and the elapsed counter did not stop running. All I’d done in the application was change the name of a single field. What was going on?

This was an amazing story full of cringe-worthy moments.

Comments closed