Sync Framework v4 is now Open Source, and ready to Connect any Device to SQL Server and SQL Azure

Sync

Microsoft has brought the power to synchronize data with either SQL Server on-premise or SQL Azure in the cloud to the world of mobility.

The profound effects of the Consumerization of IT (CoIT) is blurring the lines between consumers and the enterprise.  The fact that virtually every type of mobile device is now a candidate to make employees productive means that cross-platform, enabling technologies are a must. If you’ve ever synched the music on your iPhone with iTunes, the calendar on your Android device with Gmail, or the Outlook email on your Windows Phone with Exchange, then you understand the importance of sync.  In my experience architecting and building enterprise mobile apps for the world’s largest organizations over the last decade, data sync has always been a critical ingredient.

The new Sync Framework Toolkit found on MSDN builds on the existing Sync Framework 2.1’s ability to create disconnected applications, making it easier to expose data for synchronization to apps running on any client platform.  Where Sync Framework 2.1 required clients to be based on Windows, this free toolkit allows other Microsoft platforms to be used for offline clients such as Silverlight, Windows Phone 7, Windows Mobile, Windows Embedded Handheld, and new Windows Slates.   Additionally, non-Microsoft platforms such as iPhones, iPads, Android phones and tablets, Blackberries and browsers supporting HTML5 are all first-class sync citizens.  The secret is that we no longer require the installation of the Sync Framework runtime on client devices.  When coupled with use of an open protocol like OData for data transport, no platform or programming language is prevented from synchronizing data with our on-premise and cloud databases.  When the data arrives on your device, you can serialize it as JSON, or insert it into SQL Server Compact or SQLite depending on your platform preferences.

The Sync Framework Toolkit provides all the features enabled by theSync Framework 4.0 October 2010 CTP.  We are releasing the toolkit as source code samples on MSDN with the source code utilizing Sync Framework 2.1.  Source code provides the flexibility to customize or extend the capabilities we have provided to suit your specific requirements. The client-side source code in the package is released under the Apache 2.0 license and the server-side source code under the MS-LPL license.  The Sync Framework 2.1 is fully supported by Microsoft and the mobile-enabling source code is yours to use, build upon, and support for the apps you create.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now some of you might be wondering why you would use a sync technology to move data rather than SOAP or REST web services.  The reason has to do with performance and bandwidth efficiency.  Using SOA, one would retrieve all the data needed to the device in order to see what has changed in SQL Server.  The same goes for uploading data.  Using the Sync Framework Toolkit, only the changes, or deltas, are transmitted over the air.  The boosts performance and reduces bandwidth usage which saves time and money in a world of congested mobile data networks with capped mobile data plans.  You also get a feature called batching, which breaks up the data sent over wireless networks into manageable pieces.  This not only prevents you from blowing out your limited bandwidth, but it also keeps you from using too much RAM memory both on the server and your memory-constrained mobile device.  When combined with conflict resolution and advanced filtering, I’m sold!

I think you’ll find the Sync Framework Toolkit to be an immensely valuable component of your MEAP solutions for the enterprise as well as the ones you build for consumers.

Keep Synching,

-Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]

Consumerization of IT Collides with MEAP: Windows > Cloud

In my Consumerization of IT Collides with MEAP article last week, I described how to connect a Windows 7 device to Microsoft’s On-Premises servers.

Whether you’re talking about a Windows 7 tablet or laptop, I showed that you can follow the Garter MEAP Critical Capabilities to integrate with our stack in a consistent manner.  Remember, the ability to support multiple mobile apps across multiple mobile platforms, using the same software stack is a key tenant to MEAP.  It’s all about avoiding point solutions.

If you need a refresher on the Gartner MEAP Critical Capabilities, check out: http://robtiffany.com/meap/consumerization-of-it-collides-with-meap-windows-on-premises

In this week’s scenario, I’ll use the picture below to illustrate how Mobile versions of Windows 7 in the form of slates, laptops, and tablets utilize some or all of Gartner’s Critical Capabilities to connect to Microsoft’s Cloud infrastructure:

