index > SQL Server Everywhere Edition > SQL Server Mobile with Visual Studio 2003

SQL Server Mobile with Visual Studio 2003

I've recently noticed the availability of SQL Server Mobile, and would like to take advantage of some of the new features, however, I'm currently using Visual Studio 2003. The requirements state that Visual Studio 2005 is required, but I was wondering if anyone has had any success getting it to work with VS 2003?

I hope that I can do this with VS 2003, but if VS 2005 will be required, I'd like to clear up a few issues before asking my workplace to fork out more money for the upgrade:

What have your experiences been like in getting clients to upgrade from SQL Server CE to SQL Server Mobile? I've seen some postings regarding problems with specific devices which seem a bit worrying, but I was wondering how easy it was in general.
Does SQL Server Mobile really do a good job of reclaiming wasted space? Using SqlCeEngine's Compact method has caused problems due to the required storage space to perform the operation.
The documentation states that using SQL Server 2000 with SP3A is supported, but I was wondering if anyone has any experience with this?

Thanks in advance,
Adrien.

Adrien Regimbald

Adrien,

You will need to use VS2005 in order to develop applications with SQL Mobile.

In terms of upgrading, there are a number of considerations - upgrading the .NET Compact Framework, upgrading any existing .SDF files, and if you are using RDA or Merge Replication, upgrading the client replication tools. Perhaps the easiest way to achieve this is to post the new CABs to an IIS site and allow existing users to pull self-installing CABs from within Pocket Internet Explorer. Have the user synchronize any existing SQL CE data with the server, and then either perform a replication with AddOption.CreateDatabase to create the new SQL Mobile database dynamically on device, or provide a "starter" SQL Mobile database that can be included in the app CAB file (include it in your VS2005 project as Content)

SQLMobile does automatically reclaim unused space and recalulates index stats, but you should read the SQL Mobile Books OnLine and explore the new methods the SqlCeEngine object exposes that allow finite control over WHEN this happens and specific behaviors. It is vastly better than SQL CE where the solution to all performance and corruption problems was to compact the database.

Replication with SQL Server 2000 SP3a and later is supported. I have been helping a few people who are having trouble with this model though - if you have a very simple merge replication publication, it will work well. I am seeing issues with complex filters on the publication and of course you lose all of the enhancements that come with the SQL Mobile server tools that depend on SQL Server 2005. These are significant things like column-level tracking of changes and ability to propagate simple schema changes to the subscriber.

My suggestion would be to do a proof of concept with SQL Mobile and your existing application. There is an upgrade tool that runs at the command line on device that can convert a SQL CE database to SQL Mobile.

Have fun,

Darren

.NET Compact Framework MVP

Darren Shaffer

Hello Darren,

Your reply should help me decide which route to take, thanks! One thing does come to mind after having read it though. I currently use RDA rather than replication because I couldn't find a way to filter data based on a parameter passed from the Pocket PC. Of course I could create filters by having each user have a login for SQL Server, then make a publication per login, but with thousands of users, that's an administrative nightmare. Are there any new features in SQL Server 2005 / SQL Mobile 2005 that might make this easier?

Thanks again,
Adrien.

Adrien Regimbald

The typical approach to getting a subset of the data (perhaps based on user or device id) with RDA is using SubmitSQL to call a stored procedure on the server (not a good idea to send plain SQL due to security e.g. SQL injection attacks).

-Darren

.NET Compact Framework MVP

Darren Shaffer

Hmm, it seems I've confused you a bit. I have no problems filtering data using RDA, my problems are with trying to filter it using replication. Unless of course, you mean to use RDA to start a filter to be used by replication somehow? The scenario you mention of filtering by user or devide ID is exactly the sort of thing I'd like to do, but with replication. I already have this working with RDA, but I'd like to harness the better performance of replication.

Thanks,
Adrien.

Adrien Regimbald

reply 5