Windows Live Writer

I finally got around to building a MetaWeblog API Handler for this site, so I can use Windows Live Writer.  It certainly was an interesting task.  I wrote code for XML, SQL Server, File IO, and Authentication to get this thing working.  It’s kinda mind-boggling how many different pieces were necessary to get the Handler to function properly.

All-in-all the development was really fun.  Most people would give up on the process once they realize what’s required to debug such an interface.  But it got my chops in shape.  It’s not every day you have to use a Network Listener to debug code.  It’s certainly not something I would want to do everyday, but every so often it’s pretty fun.

While in the preparation process, there were a couple of procedures that I thought might be tricky to work out.  One in particular was automatically uploading images to my server that were placed in the post.  I could have left it to the manual process, what I started out with, which involved FTP’ing the images to the server, and then figuring out the URL for them, and manually inserting the img tag.  Or, I could let Live Writer and the Handler do all the work.  Ironically, this procedure took the least amount of code out of all of them:

public string NewMediaObject(string blogId, string userName, string password,
string base64Bits, string name) { string mediaDirectory
= HttpContext.Current.Request.PhysicalApplicationPath + "media/blog/"; if (authUser(userName, password)) { File.WriteAllBytes(mediaDirectory + name, Convert.FromBase64String(base64Bits)); return Config.SiteURL + "/media/blog/" + name; } else { throw new Exception("Cannot Authenticate User"); } }

Now its a breeze to write posts.  It even adds drop shadows to images:


Live Writer also automatically creates a thumbnail of the image, and links to the original.  It might be a pain in some cases, but it’s easily fixable.

All I need now is more topics that involve pictures.  Kitten’s optional. :)

ADO.NET Entity Framework and SQL Server 2008

Do you remember the SubSonic project? The Entity Framework is kind of like that. You can create an extensible and customizable data model from any type of source. It takes the boiler plate coding away from developing Data Access Layers.

Entity is designed to seperate how data is stored and how data is used. It's called an Object-Relational Mapping framework. You point the framework at the source, tell it what kind of business objects you want, and poof: you have an object model. Entity is also designed to play nicely with LINQ. You can use it as a data source when querying with LINQ. In my previous post, the query used NorthwindModEntities as a data source. It is an Entity object.

Entity Framework
Courtesy of Wikipedia

The Architecture, as defined in the picture:

  • Data source specific providers, which abstracts the ADO.NET interfaces to connect to the database when programming against the conceptual schema.
  • Map provider, a database-specific provider that translates the Entity SQL command tree into a query in the native SQL flavor of the database. It includes the Store specific bridge, which is the component that is responsible for translating the generic command tree into a store-specific command tree.
  • EDM parser and view mapping, which takes the SDL specification of the data model and how it maps onto the underlying relational model and enables programming against the conceptual model. From the relational schema, it creates views of the data corresponding to the conceptual model. It aggregates information from multiple tables in order to aggregate them into an entity, and splits an update to an entity into multiple updates to whichever table contributed to that entity.
  • Query and update pipeline, processes queries, filters and update-requests to convert them into canonical command trees which are then converted into store-specific queries by the map provider.
  • Metadata services, which handle all metadata related to entities, relationships and mappings.
  • Transactions, to integrate with transactional capabilities of the underlying store. If the underlying store does not support transactions, support for it needs to be implemented at this layer.
  • Conceptual layer API, the runtime that exposes the programming model for coding against the conceptual schema. It follows the ADO.NET pattern of using Connection objects to refer to the map provider, using Command objects to send the query, and returning EntityResultSets or EntitySets containing the result.
  • Disconnected components, which locally caches datasets and entity sets for using the ADO.NET Entity Framework in an occasionally connected environment.
    • Embedded database: ADO.NET Entity Framework includes a lightweight embedded database for client-side caching and querying of relational data.
  • Design tools, such as Mapping Designer are also included with ADO.NET Entity Framework which simplifies the job on mapping a conceptual schema to the relational schema and specifying which properties of an entity type correspond to which table in the database.
  • Programming layers, which exposes the EDM as programming constructs which can be consumed by programming languages.
  • Object services, automatically generate code for CLR classes that expose the same properties as an entity, thus enabling instantiation of entities as .NET objects.
  • Web services, which expose entities as web services.
  • High level services, such as reporting services which work on entities rather than relational data.

