The Question:
I am writing a database application for use in disconnected situations. The application will be installed on laptops. The intention is for the users to connect to the central SQL Server 2000 database, get the data, then go into the field and use it. They may be disconnected for several days. They data needs to persist through application closings, power offs, etc. When they are done, they will reconnect and their changes will be sent back to the database. I am new to .NET, so am looking for some advice on how to best approach this. Thanks <name withheld>
My Answer:
This is a fairly common question for those new to .NET. Yes, ADO.NET is designed around a “disconnected” approach, but it’s designed for use with ASP (networked) applications that run under IIS. The DataTable and the DataSet objects that hold them disappear when the application is shut down—they are simply an in-memory data store. No, that does not mean that ADO.NET can’t work with an independent database architecture where data needs to be persisted between queries to the host—which is what you’re describing. It does, however suggest that you might need a local DBMS of some kind to persist and manage the data while the laptops (the client systems) are not connected to the LAN. A number of alternatives come to mind that you might consider. Fortunately, many of them are free—they can be implemented with royalty-free software. You might not need a DBMS at all if the data is not that complex and security is not an issue. Using a DBMS means you can write applications that can execute SQL queries and use the power of the DBMS to do what it’s designed to do: manage your data without you having to replicate its functionality in your own code. Here are a couple of ideas that might help:
· Design around SQL Server Express edition for your laptops. This approach leverages the full-featured SQL Server engine that has all the flexibility you’ll ever need—except for one feature: security. Deployed SQL Server databases can be easily opened and accessed once the physical system is compromised (stolen from a car or a hotel room). This means sensitive data must be encrypted and decrypted on a column-by-column basis. SQL Server Express is easier than ever to install and it’s managed just like SQL Server—using the same tools. This architecture is described in my new book Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition) which should be available in early November.
· Design around SQL Server Everywhere edition. This approach uses the DBMS engine formerly known as “SQL Mobile” (and SQLCE). SQL Server Everywhere (SQL Ev) implements a subset of ANSI SQL and (IMHO) is very different than SQL Server in many respects. However, it is fully securable and the entire database can be encrypted by simply setting a password. I think it’s a great solution for data you have to take to the field. It’s limited in capacity (4GB) but can outperform other DBMSs because its functionality is so limited. Microsoft is gearing up to fully integrate SQL Ev with the Visual Studio tools to make creating and managing the databases far easier.
· Consider using replication to keep your client databases in sync with the host server. Both SQL Server Express and SQL Ev can be a Subscriber to a SQL Server (Workgroup or better) Publication. The code to set this up could not be called “easy” but it’s manageable and probably worth the effort. I would shy away from having to create my own home-grown replication software and infrastructure. I’m writing a new EBook on SQL Server Everywhere and it includes a long section on how to setup and implement replication. It should be available sometime in October.