I am now in Durban at the International Conference Center (ICC) where I will be presenting a couple of TechEd sessions starting from tomorrow.

• Microsoft SQL Server Virtualization and Consolidation Considerations and Best Practices (DAT314)
• Upgrading to Microsoft SQL Server 2008: Testing Tools, Methodology, and Best Practices (DAT310)
• Considerations for Large-Scale SharePoint Deployments on Microsoft SQL Server (DAT304)

The vibe amongst the delegate and speakers here is awesome.

Some last minute tests of some of my demo VPCs and we will be all ready to get the show on the road…. keynote in 30 minutes.

<Gary>

Earlier this week (21 July 2009) the SQL Server Release Services team released Cumulative Update #3 for SQL Server 2008 SP1 SP1 and Cumulative Update #6 for SQL Server 2008 RTM

For a better understanding of the Incremental Servicing Model for SQL Server check out  http://support.microsoft.com/kb/935897

<Gary>

I have over the past couple of years engaged with many customers around the benefits of correct partition alignment for their SQL Server disk sub-systems. There is finally a  comprehensive white paper on the subject for our reference: Disk Partition Alignment Best Practices for SQL Server.

Windows Server 2008 avoids the issue completely through  the use of a 1024Kb alignment offset and whilst the whilst this whitepaper has a SQL Serer 2008 logo in the banner this information is likely to have more benefit to those running on older versions of SQL on older versions of the the operating system (Windows Server 2003 or Windows 2000 Server). Especially for those using SANs it should also be noted that the partition alignment happens when the partition is created and as such LUNs with partitions created on older versions of the operating system will continue to be misaligned even when presented to a Windows 2008 Server.

<Gary>

Earlier this week (18 May 2009) the SQL Server Release Services team released Cumulative Update #4 for SQL Server 2005 Service Pack 3 and the Cumulative Update #14 for SQL Server 2005 Service Pack 2 

These follow on from last months release of Cumulative Update #2 for SQL Server 2008 SP1 and Cumulative Update #5 for SQL Server 2008 RTM CU#5  

For a better understanding of the Incremental Servicing Model for SQL Server check out  http://support.microsoft.com/kb/935897

<Gary>

Just a quick, last minute reminder that the next SQL Server user group will be held on Tuesday (23 June 2009) at 6:30PM at the Microsoft South Africa offices in Bryanston (3012 William Nicol Drive).

This month Mark Stacey will be talking about Service Broker

Don't forget my challenge to bring along someone else new to join in the fun.

See you there.

<Gary>

During TechEd 2009 last week it was announced that Microsoft will now support SQL Server 2008 guest failover clustering. The “Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment” article has now also been updated to reflect these important changes.

This is awesome news and provides us with maximum flexibility when configuring virtualized SQL Server deployments.

<Gary>

This is just a quick, last minute reminder that the next SQL Server user group will be held tonight (19 May 2009) at 6:30PM at the Microsoft South Africa offices in Bryanston (3012 William Nicol Drive) 

• Cedric will be presenting “Performance Monitoring using Management Studio (SSMS) - Introduction to the Management Data Warehouse”
• Clinton will be presenting “The Basics and Fundamentals of Service Broker”

Don't forget my challenge to bring along someone else new to join in the fun.

See you there.

<Gary>

This is just a quick reminder that the next SQL Server user group will be held tomorrow (17 March 2009) at 6:30PM at the Microsoft South Africa offices in Bryanston (3012 William Nicol Drive) 

There will be two sessions:

• SQL Server with Powershell  by  Paul Filmalter
• SQL Server Auditing by Paul Els

Don't forget my challenge to bring along someone else new to join in the fun.

See you there.

<Gary>

Il-Sung Lee and Art Rask’s whitepaper exploring the details of Auditing in SQL Server 2008 is now available online.

<Gary>

Its going to be in Botswana for the next month or so. More specifically the city of Gaborone (population about 200k). I have no idea what connectivity will be like so we will have to wait and see how regularly I will be able to blog.

<Gary>

It amazes me how many people have yet to discover the power of Microsoft Office OneNote as I use it every day of my life. Well those that have discovered it will soon find themselves so hooked that all those little pieces of important information, conversation reminders and general get saved in OneNote adding up to a mountain of information in very short space of time.

I have just installed Canvas for OneNote from the Microsoft Office Labs web site and am really loving the way this allows me to visualize, sort and organize my OneNote information. Its a free download so check it out.

<Gary>

Every year around this time Microsoft Research allows us a glimpse at the technological development that has been taking place behind closed doors all over the globe in Microsoft’s efforts to address some of the world’s most challenging technical problems. The TechFest event is held at the Microsoft campus in Redmond and draws specialist in diverse fields of research out into the spot light for a few days with the Microsoft product teams.

One of the most interesting technologies this year for me is GeoLife which is a location-based social-networking service running on top of Microsoft Virtual Earth. It enables users to share life experiences and build connections among each other using their location history. Laura Foy over at  Channel 10 has interview with Yu Zheng exploring this technology. 

Why GeoLife 2.0? I have no idea; but my guess is that 1.0 couldn't have been that good.

The possibilities that integration of on-device-GPS positioning with pervasive social networking is at once both exciting and scary for me. With this type of real world information finding its way, in near real time, into cyber space the prospect of  identity theft and worse needs to be properly understood by all and the security and trust of these social services will need to be correctly managed for the benefits to outweigh the obvious risks. I cant think of one burglar out there that wouldn't love to know how far you were from home at all times during that tricky removal your brand new big screen LCD TV from it’s wall mount.

