Migrating databases between SQL Server and SQL Server Compact

In this post, I will try to give an overview of the free tools available for developers to move databases from SQL Server to SQL Server Compact and vice versa. I will also show how you can do this with the SQL Server Compact Toolbox (add-in and standalone editions).

Moving databases from SQL Server Compact to SQL Server

This can be useful for situations where you already have developed an application that depends on SQL Server Compact, and would like the increased power of SQL Server or would like to use some feature, that is not available on SQL Server Compact. I have an informal comparison of the two products here. Microsoft offers a GUI based tool and a command line tool to do this: WebMatrix and MsDeploy. You can also use the ExportSqlCe command line tool or the SQL Server Compact Toolbox to do this. To use the ExportSqlCE (or ExportSqlCE40) command line, use a command similar to:

ExportSQLCE.exe "Data Source=D:\Northwind.sdf;" Northwind.sql

The resulting script file (Northwind.sql) can the be run against a SQL Server database, using for example the SQL Server sqlcmd command line utility:

sqlcmd -S mySQLServer –d NorthWindSQL -i C:\Northwind.sql

To use the SQL Server Compact Toolbox:

Connect the Toolbox to the database file that you want to move to SQL Server:

Right click the database connection, and select to script Schema and Data:

Optionally, select which tables to script and click OK:

Enter the filename for the script, default extension is .sqlce:

Click OK to the confirmation message:

You can now open the generated script in Management Studio and execute it against a SQL Server database, or run it with sqlcmd as described above.

Moving databases from SQL Server to SQL Server Compact

Microsoft offers no tools for doing this “downsizing” of a SQL Server database to SQL Server Compact, and of course not all objects in a SQL Server database CAN be downsized, as only tables exists in a SQL Server Compact database, so no stored procedures, views, triggers, users, schema and so on. I have blogged about how this can be done from the command line, and you can also do this with the SQL Server Compact Toolbox (of course):

From the root node, select Script Server Data and Schema:

Follow a procedure like the one above, but connecting to a SQL Server database instead.

The export process will convert the SQL Server data types to a matching SQL Server Compact data type, for example varchar(50) becomes nvarchar(50) and so on. Any unsupported data types will be ignored, this includes for example computed columns and sql_variant. The new date types in SQL Server 2008+, like date, time, datetime2 will be converted to nvarchar based data types, as only datetime is supported in SQL Server Compact. A full list of the SQL Server Compact data types is available here.

Saving images and long strings to SQL Server Compact with Entity Framework 4.1 Code First

This StackOverflow question points out an issue with EntityFramework 4.1, when used with a SQL Server Compact table with image columns. The “image” type is the predecessor to varbinary(MAX), and is used for storing large binary values, sometimes referred to as BLOBs.

I have created a sample Console application with fixes for the related issues.

To re-create the application, install EntityFramwork 4.1, create a C# Windows Console application in Visual Studio 2010, and add .NET references to:

System.Data.Entity
EntityFramework
System.ComponentModel.DataAnnotations

Then add code similar to this:

using System;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
//Also added reference to EntityFramework.dll (EF 4.1)
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

namespace CfTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new StudentContext())
            {
                var student = new Student { Name = "Erik", Photo = ConvertImageToByteArray(@"C:\Users\Erik\Pictures\erik.jpg"), StudentId = 1 };
                student.LongText = new string('x', 6666);
                db.Students.Add(student);
                int recordsAffected = db.SaveChanges();

                Console.WriteLine(
                    "Saved {0} entities to the database, press any key to exit.",
                    recordsAffected);

                Console.ReadKey();
            }

        }

        private static byte[] ConvertImageToByteArray(string fileName)
        {
            Bitmap bitMap = new Bitmap(fileName); 
            ImageFormat bmpFormat = bitMap.RawFormat; 
            var imageToConvert = Image.FromFile(fileName);
            using (MemoryStream ms = new MemoryStream())
            {
                imageToConvert.Save(ms, bmpFormat);
                return ms.ToArray();
            }
        } 
    }


    public class Student 
    {
        public int StudentId { get; set; }
        public string Name { get; set; }

        // Required to force Code First to create a ntext column, not a nvarchar(n)
        [MaxLength]
        public string LongText { get; set; }

        // Required to force Code First to create an image column, not a binary(n)
        [MaxLength]
        public byte[] Photo { get; set; }
    }

    public class StudentContext : DbContext
    {
        public DbSet<Student> Students { get; set; }
    }

}

