Friday, October 26, 2012

Changing the operator on a job

So I was in the position today to change the operator on a bunch of SQL Agent jobs here. Since the server was very remote, I didn't want to clickety-clack through it. Not that I'd do it that way anyways, but either way, I used Powershell.

Friday, October 19, 2012

Changing job ownership across all servers in a CMS

Central management servers (CMSes) are awesome. Powershell is awesome. By their powers combined, Captain Planet great things can be done. So, I decided it'd be a good idea to remove myself as job owner on all of the SQL Agent jobs that I've set up at my current client. Rather than click through each server and figure that all out, I wrote a small Powershell script for it. 

Tuesday, October 16, 2012

Cardinality estimates over a linked server

So, I was at SQL Saturday #149 listening to Joe Sack (blog|twitter) talk about how many different ways cardinality misestimation can really wreck the query optimizer's day. One part in particular stuck out for me, though. Specifically, if you run a query over a linked server and whatever principal you end up with on the "remote" side doesn't have either sysadmin, db_owner, or db_ddladmin, table statistics aren't returned to the "local" query optimizer and so it starts guessing (and poorly). Joe and I talked a little bit about it and he pointed me to blog posts by Linchi Shea and Benjamin Nevarez. I thought that we should be able to reduce the required permissions by doing some module signing. So I tried. I'm happy to report that it's possible, but it does involve modifying the resource database (ever so slightly). So if that's the sort of thing that makes you squeemish, stop reading now.

Tuesday, October 9, 2012

New CREATE CERTIFICATE syntax in 2012

While setting up a demo for an upcoming post, I stumbled across a new bit of hotness in CREATE CERTIFICATE. Specifically, you can specify the bits of the certificate right in the T-SQL (much like you can with assemblies). Why is this awesome? There are a couple of scenarios where you need to create the same certificate in multiple places. Off the top of my head:
  • Signed modules that need a principal both at the server and database level
  • Service broker
  • Encryption by certificate in multiple places