image

As you can see from the picture above:

  1. For the Management Tools Critical Capability, Windows 7 uses Windows Intune for Cloud-based device management and software distribution.
  2. For both the Client and Server Integrated Development Environment (IDE) and Multichannel Tool Critical Capability, Windows 7 uses Visual Studio. The Windows Azure SDK plugs into Visual Studio and provides developers with everything they need to build Cloud applications.  It even includes a Cloud emulator to simulate all aspects of Windows Azure on their development computer.
  3. For the cross-platform Application Client Runtime Critical Capability, Windows 7 uses .NET (Silverlight/WPF/WinForms) for thick clients. For thin clients, it uses Internet Explorer 9 to provide HTML5 + CSS3 + ECMAScript5 capabilities. Offline storage is important to keep potentially disconnected mobile clients working and this is facilitated by SQL Server Compact + Isolated Storage for thick clients and Web Storage for thin clients.
  4. For the Security Critical Capability, Windows 7 provides security for data at rest via Bitlocker, data in transit via SSL, & Authorization/Authentication via the Windows Azure AppFabric Access Control Serivce (ACS).
  5. For the Enterprise Application Integration Tools Critical Capability, Windows 7 can reach out to servers directly via Web Services or indirectly through the Cloud via the Windows Azure AppFabric Service Bus to connect to other enterprise packages.
  6. The Multichannel Server Critical Capability to support any open protocol is handled automatically by Windows Azure. Crosss-Platform wire protocols riding on top of HTTP are exposed by Windows Communication Foundation (WCF) and include SOAP, REST and Atompub. Cross-Platform data serialization is also provided by WCF including XML, JSON, and OData. Cross-Platform data synchronization if provided by the Sync Framework. These Multichannel capabilities support thick clients making web service calls as well as thin web clients making Ajax calls. Distributed caching to dramatically boost the performance of any client is provided by Windows Azure AppFabric Caching.
  7. As you might imagine, the Hosting Critical Capability is knocked out of the park with Windows Azure.  Beyond providing the most complete solution of any Cloud provider, Windows Azure Connect provides an IPSec-protected connection with your On-Premises network and SQL Azure Data Sync can be used to move data between SQL Server and SQL Azure.  This gives you the Hybrid Cloud solution you might be looking for.
  8. For the Packaged Mobile Apps or Components Critical Capability, Windows 7 runs cross-platform mobile apps include Office/Lync/IE/Outlook/Bing.

As you can see from this and last week’s article, Windows 7 meets all of Gartner’s Critical Capabilities whether it’s connecting to Microsoft’s On-Premises or Cloud servers and infrastructure.  They great takeaway from the picture above, is Windows 7 only needs to know how to integrate its apps with WCF in the exact same way as is does in the On-Premises scenario.  Windows developers can focus on Windows without having to concern themselves with the various options provided by Windows Azure.  Cloud developers just need to provide a WCF interface to the mobile clients.

When an employee walks in the door with a wireless Windows 7 Slate device, you can rest assured that you can make them productive via Windows Azure without sacrificing any of the Gartner Critical Capabilities.

Next week, I’ll cover how Windows Phone connects to an On-Premises Microsoft infrastructure.

Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]

SQL Server Compact 4.0 Lands on the Web

SQL Server

A decade has passed since I first started using SQL CE on my Compaq iPAQ.  What started as a great upgrade to Pocket Access turned into the ultimate embedded database for Windows CE, the Pocket PC, Windows Mobile and Windows Phones.  The one-two punch of Outlook Mobile synchronizing email with Exchange and SQL Server Compact synchronizing data with SQL Server helped set the mobile enterprise on fire.  In 2005, version 3.0 supported Windows Tablets and progressive enhancements to the code base led to full Windows support on both x86 and x64 platforms.  With the new version 4.0, the little-database-that-could has grown up into a powerful server database ready to take on the web.

