SQL Server Compact 4.0 SP1 hotfix available

I was recently made aware that a hotfix for SQL Server Compact was made available in 2014 –  but better late than never.

The hotfix can be requested via the Microsoft support knowledgebase here: https://support.microsoft.com/en-us/kb/2960153

The file/build version of the hotfix is: 4.0.8902.0 (The .NET assembly version stays at either 4.0.0.0 (or 4.0.0.1 for the “Private” .NET Assembly)

The hotfix includes the following fixes:

2984052
FIX: Unexpected ordering when you run ORDER BY statement on a column that contains “-” in SQL Server Compact 4.0

2984053
FIX: “ConnectionString cannot be null” when you use folder redirection with offline files

2979868
FIX: It takes longer time than flush interval to flush committed transactions in SQL Server Compact 4.0

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

After more than 470.000 downloads, version 4.5 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 GitHub issue tracker

I have moved the source code and issue tracker to GitHub, and I am planning to move the remaining CodePlex content “soon”. I have also moved the source of the scripting API that the Toolbox uses extensively to the same GitHub repository. As always, and suggestion, bug reports and pull requests are welcome!

New icons

The icons of the extension have all been overhauled, and updated to use the latest VS 2015 icons/images. From users already trying out the latest bits, I have received positive feedback on this visual change.

image

Query editor toolbar redesign and more

I have also started using Azure AppInsights to track (anonymously) feature usage etc. This revealed (maybe not surprisingly) that the most use feature is the query editor. So for this release, the query editor has received a lot of attention. In addtion to the changes below, the general layout of the editor has been streamlined (no more rounded corners).

image

The first button is new, and will open a “New Qery” window against the same database file. An options dropdown have been added to expose some of the (maybe overlooked) options that are available for the query editor. Setting these only affects the current query window, use the general settings (which you can reach via the Setting button in the query editor now) to change the “default” value for a setting.

Lets have a quick look at each of the options:

“Show result in Grid”: By default, the query results (from a SELECT statement) will be displayed as text for performance reasons, but you can opt-in to have the results displayed in an Excel like grid instead.

“Show Binary Values in Result”: By default, binary values (from for example “image” or “rowversion” columns) will be displayed a “System.Byte[]”. Enabling this will instead display the actual value as a hexadecimal string – but may affect performance.

“Show null values as NULL”: Until recently, NULL values were displayed as an empty string, not as “NULL”. Disabling this reverts to the old behaviour.

“Ignore DDL errors”: Enabling this (per query window only) feature, allows you to run the database creation scripts generated by the Entity Framework “Generate Database from Model” feature, and ignore errors caused by for example pre-existing tables.

“Use classic (plain) grid”: This will revert to using the “old” grid for grid results – about the “new” grid, see below!

New result grid in query editor

A new grid that allows you to further drill into the results of a query without writing any SQL is now the “default” result grid in the query editor. The grid uses http://wpfextendeddatagrid.codeplex.com/

image

“Autofilter” :The grid has built in filters, in the example above to filter on invoices for a particular customer (or even set of customers).

image

“Grouping”: Add columns you want to group by to the surface above the grid. In the example above, Invoices are grouped by Customer Id.

Options now part of Visual Studio options dialog

image

The options for the Toolbox have finally been integrated in the standard Visual Studio Tools/Options pages.

Improved SQL Server support

SQL Server Connections defined in Server Explorer now appear in the Toolbox, in order to better expose the SQL Server releated features of the Toolbox:

image

And scripting of a SQLite schema (only) based on a SQL Server database is now possible:

image

Improvements

A number of smaller improvements are also included in this release:

Improve format of INSERT statements generated by CSV import (to make the more readable)
Added latest System.Data.SQLite version (1.0.99)
Added link to blog post to DataAccess.cs to better explain how to use it.
Support for row-returning SQLite statements beginning with “WITH”
Added “Feedback” button
Using Azure Application Insights for metrics and crash reports
Ability to remove password from password protected SQL Compact file (by leaving password box blank)
Progress messages during export now displayed in Output window
Enable scripting of hierarchyid as varbinary(892)
Now able to script Adventureworks2014 schema and data to both SQL Compact and SQLite without errors
Table Data script (INSERTs) now have “RESEED” command at end always
Option to Opt out of Telemetry added

Bug fixes

In addition to the above improvements and features, and number of bug fixes are included in this release:

API:
Fix bug in SQLMetal.exe detection (on some configurations)
SQLite CREATE TABLE datetime default should be current_timestamp, not GETDATE()
Improved SQLite datetime handling in SELECT and Edit in Grid

User Interface:
CSV export from the SQL editor included one separator too many in the heading
Editor keyboard bugs fixed
Scroll issues in treeview fixed
Autohide of Toolbox bug fixed

Table builder:
Table builder was not scripting default values
Table builder should not script default value for IDENTITY columns

Document your SQL Server database with free tools: SQL Server Compact Toolbox and DB>Doc

In this blog post I will highlight an overlooked feature in my SQL Server Compact Toolbox (Visual Studio add-in or standalone), the ability to create interactive relationship diagrams of SQL Server databases. And in combination with the free, open source DB>Doc for SQL Server, you have a full suite of documentation tools.

In order to create a diagram showing tables, columns and relationships for your SQL Server database, install the Toolbox.

Right click the root node of the Toolbox window, and select “Create SQL Server Database Graph (DGML)”:

image

Select the database that you want to diagram (the tool needs SELECT permission on the system views in the database)

image

You can also select to diagram only a subset of the tables in the database:

image

You will be prompted for a location to save the DGML file, enter this, and click OK, and Visual Studio will show the generated diagram in the DGML viewer, that comes with Visual Studio 2012 Pro or higher:

image

The viewer is quite advanced, from the Layout menu you can choose between various layouts of the dependency graph, and other advanced features to analyse the graph, for example. From the viewer you can Share as Image or XPS.
UPDATE: With the DGML Power Tools installed, you can also share a .SVG, which you can view in any web browser.

image

Click on a table, and select the down arrow button to expand the table into columns, and refer to the Legend also displayed (via the Legend menu item in the viewer):

image

More information on working with the graphs is available on MSDN:

Browse and Rearrange Dependency Graphs

Edit and Customize Dependency Graphs

To create HTML/XML based documentation of your database columns, you can use the open source DB>Doc tool. The main idea [of this tool] is to serve as a complementary tool to database diagrams, focusing on columns in tables and their meaning (common database diagrams focus to entity relationships).

I also use this tool for the documentation feature in the Toolbox for SQL Server Compact database files, which produces output similar to this:

image

Happy documenting! And feel free to provide feedback at https://sqlcetoolbox.codeplex.com/WorkItem/Create

Entity Framework 6 & SQL Server Compact 4 (2)–Easy Private Desktop Deployment

UPDATE: Instead of using the NuGet package mentioned below, use the package mentioned in this newer blog post: http://erikej.blogspot.dk/2014/10/entity-framework-6-and-sql-server.html

In this post I will describe a simplified approach to SQL Server Compact Private Desktop Deployment with Entity Framework 6, for an overview blog post on Private Deployment with SQL Server Compact, see my blog post here.

When using Private Deployment with Entity Framework 6, due to the fact that the EntityFramework.SqlServerCompact also installs the SQL Server Compact NuGet package, we must use a slightly different approach from what I described here in order to keep thing simple. The main difference is that we must force the project to use x86 Target Platform rather than AnyCPU, Prefer 32 bit. This is due to the fact that when running Prefer 32 bit, the SQL Server Compact ADO.NET provider loads the wrong files, as the PROCESSORPLATFORM in this case remains AMD64 on 64 bit systems, but the executing .NET Framework is the x86 version.

To read more about the new default Platform target option introduced in .NET 4.5, see the MSDN documentation here, and the blog post here.

In addition, I will demonstrate how to use my SQL Server Compact Toolbox to quickly get started with a Database First workflow.

For the sake of simplicity, and in order to focus attention on the private deployment aspects, I will demonstrate with a console application, but the same approach will also work for WinForms and WPF applications. The approach will NOT work for dll output projects, like for example add-ins and similar, as it relies on configuration entries in app.config. And this blog post is about deployment, not layered architectures. 

In short, we need to do the following:

1: Create a project and set Target Platform to x86

2: Add the EntityFramework.SqlServerCompact.PrivateConfig NuGet package to the project

3: Add our database file as project content

4: Create EDMX (or Code First classes) and adjust connection string

5: Add code to properly deploy the database file during app launch

But let’s expand on each task below.

Before you get started, make sure you have the following installed:

1: Visual Studio 2013 Pro or higher.

2: The latest version of  my SQL Server Compact Toolbox add-in (Install via Tools/Extensions in VS). The Toolbox requires the SQL Server Compact 3.5 SP2 and 4.0 SP1 runtimes to be installed (the 3.5 requirement will be lifted in the next release of the Toolbox)

3: An existing SQL Server Compact database file, I will use Chinook

With that in place, let us open Visual Studio and get started:

Create new console application

Go to File, New Project, and create a new Windows Console application. Make sure to set the target platform to 4.0 or newer.

image_thumb3

Now set the Target Platform to x86 (this is an important step, if you forget to do this you will get a BadImageFormat exception during runtime/debug)

Go to the project properties, and select the Build section/tab, and choose x86:

image

Install Entity Framework 6 from NuGet

To get the latest and greatest Entity Framework 6 bits, always install from NuGet. And thanks to the amazing package dependency resolution features of NuGet, just install the single required package, and the remaining packages will be installed as dependencies. In this case, the only thing that is missing from the EntityFramework.SqlServerCompact package (which depends on SQL Server Compact 4 and EF6) is a DbProvider registration in the app.config file, that enables you to run without the SQL Server Compact runtime installed in GAC and machine.config. To fix this “”misssing link”, I have created the http://www.nuget.org/packages/EntityFramework.SqlServerCompact.PrivateConfig/ 

package, which simply adds the missing config entries and in turn depends on all other required packages. It adds this to the app.config file:

  <system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.4.0" />
<add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>

In other words, simply install the EntityFramework.SqlServerCompact.PrivateConfig package. Right click the References folder in your project, and select Manage NuGet Packages. Search for the package name and install it. And all required packages and references will be installed. (I am working on getting these config entries added to the base EntityFramwork.SqlServerCompact package, in order to make things more streamlined)

Add your database file to the project

I will use Chinook, which you can install from NuGet. But of course feel free to use your own database file.

Find the Chinook SQL Server Compact database package, and install it:

image

Make sure to mark the database file as Content, Copy Always in the project. I will describe later in this post how to place it correctly on the end users PC.

image

 Create the Entity Data Model (EDMX) and adjust the connection string

Build the project.

Now in the SQL Server Compact Toolbox, connect to the database file in your project folder:

image

Right click the database, and select the “Add Entity Data Model (EDMX) to current project” menu item:

image

Click OK, and the EDMX file and various other files will be added to your project:

image

Build the project.

Now let us add some test code to the Main method in order to verify that everything works so far:

using (var context = new ChinookEntities())
{
foreach (var album in context.Album.ToList())
{
Console.WriteLine(album.Title);
}

}
Console.ReadKey();

We can now access the database via the generated object model, and do not have to type SQL, but can use LINQ to Entities to query the database. In addition, we can update the database (INSERT, UPDATE, DELETE) via methods on the derived DbContext class, ChinookEntities. In app.config, the following connection string has been added:

  <connectionStrings>
<add name="ChinookEntities" connectionString="metadata=res://*/Chinook.csdl|res://*/Chinook.ssdl|res://*/Chinook.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=C:Userserik.COMMENTORDocumentsVisual Studio 2013ProjectsConsoleApplication4Chinook.sdf&quot;"
providerName="System.Data.EntityClient" />
</connectionStrings>

In order to make the connection string user and folder independent, change the data source as follows:

<connectionStrings>
<add name="ChinookEntities" connectionString="metadata=res://*/Chinook.csdl|res://*/Chinook.ssdl|res://*/Chinook.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=|DataDirectory|Chinook.sdf&quot;"
providerName="System.Data.EntityClient" />
</connectionStrings>

Deploy the database file

The final step will be done to ensure that the database file will be located in a writeable location on the users machine when deployed/installed. We will simply do this in code in order to not depend on any install actions and issues. We will use the same approach that I have already used in my blog post here, which takes advantage of the DataDirectory connection string macro. So add this piece of code to the Program class:

private const string dbFileName = "Chinook.sdf";
private static void CreateIfNotExists(string fileName)
{
string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
// Set the data directory to the users %AppData% folder
// So the database file will be placed in: C:\Users\<Username>\AppData\Roaming\
AppDomain.CurrentDomain.SetData("DataDirectory", path);

// Enure that the database file is present
if (!System.IO.File.Exists(System.IO.Path.Combine(path, fileName)))
{
//Get path to our .exe, which also has a copy of the database file
var exePath = System.IO.Path.GetDirectoryName(
new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);
//Copy the file from the .exe location to the %AppData% folder
System.IO.File.Copy(
System.IO.Path.Combine(exePath, fileName),
System.IO.Path.Combine(path, fileName));
}
}

Remember to add a call to CreateIfNotExists as the first line in the Main method:

static void Main(string[] args)
{
CreateIfNotExists(dbFileName);

You can now use ClickOnce, XCopy or an Installer to deploy your app (the files in the release folder), with no other requirements than the target .NET Framework version. Uninstall the 4.0 runtime from your PC in order to test (and/or test on another PC without SQL CE 4.0 installed)

What we have achieved:

– Simple, self contained deployment of a single user desktop app of any type to any .NET 4.0 or higher platform (not ARM, though)

– Automated creation of the required settings in app.config via NuGet packages

– RAD (Rapid App Development) “Database First” access to a well-performing, well-documented and powerful ORM.

You can download the complete solution (without packages) from here.

SQL Enterprise to SQL Standard – Handy Script to remove compression.

The following script displays the indexes and tables that use compression and generates a script that removes the compression.

SELECT DISTINCT ‘ALTER TABLE [‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + NAME + ‘] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’ 
FROM sys.partitions p
join 
sys.objects o
on p.
object_id = o.object_id
WHERE o.TYPE ‘u’
and data_compression_desc != 
‘NONE’
UNION 
SELECT ‘ALTER INDEX ALL ON [‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + NAME + ‘] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’
FROM sys.partitions p
join 
sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE ‘u’
and data_compression_desc != 
‘NONE’

Tuning the Performance of Backup Compression in SQL Server

Here is a great read on Tuning the Performance of Backup Compression in SQL Server

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

And a cheeky query to help give you a guide on the size of your backup if it was compressed !


SELECT
b.database_name ‘Database Name’,
CONVERT (BIGINT, b.backup_size / 1048576 ) ‘UnCompressed Backup Size (MB)’,
CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) ‘Compressed Backup Size (MB)’,
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /
CONVERT (FLOAT, b.compressed_backup_size))) ‘Compression Ratio’,
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) ‘Backup Elapsed Time (sec)’
FROM
msdb.dbo.backupset b
WHERE
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0
AND b.backup_size > 0
ORDER BY
b.backup_finish_date DESC


Table 1 shows the output of the above query after taking an uncompressed and a compressed backup.

Database Name Uncompressed Backup Size (MB) Compressed Backup Size (MB) Compression Ratio Backup Time (Seconds) Comments
BCTEST 292705 95907 3.05 1705 Compressed backup
BCTEST 292705 292705 1 3348 Uncompressed backup

Hekaton Explained

Here are some good sessions I found on SQL Server 2014 and the In-Memory OLTP (codenamed ‘Hekaton’) feature.