Mesh’d!

I’ve been playing around with the Live Mesh technical preview for a few weeks now. Once I found this way to
disable UAC prompting without disabling UAC, I was able to install and use Mesh. So far there’s not much more to Live Mesh than file sharing and remote control, which makes it a bit of a cross between Adesso’s now-defunct Tubes and GoToMyPc.

On the file sharing front, it’s been nothing but awesome. Mesh is like the old-school Windows 95 “Briefcase” for keeping files in sync among multiple PCs, except it actually works. The interface is monumentally easier and more intuitive than Tubes, which required a degree in particle physics just to configure it to share a simple file. Mesh integrates right into Explorer and the Windows UI. Mesh folders appear appear wherever you want them, with a distinctive blue folder (reminds me of the Crystal theme on KDE). I have a “Shared” folder that is automatically sync’d with my laptop and the website. I’ve started saving my hours logs in there so I don’t lose track of hours worked when I’m at a remote location. Mesh works offline and syncs back up when you’ve got internet access again. Conflict resolution is a manual, prompted process that’s easy to understand. If I’m at a PC that’s not a device on in my Mesh, I can still easily access my files through the web interface. It’s all very simple.

It also supports multiple users accessing folders, so a friend and I both have his folder and can share files with each other just by copying them into their. We’ve used it to exchange code projects, DLLs, study guides, etc. Each folder has a little news pane associated with it that lets you see what’s happened to it recently or post notes.

Very nice in that regard. The remote desktop component is very nice as well, letting you connect to any device in your Mesh that is running the client. It worked without any extra configuration on my router (doesn’t appear to use UPnP as far as the router reports, so it must all be server-based). Unfortunately, it’s dreadfully slow compared to a plain old Remote Desktop session. Hopefully performance improvements will be in the next version. Again, support to remote into your machine is offered via the web interface if you’re on a non-meshed PC.

I’d love to be able to eventually have my Documents/Music/Pictures folders just be mesh folders that are available on any PC I add to my Mesh rather than having to copy files over manually. I think that might be the direction Microsoft heads with this technology if it proves to be successful and they can find a way to profit from it. The infrastructure costs to mass-market support this thing beyond a tech preview would be huge, I’d think.

Pivot queries

I stumbled across the PIVOT keyword while poking through the dynamic management tables in SQL trying to track down some sproc recompiles. Pivots are great for denormalizing data for easy reporting. OLTP schema is great for quick insert/update/deletes but piecing data together into something useful can be a pain.

A simple budget table that tracks a budget item type and a budget amount (rather than having a separate amount column for each type of item) is a good example of where this is useful…

DECLARE @BudgetItems TABLE
(CustId int, BudgetItemType varchar(20), BudgetAmount money)

--Customer 1 is living the life
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (1, 'Mortgage', 1000)
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (1, 'Salary', 6000)
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (1, 'Food', 140)

--Customer 2 has no income
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (2, 'Salary', 400)
INSERT @BudgetItems (CustId, BudgetItemType, BudgetAmount) VALUES (2, 'Food', 340)

The table ends up looking like:

SELECT * FROM @BudgetItems

CustId BudgetItemType BudgetAmount
------ -------------- ------------
1 Mortgage 1000.00
1 Salary 6000.00
1 Food 140.00
2 Salary 400.00
2 Food 340.00

Again, very easy to ins/upd/del, but difficult to get a single client’s budget into a row for something like binding a report to it as a datasource. Pivoting the table is an easy (if somewhat syntax-clumsy) solution to this:

SELECT pivotTable.*
FROM (SELECT CustId, BudgetItemType, BudgetAmount FROM @BudgetItems) AS source
PIVOT ( SUM(source.BudgetAmount) FOR BudgetItemType IN (Mortgage, Salary, Food) ) AS pivotTable

CustId Salary Mortgage Food
------ ------- -------- ------
1 6000.00 1000.00 140.00
2 400.00 NULL 340.00

DTS to SSIS migration

I’ve been working the past few weekends on migrating DTS packages off our dying, 8-year old SQL2000 server over to new SSIS packages. The performance is markedly improved, often over 50% faster with SSIS for a single datapump task. The DTS-to-SSIS migration wizard is also working remarkably better than I expected, even for some of our more complex packages.