We’ve come a long way and you’re probably wondering what qualifies this new embedded database to take on the Internet:

  • Native support for x64 Windows Servers
  • Virtual memory usage has been optimized to ensure the database can support up to 256 open connections – (Are you actually using 256 pooled connections with your “Big” database today?)
  • Supports databases up to 4 GB in size – (Feel free to implement your own data sharding schemeSQL Server Compact)
  • Developed, stress-tested, and tuned to support ASP.NET web applications
  • Avoids the interprocess communications performance hit by running in-process with your web application
  • Row-level locking to boost concurrency
  • Step up to Government + Military grade security SHA2 algorithm to secure data with FIPS compliance
  • Enhanced data reliability via true atomicity, consistency, isolation, and durability (ACID) support
  • Transaction support to commit and roll back grouped changes
  • Full referential integrity with cascading deletes and updates
  • Support ADO.NET Entity Framework 4 – (Do I hear WCF Data Services?)
  • Paging queries are supported via T-SQL syntax to only return the data you actually need

Wow, that’s quite a list!  SQL Server Compact 4.0 databases are easily developed using the new WebMatrix IDE or through Visual Studio 2010 SP1.  I’m loving the new ASP.NET Web Pages.  It reminds me of the good old days of building web applications with Classic ASP back in the 90’s with Visual InterDev and Homesite.

What about Mobility?

Since SQL Server Compact owes its heritage to mobile and embedded versions of Windows, you might be wanting to know what our story is there.  The good news is that you can build and deploy v4.0 databases on Windows XP, Windows Vista, and Windows 7.  If you want to implement an occasionally-connected solution that utilizes the Sync Framework, Remote Data Access (RDA), or Merge Replication, you’ll need to stick with SQL Server Compact 3.5 SP2.  Time and resource-constraints prevented the Compact team from enabling these features.  Luckily, single-user WPF/WinForms database applications running on Windows Slates, laptops and Windows Embedded Handheld devices will work just fine with the v3.5 SP2 runtime.  Get a jumpstart with this by pickup up “Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5 Mobile Merge Replication” at   http://www.amazon.com/Enterprise-Synchronization-Microsoft-Compact-Replication/dp/0979891213/ref=sr_1_1?s=books&ie=UTF8&qid=1281715114&sr=1-1 to start building those MEAP solutions.

With the tidal wave of Windows Slates hitting the market, a secure, powerful mobile database that allows users to work offline and syncs with SQL Server is definitely going to be a hot item!

So run, don’t walk to the Microsoft Download site to download the Next-Gen database for the web:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=033cfb76-5382-44fb-bc7e-b3c8174832e2

If you need to support occasionally-connected mobile applications with sync capabilities on muliple Windows platforms, download SQL Server Compact 3.5 SP2:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e497988a-c93a-404c-b161-3a0b323dce24

Keep Syncing,

Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]

Reducing SQL Server Sync I/O Contention :: Tip 3

Primary Key
GUIDs and Clustered Indexes

Uniqueness is a key factor when synchronizing data between SQL Server/Azure and multiple endpoints like Slates and Smartphones.  With data simultaneously created and updated on servers and clients, ensuring rows are unique to avoid key collisions is critical.  As you know, each row is uniquely identified by its Primary Key.

Primary Key

When creating Primary Keys, it’s common to use a compound key based on things like account numbers, insert time and other appropriate business items.  It’s even more popular to create Identity Columns for the Primary Key based on an Int or BigInt data type based on what I see from my customers.  When you designate a column(s) to be a Primary Key, SQL Server automatically makes it a Clustered Index.  Clustered indexes are faster than normal indexes for sequential values because the B-Tree leaf nodes are the actual data pages on disk, rather than just pointers to data pages.

While Identity Columns work well in most database situations, they often break down in a data synchronization scenario since multiple clients could find themselves creating new rows using the same key value.  When these clients sync their data with SQL Server, key collisions would occur.  Merge Replication includes a feature that hands out blocks of Identity Ranges to each client to prevent this.