LINQ and SQL Server 2008

No, Zelda is not back.  LINQ stands for Language Integrated Query. It's a set of query operators that can be called in any .NET language to query, project, and filter data from any type of data source. Types include arrays, databases, IEnumerables, Lists, etc, including third party Data Sources. It's pretty neat.

Essentially LINQ pulls the data into data objects, which can then be used as you would use a Business Object. The data object is predefined by a LINQ Provider. Out of the box you have LINQ to SQL, LINQ to XML, and LINQ to Objects for providers. Once you define the data object based on provider you can start querying data:


Within the foreach loop the 'Customers' class is a data class that was defined based on a LINQ to SQL Provider. In this case, the database was Northwind.

Syntactically LINQ is very much like the SQL language, mainly because they both work on the same principle. Query (possible) large amounts of data and act on it appropriately. SQL is designed to work with large datasets. Most other languages work iteratively. So SQL was a good language choice to mimic.

However, there is a small problem that I see with LINQ. If I'm doing all the querying at the DAL layer instead of using things like Stored Procedures within the database, and I need to modify a query for performance concerns, that means the DAL has to be recompiled and redistributed to each application out in the field. That could be 10,000 different instances. Wouldn't it make more sense to keep the query within a Stored Procedure? Just a thought...

Protecting Data in Transit between applications and SQL Server

Alright, so you've just implemented Transparent Data Encryption on your database.  Your database is extremely secure.  The data, not so much.  You see, the problem is this: the data travels unencrypted between SQL Server and your application.  Whoops.
To enable SSL Encryption on the server side, there are a couple of fairly simple steps involved:
  1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties.

  2. In the Protocols for <instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.

  3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.

  4. Restart the SQL Server service.

To enable SSL Encryption on the client side:

  1. Copy either the original certificate or the exported certificate file to the client computer.

  2. On the client computer, use the Certificates snap-in to install either the root certificate or the exported certificate file.

  3. In the console pane, right-click SQL Server Native Client Configuration, and then click Properties.

  4. On the Flags page, in the Force protocol encryption box, click Yes.

finally, set your connection string within the application to 'Use Encryption for Data=True'. 

Driver={SQL Native Client};
That's really not all that difficult. One more reason to have a more secure infrastructure!>

Microsoft SQL Server 2008 Internals

It's almost released!  Only Three and a half months to go!  Pre-Order at Amazon.,, just registered

Boredom is a bad thing!  Especially when you are putting off work.  So what do I do to waste my time?  Check out local user groups.  The websites at least.  A few days ago I posted a few links to some promising groups.  To my disappointment there really aren't that many Microsoft oriented user groups in Toronto.  I wouldn't call it a bad thing.  More of an opportunity.
I have determined that,, and were not registered.  So for $30 I registered all three of them.  Now I have to put them to good use.  Currently they are pointed to, until I find a proper home.
More to come on that front! 

On-Disk Data Structures in SQL Server

Just some links to read.  Carefully.  There's a lot of information.  These posts were made by Paul S. Randal on  I'll do a more thorough job of weeding out information when I'm not strapped for time.

Toronto and Area User Groups of Interest

Since moving to Toronto I have been looking for user groups that I think I could benefit from.  So far I have found a couple of interest:
TSQL.CA - Toronto SQL Server User Group -
Toronto .NET User Group -
ISSA Toronto (Information System Security Association) - 
I'm still looking, but these look promising.  

Floating down the FILESTREAM...

