Destination SQL Server 2005 (Mobile Edition)


Hi Folks...

The title of this post may make it sound like a movie. But it ain't. This is a contemporary post noir set on the devices of a gritty, yet colorful SQL Server neighborhood... 😀 (That was gross, I concur! Hopefully the meat of the post will have some substance and better humor than its opening line)


The reason for this post was is the following question posted on one of our internal Data on Devices related aliases.



We wish to push SQL Server Mobile database file from a desktop application developed in VS2005 to the mobile device Here it is in a nutshell:


1) POPULATE the mobile database with data on the desktop
2) PUSH the mobile database to the mobile device



We have following Questions


1) Figuring out how to populate a SQL Server Mobile database file from a desktop application developed in VS2005 (and to do so directly, without RDA, Replication or XML).
2) Identify the best way to PUSH the above SQL Server Mobile file to the mobile device (i.e., do we still use RAPI's DesktopToDevice?)
3) Determining which .DLL's our application will need to install on the end-users Desktop PC to enable both the POPULATE and PUSH functionality as developed
4) Making sure we have Microsoft's permission to redistribute said .DLL's.


As you have noticed, we have a rich data-on-devices story with Visual Studio 2005. A lot of work went into this area since Visual Studio 2003 and data-on-devices story is now on par with the desktop data scenarios. Which means that the answer to the above question needs to be broadcasted to a much bigger audience...


Here it is, from Laxmi Narsimha Rao, my colleague from SQL Server 2005 Mobile Edition team:



I wanted to let you know the following things before jumping into answering your questions:


1) SQL Mobile is unrestricted only for devices and tablet-pc
2) SQL Mobile is unrestricted for desktops that have either VS 2005 or SQL 2005 installed


Given that information, coming to your operations:


1) POPULATE – This is done on the desktop. It means that you should either have VS 2005 or SQL 2005 in order for SQL Mobile to work. 
    a. Say the desktop has VS 2005, you can populate the database using our System.Data.SqlServerCe namespace 
        i. SqlCeEngine – Create Database 
        ii. SqlCeConnection and SqlCeCommand – Populate the database 
        (Please note that SQL Mobile supports parameterized queries. Let the customer know about this to get best out of SQL Mobile.)
2) PUSH – This is nothing but copying the database file created in the above step to the device. There are lot of ways you can do this: 
    a. VS 2005 Auto Deployment – Add SQL Mobile .SDF file to the device project and when you hit F5, it automatically deploys the .SDF file too 
    b. Use Active Sync File Explorer and copy manually 
    c. Use RAPI


And the answers for your questions:


1) RDA, Merge Replication – None of these is needed to populate the database when you have the data at your hand. But any of these is needed when you have the data in SQL Server.
2) As explained above
3) This depends on what your device app is going to do with database on device. If your app is using the DB just as a local store, then you just need first two cabs. If your device app is going to do some sync with SQL Server, then you need the third cab.  
4) Covered by earlier answers and information 


Let me know if you need some more info and/or other clarifications on the above topic. In fact any topic on Data-on-Devices.


As a side note, I own the Visual Studio for Devices, Data components and I do have plans for a few posts on Data-on-Device. But if there is something specific that you want me to make a post on, I'd be glad. Just let me know...


See you then, in my next post...

