Pre-building a Database (SQL Server Compact Edition)

  • Article

If your device application will be a replication Subscriber and will be connecting to a database that has lots of information, it can be very time consuming for each device to perform the initial synchronization with Microsoft SQL Server. To save time, you can create the initial database, populate it with the data from the initial synchronization, and then copy the database to each of your devices. You can pre-build the database on a single device, or you can pre-build it on your development computer if you have SQL Server 2005 installed.

Note

Because Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) databases that are involved in replication are uniquely identified by a Subscriber ID, SQL Server Compact Edition automatically assigns a new Subscriber ID on the next synchronization if the database is moved or copied to a new location.

Pre-building the Database on a Smart Device

To pre-build a SQL Server Compact Edition database on a smart device, follow these steps:

  1. Install your application on a smart device.
  2. Use the application to create a new SQL Server Compact Edition database file. Then, use your application to connect to a SQL Server database by using either remote data access (RDA) or replication, and perform the initial data download to the local database file.
  3. Stop the application.
  4. Use Microsoft ActiveSync to connect to the device.
  5. Use the ActiveSync Explorer function to copy the .sdf file from the smart device to your development computer.
  6. You can now copy the .sdf file to other smart devices by using ActiveSync Explorer, or you can install it as part of the application's .cab installation file.

Pre-building the Database with SQL Server 2005

SQL Server 2005 Management Studio lets you create a SQL Server Compact Edition .sdf file on the development server. This can save you time because you do not have to deploy an application to a device, synchronize the data, and then manually copy the database file back to the server. To create the database file directly on the desktop, you must have SQL Server 2005 installed.

For more information about creating the SQL Server Compact Edition database locally, see How to: Create a SQL Server Compact Edition Database on a Server.

After you create the database, you can populate it with data from a publication. For more information, see How to: Create a SQL Server Compact Edition Subscription (SQL Server Management Studio).

After the .sdf file has been created and populated with data, you can distribute it to supported devices by:

  • Using ActiveSync to copy the .sdf file to smart devices
  • Copying the .sdf file directly to Tablet PCs
  • Copying the .sdf file to removable media, such as Secure Digital or CompactFlash media
  • Including the .sdf file in the installation .cab file for smart device applications or the installation .msi or .exe file for Tablet PC applications.

Subscribing to Publications with Auto Identity Range Management

Publications that use Auto Identity Range Management allocate a range of identity (ID) values to each Subscriber. This prevents ID conflicts when multiple Subscribers are adding new rows to the publication. To ensure that each Subscriber has a unique range of IDs, each of your Subscribers must synchronize with the publication after the initial .sdf file is copied and before new rows are added to the database.