Merge replication performance tips

The SQL Server Support team has posted some advice for improving initial subscription performance with SQL Compact 3.5, which for various reasons has decreased significantly compared to SQL CE 2.0 with SQL Server 2000. Note, that when pre-creating on the desktop you must use Windows XP/Server 2003 and not Vista/Server 2008, or index rebuild will occur on the device.

Propose / Vote for new features on CodePlex

If you have any ideas and suggestions for the future versions of the ExportSqlCe command line utility or SSMS 2008 addin, please go here and vote for or propose new features. Any ideas and suggestions are welcome, to the benefit for all users of the tools.

Scripting SQL datetime fields and avoiding localization problems

If you need to export you data to a .sql file for various reasons, you have different options when “serializing” datetime fields. Often they are scripted as: ‘2009-05-11 11:00’ or ‘May 5 2009 12:00AM’. This will break if the script is run with a tool/server that uses a different locale (like a European/Asian), and cause either run-time errors or even worse, incorrect dates in the database.

The Database Publishing Wizard / Publish to Provider… uses this format:

CAST(0x00008C3300000000 AS DateTime)

which is probably correct and portable, but not very notepad friendly.

I use the ODBC Escape sequence formatting, which looks like this, and works across any locale (and is more friendly on the eye!):

{ts ‘2009-05-11 23:00:00’}

Here’s the .NET code to create this format:

_sbScript.Append("{ts '");
_sbScript.Append(date.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture));
_sbScript.Append("'}");

Script SQL Compact data and schema directly from SQL Server 2008 Management Studio (Express)

I have just released ExportSqlCE support for scripting SQL Compact data and schema directly from the SQL Server Management Studio Object Explorer. Read more about it here.

SQL Compact version on Windows Mobile 6.5

I have just installed the Windows Mobile 6.5 emulator, and just wanted to check the version of SQL Compact installed on this OS – the version is: 3.0.5300.8, which corresponds to this hotfix: http://support.microsoft.com/kb/960142 (and all hotfixes prior to this build).

New features in Sync Framework

A 90 minute webcast detailing the new features in the next version of the Microsoft Sync Framework (version 2) is now available for offline viewing and PPT download. The new version with the features presented here will RTM in 2nd half of 2009 (and a CTP will come very soon – June (?)).

Notable new features (relating to SQL Compact) include:

P2P Sync with SQL Compact 23:00– both on desktop and devices – provides ability to Sync 2 SQL Compact databases with each other! Can’t wait to try that out!

Snapshot Initialization 39:40 pre generate the SQL Compact file on the server and then download to the client, and only incremental changes need to be downloaded afterwards. Limits time to get initial data down, and decrease server load. Initially all data is downloaded to the web service in a “source” SQL Compact file, and based on this a special snapshot SQL Compact file is created. The client know that this is a snapshot, and will only download incremental changes after getting the snapshot.

Opt out of ADO.NET DataSets – mapping to a developer defined payload. (Not in version 2)

Using SqlCeResultSet for client side inserts – to improve performance.

UPDATE: CTP2 is now available for download.