The old CCR lyrics popped into my head "Rollin' down the river..." and then of course I realized a river isn't a stream, nor is it any way related to data streams, and I lost my concentration for 20 minutes-ish.  What does this have to do with FILESTREAMS?  Nothing really.
Let's talk a little about Filestreams in SQL Server 2008.  So, what is FILESTREAM?  According to Microsoft, FILESTREAM is a technology "which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system".  Thats all fine and dandy, but what does it really mean?  FILESTREAM, very eloquently put, stores large BLOBs of data on the file system.
In previous versions of SQL Server, you didn't have all that much choice when it came to working with large BLOB data.  Your options were three-fold.
1. Store it in the database.
2. Store it on the file system.
3. Store it in a BLOB store.
Storing the data in the database was tricky.  If the data was small in size, it worked out fairly well.  But in instances where you had to store large pieces of data, such as videos, or audio files, keeping the data within the database was a major blow for performance. 
So what is the next best option?  Store the data on the file system, and leave a pointer in the database.  You didn't have performance issues, and data was streamed directly from the file system.  However, you did have problems with data consistency.  You didn't benefit from the transactional properties of SQL Server.  Backup's were a pain, and managing the streaming was tricky from a programmatic perspective.
Next up, and the final option was a BLOB store.  Basically, it works much like the filesystem approach, except it was more manageable, and had transactional characteristics due to a tie in with the database.  An example is the EMC Centera.  Drawback: Fricken EXPENSIVE.  Worked great though... 
 Now onto SQL Server 2008.  FILESTREAM works very much like option number 2, has transactional benefits like option number 1, and is cheaper than option number 3.  How does it do it?  It's actually pretty simple.  A column of VARBINARY(MAX) is created in a table, with an AS FILESTREAM attribute.  Once data is stored within the column, a piece of metadata tells SQL Server to write the file to disk, and stick a pointer in the column.
So far that takes care of replacing option number 2.  So how is transactional consistency dealt with?  Easy.  NTFS as a file system is transactional by nature, so FILESTREAM takes full advantage.  Backing up is dealt with the same way as all databases are, and is done transparently.  That takes care of option number 1.
Now comes the issue of price.  Well, it turns out FILESTREAM is available for free.  It is built in, starting with SQL Express.  That's all I have to say about that, which takes care of option number 3.
That in a nut shell is SQL Server FILESTREAM.  My next post will be on how to actually implement FILESTREAM on a 2008 database.

Presenting at Techdays!

What is Techdays?

 Microsoft Techdays

The Canadian IT Pro Team would love to call it a Tech-Ed of the north, except on tour. Check out the site: to get the info, but the dates are:

Date City Venue
October 29/30 Toronto Toronto Congress Centre
November 6/7 Montreal The Palais des Congrès
November 27 Ottawa Mariott Hotel
December 4 Winnipeg Delta Hotel
December 10/11 Calgary Calgary Stampede Roundup Centre
December 17 Halifax Halifax World Trade Centre
January 21/22 Vancouver Vancouver Convention Centre

I will be doing a presentation in Montreal and Ottawa entitled Microsoft SQL Server: Essential Database Maintenance for New and Seasoned DBAs. The synopsis is:
Every DBA knows that managing a database using SQL Server requires dealing with a key set of components of SQL Server in an optimal in order to make their lives easier. But what are the elements of SQL Server that you need to really focus on to get the best bang for the DBA buck, and what best practices should be followed to ensure an optimally-running an instance in SQL Server? In this session we will walk through the Top 10 List of DBA techniques and best practices to ensure a smooth running database and instance. You’ll learn: how to optimize data files and transaction logs; why TempDB is special and how to treat it properly; indexing strategies dealing with corruption; and much, much more.

I'm also doing a session entitled Beyond Relational SQL Server 2008: Managing Unstructured and Semi-Structured Data:
The amount of data that does not fit into the tabular format of relational tables is increasing tremendously, be it images, sounds, text documents, XML documents, or semi-structured data. Integrating this data with the robust, efficient processing capabilities of SQL Server and providing integrated querying and management of that data together with the standard relational data becomes increasingly more important. This presentation will present new and existing functionality on how SQL Server 2008 supports these non-relational kinds of data. The presentation will provide insights into FILESTREAM, Remote Blob storage, new XML functionality, integrated Full-Text Search, sparse columns, filtered indexes and the new hierarchyID type.

Should be fun. See you there!