One of the technologies I really wish we had a better solution for is sync for mobile apps. I love the idea, but the implementation is really hard for most companies to implement. Well, it looks like with SQL Server 2012 it has gotten easier to do this, and a recent blog series from Rob Tiffany is walking through it.

I am getting ready to implement something like this for an app, and thought I would share some of my research on the topic.

Rob Tiffany Blog Series

Episode 1 – SQL Server 2012 and SQL Server Compact

You need to get the environment setup for this, and the post walks through the steps.

Episode 2 – Building the databases

This is the part that is hard to maintain in a real world app. You have to build the schema server side, and client side. And every schema change will break your app. (not what you want)

Episode 3 – Remote Data Access

You build a service side IIS site to allow the device to connect to the database server. The service ends up being the part that does the majority of the sync work (which is good).

Did you know you can create a new SQL Azure database as a copy of an existing database?  Yes, you can.  There are some important limitations on how this works which I will cover below.  

Create the new database

-- create a new database as a copy
create database new_database as copy of original_database

In order to do this you must be logged in as the DBO of the original database.  See the NOTE in the article as well.  I think this is important difference for most database apps, the logins that are contained in the database must be updated.

Note After the cross-server copy process is complete, use the DBO login and the ALTER USER statement to map users in the new database to logins on the new SQL Azure server. For example: ALTER USER userName WITH LOGIN='loginName'. For more information, see ALTER USER (SQL Azure Database).

More...

This is pretty cool news to me. Red Gate has a very good track record of supporting SQL Server products and technologies. Well, they just updated their SQL Backup product to a new beta SKU including SQL Azure Backup that is using the Import Export service built by the DAC team!

Red Gate SQL Azure Backup

This gives us a much better user experience than our own CLI tool, or even the soon to be released Azure Portal update.

Import Export Service?

The I/E Service is a Windows Azure based mid tier service that runs in each Microsoft Datacenter. The user submits jobs to the service, and it takes care of the long running operations.

More...

OK, fair point.  According to a friend I didn’t make my case very well in the previous post on SqlBulkCopy and Dispose.

As he stated, yes, you can put it in a using statement. And it does implement IDisposable explicitly.  Doesn’t that mean that everything is done?

using(SqlBulkCopy bcp = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default))
{
    // DO something
}

// OR YOU CAN DO THIS
SqlBulkCopy bcp = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default);

((IDisposable)bcp).Dispose();

Internally the using seems to get converted to call to the virtual IDisposable.Dispose method according to the IL code I have looked at for the code above.

As an alternative, you could cast it to an IDisposable and call Dispose.  The class does have IDisposable, but only exposes Dispose explicitly (through the cast above).  This is a bad practice in my opinion because most developers won’t look for it.  They will try .Dispose through Intellisense.  If it doesn’t work they assume they don’t need to clean it up.

More...

Ran into this issue today and wanted to write it down for others to hopefully find and save them some time in the future.

IDisposable means you need to cleanup, or does it?

If you use the SqlBulkCopy class (from System.Data.SqlClient) you would think that the object does not need to be cleaned up because it does not implement implicitly IDisposable, but you would be wrong.  Take a look in your favorite .net de-compiler at the Close method.

public void Close()
{
    if (this._insideRowsCopiedEvent)
    {
        throw SQL.InvalidOperationInsideEvent();
    }
    this.Dispose(true);
    GC.SuppressFinalize(this);
}

Notice that call to this.Dispose.  It is private, you can’t actually call it.  

It does implement the method explicitly, but you need to cast it ((IDisposable)bcp).Dispose() to get at the method.

More...

With the release of the Entity Framework 4.1 I was interested to see how well the code first aspect works with multiple database vendors.  Could you use the code first modeling to change the database through a connection string?  I thought it might be worth a quick sample app to find out.

Download the sample Project

About the sample databases

I have an identical database in both SQL CE 4 and VistaDB 4.1.  Since they both have ADO.Net providers for the Entity Framework I decided to use them for this test.  The databases must have identical schema for this to work, otherwise your code wouldn’t match the database.  The data in the databases is slightly different, this is from an RSS Reader app.

The schema is fairly simple, only two tables.  RssFeeds (list of feeds the user wants to get entries from), and RssEntries (the actual RSS entries from the feed).  The schema is simple enough to be used for demo, but has real world data that always makes a demo much nicer.

More...

There has been a buzz and hype about the No SQL movement for quite some time.  There was the much publicized Netflix using No Sql, which turns out to be a lot more about reaching internet scale rather than a discussion about relational databases.  In most of the cases where I see people hyping replacement of relational databases they are usually really talking about relaxing ACID compliance, eventual consistency of writes, or caching.

NOSQL Site Link

I recently got a link to an interesting site from a colleague and wanted to share it.

NoSql Summer

Specific Points

There is a lot of good information on the site, but here are a few I wanted to point out.

Cassandra — A Decentralized Structured Storage System – Interesting on a number of levels about scale and failover.

On Designing and Deploying Internet-Scale Services  - A lot of the scalability of any solution comes around a proper design for the scenario.  Why enforce a complete relational validation on something that is temporary for a service?  There are lots of reasons when you should not use a database at all for a problem.