<Gary>

As a follow-up to my previous post I thought I would have a look at the performance of this type of conversion using the XML query and T-SQL type conversions versus the equivalent code written in C# and executed by the SQL CLR engine.

The first thing was to convert the code to the equivalent  T-SQL functions:

CREATE FUNCTION ConvertToBase64SQL(@toEncode varchar(max)) RETURNS varchar(max)
  AS
    BEGIN
        DECLARE @xml xml
        DECLARE @bin varbinary(max)
        SELECT @bin = CAST(@toEncode as varbinary(max))
        SET @xml = (SELECT * FROM (SELECT @bin as data) as row FOR XML AUTO,  BINARY BASE64)
        RETURN @xml.value('(/row/@data)[1]', 'varchar(max)')
    END

CREATE FUNCTION ConvertFromBase64SQL(@toDecode varchar(max)) RETURNS varchar(max)
  AS
    BEGIN
        DECLARE @xml xml
        DECLARE @bin varbinary(max)
        SELECT @xml = CAST( '<row data="' + @toDecode + '" />' as xml )
        SELECT @bin = @xml.value('(/row/@data)[1]', 'varbinary(max)')
        RETURN CAST(@bin as varchar(max))  
  END 

Secondly to create the equivalent code in C# and deploy this to the SQL instance:

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ConvertToBase64CLR(SqlString toEncode)
    {
      byte[] toEncodeAsBytes = System.Text.ASCIIEncoding.ASCII.GetBytes(toEncode.ToString());
      SqlString returnValue = System.Convert.ToBase64String(toEncodeAsBytes);
      return returnValue;
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ConvertFromBase64CLR(SqlString toDecode)
    {
        byte[] DecodedBytes = System.Convert.FromBase64String(toDecode.ToString());
        SqlString returnValue = System.Text.ASCIIEncoding.ASCII.GetString(DecodedBytes);
        return returnValue;
    }

Finally compare the performance:

DECLARE @Counter INT = 100000
DECLARE @Output varchar(max)
DECLARE @StartTime DateTime = GetDate()
WHILE @Counter > 0 
BEGIN 
    SET @Output =  dbo.ConvertFromBase64SQL(dbo.ConvertToBase64SQL('This is a small test message'));
    SET @Counter = @Counter  - 1
END
PRINT @Output
PRINT 1.0*DATEDIFF(MS,@StartTime,GetDate())/1000
-- 25.440000 seconds

DECLARE @Counter INT = 100000
DECLARE @Output varchar(max)
DECLARE @StartTime DateTime = GetDate()
WHILE @Counter > 0 
BEGIN 
    SET @Output =  dbo.ConvertFromBase64CLR(dbo.ConvertToBase64CLR('This is a small test message'));
    SET @Counter = @Counter  - 1
END
PRINT @Output
PRINT 1.0*DATEDIFF(MS,@StartTime,GetDate())/1000
-- 2.663000 seconds

The results indicate that whilst the C# CLR implementation is significantly faster, the benefit of this performance difference (almost 10 times faster) it would likely only have a real impact if there where a large number of documents being converted.

I have not run this test on any larger documents which may result in a different performance results.

I think the real point here is that today there are a number of ways of doing these types of operations and you need to keep your mind open and evaluate the performance of your chosen implementation.

<Gary>

Someone asked the question “Using T-SQL only, is there a way to decode Base64 data” and Peter Kalbach replied with what I thought was an elegant answer my implementation of which is shared bellow. The XML capabilities introduced in SQL Server 2005 opens up a broad range of functionality that goes well beyond its directly intended application and this example reminded me that we need to keep our minds open to some lateral application of these technologies.

  DECLARE @str VARCHAR(max), @xml xml, @bin varbinary(max)
  SET @str = 'RG8gbm90IGZvcmdldCB0aGUgU1FMIFNlcnZlciBVc2V
              yIEdyb3VwIG1lZXRpbmcgb24gdGhlIDNyZCBUdWVzZG
              F5IG9mIGV2ZXJ5IG1vbnRoLiA8R2FyeT4='
  SELECT @xml = CAST( '<row data="' + @Str + '" />' as xml )
  SELECT @bin = @xml.value('(/row/@data)[1]', 'varbinary(max)')
  SELECT @str = CAST(@bin as varchar(max))
  SELECT @str

I have also embedded a “secret” reminder for all of you in this code.

To encode a Base64 data the reverse logic applies as implemented here:

  DECLARE @str VARCHAR(max), @xml xml, @bin varbinary(max)
  SET @str = 'This is the new message to encode. <Gary>'
  SELECT @bin = CAST(@str as varbinary(max))
  SET @xml = (SELECT * FROM (SELECT @bin as data) as row FOR XML AUTO,  BINARY BASE64)
  SELECT @str = @xml.value('(/row/@data)[1]', 'varchar(max)')
  SELECT @str

<Gary>

One of the guys at the office pointed me to a video of one of the coolest TED presentations I have seen. For those who don’t know TED is a conference and the letters stand for Technology, Entertainment, Design. It started out (in 1984) as a conference bringing together people from those three worlds. Since then its scope has become ever broader.

MIT grad student David Merrill demos Siftables: cookie-sized, computerized tiles you can stack and shuffle in your hands. These future-toys can do math, play music, and talk to their friends, too.

Check it out, I just love these little “blocks”

<Gary>