And add an app.config with contents like this. The connection string name matches the DbContext name and this causes Code First to magically create a database file in the specified location, with a Students table:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="StudentContext"
         providerName="System.Data.SqlServerCe.4.0"
         connectionString="Data Source=C:\Users\Erik\Documents\visual studio 2010\Projects\CfTest\Students.sdf"/>
  </connectionStrings>
</configuration>

This is the resulting Students table:

-- Script Date: 11-07-2011 09:43  - Generated by ExportSqlCe version 3.5.1.3

CREATE TABLE [Students] (


  [StudentId] int NOT NULL  IDENTITY (1,1)


, [Name] nvarchar(4000) NULL


, [LongText] ntext NULL


, [Photo] image NULL


);


GO


ALTER TABLE [Students] ADD CONSTRAINT [PK__Students__000000000000000A] PRIMARY KEY ([StudentId]);


GO





This code demonstrates how to convert an image to a byte array, and also highlights the attributes required to work with image columns:

// Required to force Code First to create an image column, not a binary(n)
       [MaxLength]
       public byte[] Photo { get; set; }

The Photo column must be decorated with the MaxLength attribute.

// Required to force Code First to create a ntext column, not a nvarchar(n)
[MaxLength]
public string LongText { get; set; }

Likewise the LongText column must specify the MaxLength attribute.

public class StudentContext : DbContext
{
    public DbSet<Student> Students { get; set; }
}

UPDATE: @DamienGuard, of LINQ to SQL and EF Code First fame, pointed out that a better and more provider agnostic solution is to use MaxLength rather than TypeName = “ntext”.

UPDATE 2: Using [MaxLength] prevents any validation errors, and disabling validation is not required.

Windows Phone Local Database tip: Exploring multiple UPDATEs and rowversion impact

According to the Local Database best practices, it is recommended to use a rowversion column in your local database table, if you are performing many updates. This blog post will examine the performance and changes to UPDATE handling that are caused by the presence of a rowversion column.

But first, how to add a rovversion column: If you are using code first, this simple column declaration will suffice:
[Column(IsVersion=true)]
private Binary _version;

If you are using database first, simply add a rowversion column like so:
ALTER TABLE [Artist]
ADD [Version] rowversion NOT NULL

Let us run the following test code which updates 100 rows with and without a rowversion column. I am using a prepopulated Chinook database, added as en embedded resource to the project. See this blog post for details on how to do this.

//New database from embedded resource
            using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
            {
                if (db.DatabaseExists())
                    db.DeleteDatabase();

                db.CreateIfNotExists();
            }

            using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
            {

                try
                {
                    db.LogDebug = true;

                    var arists = db.Artist.Take(100);

                    foreach (var artist in arists)
                    {
                        artist.Name = Guid.NewGuid().ToString();
                    }

                    System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
                    sw.Start();

                    db.SubmitChanges();

                    sw.Stop();
                    System.Diagnostics.Debug.WriteLine(sw.ElapsedMilliseconds);
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex.ToString());
                }
            }

Now lets us see what the SQL statements executed look like without a rowversion column on the table:




UPDATE [Artist]


SET [Name] = @p2


WHERE ([ArtistId] = @p0) AND ([Name] = @p1)

As you can see, a standard UPDATE statement is executed, including a WHERE clause referring to all columns in the table, in order to trap concurrency exceptions. The time to do 100 updates is about 320 ms.

Now let us add the rowversion column to the Artist table, recreate the DataContext so it contains the Version column, and run the same code.

Now the SQL statements look like so, indicating that the query processor is being bypassed:

-- CURSOR BASED INDEX UPDATE [Artist].[PK_Artist] (

--     ArtistId: [99]


--    )


-- EQUALITY CHECK [Version], [System.Byte[]] = [System.Byte[]]


-- [Name] <= [5ad7e0d2-925e-44f7-bcd7-c3bca32de745]


-- AUTOSYNC [Version] <= [System.Byte[]]

The time to do 100 updates is now about 110 ms, a significant improvement from 320 ms. So if you expect to do UPDATEs on your local Windows Phone database, add a rowversion column to your tables!