Monday, October 25, 2010

Karaoke - SQL Geek Style

Earlier this year there was a lot of smack talk around the (now) annual SQL Karaoke outing at the PASS Summit. Like a good data professional, I compiled those which came across my screen. Here they are - enjoy!

These should all be available if you search the #sqlkaraoke twitter tag:
GarethSwan - CCR Have you ever seen the rain

Lori Edwards - Looking Glass Brandi you're a Fine Girl; Foreigner Juke Box Hero
Colin - Air Supply Making Love out of Nothing At All; Lionel Richie Hello

Billy Squire The Stroke (*The Cros Josh Crosby)
Bell Biv Devoe - Poison (*Brent Ozar)
Prince - Kiss (*Colin)
Thin Lizzy - The Boys are Back In Town (Pat Wright)
Neal Mccoy - Billy's Got His Beer Goggles On (*Allen Kinsel)
Biz Markie - Just a Friend (*Brent Ozar)
* Someone wants you to sing this or heard you would be willing to sing it

Personally, I'm never sure what I'll get onstage with, but I have a few ideas!

Friday, October 22, 2010

My Latest Project - Sync Framework

Okay, it's been quiet around here. I've been working on a new angle for a client to research an alternative to Merge Replication. Enter: Microsoft's Sync Framework (Sync FX). Yes, it's true, I'm actually coding. In C# nonetheless. Go figure!

So what is this Sync Framework all about anyway? The concepts are really not that foreign. Other than not having a fancy GUI or Wizard to walk you through the setup (enter C# progamming) the concepts aren't too difficult to understand. Let's break them down in terms we're familiar with:

You still have your teams - Source and Destination - but when working with Sync, we're going to call them Servers and Clients. Instead of configuring your Publisher and Subscribers as one would with replication, now you are going to Provision your Server or your Client. Okay, simple enough. Here is also where instead of creating a Distribution database, you have now created tracking tables, triggers and sync stored procs to handle your inserts/updates/deletes.

Next up would be creating the Publications. However, we're renaming those to Scopes now. Before you get too worked up, yes, you can Filter your Scopes. This is where things can get intricate depending on your architecture. A 2-tier architecture is rather straightforward, but start going with n-tier (which is supported) and you had best have it all mapped out before you start Provisioning and building out Scopes, much less adding Filters.

The third component in Sync Framework would be the actual Sync. There are no Agents or Jobs to monitor, as this would all be handled in your application with code. This is where you kick off the Sync processing and you can choose to only go one direction, download (ie: Server to Client) or go the other direction, upload (Client to Server) or both! The flexibility allowed with Sync Framework also supports Peer-to-Peer/Mesh sync-ing capabilities.

Since each aspect is controlled by the code and the stored procedures/triggers, you get the utmost in flexibility with creating your own Sync scenarios. Every item is incredibly customizable!

I will endeavor to continue a series of postings about Sync FX. If you have questions, please add a comment and I'll do what I can to help answer. At this time, most of the available information is out on MSDN or in the Sync Framework Forums. Check those out here:
http://msdn.microsoft.com/en-us/sync/default.aspx
http://blogs.msdn.com/b/sync/

For any of you working with SQL Azure, Sync FX will be your Go To option for data synchronization. There is no Replication support: http://msdn.microsoft.com/en-us/library/ee336241.aspx.

A Few Downsides
One thing that has come up in the Forums is the fact that since Replication is not supported, and everyone is pushed to using Sync FX, it should follow that Sync FX would make it much easier to use Dynamic Filtering scenarios. Currently, this is not the case.

Additionally, there is (in the current release) no support for changes to DDL's. So, don't go adding/changing your datamodel or plan to have your Stored Procs, Views, Function, etc. Sync'd automatically.

Pretty Awesome Stuff
Okay, so even with some of the current drawbacks, I have a great vision of this Framework for the future. It is highly customizable, making it extremely flexible to fit the needs of almost any organization. Oh, and to top it all off - you can build your own custom Data Providers to connect to ANY datasource. SQL not required.