When using other Microsoft sync technologies like the Sync Framework or RDA, no such Identity Range mechanism exists and therefore I often see GUIDs utilized as Primary Keys to ensure uniqueness across all endpoints.  In fact, I see this more and more with Merge Replication too since SQL Server adds a GUID column to the end of each row for tracking purposes anyway.  Two birds get killed with one Uniqueidentifier stone.

Using the Uniqueidentifier data type is not necessarily a bad idea.  Despite the tradeoff of reduced join performance vs. integers, the solved uniqueness problem allows sync pros to sleep better at night.  The primary drawback with using GUIDs as Primary Keys goes back to the fact that SQL Server automatically gives those columns a Clustered Index.

I thought Clustered Indexes were a good thing?

They are a good thing when the values found in the indexed column are sequential.  Unfortunately, GUIDs generated with the default NewId() function are completely random and therefore create a serious performance problem.  All those mobile devices uploading captured data means lots of Inserts for SQL Server.  Inserting random key values like GUIDs can cause fragmentation in excess of 90% because new pages have to be allocated with rows pushed to the new page in order to insert the record on the existing page.  This performance-killing, space-wasting page splitting wouldn’t happen with sequential Integers or Datetime values since they actually help fill the existing page.

What about NEWSEQUENTIALID()?

Generating your GUIDs on SQL Server with this function will dramatically reduce fragmentation and wasted space since it guarantees that each GUID will be sequential.  Unfortunately, this isn’t bulletproof.  If your Windows Server is restarted for any reason, your GUIDs may start from a lower range.  They’ll still be globally unique, but your fragmentation will increase and performance will decrease.  Also keep in mind that all the devices synchronizing with SQL Server will be creating their own GUIDs which blows the whole NEWSEQUENTIALID() strategy out of the water.

Takeaway

If you’re going to use the Uniqueidentifier data type for your Primary Keys and you plan to sync your data with RDA, the Sync Framework or Merge Replication, ensure that Create as Clustered == No for better performance.  You’ll still get fragmentation, but it will be closer to the ~30% range instead almost 100%.

Keep synching

Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]

Reducing SQL Server I/O Contention during Sync :: Tip 2

Database Storage
Indexing Join Columns

In my last Sync/Contention post, I beat up on a select group of SAN administrators who aren’t willing to go the extra mile to optimize the very heart of their organization, SQL Server.  You guys know who you are.

This time, I want to look at something more basic, yet often overlooked.

All DBAs know that Joining tables on non-indexed columns is the most expensive operation SQL Server can perform.  Amazingly, I run into this problem over and over with many of my customers.  Sync technologies like the Sync Framework, RDA and Merge Replication allow for varying levels of server-side filtering.  This is a popular feature used to reduce the size of the tables and rows being downloaded to Silverlight Isolated Storage or SQL Server Compact.

It’s also a performance killer when tables and columns participating in a Join filter are not properly indexed.  Keeping rows locked longer than necessary creates undue blocking and deadlocking.  It also creates unhappy slate and smartphone users who have to wait longer for their sync to complete.

Do yourselft a favor and go take a look at all the filters you’ve created and makes sure that you have indexes on all those Joined columns.

Keep synching,

Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]

Reducing SQL Server I/O Contention during Sync :: Tip 1

SAN Storage
RAID

Sync technologies like Merge Replication and the Sync Framework track changes on SQL Server using triggers, stored procedures and special tracking tables.  The act of tracking changes made by each SQL Server Compact or Silverlight sync subscriber can cause a lot of locking and blocking on the server.  This diminishes performance and sometimes leads to deadlocks. SAN Storage

Therefore, don’t listen to your SAN administrator when he says the RAID 5 will do.  RAID 1 or 10 must always be used for all databases, tempdb, and transaction logs.  Furthermore, each of these database objects must be placed on their own dedicated RAID arrays.  No sharing!  Remembers, as a DBA and sync expert, knowledge of SAN configuration must always be part of your skillset.

Keeping synching,

Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]

Sync Framework 2.1 has Arrived!

Sync

Sync Framework 2.1 includes all the great functionality of our 2.0 release, enhanced by several exciting new features and improvements.

