Merrill Aldrich

SQL Saturday 108 Redmond PowerShell Session Material

2012-02-10T21:43:24Z

I am honored to be presenting my first SQL Saturday session in Redmond on the 25th of this month. The session will be a PowerShell basics class, emphasis on helping people who might be starting out with PowerShell, or feel intimidated by PowerShell’s syntax or object-orientation. I have demos and material focused on PowerShell syntax in general, for any task, and then a few demos with a SQL Server slant. The main idea is to get an understanding of how and why PowerShell syntax works, which should...(read more)

One Database or Ten?

2011-11-25T22:36:09Z

In my career to date I’ve worked as a DBA for mainly “buy don’t build” scenarios. One advantage - and hair-graying problem, if I am honest - has been that I think I’ve seen upward of one or two hundred different ISV-provided applications’ SQL Server databases. It’s a great learning opportunity, as I can see successes and failures in a large variety of designs, and sort of “fast-forward” to version five or eight of a given design pattern to see things like whether it scales, or has storage problems,...(read more)

T-SQL Tuesday 24: Ode to Composable Code

2011-11-08T06:54:05Z

I love the T-SQL Tuesday tradition, started by Adam Machanic and hosted this month by Brad Shulz . I am a little pressed for time this month, so today’s post is a short ode to how I love saving time with Composable Code in SQL. Composability is one of the very best features of SQL, but sometimes gets picked on due to both real and imaginary performance worries. I like to pick composable solutions when I can, while keeping the perf issues in mind, because they are just so handy and eliminate so much...(read more)

PASS Summit: What’s the Shape of the Future?

2011-10-19T05:53:53Z

Gushing First I have to gush a little about the PASS Summit. Skip ahead if you like - this type of stuff doesn’t make for the best reading, and I won’t be offended. The Summit was amazing as ever this year. The thing I love about it, in addition to just the wonderful, supportive atmosphere, is that most of the people involved are real professionals doing real work, on the ground, with the Microsoft SQL Server stack. There’s some marketing spin, of course, but it’s tempered. You’re as likely to hear...(read more)

SCOM, 90 days in, IV. Fixing that SQL Agent Job thing. Yeah, that.

2011-09-23T05:08:22Z

OK, we’re way over 90 days in with our SCOM implementation, but I picked a title theme, and now we’re kind of stuck with the title. In any case, today is a short and to-the-point post about how to get Agent jobs alerting on failure through Operations Manager. Others, including Thomas LaRock, have covered much of this before so I will try not beat a dead horse here. Out of the box, the SQL Server Management Pack doesn’t have SQL Agent job discovery or alerts turned on. And every DBA I know wants to...(read more)

How to Run a Series of T-SQL Scripts in a Specific Order

2011-09-12T16:36:37Z

Another post in the handy-but-not-bleeding-edge category. In the past few months I’ve seen a number of folks struggle with how to reliably/repeatedly execute a heap of T-SQL Script files, in order. One could certainly argue about why there’s the need for piles of scripts in text files, but that’s outside the scope of this post – today I want to focus on how to “get 'er done,” and save that philosophical discussion for another time. Problem: Heap o’ Script Files You receive a folder full of scripts...(read more)

Dear SQL Tools Team(s): Stop Starting Over. Seriously.

2011-08-20T22:52:20Z

I have two little boys at home, and my parents were both teachers. It gives me a strange relationship with our public schools – I am passionate about them, and the quality of education I want for my kids, but I have to keep my distance a little, lest they drive me completely around the bend. One reason it’s so frustrating: among all the complex challenges our schools face, administrators and school reformers alike are stuck in an infinite loop of starting over again before anything is finished. Before...(read more)

Handy Trick: Move Rows in One Statement

2011-08-17T23:44:22Z

Today I am posting a wee 200-level trick, taken from some work I am doing with archiving. Here’s the scenario: I have seen a few applications that have performance problems where “active” records in the database are comingled with “inactive” records. For the purposes of this post, imagine a company has a database with customer data, where the customer records are all stored in one large table. Suppose that only a small percentage of the records relate to active customers, and the rest are inactive...(read more)

