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

I did an after hours XAPFest talk a while back on Managing Data on Windows Phone 7 that is now published on the Microsoft Research site!  I am actually sort of proud that this session was the #2 most attended talk at XAPFest!

Microsoft Research – Managing Data on Windows Phone 7

I did a follow up blog post entitled Minimal virtualized data list for WP7 showing how to implement the code I spoke about in the video.  The source for that is available on Bitbucket.

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

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.

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

Have you tried to use LINQ to query a database using Visual Studio?  It can be a frustrating experience of things that compile fail at runtime, and that edit / compile / test cycle can quickly lead to hours of lost time trying to get a single complex query to work correctly.

LINQPad is an editor for LINQ?

LINQPad is sort of like a Notepad, you can write and edit .linq files using it.  But that is where the Notepad similarity stops. You can execute your LINQ queries and see the results without having to run your application.  It is truly something you have to watch in order to believe how much more productive it can make your writing of LINQ.

How I found LINQPad

When I was first working on the VistaDB product store and account manager writing LINQ queries against my Entity Framework objects was incredibly frustrating.  Most of the documentation and samples I found was for Linq2Sql, which is similar syntax… But not the same. And worse, most of the syntax compiles fine, but blows up at runtime with cryptic error messages.

My typical dev and test cycle was around 5 minutes to compile the DAL / Site, login to the account, navigate to the correct page, and visit the yellow screen of death from asp.net.  It was not fun, so I started working in a stand alone tester app I built just for this purpose.  Write the query, compile, debug, step, step, read exception and try to decipher it.

I was reading a post on Stack Overflow about one of those cryptic errors when someone suggested to the poster they use LINQPad to test their queries first before putting them into their apps.  Wow, what a great idea!  Where was this tool?  (You can download it for free from Linqpad.net )

LINQPad to the rescue

LINQPad allows you to execute single LINQ commands against an existing EF model, or even to write dot net code in the editor and execute it like a little dynamic dot net environment.  The main application is free, but there is an auto-complete feature to the editor that you must pay in order to activate. Believe me it is worth it to pay for the license, you also support the author and show him the application is worth money.  The license is very inexpensive and well worth the price in order to get intellisense like behavior on your LINQ queries.

More...

NOTE: This article was originally published on the VistaDB blog, but has been moved to this location permanently.

Each of these items could be a blog post unto themselves, but I am going to try really hard to not be too verbose and just cover the core of the concept and why you need to do it.

Data driven applications rely on their databases

Everyone knows that any app driven by data is much more than just the app.  In most cases the app without a database doesn’t even function, or fails to function properly.  If a database is an integral part of your application, then shouldn’t you be doing all you can to ensure it stays healthy and prepare for the worst case events of corruption or dead drives?

This week we have been contacted by 5 long time users who suddenly lost or corrupted their data.  Two of these were end user data that is going to cost a huge amount of labor to reproduce.  In all of these cases the following simple procedures would have prevented the situation entirely.

More...