The most exciting of these lets you synchronize data stored in SQL Server or SQL Server Compact with SQL Azure in the cloud. We’ve added top customer requests like parameter-based filtering and the ability to remove synchronization scopes and templates from a database, and of course we’ve made many performance enhancements to make synchronization faster and easier. Read on for more detail or start downloading now!

 

SQL Azure Synchronization

With Sync Framework 2.1, you can leverage the Windows Azure Platform to extend the reach of your data to anyone that has an internet connection, without making a significant investment in the infrastructure that is typically required. Specifically, Sync Framework 2.1 lets you extend your existing on premises SQL Server database to the cloud and removes the need for customers and business partners to connect directly to your corporate network. After you configure your SQL Azure database for synchronization, users can take the data offline and store it in a client database, such as SQL Server Compact or SQL Server Express, so that your applications operate while disconnected and your customers can stay productive without the need for a reliable network connection. Changes made to data in the field can be synchronized back to the SQL Azure database and ultimately back to the on premises SQL Server database. Sync Framework 2.1 also includes features to interact well with the shared environment of Windows Azure and SQL Azure. These features include performance enhancements, the ability to define the maximum size of a transaction to avoid throttling, and automatic retries of a transaction if it is throttled by Windows Azure. All of this is accomplished by using the same classes you use to synchronize a SQL Server database, such as SqlSyncProvider and SqlSyncScopeProvisioning, so you can use your existing knowledge of Sync Framework to easily synchronize with SQL Azure.

 

Bulk Application of Changes

Sync Framework 2.1 takes advantage of the table-valued parameter feature of SQL Server 2008 and SQL Azure to apply multiple inserts, updates, and deletes by using a single stored procedure call, instead of requiring a stored procedure call to apply each change. This greatly increases performance of these operations and reduces the number of round trips between client and server during change application. Bulk procedures are created by default when a SQL Server 2008 or SQL Azure database is provisioned.

 

Parameter-based Filtering

Sync Framework 2.1 enables you to create parameter-based filters that control what data is synchronized. Parameter-based filters are particularly useful when users want to filter data based on a field that can have many different values, such as user ID or region, or a combination of two or more fields. Parameter-based filters are created in two steps. First, filter and scope templates are defined. Then, a filtered scope is created that has specific values for the filter parameters. This two-step process has the following advantages:

  • Easy to set up. A filter template is defined one time. Creating a filter template is the only action that requires permission to create stored procedures in the database server. This step is typically performed by a database administrator.
  • Easy to subscribe. Clients specify parameter values to create and subscribe to filtered scopes on an as-needed basis. This step requires only permission to insert rows in synchronization tables in the database server. This step can be performed by a user.
  • Easy to maintain. Even when several parameters are combined and lots of filtered scopes are created, maintenance is simple because a single, parameter-based procedure is used to enumerate changes.

Removing Scopes and Templates

Sync Framework 2.1 adds the SqlSyncScopeDeprovisioning and SqlCeSyncScopeDeprovisioning classes to enable you to easily remove synchronization elements from databases that have been provisioned for synchronization. By using these classes you can remove scopes, filter templates, and the associated metadata tables, triggers, and stored procedures from your databases.

 

SQL Server Compact 3.5 SP2 Compatibility

The Sync Framework 2.1 SqlCeSyncProvider database provider object uses SQL Server Compact 3.5 SP2. Existing SQL Server Compact databases are automatically upgraded when Sync Framework connects to them. Among other new features, SQL Server Compact 3.5 SP2 makes available a change tracking API that provides the ability to configure, enable, and disable change tracking on a table, and to access the change tracking data for the table. SQL Server Compact 3.5 SP2 can be downloaded here.

For more information about Sync Framework 2.1, including feature comparisons, walkthroughs, how-to documents, and API reference, see the product documentation.

Stay in sync and Download Sync Framework 2.1 today and find out what synchronization can do for you!

-Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]

Microsoft SQL Server Compact 3.5 SP2 has Arrived

SQL Server Compact