Other DTSes we’ve put together that regularly email out Excel spreadsheets to business users have been migrated to the internal SSRS portal with predefined email subscriptions. Users seem really excited about being able to just go to a website and get their data.

Not having to constantly deal with the day-to-day CM/DBA stuff is leaving me lots of time to explore this cool stuff we’ve been sitting on since the migration from SQL2000 to SQL2005. I wish I’d gotten a chance to play with it sooner.

More ANTS Profiler

My pleas were answered (thanks!) and I now have a Pro version of ANTS Profiler. I have mostly focused on memory usage over the last week trying to track down issues for our client. When the app started up, there was a long delay before we got UI-responsiveness. I figured this was just overhead from ANTS profiling it. I happened to run it without ANTS today and got the same result though, so something was blocking the UI thread for ~15 seconds during start-up.

When I was trying out ANTS last week, my sales rep at Red Gate talked up the performance profiling aspects quite a bit, so I decided to try tracking down my 15 seconds in ANTS. Unlike the memory profiling, which required some pre-sales support and hand-holding (though a token effort on my part to read the manual would probably have helped ;), the performance profiler was very straight-forward. Just start profiling in performance fast-mode and take a snapshot when slow things occur. After the 15-second lag passed and the UI started up again, I took a snapshot and saw this immediately:

This call is made to determine any timezone difference between the SQL Server and the client our app is running on. It’s called once for each server registered in the app, and for some reason this call to the single server I have registered took 14.47 seconds. Digging in further showed that GetServerTime() called RemoteTime() which was making native calls to NetRemoteTOD to get the time from the operating system, parsing out the timezone data returned, adding it to our current time to get the difference, etc. OS time retrieval works great if you have rights to the server itself, but I don’t so I lock up for 15 seconds before getting an “Access denied” return code. Changing GetServerTime() to just use our already-established SQL connection to call GETDATE() reduced the method call from 14.47s to 0.03s:

So that took about 10 minutes to find and fix, shaved about 14.4 seconds off each server call during start-up, and probably saved my client 3 hours of billable time had we tried to fix this by manually stepping through everything.

The downside, of course, being that it shaved off 3 hours of billable time. 😉

Quick Launch keyboard shortcuts

I was trying to use a keyboard shortcut I set up for Windows Key + ` and accidentally fat-fingered the 1 instead. To my bewilderment Visual Studio launched. I hit it again and got another instance of VS. Windows+2 launched VMware and Windows+3 launched SQL Management Studio. After bumbling around for a bit, I realized that it was launching things in the order they were in my Quick Launch toolbar. Trying to get a friend excited about it revealed that it’s a new feature in Vista, whoops!

Product Pimping (and pleading)

A while back, I got the opportunity to test out Hyperbac, a backup solution for SQL Server. I love pimping Hyperbac because it is dirt-cheap and has seamless integration with SQL 2005. Most third-party backup products required using extended stored procs to do backups, which killed my ability to use SQL 2005’s vastly improved maintenance plans. Hyperbac works with the native commands as it simply intercepts the write operations at the OS level based on the (completely customizable) file extensions you’re writing to. So just using a basic BACKUP DATABASE TO DISK command and specifying a filetype of .HBC would automatically compress the file. Hyperbac also supported backing up to standard .ZIP. So much nicer than the tools it replaces. Not sure why they’re not a bigger name in the backup/recovery market yet.

I started my new consulting gig. It’s cool getting to just focus on one project and just grind away at it. One of the issues our client wants fixed is memory bloat. The app just consumes gobs of memory, even when not seeming to do anything. I downloaded a copy of ANTS Profiler and once I found the call stack window, I was able to dig into what was going on in about two hours, whereas I had been stumbling around for a good day or so with no results prior. It’s pretty reasonably priced ($495/license for Pro), but unfortunately I’m still in start-up mode where “food and shelter” takes precedence. If any rich people that stumble across this want to buy a copy for me before my trial runs out, I’m about $492 shy:

Your kindness will not be easily forgotten. Red Gate rocks!