Monday, April 16, 2012

Changing the database of all synonyms in a database

So, I came across a problem today at the client where a database had been restored from production into the QA environment, but there were synonyms in the database that pointed to an external database. So, the restored database's synonyms were pointing to the wrong place. I figured this would be easy enough. And it was... I just wish it had been easier. Specifically, I with the following had worked:

$server = new-object Microsoft.SqlServer.Management.Smo.Server '.';
$db = $server.Databases['AdventureWorks2008R2'];

foreach ($syn in $db.Synonyms) {
    if ($syn.BaseDatabase -eq 'OldOtherDB') {
        $syn.BaseDatabase = 'OtherDb';
        $syn.Alter();
    }
}
But it doesn't. In theory, SMO could make it happen by dropping and re-creating the synonym, but it doesn't. I don't know why. Oh well. I bend the computer to my will, not vice versa. So here's what I came up with:

$server = new-object Microsoft.SqlServer.Management.Smo.Server '.';
$db = $server.Databases['AdventureWorks2008R2'];

foreach ($syn in $db.Synonyms) {
    if ($syn.BaseDatabase -eq 'OldOtherDB') {
        $s = new-object Microsoft.SqlServer.Management.Smo.Synonym
        $s.Schema = $syn.Schema
        $s.Name = $syn.Name
        $s.Parent = $db
        $s.BaseObject = $syn.BaseObject
        $s.BaseSchema = $syn.BaseSchema
        $s.BaseDatabase = "OtherDB"
        $syn.Drop();
        $s.Create();
    }
}
Even with all of this, I love synonyms. They're implemented at my current client on my suggestion. Before, they'd hardcoded the external references into stored procedures. Which makes cross-environment restores "fun". After I ran the above script, everything was back to normal. In a lot less time than if I'd had to have updated all of the stored procedures by hand.

Update: A newer version can be found here.

Friday, April 6, 2012

Automate everything... within reason

I have a confession: I'm lazy. But I'm also a big fan of Larry Wall (creator of perl) who is often quoted as saying "laziness is a virtue". So when I read an article on SQL Server Central today, I thought it would either affirm my world view or show me a new trick. To my surprise, I didn't agree with it. For me, automation comes down to return on investment (ROI). For instance, why would I automate a task that done the "manual way" happens once a year, takes me 30 seconds to do, anyone in my field would know how and when to do it, and would take me 10 hours to automate and test? Aside from exercising skills that I might not otherwise use (which, by the way, I think is important), the ROI for automating that process is longer than my career. The math is simple: there are 1200 30-second intervals in 10 hours. So, the payoff (in terms of time) only happens on the 1201st and subsequent executions. Of course, that discounts any changes to the process and system(s) involved. My point is, figure out what you're getting out of your automation efforts. Otherwise, you run the risk of committing what Larry calls "false laziness".

Sunday, April 1, 2012

New date/time functions in SQL 2012

So I was just doing a little reading on some new features in SQL 2012 and the new temporal functions sparked my imagination. I can't count how many times I've wanted an easy way to be able to do something like find the first of the month given only the year and month number. Usually, you jump through some hoops to do that in any version up to and including SQL 2008R2. But now, it seems that they've added a family of functions that let you pass in each date part as an argument and it spits out the appropriate data type. Going back to the first of the month example, one can now write "select datefromparts(year, month, 1) from table" and it'll spit out result set with the given firsts of the month. Sometimes, it's the little things.