My favorite embedded database for Windows Phones, laptops, tablets and desktops has been released to the Web along with Visual Studio 2010.

New features for SQL Server Compact 3.5 SP2 include:SSCE

  • Supports working with a SQL Server Compact 3.5 database using the Transact-SQL Editor in Visual Studio 2010. The Transact-SQL Editor can be used to run free-text Transact-SQL queries against a SQL Server Compact 3.5 database. The Transact-SQL Editor also provides the ability to view and save detailed estimated and actual query show-plans for SQL Server Compact 3.5 databases. Previously, the functionality provided by the Transact-SQL Editor was only available through SQL Server Management Studio.
  • New classes and members named SqlCeChangeTracking have been added to the System.Data.SqlServerCe namespace to expose the internal change tracking feature used by Sync Framework to track changes in the database. The SQL Server Compact change tracking infrastructure maintains information about inserts, deletes, and updates performed on a table that has been enabled for change tracking. This information is stored both in columns added to the tracked table and in system tables maintained by the tracking infrastructure. By using System.Data.SqlServerCe.SqlCeChangeTracking one can configure, enable, and disable change tracking on a table, and also access the tracking data maintained for a table. The API can be used to provide functionality in a number of scenarios. For example it can be used to provide custom implementations of client-to-server or client-to-client sync for occasionally connected systems (OCS) or to implement a custom listener application.
  • The managed assemblies of SQL Server Compact for use by the applications that privately deploy SQL Server Compact are installed in the folder %Program Files%\Microsoft SQL Server Compact Edition\v3.5\Private. Using these assemblies ensure that the application uses the privately deployed version of Compact even when a lower version of SQL Server Compact 3.5 is installed in the GAC.
  • Visual Studio 2010 installs both the 32-bit and 64-bit versions of SQL Server Compact 3.5 SP2 on a 64-bit machine. If a SQL Server Compact application is deployed using Click Once in Visual Studio 2010 then both the 32-bit and the 64-bit version of SQL Server Compact are installed on a 64-bit machine
  • SQL Server Compact 3.5 SP2 adds support for Windows Mobile 6.5, Windows 7 and Windows Server 2008 R2, and can sync data using Merge Replication and RDA with SQL Server 2008 R2 November CTP.
  • The SqlCeReplication object gets a new property called PostSyncCleanup which you can use to prevent SQL Server Compact from Updating Statistics after an initial Merge Replication initialization.  This has the potential to shave a substantial amount of time off of your initial syncs depending on the size of your database.

 

