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 make it easier to understand script samples one might find on the web, or get started and get productive quickly.

If you’d like a preview, or can’t make it to SQL Saturday Redmond, there’s a copy of the presentation and demos available for download on the SQL Saturday site: http://sqlsaturday.com/108/schedule.aspx > “PowerShell 101.”

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 hassle and complexity.

What is composability? Here’s a quick example. Stored procedure calls do not compose. That fact creates pretty ugly workarounds like creating temp tables, guessing at their schema requirements, and using insert … exec to get back results from a stored proc -- just to work with their results in any meaningful way:

SELECT col1, col2, col2
FROM dbo.mytable t
INNER JOIN dbo.myview v ON t.keycol = v.fkeycol
INNER JOIN EXEC dbo.storedProcThatJustReturnsRows p 
    ON oops.thatWill = NEVER.work –- #FAIL

This is because the results returned from a stored procedure are not really defined in terms of shape: the server cannot tell what to expect pack from a stored procedure, if anything, and therefore cannot allow that result to be composed in the context of an enclosing SQL Statement. That limitation is why I listed insert-exec among my Top 10 T-SQL Code Smells.

On the other hand, structures that do have a well-defined “result shape,” including views and table-valued functions, are composable:

SELECT col1, col2, col2
FROM dbo.mytable t
INNER JOIN dbo.myview v ON t.keycol = v.fkeycol
INNER JOIN dbo.myTVF( @someParameter ) ON ...

This provides a huge amount of flexibility when writing code.

The very best composable behavior is when the code also allows the SQL Server query optimizer to unpack and optimize the content of the composed structures in the larger context of your query, which is true in most cases for views and inline table-valued functions. There are some specific limitations that can prevent this type of expansion/optimization, and, of course, it’s important to be aware of those issues from a performance point of view. But in general, if you can allow the optimizer greater flexibility in choosing a query plan, it at least has a chance at improving performance.

Example: suppose I have a stored procedure that returns 10 columns and 100 rows, and I only really need the values from 2 columns in 25 rows. If I have to execute the proc, output all the results to a temp table, then work on them, there’s a decent chance the optimizer could factor out quite a lot of the work involved if it were given more options. Encapsulating the procedure code so fully as to force the optimizer into a specific execution sequence can actually limit the ways the query could be executed, and thereby “defeat” one of the server’s primary and most valuable features.

I don’t mean to argue that stored procs don’t have a place, because they do. I also would not argue that this type of inline optimization always works, because it doesn’t. But in the main I prefer to take advantage of composability when I can, only optimize back from that where there’s a legitimate reason to do so, and try in those cases to understand why optimization isn’t working.

(And three cheers to the SQL Server team for following this philosophy in moving to dynamic management views and functions and away from diagnostic stored procs!)

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 know when SQL Agent jobs fail, unless they work in such a catastrophically awful environment that jobs are made to be ignored, and fail either all the time, or by design. Personally, I want to know about every failure, and then maybe exclude some specific problem jobs or servers using overrides.

So here’s the skinny, to get to alerting on individual job failures:

1. You must override the discovery of SQL Agent jobs so that they are picked up by SCOM at all, when you first configure the management pack. See http://skaraaslan.blogspot.com/2011/08/how-to-monitor-sql-agent-jobs-using-sql.html or Google for how to do that. If not, individual jobs are ignored, and only the state of the SQL Agent service itself is discovered and reported.
2. Once the jobs are discovered, it’s tempting to look at the Last Run Status monitor as your savior for catching failed jobs. The problem is, that thing stinks in real life. We tried it in a large environment, and it works only about half the time in real-world scenarios. It makes a kind of half-hearted check every so often, and seems oblivious to details like jobs being enabled or disabled. Not granular enough.
3. You’ll want to enable the rule, which is disabled by default, SQL 200x Agent > An SQL Job Failed to Complete Successfully. This will ring a bell on each and every job failure, which is what I want. It might be very noisy at first, so be careful when you switch it on, but you will hear about each and every failed job outcome.

Lastly, it’s helpful to set all your jobs to write to the event log on failure. I know what you may be thinking, “How am I going to go turn that setting on in all the jobs I have across the enterprise?” Answer: multi-instance query and some dynamic SQL. The basic query we want to run is fairly simple:

EXEC msdb.dbo.sp_update_job 
    @job_name=N'Some Job', 
    @notify_level_eventlog=2

How do I know where to find that code? I cheat: I open the associated dialog box in SSMS, against a test server, make the change for one object, and script out the change instead of applying it. I then hit BOL Search with the code from the resulting script, to be sure I know how the script works and understand what’s going on. The quality of SSMS-generated code is uneven, so it’s very important to understand how things actually work, but this is a simple starting point.

Next, we need a script that will run that stored proc for all SQL Agent jobs. A simple way to do that is just to select from a system table or view that has all the required objects, and use the result to compose a series of statements, one for each object. If this gives us the list of Agent job names:

SELECT [name]
FROM msdb.dbo.sysjobs; 

Then this combination will compose a series of sp calls and execute them:

DECLARE @sqlcmd nvarchar(MAX);
SET @sqlcmd = '';