Chasing the ISV, or, “That code makes my teeth hurt.” T-SQL Tuesday (ish) #21

2011-08-10T14:00:00Z

This month’s T-SQL Tuesday – a blog party dreamed up by sqlblog.com’s Adam Machanic ( blog | @ AdamMachanic ) – is about that code we’ve all written that we don’t really like to think about too often. You know the stuff. I can’t help but imagine the next poor guy who comes across some of mine and thinks, “What the … How in … Seriously?” I have two gems to share today. They share the theme, “Chasing the ISV,” because they are both SQL Agent jobs that essentially follow ISV code and constantly, 24...(read more)

It’s 2011: Do you know where your SA credentials are?

2011-07-12T20:44:06Z

Today I am assisting a vendor with an upgrade / migration, as is very common in my work. I am amazed to still see the following practices in place with software vendors, even today, even after so many well-publicized data breaches. We’ve done what we can to mitigate, but the lax practices that were suggested, and that I keep seeing from ISV’s, still take my breath away: The vendor demands that we place an executable file for setup directly on the database server. In today’s world, most SQL Servers...(read more)

T-SQL Tuesday #19: Blind Spots

2011-06-14T05:31:59Z

A while ago I wrote a post, Visualize Disaster , prompted by a real incident we had at my office. Fortunately we came through it OK from a business point of view, but I took away an important lesson: it’s very easy, whether your organization and your team is savvy about disaster recovery or not, to have significant blind spots with regard to recovery in the face of some large, unexpected outage. We have very clear direction and decent budgets to work with, and the safety and recoverability of applications...(read more)

Flash-y Re-index: Why Defrag on SSD’s?

2011-05-27T23:56:03Z

Some time ago, I blogged about how to really comprehensively re-index a data warehouse: http://sqlblog.com/blogs/merrill_aldrich/archive/2009/08/09/hexagonal-close-packing-for-your-fact-data.aspx . (Wow, 2009. Time flies.) Today I am reviving that old post, because I had a chance this week to put that technique into practice against a +/- 1 TB warehouse running on SSDs. Noteworthy: the technique reclaimed 20 percent of the disk space on the SSD disk set. This is significant on a number of levels...(read more)

Case Study: Secure Log Shipping via SSL FTP

2011-05-20T00:12:21Z

Today I’m putting up sort of an oddball solution I build a couple of months ago. We had the need to provide a reporting copy of some production databases for analysts to do ad-hoc reporting. The trick was that we needed to move the databases from a less secure location into a more secure location, with an untrusted domain boundary and a firewall in between. Log shipping to Standby Mode databases fit the bill from a business perspective, but doing it securely through the firewall was a bit of a stunt....(read more)

Scandalous II: Shh! I am De-duplicating Compressed Backups

2011-04-23T05:01:50Z

This is part II of two Scandalous posts . Watch, mouth agape, as I run with scissors, right up against prevailing wisdom! Unfollow me now, before it’s too late! Here’s the thing. There are two really outstanding posts out there on the ‘tubez that explain in vivid detail the problems with sending compressed data into a de-duplicating appliance. And these guys are both absolutely right. Everything in their posts is correct, and I would ask that, if you haven’t, you please read them before mine: First,...(read more)

Top Tools I Didn’t Know I Needed as a DBA

2011-04-07T23:01:01Z

Today’s post is an unabashedly subjective plug for a bunch of unrelated tools I have come to rely on and love. I’m not really a “tools guy,” so there are no doubt better and worse tools for these tasks out there. I’m not affiliated with any of the vendors or authors. There’s no scientific method here - I just like these, and use them daily, and you might too. Sole criteria for inclusion: if I lost one of these, I would be both sad and less productive. T-SQL Code Formatter: SQL Prompt Pro Number one...(read more)