Eventually Consistent – This is perhaps one of the best topics (to me).  There are lots of scenarios where you know your data is cached on a mid tier, and you really only want to make sure the persisted data is eventually consistent.  Perhaps you are always going to read from some cache unless it is empty, then you go back to disk.

The Log-Structured Merge-Tree (LSM-Tree) – Ok, this is just for us computer science geeks…  But it is a really cool concept.  Anyone who has implemented a B-Tree and had to deal with merge operations in a multi threaded scenario will appreciate the thought in this paper.  Not for the neophyte.

If you are at all interested in these topics jump to the site and read through the complete list of papers.

LINQ is fantastic for the ability to write queries that express intent much more clearly than the same SQL, or structured code. One problem that I have run into though is handling NULL database values that are part of a group by statement.

Grouping by ProductSKU

Grouping in LINQ allows you to return sets of data from a collection for a given key value. The group by clause is what the key ends up being in the result set. Let's take a grouping of the Products by the SKU.

Collapse | Copy Code

from p in Products
    group p by p.ProductSKU

Enumerable IGrouping Collection

This results from the group by are enumerable groups (IGrouping<String, Product>) with the String being the Key for the groups (the ProductSKU field from the table). The typical way you walk through this result is a nested for loop.

Collapse | Copy Code

var groups = from p in Products
    group p by p.ProductSKU;
    
foreach( var groupentry in groups )
{
    Console.WriteLine( "Group: {0}", groupentry.Key );
    
    foreach( var groupitem in groupentry )
    {
        Console.WriteLine("Product: {0}", groupitem.ProductSKU);
    }
}  

I end up with a list that looks something like this:

Collapse | Copy Code

Group: VDB4DBA
Product: VDB4DBA
Group: VDB4DMW
Product: VDB4DMW
Group: VDB4PARTNER
Product: VDB4PARTNER

This works, but not what I really wanted. In this case, the first four characters of the SKU are the same per product family (VDB4 for all VistaDB 4 SKUs). I would like to be able to group by only those first four characters instead of the complete ProductSKU. You can do this with the following code:

Collapse | Copy Code

from p in Products
    group p by p.ProductSKU.Substring(0, 4)

What If There Are NULL Entries?

But what happens if there is a NULL entry in the ProductSKU? You get a ConstraintException: The property cannot be set to a null value.

Ternary and Null Coalescing Operators to the Rescue

There are two operators you can use to modify the null values into something you can use. In SQL, you would use the COALESCE or ISNULL operations, these are pretty close matches.

The ternary operator is a shortcut for:

Collapse | Copy Code

if( condition ) then (true code) : (false code)

The null coalescing operator is used to define a default value if the variable is null.

Collapse | Copy Code

variable = ( condition ) ?? ( defaultvalue)

The code to use both of these follows:

Collapse | Copy Code

var groups = from p in Products
    group p by p.ProductSKU == null ? "<null>" : p.ProductSKU.Substring(0, 4);

var groups2 = from p in Products
    group p by p.ProductSKU.Substring(0, 4) ?? "<null>";  // FAILS

In this case, the ternary operator is the only one that will work. This is because the test is independent of the operation. The second example above will crash with the same constraint exception because the ProductSKU.Substring is attempted to be evaluated first, and substring on a null doesn’t work!

The null coalescing operator would work if we only wanted to test if the ProductSKU was null, but in this case the ternary is the only way to get the desired result.

Final Result

So the final result after the ternary operator looks like this:

Collapse | Copy Code

Group: VDB3
Product: VDB3SRC
Group: VDB4
Product: VDB4DMW
Product: VDB4PROB
Product: VDB4CORE
Product: VDB4ASPPAK
Product: VDB4DBA

Now I have cleaner groups like I wanted without having to write string parsing after the query.

Summary

LINQ has a very expressive syntax that allows you to do some amazing queries without resorting to SQL.

Group by can also be used on composite keys (more than one column) by projecting into an anonymous type. Maybe I will leave that for another post.

We have spent a lot of time on our Entity Framework provider over the past 18 months or so.  There is a sample EF provider from Microsoft that illustrates basic points, and if you look at all the open source databases they pretty much all used the default code in their implementations.  But what if that sample code doesn’t implement something?  Or if it is just plain wrong?

Unexpected Results

I have to tell you we didn’t expect the results we got from this test at all.  We expected that we probably had some room for improvement and that SQL Server would ace all the tests.  Didn’t turn out that way.

Entity Framework Compliance?

The short answer to this question is that there IS NO compliance test to make sure your EF provider works correctly, or matches some baseline specification.  We really wanted some way to see if we matched up against the SQL Server provider, because we try really hard to be as compatible with SQL Server as possible. 

This post is about the journey we have taken so far in trying to figure out what that compliance might look like, how do we score today, what tests are missing, and what do others do to test their providers?

Standard LINQ test?

Well, the first thing I thought of was the LINQ 101 Samples from the MIX 2007 conference that Microsoft produced.  I have seen a number of other providers implement them as “proof” of their compliance, so I thought that would be a good starting point.  We found the 101 LINQ samples page on Microsoft, but most of them are not database related.

There is also a post on the ADO.Net Team Blog about 101 samples, but those are pretty old and still referencing Orcas as the target (Visual Studio 2008).  The most recent version of this we could find was on CodePlex under the ADO.NET Samples project.

More...