In addition to these new features, the following hotfixes from SQL Server 2005 Compact Edition or SQL Server Compact 3.5 SP1 have been rolled up in SQL Server Compact 3.5 SP2:

  • http://support.microsoft.com/kb/953259: Error message when you run an SQL statement that uses the Charindex function in a database that uses the Czech locale in SQL Server 2005 Compact Edition: “The function is not recognized by SQL Server Compact Edition”
  • http://support.microsoft.com/kb/958478: Error message when you run a “LINQ to Entities” query that uses a string parameter or a binary parameter against a SQL Server Compact 3.5 database: “The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses”
  • http://support.microsoft.com/kb/959697: Error message when you try to open a database file from a CD in SQL Server Compact 3.5 with Service Pack 1: “Internal Error using read only database file”
  • http://support.microsoft.com/kb/960142: An error message is logged, and the synchronization may take a long time to finish when you use an application to synchronize a merge replication that contains a SQL Server 2005 Compact Edition subscriber
  • http://support.microsoft.com/kb/963060: An error message is logged, and the synchronization may take a long time to finish when you synchronize a merge replication that contains a SQL Server Compact 3.5 subscriber: “UpdateStatistics Start app=<UserAppName>.exe”
  • http://support.microsoft.com/kb/967963: Some rows are deleted when you repair a database by using the Repair method together with the RepairOption.RecoverCorruptedRows option in SQL Server 2005 Compact Edition and in SQL Server Compact 3.5
  • http://support.microsoft.com/kb/968171: Error message when you try to create an encrypted database in SQL Server 2005 Compact Edition: “The operating system does not support encryption”
  • http://support.microsoft.com/kb/968864: Error message when you run a query in SQL Server Compact 3.5: “The column name cannot be resolved to a table. Specify the table to which the column belongs”
  • http://support.microsoft.com/kb/969858: Non-convergence occurs when you synchronize a SQL Server Compact 3.5 client database with the server by using Sync Services for ADO.NET in a Hub-And-Spoke configuration
  • http://support.microsoft.com/kb/970269: Access violations occur when you run an application under heavy load conditions after you install the 64-bit version SQL Server Compact 3.5 Service Pack 1
  • http://support.microsoft.com/kb/970414: Initial synchronization of a replication to SQL Server Compact 3.5 subscribers takes significant time to finish
  • http://support.microsoft.com/kb/970915: Error message when you synchronize a merge replication with SQL Server 2005 Compact Edition subscribers: “A column ID occurred more than once in the specification. HRESULT 0x80040E3E (0)”
  • http://support.microsoft.com/kb/971027: Error message when you upgrade a very large database to SQL Server Compact 3.5: “The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only”
  • http://support.microsoft.com/kb/971273: You do not receive error messages when you run a query in a managed application that returns columns of invalid values in SQL Server Compact 3.5
  • http://support.microsoft.com/kb/971970: You cannot insert rows or upload changes into the SQL Server 2005 Compact Edition subscriber tables after you run the “sp_changemergearticle” stored procedure or you add a new merge publication article when another article has an IDENTITY column
  • http://support.microsoft.com/kb/972002: Error message when you try to create an encrypted database in SQL Server Compact 3.5: “The operating system does not support encryption”
  • http://support.microsoft.com/kb/972390: The application enters into an infinite loop when you run an application that uses Microsoft Synchronization Services for ADO.NET to synchronize a SQL Server Compact 3.5 database
  • http://support.microsoft.com/kb/972776: When the application calls the SqlCeConnection.Close method or the SqlCeConnection.Dispose method in SQL Server Compact 3.5, the application may stop responding at the method call
  • http://support.microsoft.com/kb/974068: Error message when an application inserts a value into a foreign key column in SQL Server Compact 3.5: “No key matching the described characteristics could be found within the current range”

 

Web downloads for SQL Server Compact 3.5 SP2 is as listed below:

SQL Server Compact 3.5 SP2 for Windows desktop (32-bit and 64-bit)

Note that the file available for download is a 6 MB self-extracting executable (exe) file that contains the 32-bit and the 64-bit Windows Installer (MSI) files for installing SQL Server Compact 3.5 SP2 on a 32-bit and a 64-bit Computer. It is important to install both the 32-bit and the 64-bit version of the SQL Server Compact 3.5 SP2 MSI on a 64-bit Computer. Existing SQL Server Compact 3.5 applications may fail if only the 32-bit version of the MSI file is installed on the 64-bit computer. Developers should chain both the 32-bit and the 64-bit MSI files with their applications and install both of them on the 64-bit Computer. Refer to the KB article for more information.

SQL Server Compact 3.5 SP2 for Windows mobile devices (all platforms & processors)

SQL Server Compact 3.5 SP2 Server Tools (32-bit and 64-bit)

SQL Server Compact 3.5 SP2 Books Online (Note that the books online will be available for download by the third week of April 2010)

SQL Server Compact 3.5 SP2 Samples

Visual Studio 2010 and .NET Framework 4

This is a great release for SQL Server Compact that adds some important new features, squashes a bunch of bugs and adds support for our newest operating systems.  I strongly recommend you update your existing SSCE runtimes with SQL Server Compact 3.5 SP2.

Keep on Synching,

Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]

What ever happened to RDA?

Who remembers using RDA Remote Data Access to synchronize data between SQL Server and SQL Server Compact?  I certainly do!

Before I dove head first into the world of Merge Replication, I always used RDA to get my customers up and running quickly.  Mobilizing an organization’s workforce quickly and easily is what it’s all about so they can start reaping the benefits.  In addition to a speedy time to market, there’s no faster or more scalable mobile sync technology on the market anywhere.

