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...

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...

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...

I was recently tasked with creating a number of samples testing the compliance of both VistaDB and Microsoft SQL Server with Linq to Entity queries. Each sample tested if the provider was able to execute the query without error and then compare both queries to ensure that both providers returned the same results. To ensure valid test results I needed all queries to be executed against a single entity model for both database providers. Entity Framework was designed in a generic manner for this very purpose so my task should be trivial correct? In this article I will explain the complications I ran into during my task.

Generating the original model

My original Entity Data Model was generated from a VistaDB Northwind example which I had an identical copy of in my local SQL Express server. The process of generating a new data model will add a new appconfig file to your project if there is not already one present. If there is already an appconfig file present the data model will simply add a new connection string to it. Entity Framework connection strings always includes paths to the three files that make up the data model, the database connection string and provider information. The EF connection string that was auto generated for Northwind.vdb4 looks like this.

<add name="NorthwindEntities" connectionString="metadata=res://*/NorthwindModel.csdl|
res://*/NorthwindModel.ssdl 
|res://*/NorthwindModel.msl;
provider=System.Data.VistaDB;
provider connection string='Data Source=&quot;C:\Northwind.vdb4&quot;'" 
providerName="System.Data.EntityClient" />

Things to note about the connection string:

· The path of the CSDL file */NorthwindModel.csdl

· The path of the SSDL file */NorthwindModel.ssdl

· The path of the MSL file */NorthwindModel.msl

· The database connection string Data Source=C:\Northwind.vdb4

· Provider Name System.Data.VistaDB

More...

This is an interesting thought experiment posted on Paul Randel’s blog – What 5 things should SQL Server get rid of?

I like it, the idea that features should be removed. I love SQL Server, it is awesome. But SQL Server is also HUGE, some of the features are legacy dating all the back when Sybase owned the product.  I mean do we really still need all that baggage?  I somehow seriously doubt that those people using those legacy features are upgrading their database to SQL 2008 R2.

I decided to do my own take on the list…

More...

A lot of confusion seems to exist around the ability to round floating point numbers, I am going to try to shed a little light on this topic.  Bear with me, the math may get a little involved, but the answers will be worth it (I think).

Float is an estimated data type

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

MSDN SQL Float Article

Float is never an exact number.  The number is internally stored in more than one part and has to be processed into something we humans can understand.  That means something like 1/3 can be represented as 33%, but it is not actually a nice even number.

For the full mathematical treatment of why this is so, feel free to read the fantastic paper “What Every Computer Scientist Should Know About Floating-Point Arithmetic”.  Warning, that paper is not for the feint of heart, it has a lot of math behind it.

The important thing to pick up out of that paper is that all possible numbers between 0 and 1 cannot be represented in 32 or even 64 bits. In order to store a complicated number with decimal places it has to be estimated.  This estimation introduces Rounding Errors.  When you start with one small error, everything that comes afterwards is not what you expect, these types of errors lead to errors like the Patriot Scud Missile Error.

A C# Example of Rounding

If you run the following C# code you will see an object that has a result of 5.1400000000000006.  Even though it looks like op1 = 2.56 and op2 = 2.58.

double type example

double op1 = Math.Round(2.562, 2);
double op2 = Math.Round(2.577, 2);
double opresult = op1 + op2;

Set a breakpoint on the opresult assignment and look at the value.  Now go into your immediate window and type ? opresult.ToString() and you will see an answer that most people probably expect (5.14).  But put your mouse over the opresult object and you see the actual value.

I know at this point it probably seems pretty confusing.  A double is what VistaDB internally stores a FLOAT column type.  Why?  Because they match pretty well in bit size and what they can do, but it does lead to an interesting rounding problem like the one above.

Lets take the same code with a decimal type, what changes?

More...