Monday, May 14, 2012

On taking backups and checking them

So I just watched a story about how the movie Toy Story 2 was almost lost due to user error. Check it out here. Moral of the story: backing up isn't enough. You gotta test 'em, yo. They got lucky and didn't lose 30 man-years of work. The old adage goes "better lucky than good". If you have a choice, be both. Most of us don't, so choose to be good.

Friday, May 11, 2012

So crazy, it just might work

Ever since I got into computers professionally, I've been about extending the tools that were available in order to make complex behavior easy. Back when I did perl (still a guilty pleasure and go to for quick text parsing), I wrote a couple of libraries for internal use that treated a non-newline delimited file format as though it were newline delimited. I suppose that bears a little more explanation. The format in question began every line with a two byte header that said how long the line was. The idea being that since the computer didn't have to seek to find the end of the line. Premature optimization perhaps, but it's what we had. So as a support guy, I'd typically have to "fix" files that got bogus data in them by whatever mechanism. Since most (if not all) tools at my disposal treated the notion of a line as a newline delimited one, I had to come up with something different. The details are a bit fuzzy now, but perl allows you to create code that overrides the typical semantics of any data type in the language. Since filehandles were included in that, I wrote code to treat lines how they were supposed to be treated. Which allowed me to get on with my life. Fast forward to today. I was recently doing some one-off reporting for the business and thought "man... a treemap would be a really great way to present this data". But alas, I didn't find an easy way to do this in SSRS or otherwise. But I've been doing some thinking and CLR is kind of the glue that allows one to extend SQL Server. So I got to thinking that perhaps CLR aggregate function could be written that leverages the geometry data type to do what needs doing. I'm no C# wizard by any means, but I think I'm going to start tinkering and see what I can do.

Thursday, May 10, 2012

Changing the database of all synonyms - redux

In a previous post, I posted some code that should change the synonyms that point to one database and point them to another. I tried to use that code today and it didn't go so well. Specifically, powershell gave me some guff about changing the collection in the foreach loop. Here's the workaround that I came up with:
$server_name = '.';
$db = 'AdventureWorks';
$old_syn_db = 'old_db';
$new_syn_db = 'new_db';

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

$old_synonyms = @();
$new_synonyms = @();

foreach ($syn in $db.Synonyms) {
    if ($syn.BaseDatabase -eq $old_syn_db) {
        $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 = $new_syn_db
        $old_synonyms += $syn
        $new_synonyms += $s;
    }
}

foreach ($syn in $old_synonyms) {
   $syn.Drop();
}

foreach ($syn in $new_synonyms) {
   $syn.Create();
}

Hope that helps.