So why wouldn’t I always use RDA?  Here’s a quick list:

  1. You’re using Identity columns.
  2. You want to replicate schema changes to the client.
  3. You want change tracking on both the client and server to perform diffs of each of the tables during a sync instead of re-downloading the entire table.
  4. You want to automatically resolve conflicts that arise when 2 people update the same data.
  5. You want referential integrity constraints to be pushed down to the client database from SQL Server.
  6. You don’t want to write code to perform synchronization or filter data.

If anything on the above list applied to you, you would shift to Merge Replication because it could manage ranges of Identity columns, push down schema changes, only sync data differences, resolve conflicts and push down a database’s referential integrity constraints.  Merge requires almost no code to get started and tables and columns are filtered visually via a wizard.

So why might you choose to use RDA?  Here’s another list:

  1. Your Primary Keys use GUIDs instead of Identity columns.
  2. Users don’t overwrite each other’s data so you don’t need conflict resolution.  The rule of “Last in Wins” works for you.
  3. While you want indexes to be pushed down, you don’t care if your local SSCE database has referential integrity constraints applied.
  4. You want to wrap the changes you upload to SQL Server in a transaction so that all changes are applied or none of them are.
  5. Change tracking on the client is good enough and re-downloading updated server tables doesn’t take too long.
  6. You developers don’t mind writing some sync code.
  7. Be able to execute SQL and Stored Procedures directly against SQL Server via IIS.
  8. You’re downloading read-only data.

If your solution meets the criteria in the list above, you’re probably a good candidate for using RDA instead of Merge.  Are there any other choices out there?

Back at MEDC 2007, we announced a new data replication technology for devices called Occasionally Connected Sync that would sit somewhere between RDA and Merge.  OCS as it used to be called was renamed Sync Services for ADO.NET and then was eventually merged into the Sync Framework.

The Sync Framework is a developer-focused technology:

  1. Supports conflict resolution.
  2. Change tracking on the server as well as the client so that only data differences are exchanged.
  3. Peer to Peer sync in the forthcoming v2 of Sync Framework.
  4. Sync with databases other than SQL Server.
  5. Best suited for SSCE running on a desktop or laptop.

The clearest differentiation that the Sync Framework has over Merge is its provider model which allows it to sync with other ADO.NET databases like Oracle or DB2.  SQL Server supports built-in P2P Transactional replication and v2 of the Sync Framework will allow you to do this via WCF.  If you development team doesn’t mind writing lots of sync code and needs to support scenarios like synchronizing with other databases from SSCE on the desktop, then the Sync Framework might be the way to go for you.  I wouldn’t yet recommend the Sync Framework for device sync since its wire protocol is currently based on the DataSet which may cause out of memory errors on Windows phones with limited working sets.

So where does this leave RDA?

The reason I’m writing this blog post is because time and time again I run into customer sync scenarios that don’t always need the power of Merge or the extra flexibility of the Sync Framework.  Most field service applications follow the same kind of pattern:

  1. Lots of download-only lookup/reference tables that aren’t changed by the user.
  2. Tables that are pushed down to the device that tell a user where to go and what to do.
  3. Tables (sometimes empty) that are used to capture data from the user in the field that are upload-only.

These kinds of schemas don’t require conflict resolvers or server change tracking and are therefore well suited for RDA.

What’s the big benefit of using RDA if a sync scenario meets its criteria?

  1. You won’t modify SQL Server’s schema with GUIDs and Triggers.
  2. You won’t degrade the performance of SQL Server by having it track changes and maintain extra metadata.
  3. You will have the fastest and most scalable sync solution with least amount of hardware.
  4. Time to market is shorter.

The big takeaway here is that I want you to consider your sync solution carefully before choosing a technology.  If your customer’s needs are met by RDA, then you should use it and reap the benefits of developing and deploying a simpler solution with fewer moving parts.

Remember Occam’s Razor.

-Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]