Comments (10)
  1. EricR says:

    Hi there!

    I’m the the original source of the "In a nutshell" that is the topic of this post on your blog.

    First, thank you for taking time to address our issue as we are at a dead-end. Your blog post is a fine summary of information presented on the newsgroups and elsewhere.

    Unfortunately, it is of NO HELP to us in our effort to move our Windows Mobile application from eVB/ADOCE to VS2005/SQL-Mobile.

    The "In a nutshell" text shown in your post above was originally preceeded by an explaination as to why we are trying to do what we are trying to do. This information is missing from your blog posting.

    Thus, your post is answering questions we already know the answer for — and fails to address the fact that MS has created a SHOWSTOPPER for us (and many other developers I am sure). To add insult to injury, this showstopper NOT really a technical issue, but appears to be nothing more than an ill-advised and even arbitrary fiat from my dear friends at MS.

    In a attempt to clarify I am posting my original inquiry in its entirety.

    — original inquiry —

    We are NOT an IT department looking to push data from our corporate database out to mobile device and then back again.

    We, like Microsoft are developers and sell software PRODUCTS. Just like with MS Excel or MS Project people purchase our product, put the CD into their computer, run the setup routine, then begin using the software.

    Our customers are industrial manufacturing plants. While almost all of our customers are major corporations (Dupont, Chevron, Dow-Corning, etc.) we are NOT dealing with the corporate business office. Often these plants are in remote and/or rural areas and in many cases the support received from their IT department is poor to non-existent. And in some cases they are not allowed to purchase ANY software requiring the assistance of the IT department, and in other cases doing so requires a 12-24+ month approval process. These IT departments don’t want a plant to mess up anything corporate initiatives and/or applications. And they don’t want to have to travel out to a plant to troubleshoot if anything goes wrong.

    Thus, our software product CANNOT install or require access to IIS. Nor can we require customers to implement SQL Server or grant our software product access to an existing corporate SQL Server. To do so would likely CUT OUR SALES IN HALF (or more) and would cause the majority of our existing customers to NOT upgrade when the next version is released.

    So, if I understand correctly, NO IIS means no RDA, and NO SQL Server means no merge replication. And quite honestly, we can see no good reason or advantages to adding such complexity to our product.

    Using our product on a desktop, users are provided a "Check-out" process which transfers the desired data onto their Pocket PC device. They then go do the work as indicated on the Pocket PC, marking work as completed along the way. When they are done they then use the provided "Check-In" process on the desktop to transfer the data back from the Pocket PC. For our users, this process is SIMPLE, EASY, RELIABLE, and fairly QUICK. In fact, he most difficult part about this process is making sure the Pocket PC is in the cradle and Active-Sync is running, and at that point it’s just a few mouse clicks.

    What’s going on inside the application when the user executes the above process is quite straightforward also. We simply move the appropriate data from the main database into a .MDB file, then call RAPI DesktopToDevice to push this file to the Pocket PC (via Active-Sync). On the Pocket PC, the file (now a .CDB) is used by our eVB code. Getting the data back is just as simple, call RAPI DeviceToDesktop to get the .MDB file back, then pull the data out of it and update the main database accordingly. For us as developers, this process is SIMPLE, EASY, RELIABLE, and fairly QUICK!

    Beyond getting Active-Sync installed and working correctly, the above approach is ZERO CONFIGURATION and creates NO SECURITY ISSUES for corporate to be concerned about!

    Here it is in a nutshell:

    1) POPULATE the mobile database with data on the desktop

    2) PUSH the mobile database to the mobile device

    POPULATE & PUSH — TA-DA! Done!

    It’s simple, elegant and very functional. Switching from this approach to RDA or Replication makes no sense in our situation. In fact, I can’t even imagine the extent of additional costs we would incur in Set-up (install) and configuration development, additional QC testing, and support incidents related to customer install and configuration issues.

    Last spring we tried to recreate this simple POPULATE & PUSH approach using SQL CE and VS2003/Compact-Frameworks (as replacements for .MDB and eVB). We discovered the only method to POPULATE the SQL CE file available to us was to use an intermediary XML file, and this proved to be WAY to slow. About this same time MS was beginning to disseminate information about the upcoming SQL Server Mobile. At one point, on a SQL Server Mobile beta info web-page (now gone), a product manager spoke of Microsofts awareness that RDA and Replication, while great for corporate IT types, did not meet the needs of many ISV’s, and that the shipping version of SQL Server Mobile would address this shortcoming and allow ISV’s to develop vertical applications that populated SQL Server Mobile from the desktop.

    Now, I know you can populate SQL Server Mobile if the OS is Windows XP Tablet. I know you can also do so from Windows XP (non tablet) if VS2005 is installed. This means there is NO technical reason we can’t develop this POPULATE & PUSH functionality using SQL Server Mobile and VS2005.

    So I am asking for help with the following:

    1) Figuring out how to populate a SQL Server Mobile database file from a desktop application developed in VS2005 (and to do so directly, without RDA, Replication or XML).

    2) Identify the best way to PUSH the above SQL Server Mobile file to the mobile device (i.e., do we still use RAPI’s DesktopToDevice?)

    3) Determining which .DLL’s our application will need to install on the end-users Desktop PC to enable both the POPULATE and PUSH functionality as developed

    4) Making sure we have Microsoft’s permission to redistribute said .DLL’s.

    — end original inquiry —

    Does this make our senerio clear?

  2. EricR says:

    Ah yes, one more thing.

    Since the time of the original inquiry late last year, we have become successful with Populate & Push using VS2005/SQL-Mobile on the development PC.

    So at this point, we only need help with items 3) & 4) as listed in my previous comments.

    And as long as I am here, let me reiterate a point from my original inquiry:

    "…About this same time MS was beginning to disseminate information about the upcoming SQL Server Mobile. At one point, on a SQL Server Mobile beta info web-page (now gone), a product manager spoke of Microsofts awareness that RDA and Replication, while great for corporate IT types, did not meet the needs of many ISV’s, and that the shipping version of SQL Server Mobile would address this shortcoming and allow ISV’s to develop vertical applications that populated SQL Server Mobile from the desktop."

    Thanks!

    EricR

  3. sriram says:

    Eric – I’m the PM for data and devices in Visual Studio 2005.

    I’ve sent a pretty long mail to the right folks on this.I’ll post something back here the moment they get back to me.

    I know this sounds like a typical corporate response – but trust me, it isnt – we care and we’ll get back to you as soon as we can.

    If you want to mail me, do so at sriramk at microsoft.com

  4. JoelT says:

    I am an ISV in the same boat, too bad MS is trying to sink our boat. EricR, have you found a solution to this huge delimma yet or heard back from MS?

  5. Björn Carlsson says:

    Also in the same little boat, I hope I don’t have to try to port SQLite to WindowsCE, just because of licence issues with SQL Server 2005 (Mobile Edition)…

  6. Anthony Wells says:

    Same problem here. Why is MS complicating everything. To me this is an obvious need for many developers that are creating and selling to the masses. MS has also stopped support for ActiveSync via wi-fi because of some cry baby in an IT department somewhere. That’s another issue though. Good post.

  7. parthopdas says:

    Eric, Joel, Bjorn and Tony: We are not trying to sink your boat. We are also not trying to complicate things. As a company we @ Microsoft strongly believe that we succeed only if you succeed.

    I think your issues should all be resolved with http://www.microsoft.com/sql/letter.mspx

    Let me know if you need more info…

Comments are closed.

Skip to main content