SQL Server Compact & SQLite Toolbox 4.3 – Visual Guide of new features

After more than 415.000 downloads, version 4.3 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio 2010 and later is now available for download and available via Tools/Extensions and Updates in Visual Studio. This blog post is a visual guide to the new features, improvements and bug fixes included in this release, many suggested by users of the tool via the CodePlex issue tracker

This new version was released in July at the time that Visual Studio 2015 went live, and is of tested with Visual Studio 2015 – keep in mind that all extensions now work with the free Visual Studio Community Edition. This release has a couple of new features that improves on the SQLite support originally added in version 4.0

Scan solution for SQLite database files

image

The “Add Connections from Solution” feature has been enhanced to scan all projects in the current solution for any SQL Server Compact and SQLite files. If any are found (based on the file patterns defined in Options), the Toolbox will attempt to add them as new connections.

The current file patterns are defined as shown below, but you can change them to anything you like:

image

Rename Connection name

The names of connections currently defaults to simply the file name of the database file. This becomes an issue, if you have many same named files in different folders connected. Therefore, you can now rename the Connection name:

image 

SQLite-net DataAccess.cs improvements

The SQLIite-net code generation feature has received a couple of small updates based on user feedback:

image

The generated classes will also contain any views defined in the SQLite database, and nullable values will be mapped accordingly (for example int?).

NULL value display

image

NULL values are now displayed as ‘NULL’ in Query results text and grid. Remember that you can see query results both as text and in a grid, the default is text for performance reasons, but this can easily be changed in Options:

image

Also notice that if you prefer the previous behaviour, this can be changed here, via the ShowNullValuesAsNULL option.

Other improvements

Unique indexes now always scripted as UNIQUE constraints (a long standing omission, which hit me when I was creating the reverse engineering for the SQL Server Compact providers for Entity Framework 7)

As usual, any feedback and comments are welcome, and if you like my free tools, I am always very grateful for a review on Visual Studio Gallery

Merge Replication with SQL Server Compact 3.5 SP2 and SQL Server 2014 and 2012

Time for a SQL Server 2014 related blog post to celebrate the release of this product. (And to celebrate me entering my 6th year a s Microsoft MVP).

SQL Server 2012

SQL Server Compact based Merge Replication is “supported” by SQL Server 2012, but there are a few “gotchas” that you need to be aware of before implementing this.

This blog post covers these in detail. As you may know, there are 3 components involved in a replication setup: Client, Web Server and Database Server. For each of these components, certain requirements must be met for the solution to work. And each component does not “just work” out of the box.

Client (Windows desktop/server/Mobile)

The client, which can be a Windows desktop or Windows Mobile device, must run a recent build of the SQL Server Compact runtime files. The file version/ServerVersion of the runtime must be 3.5.8088 or later. You can find download locations for the installers of the recent runtime here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html

Web Server (IIS)

The SQL Server Compact agent (sqlcesa35.dll) must be version 3.5.8088 or later. You can find download locations for the installers of the most recent Server Agent here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html

In addition if you have a separate web and database server, you must install the SQL Server 2012 Database Engine component (and most likely you want to disable the SQL Server service after install) in order to get the required SQL Server COM files installed. 

You must also install SP1 and the latest CU, as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/

Database Server (SQL Server 2012)

You must install Service Pack 1 and the latest CU (Cumulative Update), as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/

SQL Server 2014

SQL Server 2014 does NOT support merge replication with SQL Server Compact: http://msdn.microsoft.com/en-us/library/bb500342(v=sql.120).aspx – end of story.

But I am working on a blog post about an alternative solution that “just works” and enables you to Synchronize from any device, including Windows desktop/server, WinRT, Windows Mobile, Windows Phone, Android, iOS, Mac OS X and Linux to SQL Server 2008 R2, 2012 and 2014 (even SQL Server Express). Stay tuned, and follow me on Twitter @ErikEJ and subscribe to my blog RSS feed.

SQL Server – using newsequentialid() as a function, similar to newid()

This blog post shows how you can use newsequentialid() as a function in scripts etc., not only as a column default value.

In many scenarios, unique identifiers are used a clustered, primary keys in database tables for various reasons. This blog post will not discuss the pros and cons of doing this.

Usage of GUID/uniqueidentifer and it’s implication on fragmentation, and how newsequentialid() can help improve this, has been documented in various places

A limitation of newsequentialid() is that it can only be used as a default value for a column, not as a function, in for example ad-hoc INSERT scripts. By taking advantage of SQLCLR, this situation can be changed.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class SqlFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlGuid newsequentialid()
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
var sql = @"
DECLARE @NewSequentialId AS TABLE (Id UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID()))
INSERT INTO @NewSequentialId DEFAULT VALUES;
SELECT Id FROM @NewSequentialId;"
;
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
object idRet = cmd.ExecuteScalar();
return new SqlGuid((Guid)idRet);
}
}
}

}

The code above implements a SQLCLR function named newsequentialid(), To build this code, simply create a C# class library, include the code, and build. The code is inspired by this thread on SQLServerCentral: http://www.sqlservercentral.com/Forums/Topic1006731-2815-1.aspx

To make deploying the function even simpler, the script outlined below can add the assembly code to your database and register the function:

EXEC sp_configure @configname=clr_enabled, @configvalue=1;
GO
RECONFIGURE;
GO

IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlFunctions' and is_user_defined = 1)
CREATE ASSEMBLY [SqlFunctions]
FROM 0x4D5A… (rest omitted, use full script)
WITH PERMISSION_SET = SAFE

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newsequentialid]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[newsequentialid]()
RETURNS uniqueidentifier
AS EXTERNAL NAME [SqlFunctions].[SqlFunctions].[newsequentialid];
'

END
GO

You can download the full script from here: http://sdrv.ms/1hhYDY1

Testing with 50.000 inserts, like in the CodeProject article reveals the following figures:

Newsequentialid as DEFAULT:
Run time: 1:18, pages: 1725, fragmentation: 0,7 %

Newsequentialid as function in INSERT statement, no default value on table:
Run time: 2:03, pages: 1725, fragementation: 0,7 %

To use the function as a replacement for newid(), simply use dbo.newsequentialid() instead. But please also consider using another column as clustering key in your table…

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:

image

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

image

Optionally, select which tables to script and click OK:

image

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

image

Click OK to the confirmation message:

image

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:

image

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.