SELECT @sqlcmd = @sqlcmd 
      + 'EXEC msdb.dbo.sp_update_job @job_name=N''' 
      + REPLACE([name], '''', '''''') 
      + ''', @notify_level_eventlog=2; ' 
      + CHAR(13)
FROM msdb.dbo.sysjobs;

-- BE VERY CAREFUL WITH THIS:

EXEC( @sqlcmd );

Note the crazy number of escaped single quotation marks – common in Dynamic SQL, some method often is required to handle quotation marks that might be in the names of objects being manipulated. The code is ugly, and would not be happy production code for a real application, but for a basic administrative task it’s quite serviceable.

With this combination, you should have events in the log for each job failure, and a working rule in SCOM to collect that information and alert as appropriate.

Happy monitoring!

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 one curriculum is complete, someone wants a new one. A new educational idea arrives on the scene before the last one even gets a chance to be evaluated. We have to break eggs to make an omelet, but all we do is the egg-breaking part. And our school system is the very apex of the idea, “too many cooks in the kitchen.”

Sadly, the people working on all the flavors of SQL Server management tools seem to me to be stuck in the same type of self-destructive loop. For now six or eight years, depending how you count, the management tools for SQL Server have gone sideways, or around in a circle, instead of forward.

Five Versions 2.0 != One Version 10.0

This post was prompted by a short exchange I had on Twitter this morning. Someone asked a simple question: Why is there, again, another management & dev tool for SQL Server. My off-the-cuff response was that the team at Microsoft keeps on starting over again instead of moving forward. But the more I thought about it, the more I realized that that is literally true: through Management Studio, BIDS, Team System/Data Dude and now Juneau, and even to some degree Crescent, the various tools teams continue to slide sideways, and deliver sort of disjoint, bland, V 2.0 mediocrity. We need version 10 at this point, not version 1 or 2 again. Really.

This isn’t an attack on any of the developers working hard on those tools and features. Some of the specific features and capabilities are really interesting. The problem is with the process – just as in some fundamental public school issues. In the current vernacular:

(Smart People + Good Intentions) * Deeply Flawed Coordination = #EPICFAIL

There are a lot of good ideas. There are a lot of challenges. There are a lot of conflicting requirements and conflicting opinions about what a tool like this should be. That’s what design is for. If the problem were simple, design would not be necessary. This is just a design problem.*

80% of Good Design is Synthesizing Conflicting Requirements

So it’s a hard problem. Administrators want administration tools, monitoring, troubleshooting. Developers want development tools, code writing environments. BI people maybe want graphical programming tools like BIDS SSIS projects. We all want the tools to be elegant and simple to use. I get that. But that doesn’t mean you’ll solve the problem by ten years of starting over again, annually, creating different flavors of the same tools. It gets solved by leadership, visionary design, and synthesis. It is possible to make one environment that meets all these needs. But it takes conviction, courage, time and, most of all, design.

What do we have now, six years or so into the SSMS “era?”

• Still total inconsistency in the way SSMS treats basic features like scripting objects.
• One passable query writing environment in the SSMS query window itself, which somehow(?) has not made it into any other place in the whole suite of products. There is no even C- level query editing capability in any MS BI tool, for example. It’s not even in the Table Designer. But the code’s been sitting there in SSMS all this time.
• B- Intellisense, with better provided by a third party.
• No code formatting, again with a fine third party solution.
• The object tree in SSMS has had the same cruel joke of a design, that most people seem to either barely tolerate or hate, this whole time.
• Activity Monitor. Ugh.
• A query plan viewer that is so stale, so old fashioned, that a third-party company (and thank you SQL Sentry) could walk in and create a free one that is many times better.
• Decent diagnostic data from the engine (DMV’s) with NO decent UI associated.

The reasonable thing would have been to put someone with some design sense on this problem to improve the tool. Version 3, version 4, version 5. Forward progress.

Instead, people complained from outside MS, and people within perhaps had some new ideas, but instead of having the courage to synthesize those challenges into a better product, we keep going in a circle. So I am begging: would someone please stand up over there in Redmond and say, “enough.” I want SSMS 3.0.

* This is the thing Steve Jobs, love him or hate him, “gets.”

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 x 7, mop up problems with applications.

The first example I’ll put up here is one I blogged about briefly last year. We had an ISV system that was riddled with pretty horrible triggers, which I was hesitant to touch for fear of the “Jenga effect.” Pull just one line of trigger code out and – who knows?

Said system had a production issue because a value stored in two places, that was supposed to match (winning system here) no longer did. I discovered that the underlying reason was that the tables in play had triggers that could not handle a set of rows, but just one row at a time – a too-common anti pattern.

IF Problem GOTO Workaround

So, what to do. Production issue. Deep breath. Here goes:

Again:

UPDATE TOP (1) dbo.someTable SET field3 = 'NEW'
WHERE field2 = 'NEW' AND field3 = ''

IF @@ROWCOUNT > 0 GOTO Again

Into a SQL Agent job with that, executed once every few minutes.

So. Wrong.

Also fixed the issue. And it’s still in use, I believe. I try not to think about it.

IN ( Pain, Suffering )

The second bit of pure awesome I have to offer today takes a bit more explanation. I think I should anonymize this one to protect the guilty. (That first example has, believe it or not, the real column names.)

Another ISV provided a system to one of my past employers that, let’s say for argument, provided a web application to manage distributed offices in locations all over the country. The structure of the system required that people at the company be able to view one or a few of these locations’ data, but not the data from other locations. So the system had a row-level security mechanism to filter the data by groups - a fairly standard idea, but theirs was not a typical implementation at all. The way the initial version of this software operated was, roughly:

1. An administrator for the application would change some aspect of the security groups using the web application.
2. The system would, upon clicking Submit, create (or recreate) one or several view definitions, inside of which there was a SELECT with a static IN (,,) list of any and all the locations that a given group or individual could see on the system. The view would provide the row-level security. The IN(,,) list could have a few or hundreds of items. A classic data-as-code anti-pattern.
3. The system was very busy, so altering the view would often fail or cause … “side effects.”

The company actually instituted a rule that security changes like this could only be made after hours, and made staff stay late to do that work. If this happened during the day, the system would generally grind to a halt, and none of the 1,000 or so concurrent users could do anything. Why? The IN() lists in these views could have hundreds of values, and they worked against millions of rows of data. That meant that with no meaningful stats to work with about these embedded lists, the query optimizer would always pick a terrible query plan for vital processes on the system.

The ISV almost fixed this problem. Nearly.

When complaints surfaced, they went in and added a more scalable feature that would use a many-to-many table to store the security relationships. The table could be indexed, and would have stats, which was wonderful, and it solved the performance issue.

When they used it.

Here’s the rub: the were so fearful about disrupting their customers’ existing deployments – a noble but ultimately doomed posture – that they kept the old view mechanism too. Any old features of the product used the old system, while new or rewritten features used the new system. Now their code had to maintain both systems, and attempt to be sure they matched. Which they did. Mostly. Except that time we had to have one of their developers spend a week untangling the thing. But I digress.

If I recall, and this was a few years ago, so I may have this wrong, it seems like their first table-based implementation didn’t exactly work, and they had to create a second one, and then they kept all three systems, with some massive stored procs to keep them all in sync. And the proc worked, except when there was a logic problem like duplicate rows in the tables, and then it didn’t anymore.

ALTER VIEW dbo.FixMe

This is where our little DBA group came flying in with our superhero capes and a brilliant, yet horrible, workaround. I can’t quite remember who created this, so my apologies if I cannot credit the specific individual, but I know I, erm, “tuned” it over the years. I also vividly remember the phone calls and the shouting when this workaround was not operating as designed.

We made a SQL Agent job to run every few minutes. The code for the job was, essentially:

1. Examine the text for all the views in the database, via syscomments, where the view name matched some text pattern that makes it look like one of these security views.
2. If the view definition contains one of these IN(,,) clauses then rewrite the view, via dynamic SQL, as a join between the security tables.

Like the example above, this job would basically sit there and chase the underlying system, and if a security change was made, it would locate the offending view definition and rewire it on the fly to use the more performant and optimizer-friendly tables.

I think the vendor has since fixed the view definition issue. Happily, I don’t have to work on this system any longer, so I can’t say whether the views themselves persist to this day.

Here’s the code, unvarnished but anonymized. I found it on an old flash drive:

IF EXISTS ( SELECT * FROM syscomments sc1    
   INNER JOIN sysobjects so ON so.id = sc1.id   
   WHERE so.type ='V'    
      AND so.name LIKE 'FOO\_%' ESCAPE '\'    
      AND sc1.text LIKE '% IN (%)'   ) 
BEGIN     
   SELECT     'ALTER VIEW dbo.' + so.name + 
      ' AS SELECT f.* FROM dbo.foo f, bar l ' +
      ' WHERE f.id = l.id ' +
      ' AND l.id = '     +  
       RIGHT(so.name, len(so.name) - charindex('_', so.name))    
            AS alterViewCommand     
   INTO #viewCorrections     
   FROM syscomments sc1     
   INNER JOIN sysobjects so ON so.id = sc1.id   
      WHERE so.type ='V'    
      AND so.name LIKE 'FOO\_%' ESCAPE '\'    
      AND sc1.text like '% IN (%)'     

   DECLARE @sql VARCHAR(4000)     

   WHILE EXISTS( SELECT 1 FROM #viewCorrections )    
   BEGIN
         SELECT TOP 1 @sql = alterViewCommand FROM #viewCorrections

         PRINT @sql

         EXEC ( @sql )

         DELETE FROM #viewCorrections WHERE alterViewCommand = @sql

       WAITFOR DELAY '000:00:05'
    END

    DROP TABLE #viewCorrections
END

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 and data is a real, primary objective at my workplace – and still this was a take-your-breath-away, eye opening kind of experience. Here are some common places I have seen such blind spots in my past work. Perhaps you can have a look around you and see if you see these wherever you are today, and maybe some ways to combat them:

Invincibility Blind Spot

I think most of us have worked at a place where the leadership is just oblivious to the idea that anything damaging could happen to their business from an IT failure. I did some consulting at a place where one of the owners flat-out told me, “I’m not sure all this technology really helps us. It’s certainly not essential. We could go back to pencil and paper and be just fine.” I knew just from watching their operation for a short time that if they lost their technology, they would probably go straight out of business. Their server was the type you see pictures of as jokes on the web – in an un-conditioned room, with an oscillating fan aimed at it, on a rickety shelf, shared password, etc.

This can be hard to combat. A typical organization like this would look at ideas like backup or disaster recovery, and immediately balk because it “sounds expensive” or “there isn’t time for that, because it’s not real work.” And they aren’t always small companies – one place I did work for had 1500 employees and essentially no DR strategy other than some half-hearted tape backups.

The only way I have gotten traction with these cases is to do two things:

1. Make sure the leadership hears the argument for DR from someone they trust and that has credibility with them. In some cases, that was me, once I built trust working with them. In other cases the argument, right or wrong, had to come from someone else – perhaps another business person, not even in I.T. – for it to carry any weight.
2. Once you have that voice that carries real weight, walk the leaders of that organization through a visualization of what could really happen if they lost their infrastructure: the sending people home, the loss of credibility with customers, the real, no-hand-waving, no-magic amount of time it would take to recreate a functional system, the work lost. It has to be real, and it has to burst that imaginary bubble that can surround computer technology and make it seem like it’ll just somehow keep working. A building fire is usually a good scenario, because you don’t have to be in IT to relate.

Ego Blind Spot

The Ego blind spot is somewhat trickier. The place this can lurk is with capable IT staff who do have a mandate to make DR work, but whenever they are approached about discussing DR or testing their systems, may become defensive or make excuses. There can be an undercurrent in the conversation that insinuating that DR isn’t “covered” by their systems is some sort of an insult. Often that undercurrent actually comes from insecurity – there may really be gaps in their systems that they privately worry over, but don’t want to crack open and solve because either a. it’s embarrassing or b. they don’t relish the extra work and risk it could take to reconfigure a running system. These folks generally have the best intentions, but getting at the gaps in the technology can be a real problem, just because of personalities.

Here the only remedy I know of is sociological – the business continuity leader (or the IT team lead, if it’s the same person) has to have the leadership skills to win these folks over. The technical staff have to be in a position where finding the DR gaps and improving their systems is something they perceive will provide an opportunity to demonstrate, and not threaten, their skills. It has to feel like a worthwhile project. It’s almost impossible to get at the underlying problems any other way. The leader in this scenario will need their technical expertise, their on-the-ground view of how systems really work to even locate the issues, and for that, grudging cooperation will not do. Working DR has to become a real part of the staff’s fully owned, personal priorities. If the person who knows the low-level detail about how a system works is armed with DR know-how, and committed to making DR work, the gaps will disappear. If, on the other hand, the technical people don’t want to see the gaps, and the leadership isn’t capable of seeing the gaps, the gaps will remain until some incident exposes them.

Magic System Blind Spot

This is an interesting one – the Magic System blind spot is essentially a blind faith that some of the latest gee-whiz tech is the silver bullet that will save everything. “We have DR covered because we virtualized.” “We have DR covered because we replicate.” “We have DR covered because we load balance.” “Disaster can’t touch us – we have a SAN!”

“Cloud.”

I’ve seen naïve, young people succumb, I’ve seen leadership (the ones out of touch with the technology, generally) succumb, but surprisingly I have also seen savvy people I would never have expected succumb to this.

The remedy here looks simple to a staffer, but maybe difficult to a leader: no matter what a vendor claims or advertises, what we imagine a magical system can do, you must have someone available who, impartially, knows how that technology works enough to dispel the magic. All this stuff works for a reason. Using dedupe? Make sure someone on staff understands how that really works. Snapshots? How. Relying on virtualization for DR? Exactly how does that work? Only by unpacking how these systems do what they do can you be sure they will work at crunch time.

Devil in the Details Blind Spot

Lastly, we have the blind spot that is the nemesis of us all. The one present in every organization, extremely difficult to stamp out, “When we fail over, when the data center goes down, will it work?” This is a simple question, but here’s why it is so difficult: every system has so many moving parts, each of which perhaps requires specialized knowledge, and a seemingly small detail that nobody thought of can absolutely wreck the DR process when you have a real incident. It’s very easy to have a scenario where practically everything works except that one tiny thing that prevents it all working – the database is there, the web servers are up, we have network connectivity and name resolution but everyone forgot that the encryption key to the whoozit has to be loaded into the whatchacallit. It’s really easy to miss something. And because the something that was missed is small, maybe nobody took it very seriously.

Remedies for this are more difficult. For some organizations with the finances, it might be possible to actually run multiple data centers and, in fact, fail production systems between them. That would ensure the design is sound. Most of us, though, have to use test systems and then just try our darnedest to be really careful.

If you can’t test with production systems, the next best thing would be to have a pre-prod or staging system that is comparable to production where you can do rehearsals. Such a rehearsal can be a drill around some imaginary scenario, say “It’s 5:00 am and Data Center A is on fire. (This is a drill.) Go.”

Failing that, the only recourse – and it’s much less accurate – is a careful and detailed tabletop visualization. Visualizations like this are great, and valuable, if they are run well. Vital ingredients:

1. Effective leadership that can persuade people to check egos at the door and take it seriously. Without buy-in, you never get to the details that matter.
2. A facilitator that can ask relevant but probing questions, in order to eliminate the inevitable hand-waving that masks gaps in the system. Example: “At this point we would load the logins into the DR SQL Server.” The facilitator should not say “OK.” She should say “From where? How? Who?”
3. Detail. Everything in a reasonably sane organization works at a high level. It’s only by diving into the details and making a visualization real that you uncover those small, system-breaking gaps.
4. Note takers. In every tabletop I’ve attended, a huge number of issues were uncovered, and in order to get the most value, it’s important to capture them all right then, in the room. Otherwise they escape!

Do you see one of these four blind spots in your organization? Others? Any tips or processes for stamping them out? I’d love to hear.

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.

It’s worth noting that log shipping to standby isn’t always a good solution to this problem – because the users of the reporting database have to be “kicked off” each time a log backup is restored, you can’t really get close to real-time reporting with this design. In our case, however, day-old data is more than sufficient, so we just stage the log backup files during the day, and then restore them all at night to bring the standby databases up to some point in time a few hours behind production.

For this solution we settled on a secure file transfer using SSL + FTP (aka. SFTP). This basically makes an SSL connection to an FTP server, and then uses that secure channel to transmit the files using FTP. Other methods like Secure Copy (SCP) could also be made to work, I think. The trick was to locate the details about how to do an SFTP upload with PowerShell, and then to devise a way that the upload could incrementally push only new log backup files and not repeatedly upload the same files. We also use “vanilla” log shipping on the system in question for disaster recovery, so we needed to tap into that set of files, without disrupting it.

Overall, the system looked like this:

1. Our source SQL Server system already has an out-of-the-box log shipping setup, so there is an existing file share that contains a “sliding window” of log backup files using Microsoft’s standard conventions.
2. We have a Linux FTP server on the other side of a firewall from this source system. Windows file sharing, however, is blocked, so moving the log backup files with the usual method is not possible.
3. Behind that FTP server we have some automation that can move files across the network to the destination SQL server we’ll use for our reporting function.

Prereq’s

Before our SSL setup would work, we needed to have a certificate arrangement in place where the source servers would trust the FTP server. We have internal certificates for this type of function, so the first step was to export a root certificate for the FTP server and add that to the source servers’ cert stores. This is a requirement because, as will become clear below, we are using the stock .NET framework FTP client classes, and there isn’t a safe way to force those to trust a server without a valid cert setup.

Second, being a PowerShell rank amateur, I had to do some research into how exactly one would use it to invoke FTP client classes. I found a few articles were very helpful on this topic:

http://msdn.microsoft.com/en-us/library/ms229715(v=vs.90).aspx

http://sharpertutorials.com/ultimate-guide-ftp/

http://stackoverflow.com/questions/265339/whats-the-best-way-to-automate-secure-ftp-in-powershell

Provided there’s appropriate cert/trust set up between the machines, automating SFTP doesn’t require any software purchase, because the .NET framework has quite suitable classes built right in. Accessing those from PowerShell is pretty easy – the last article shows how to port some of the C# examples from the first two over to PowerShell.

Sending a File

Getting one file across is a good starting point. The basic process is encapsulated in the following function – assuming a few variables are available to the function, such as  the FTP site address, username and password, and so on, this function will open a file and transmit it to an FTP site destination. Enabling SSL support is as simple as setting the “.EnableSSL” property for the ftp request object:

function sendFile ( $fullSourcePath ) {

    # Function uploads one file via SFTP:

    $fileToUpload = Split-Path $fullSourcePath -leaf 
    $fromPath = Split-Path $fullSourcePath -parent
    $buffersize = 1024 * 1024 

    # Create an FTPWebRequest object to handle the connection 
    # to the ftp server 
    Write-Host Connecting to FTP Server
    $ftpRequest = [System.Net.FtpWebRequest]::create( `
            [IO.Path]::Combine( $ftpFullPath, $fileToUpload )  )

    # Set the request's network credentials, settings  

    $ftpRequest.Credentials = $SFTPCredential.GetNetworkCredential() 
    $ftpRequest.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile 
    $ftpRequest.UseBinary = $true 
    $ftpRequest.KeepAlive = $false 
    $ftpRequest.UsePassive = $false 
    $ftpRequest.EnableSsl = $true 

    # Get an upload stream from the FTP server
    Write-Host Get Server Request Stream
    $requestStream = $ftpRequest.GetRequestStream() 

    # Create a stream to read from the source file, and a 
    # buffer to stage the data
    Write-Host Get File Source Stream
    $sourceStream = New-Object IO.FileStream( $fullSourcePath, `
                                           [IO.FileMode]::Open, `
                                           [IO.FileAccess]::Read )
    [byte[]]$readBuffer = New-Object byte[] $buffersize  

    # Transfer the file data to the destination via the buffer
    Write-Host Transfer the file
    
    do{
        write-Host Transferring file data ...
        $readLength = $sourceStream.Read( $readBuffer, 0, $buffersize )
        $requestStream.Write( $readBuffer, 0, $readLength )
    } while ( $readLength -ne 0 )

    Write-Host Close Streams
    $requestStream.Close()
    $sourceStream.Close()

    Write-Host Get Response
    $ftpResponse = $ftpRequest.GetResponse()  
    $ftpResponse.Close()
}

Storing Credentials

Because this will be a fully automated process, and isn’t within the realm of Windows Auth, I also needed to store the FTP site user credentials in some secure fashion. PowerShell has a pretty neat pair of command-lets that can take a string and store it, in a file, as a value encrypted by the current Windows user key. Those are, essentially:

a. To store a password as an encrypted value in a file:

> $newPassword = read-host –assecurestring
  <type the password to encrypt>
> ConvertFrom-SecureString $newPassword | Set-Content 'someFile.txt'

b. To read the file, decrypt the value, and then make it usable in a “network credential” object:

> $credential = New-Object System.Management.Automation.PsCredential( `
  'UserName', ( Get-Content 'someFile.txt' | ConvertTo-SecureString ) )
> $credential.GetNetworkCredential()

See http://www.leeholmes.com/blog/2008/06/04/importing-and-exporting-credentials-in-powershell/ for more background and details.

Remember, though, that only the user who encrypts the value can decrypt it, because this encryption method uses the current Window’s user’s key.

Now, armed with a method to upload files, and to keep the required credentials, the next problem was how to automate the process, making sure we didn’t repeatedly upload the same log backup files. The sending server would have no knowledge of the list of files on the destination server, so essentially we have to track which files were successfully transmitted in each pass of the upload script, and then send only the net-new files from a folder.

Tracking Sent Files

From the point of view of our source server, these log backup files go into a “black hole” – there’s no visibility to the destination server to see which files were sent already. So we wrapped the send function in some added code that uses a small text file to keep track of each file that was successfully transmitted. The basic idea is this, in pseudocode:

1. Set parameters such as file locations, ftp server URL, credentials.
2. If there is a text file available, containing a list of the log backup files that have already been sent, read it and put the list of files into a hash table (prevents uploading the same files twice).
3. Create an empty list in memory for new, pending files to send.
4. For each .TRN file currently present in the transaction log backup folder, matching some naming pattern:
1. If the file is on the list from #1, then note that it was already sent.
2. If the file is not on the list, then add it to the list of pending files to send.
5. Replace the list from #2, on disk, with the new list from #4.1. This is the new sent file list. (Performing this exchange automatically discards the oldest file names from the list, preventing the need to create a process to circle back and delete the oldest file names from the list.)
6. For each file on the list of files to send:
1. Upload the file by SFTP (function above).
2. Add the name of the file, after successful upload, to the new sent file list.

The full text of the script, including these details, is at the end of this post. The script runs as a scheduled task under a service account. (Worth noting: when encrypting the FTP password for this process, one has to use Run As, run a PowerShell session as the service account, and encrypt the password using that account’s key, so that the scheduler can decrypt the data later.)

Half a Log-Shipping Config

So, with this process in place, we should have files moving over the network and arriving at the destination server. The next piece is to enable the automated restores at the reporting server. It’s not possible to use the log-shipping GUI to set this up, because there’s no access to the source server. You can, however, use the log shipping stored procs to set up just the receiving half of a stock log-shipping config, like:

declare @copy_job uniqueidentifier,
       @restore_job uniqueidentifier,
       @secondary uniqueidentifier
      
exec sp_add_log_shipping_secondary_primary
       @primary_server = 'someServer.abc.com',
       @primary_database = 'someDB',
       @backup_source_directory = 'G:\SQLBackup\LogShipping' ,
       @backup_destination_directory = 'G:\SQLBackup\LogShipping',
       @copy_job_name = 'someDB Log Copy',
       @restore_job_name = 'someDB Log Restore',
       @file_retention_period = 14420 ,
       @copy_job_id = @copy_job OUTPUT ,
       @restore_job_id = @restore_job OUTPUT ,
       @secondary_id = @secondary OUTPUT
select @copy_job CopyJob, @restore_job RestoreJob, @secondary Secondary
 
exec sp_add_log_shipping_secondary_database
       @secondary_database =  'someDB',
       @primary_server = 'someServer.abc.com',
       @primary_database = 'someDB',
       @restore_all = 1,
       @restore_mode = 1,
       @disconnect_users = 1,
       @restore_threshold = 4320 – Minutes

This type of script will create the destination log shipping config only, and will make disabled, unscheduled jobs for restore and copy. Based on those results, I enabled the restore job and scheduled it to run only at night (to catch the reporting databases up to production during off-hours). The FTP upload, together with the automation we have to move the files from the FTP server to the destination SQL Server, makes the copy job irrelevant, so I just left it disabled.

Last Details: Use the .WRK Extension, Agent Proxy issue

In testing this, I did find one bug in my crazy scheme: the destination server would try to open a .TRN file before that file had finished copying into the destination folder. To solve that issue, I just mimicked what SQL Server does natively: we adjusted the automation between the FTP server and the destination server to rename the files with a .WRK extension, then change the names back when delivery of each file is complete, so that SQL Server can open them safely.

Finally, while I had originally intended to schedule the upload script with SQL Agent, there’s some conflict, apparently, between using a SQL Agent proxy account and the PowerShell secure string methods. I had to fall back on the (less desirable) Windows Scheduled Tasks to get the password decryption to work as designed.

Final Script

Disclaimer: This is a crazy idea, and I am bad at PowerShell. If it helps you out, that’s great – but please read and understand the script, and test carefully before attempting to deploy in your environment. Also, sadly, this is a PowerShell 1.0 script, and therefor lacks Try/Catch, in favor of Trap. If you have PowerShell 2 as an option, it’s probably advisable to fix my questionable error handling:

# Script to incrementally upload new transaction log
# files via SFTP for log shipping to a remote site

$ErrorActionPreference = "Stop"

# Set file locations, parameters here:

    # FTP Site location and credentials:

    $ftpFullPath = "ftp://yourserver.com/someDirectory/"

    $userName = "ftpuser"

    # Load the FTP password from an encrypted file:
    $password = ConvertTo-SecureString ( `
                Get-Content D:\someFolder\passwordfile.txt )

    $SFTPCredential = New-Object System.Management.Automation.PsCredential( `
                                  $userName, `
                                  $password )

    # Location and file name pattern for source files to copy:

    $logBackupPath = '\\mysqlserver\logshippingshare'
    $logBackupNameRegex = '(^database_a|^database_b|^database_c).*\.trn'

    # Name of the file to track sent file information
    $fileListFileName = '_FTP_Transferred_Files'
    
function sendFile ( $fullSourcePath ) {

    # Function uploads one file via SFTP to location set above:

    $fileToUpload = Split-Path $fullSourcePath -leaf 
    $fromPath = Split-Path $fullSourcePath -parent
    $buffersize = 1024 * 1024 

    # Create an FTPWebRequest object to handle the connection 
    # to the ftp server 
    Write-Host Connecting to FTP Server
    $ftpRequest = [System.Net.FtpWebRequest]::create( `
            [IO.Path]::Combine( $ftpFullPath, $fileToUpload )  )

    # Set the request's network credentials, settings  

    $ftpRequest.Credentials = $SFTPCredential.GetNetworkCredential() 
    $ftpRequest.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile 
    $ftpRequest.UseBinary = $true 
    $ftpRequest.KeepAlive = $false 
    $ftpRequest.UsePassive = $false 
    $ftpRequest.EnableSsl = $true 

    # Get an upload stream from the FTP server
    Write-Host Get Server Request Stream
    $requestStream = $ftpRequest.GetRequestStream() 

    # Create a stream to read from the source file, and a 
    # buffer to stage the data
    Write-Host Get File Source Stream
    $sourceStream = New-Object IO.FileStream( $fullSourcePath, `
                                           [IO.FileMode]::Open, `
                                           [IO.FileAccess]::Read )
    [byte[]]$readBuffer = New-Object byte[] $buffersize  

    # Transfer the file data to the destination via the buffer
    Write-Host Transfer the file
    
    do{
        write-Host Transferring file data ...
        $readLength = $sourceStream.Read( $readBuffer, 0, $buffersize )
        $requestStream.Write( $readBuffer, 0, $readLength )
    } while ( $readLength -ne 0 )

    Write-Host Close Streams
    $requestStream.Close()
    $sourceStream.Close()

    Write-Host Get Response
    $ftpResponse = $ftpRequest.GetResponse()  
    $ftpResponse.Close()
}

# Hash table and array used to track sent files vs. files queued to send:

$filesAlreadySent = @{} 
$filesToSend = @()

$fileListFullPath = ( `
    join-path $logBackupPath ( $fileListFileName + ".txt" ) )
$tmpListFullPath = ( `
    join-path $logBackupPath ( $fileListFileName + ".tmp" ) )

# Check for / delete file list temp file, if there's one left from 
# a failed prior run. Prevents appending to an invalid/existing list:

if( Test-Path $tmpListFullPath ) {
    Remove-Item $tmpListFullPath
} 

New-Item $tmpListFullPath -type file

# Read the list of already-sent files from text file into the 
# file list hash table

Write-Host Reading list of sent files

if( Test-Path $fileListFullPath ) {
    Get-Content $fileListFullPath  `
    | Where-Object { $_.length -gt 0 } `
    | foreach {
        $filesAlreadySent.Add( $_.TrimEnd(), 1 )
    }
} 

# Walk all the files currently in the source folder, and transmit 
# any new ones added since the last run

Write-Host Walking all current log backup files

Get-Childitem $logBackupPath `
| Where-Object { $_.Name -match $logBackupNameRegex } `
| foreach { 

    if( $filesAlreadySent.contains( $_.name ) ) { 

        # File was already sent
        Write-Host "File was already transmitted:" $_.name

        # but retain that fact for the next run:
        $_.name | out-file $tmpListFullPath -append 

    } else { 
        # File needs to be transmitted
        $filesToSend = $filesToSend + $_.fullname 
    } 
}

# Exchange the new/temporary sent file list for the old one

if( Test-Path $fileListFullPath ) {
    Remove-Item $fileListFullPath
}
Rename-Item $tmpListFullPath ( $fileListFileName + ".txt" )

# Send the pending files and append them to the sent file list

foreach( $filename in $filesToSend ) {

    Write-Host "Sending File:" $filename

    &{ # Try to upload the file

        $file = get-item $filename
        sendFile( $file.FullName )

        # if successful, append the current file to the sent file list
        $file.name | out-file $fileListFullPath -append 
    }

    trap {
        Write-Host "There was an error sending file $filename"
        Write-Host "Skipping File"
        Write-Host "Error was:" $_.Exception.GetType().FullName $_.Exception.Message
        continue
    }
}

Write-Host File Transfer Complete

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 on my list is a good T-SQL code formatter. I really like clean, well-formatted code that is legible. But I’m not fanatical about the details, and I really don’t enjoy spending a lot of time putting spaces and line breaks into some other person’s code. What’s worse - a lot of what I do involves troubleshooting code from unknown sources, mostly ISV’s, that I scrape from performance DMV queries, or Profiler, or existing stored procedures. Often these come through as one long line of unformatted SQL. My best friend: Red Gate SQL Prompt Pro > Format SQL.

Or, as I like to call it, “CTRL+ V A K Y, Shazzam.”

I think I have saved more time on code formatting with this tool than all the time I’ve spent sitting at red lights in my life to date, combined. A bargain at twice the price.

T-SQL Code HTML-ifier / Prettifier: Paste from Visual Studio

I blog occasionally, and as a natural extension of the first item, I wanted a way to get my wonderfully formatted SQL code onto the web, in a presentable form, with little manual labor. For this my online friends pointed me to the combination Windows Live Writer and Paste from Visual Studio (aka VSPaste). Management Studio and Visual Studio use essentially the same RTF format in the clipboard, as far as I can tell, so you can use this little gem to copy T-SQL and paste it straight into Live Writer as nicely-formatted, color-coded HTML. Super handy. Works for any Visual Studio code, too.

Autocompl<tab>: SQL Prompt

Intellisense, auto completion, however it’s branded, I gotta have it. I work in a mixed 2000 (Ack! I know.), 2005 and 2008 environment, so again my buddy is Red Gate SQL Prompt. It seems to coexist happily with Management Studio’s built in, 2008 and higher, Intellisense, but works with older versions of SQL Server too. Everyone that has ever looked over my shoulder while I use it has said, flatly, “I want that. Where do I sign.”

Snippets: SQL Prompt Again

Being lazy, in the coder sense, I love tab-auto completion for custom code snippets too. For this - I fear I am repeating myself - Red Gate SQL Prompt wins again. Customizable snippets of code are very easy to set up and can be shared with teammates. Example: to restore a Litespeed Backup using T-SQL, in the past I would constantly trip over the syntax and spend time looking up the options. I made a snippet, and now I can type lsr<tab>, and, shazzam, I get:

EXEC master.dbo.xp_restore_database
      @database = N'' /* 'database_name' | @database_name_var */
      , @filename = N'' /* 'backup_file_name' | @backup_file_name_var */
      --, @with = N'MOVE ''logicalFile1'' to ''physicalFile1'' '
      --, @with = N'MOVE ''logicalFile2'' to ''physicalFile2'' '
      --, @with = 'REPLACE'
      --, @with = 'NORECOVERY'
      --, @with = '' /* 'additional_with_parameters' | @additional_with_parameters_var */

(By the way, I just pasted that code straight in here with Live Writer > VSPaste. I love that!)

T-SQL Execution History: SSMS Tools Pack

Our shop is also cross-platform, so I sit side by side and cross-train with Oracle DBAs. They have Toad, obviously, and one of the best Toad features is that it retains a history of the queries you execute. It doesn’t seem essential - until you’ve tried it for a while, and then you wonder how you ever lived without it. Enter Mladen Prajdić’s SSMS Tools Pack. This is a bundle of useful add-ons for Management Studio, including a Toad-like query history. There’s some overlap in features with SQL Prompt. (Because I have both, I’ve disabled a couple of the SSMS Tools Pack functions that are redundant with SQL Prompt, but Mr. Prajdić makes this very easy with a menu of options for each add-on feature.) On the other hand, that might be great if you can’t afford SQL Prompt.

Plan Analysis: sp_whoisactive + SQL Sentry Plan Explorer

I live in the land of Buy Don’t Build, and in that far country work often looks like, “Hey, there’s a performance issue, on this system you’ve never seen before, that comes from a totally unresponsive vendor. Can you fix that?” I like a challenge, but my best wing-men in this scenario are sp_whoisactive from Adam Machanic and SQL Sentry Plan Explorer. These two, together, allow me to quickly and easily get the plan of a query from the procedure cache on a server, and see what it’s up to. Phenomenal. I’ve even been known to say, “this is what Management Studio ought to do out of the box.” Intellectual property rights aside, I do sometimes wish that the plan viewer and activity monitor in SSMS literally were like these two free tools. Fantastic.

Automated Monitoring and Baselining: Pick one

I have posted before about why I prefer to purchase, rather than build from scratch, a monitoring solution. I am currently using System Center Operations Manager and it’s SQL Server Management Pack, and I like it a lot. We’ve had to do some customization, but it’s a good platform to start from and I found it had 80% of what I was looking for. It is rather complex, and some commitment is required to set it up, but once installed it’s a good way to get to a “single pane of glass,” where everyone can see the same alerts and performance data. I have also used Quest Spotlight on SQL Server Enterprise (“Enterprise” is vital there, it’s not the little free version) and Idera SQL Diagnostic Manager, and those two tools were also very good. They tend to be stronger in terms of depth with SQL Server, and easier to deploy, but without the scalability or reach of SCOM or the ability to easily share a platform with other applications and OS monitoring. In addition, the SQL Sentry and Confio tools look really great to me, but I have not used them in production. In summary, though, I want something watching my servers while I sleep, and recording their performance stats all the time. Vital!

Schema Search: Red Gate SQL Search

It is possible to search through the metadata in a new database with queries, but honestly why bother when there’s Red Gate SQL Search. I must sound like I work for Red Gate at this point, but I don’t, I promise. I use this tool all the time to examine those ISV databases that are new to me, where I need to locate columns or tables or code in a hurry.

Remote Desktop: Remote Desktop Connection Manager

For those tens or hundreds of machines you might need to remote into with RDP, especially all at once, there is Remote Desktop Connection Manager, created within MS, released as a publicly available download. Most days I have this open and use it all day long. Sweetness: it can store not only groups of servers, but also encrypted credentials, allowing one to log into or out of multiple servers all at the same time, even with differing credentials. I love this one.

Cloud Script Clippage: Simplenote

This one is a little harder to explain. Like a lot of DBA’s I have a pile of T-SQL scripts that act like my SQL Server Leatherman, or my Swiss-QL Army knife. These are all little code snippets that are too long to memorize or type, but generally the same from one project to the next – like that “just right” DMV query that shows me exactly what I need. I have kept these in a folder or on a thumb drive before, and I know many people use something like Dropbox to keep this type of stuff on the web, which is a great solution. If you work in a security-sensitive location, though, Dropbox might not be accessible from the machines where you need to work. And in my case, I do a poor job of keeping folders full of individual files neat and tidy, and then a poor job of finding the snippet of code I need later. What I wanted was a simple, web/cloud based tool to keep very basic text snippets. My dream was a little collection of instantly searchable text clippings, accessible from the browser or right in an editor.

I tried a few services without being really satisfied, including Springpad (they abandoned the version of IE I am required to use at work) and Evernote (demands that notes be formatted HTML and not plain text, and thus wrecks SQL code). Most are not suitable for code, but work well for HTML content, like clipping a whole web page.

Then I stumbled upon an improbable set of three tools that work together: Simplenote is a web service that can be accessed by a conventional web site or by a web services API. That means that other people can easily write clients that interoperate with it.

On my Mac, I have a fantastic little program - so lean it’s practically a widget - called Notational Velocity, that can use the Simplenote web service to store text in the cloud. What’s especially sweet about this tool is that I don’t generally interact with the text snippets as if they were files, but instead using instant search and tagging. For me, a much, much better UI. It’s easier to add things, to stay organized, and to find things.

On Windows, I am using a similar lean app called ResophNotes. Both Notational Velocity (Mac) and ResophNotes (Windows) can use the Simplenote web service to store data in the cloud. It’s really useful, and genuinely simple. I’m loving it.

Here’s an image of T-SQL Scripts from the cloud on Simplenote, Notational Velocity (MacOS) and ResophNotes (Windows VM + Fusion Unity) at the same time:

*Sample scripts shown are from Glenn Berry’s blog, run through SQL Prompt formatter and saved to Simplenote. He is a master of just such T-SQL snippets.

Cloud Bookmarks: XMarks

Then there is the illusive problem of bookmarks. Back in the day, most of us had one computer, which sat on a desk plugged into a network, or, if you were really fancy, maybe a laptop on a desk (they were heavy) plugged into a network. Bookmarks were just shortcuts you kept in a folder. Today my normal work environment is a MacBook Pro, with vmware Fusion running Windows 7 alongside Mac OS, an SSL VPN to a work Windows XP virtual machine, and my Android phone. These all work reasonably well in concert for me to get things done. The calendars, email, contacts and so on all (mostly) coordinate via either Exchange or Google and get presented in an integrated client on each platform.

The one last annoying bit that hasn’t worked is bookmarks. There are a ton of bookmark services out there, but for some reason I’ve had a very hard time finding one with all the right features:

• Private (reasonably)
• Cross-platform
• Cross-browser
• Accessible through the browser as well as with a plugin or extension
• Well integrated with the browser

I finally settled on XMarks, which, though they had a near-death experience recently, does all that stuff pretty well. They supply a plugin for each major browser that will sync each browser’s bookmarks with the service. (If, for example, you’re in IE or Safari, you are really using IE or Safari bookmarks, and not some awkward, added thingamajig.) They also provide a web interface so that if you are on a machine where you can’t install the plug-in for some reason, you can still get to the bookmarks. This is vital for me because I keep a pretty huge collection of reference links, KB articles, blog posts and so on, and I want to be able to get to them from any of my three OS’s / five browsers, without a lot of effort. The only problem I’ve had is conflict resolution when I have two systems running at the same time, but it’s minor.

Blog Reader: Google Reader

In the last few years Blogs have become absolutely vital - you’re reading this, so there it is! Still I find some people who are slow to come to this realization, and seem to live in a bubble.

The trick with blogs today, though, is that there are too many, and on each one there’s likely to be some noise in between the juicy parts. For this I am a fan of Google Reader. Handy, cloud-based, works in all my browsers / OS’s and on my phone. There’s probably a better reader, but I’m too happy to go looking at this point.

Twitter: TweetDeck

I was a late convert to Twitter and joined only recently (@onupdatecascade). I will say that the SQL community advantage of Twitter, both for camaraderie and for hard technical issues, is real. If you haven’t tried it, do! Nobody will force you to stay, if it’s not useful. I don’t really use it socially, but do use it professionally for SQL Server information and contacts. The default Twitter client isn’t that great, but I do like TweetDeck instead.

That’s That

The list could go on and on (TFS / Source Control, SQL Compare, PowerShell, others) but these are the items I thought might be interesting, or obscure or harder to find.

Reflecting on all all these tools and gadgets, I realize I didn’t really go looking for them. (Except the bookmarks thing - that was a quest.) This has just been a slow process of accretion, where one day I say to myself, “Where IS that script?” or “I can’t see anything in this query plan,” or “Dang, I didn’t save that query before I closed it.” In most of these situations I just moved on, but at some point I would stop and exclaim, “Alright, today’s the day I stop this nonsense and find a way to solve this issue. Someone out there must have fixed this by now.” And usually, someone has.

What do you use? You’ve probably got medicine for a pain I didn’t even know I had. Drop me a line, or post your favorite tools here.