Configuring Internet Information Services for SQL Server 2005 Compact Edition Remote Data Access and Merge Replication

  • Article

 

Microsoft Corporation

January 2007

Applies to:
   Microsoft SQL Server 2005 Compact Edition
   Microsoft SQL Server 2005
   Microsoft Internet Information Services (IIS)

Summary: Learn how to configure Internet Information Services (IIS) for both remote data access (RDA) and merge replication, two technologies that you can use to synchronize data between a Microsoft SQL Server 2005 back-end database and a local Microsoft SQL Server 2005 Compact Edition database. This tutorial covers everything that you must do to configure a server for data synchronization using RDA. To perform data synchronization with merge replication, you must also configure SQL Server 2005. See the Books Online documentation, "Implementing Replication," for information on how to configure SQL Server 2005 for merge replication with SQL Server Compact Edition. (24 printed pages)

Contents

Tutorial Objective
Configuring IIS for SQL Server Compact Edition Remote Data Access and Merge Replication
Conclusion
Appendix A: Setting Up Your Computer

Tutorial requirements

The following applications are required to run this tutorial:

  • Microsoft Windows XP Professional

  • Microsoft Internet Information Services (IIS)

    This tutorial uses IIS for Windows XP Professional. Be sure that the IIS component is installed. For more information, see Appendix A.

  • Microsoft Visual Studio 2005

    This tutorial requires Visual Studio 2005 Standard, Professional, or Team System Edition. It will not work with any of the Express Editions. If you do not have the correct edition of Visual Studio 2005, find out how you can acquire it from the Visual Studio 2005 Developer Center.

  • Microsoft SQL Server 2005

    Find out how to acquire a copy at the SQL Server Developer Center.

  • Visual Studio 2005 Service Pack 1

  • SQL Server 2005 Compact Edition

Credentials used

In this tutorial, you will need the following information:

  • Computer's name
  • Current Windows user's account name

Level

200

Estimated time to complete this tutorial

15 minutes

Tutorial Objective

Learn how to configure Internet Information Services (IIS) for both remote data access (RDA) and merge replication, two technologies that can be used to synchronize data between a SQL Server 2005 back-end database and SQL Server 2005 Compact Edition.

If you are interested in learning how to create an application that synchronizes SQL Server Compact Edition data, see the "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual Basic.NET" and "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual C#.NET" tutorials.

In this tutorial, you will perform the following exercise:

  • Configuring IIS for SQL Server Compact Edition remote data access and merge replication

Configuring IIS for SQL Server Compact Edition Remote Data Access and Merge Replication

RDA and merge replication both use an IIS ISAPI plug-in to synchronize data between SQL Server and SQL Server Compact Edition. In order to configure IIS for either RDA or merge replication, you must install the SQL Server 2005 Compact Edition Server Tools and then run the Configure Web Synchronization Wizard.

This exercise covers everything that you must do to configure a server for data synchronization using RDA. To perform data synchronization with merge replication, you must also configure SQL Server 2005. See the Books Online documentation, "Implementing Replication," for information on how to configure SQL Server 2005 for merge replication with SQL Server Compact Edition.

**Important Note   **When you install Visual Studio 2005 with the default options, the setup procedure installs Visual Studio device extensions, including the SQL Server 2005 Mobile Edition 3.0 runtime and the SQL Server 2005 Mobile Edition Server Tools installation package. Note that the SQL Server 2005 Mobile Edition Server Tools installation package is not run automatically when Visual Studio 2005 is installed, but an installation package is copied to your machine for optional installation. However, if this package has been run and your machine already has SQL Server 2005 Mobile Edition Server Tools installed, you should uninstall it before continuing with this tutorial. If you do not uninstall it, the paths that you are instructed to use in this tutorial will differ from the corresponding paths on your machine. The first task in this tutorial instructs you to install the new SQL Server 2005 Compact Edition Server Tools, which should not be done until SQL Server 2005 Mobile Edition Server Tools are uninstalled.

To install SQL Server 2005 Compact Edition Server Tools

You will use Windows Explorer to navigate to and run the Server Tools installation package.

  1. Click Start | Run.

  2. In the Open drop-down list of the Windows Run dialog box, type C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0, and then click OK, as shown in Figure 1.

    Figure 1. The Windows Run dialog box

  3. Double-click the Sqlce30setupen.msi file to run the SQL Server 2005 Compact Edition Server Tools installation package, as shown in Figure 2.

    Click here for larger image

    Figure 2. Selecting the Sqlce30setupen.msi installation package (Click on the picture for a larger image)

  4. After the Microsoft SQL Server 2005 Compact Edition setup wizard appears, as shown in Figure 3, click Next.

    Figure 3. The Microsoft SQL Server 2005 Compact Edition setup wizard

  5. When you are presented with the software license agreement, click I accept the terms in the license agreement, and then click Next, as shown in Figure 4.

    Figure 4. Accepting the license agreement

    The installation program then performs a system configuration check to verify that your computer is properly configured for SQL Server Compact Edition Server Tools. This might take a few moments.

  6. If a Status of Warning is displayed for SQL Server requirement, you can click the More button to the right of that line in the dialog box to see the reason for the warning, as shown in Figure 5.

    Figure 5. System Configuration Check

    As you can see in Figure 6, the warning informs you that you will not be able to synchronize with SQL Server 2000. For the purposes of this tutorial, you can ignore this warning.

    **Note   **If you have SQL Server 2000 Replication Components installed on your machine, you will not see this warning.

    Figure 6. System Configuration Check screen

  7. Click OK to close the warning dialog box.

    **Note   **If you receive warnings for any other system configuration checks, click the More button next to each warning and follow the displayed recommendations to meet the requirements.

  8. Click Next to continue with the setup wizard.

    The Microsoft SQL Server Version screen appears, as shown in Figure 7.

    Figure 7. Microsoft SQL Server Version screen

    On the Microsoft SQL Server Version screen, if SQL Server 2000 replication components are not installed, the check boxes are disabled, and Synchronize with SQL Server 2005 is checked. You can also modify the installation path for the Server Tools on this screen.

  9. Make sure that only Synchronize with SQL Server 2005 is checked, and then click Next.

  10. Click Install to install the Server Tools, as shown in Figure 8.

    Figure 8. The setup wizard is ready to install the program.

  11. After the wizard completes the installation, click Finish, as shown in Figure 9.

    Figure 9. The setup wizard is complete.

To create a merge-replication snapshot share

For a merge-replication publication, you must create a shared network folder that will contain a snapshot of the server data. You will use this shared folder in the next task when you configure synchronization.

  1. Click Start | Run.

  2. In the Open drop-down list of the Windows Run dialog box, type C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\sync\SQL, and then click OK.

  3. In the Windows Explorer window that is displayed, click File | New | Folder.

  4. When the new folder appears, type ReplicationData as the new folder name, as shown in Figure 10, and then press ENTER.

    Click here for larger image

    Figure 10. Creating the snapshot share folder (Click on the picture for a larger image)

    Before sharing the folder, you must make sure that your computer is not set to use simple file sharing.

  5. In the Windows Explorer window, click Tools | Folder Options.

  6. In the Folder Options dialog box, click the View tab, and then scroll to the bottom of the Advanced settings list.

  7. Be sure to clear the Use simple file sharing (Recommended) option, as shown in Figure 11, and then click OK. Turning this option off allows you to set specific sharing and security options, which you will be doing in the next few steps.

    Figure 11. The Folder Options dialog box

    Now, you can share the snapshot folder.

  8. Right-click on the ReplicationData folder in Windows Explorer, and then click Sharing and Security.

  9. In the Properties dialog box, on the Sharing tab, select the Share this folder radio button, as shown in Figure 12.

    Figure 12. Sharing a folder

  10. Click OK.

  11. Leave the Windows Explorer window open, because you will use it in the next task.

To configure IIS for SQL Server Compact Edition synchronization

  1. In the Windows Explorer folder (which should be open to C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\sync\SQL), notice the file sqlcesa30.dll. This file is the ISAPI extension that IIS will use to perform SQL Server synchronization. To make configuration easy, you can use the ConnWiz30.exe wizard, as you will now see.

  2. Double-click the ConnWiz30.exe file to run the wizard, as shown in Figure 13.

    Click here for larger image

    Figure 13. Selecting the ConnWiz30.exe wizard (Click on the picture for a larger image)

  3. After the wizard appears, as shown in Figure 14, click Next.

    Figure 14. Configure Web Synchronization Wizard welcome screen

  4. On the Subscriber Type page, select SQL Server Compact Edition, as shown in Figure 15, and then click Next.

    Figure 15. The Subscriber Type page

    **Note   **The subscriber-type selection is a radio button, because each IIS virtual directory must be configured for a specific synchronization subscriber type: synchronization with SQL Server Compact Edition or synchronization with SQL Server itself. For the purposes of this tutorial, you will configure only SQL Server Compact Edition synchronization.

  5. On the Web Server page of the wizard, be sure that your computer name appears in the Enter the name of the computer running IIS text box. In a production environment, you would most likely use an IIS synchronization server that is separate from your SQL Server data server to avoid exposing your database directly to the Internet. But for the purposes of this tutorial, you will configure your machine as both the SQL Server data server and the IIS synchronization server.

  6. Select Create a new virtual directory.

  7. Still on the Web Server page of the wizard, under Select the Web site in which to create the new virtual directory, expand the local computer, expand Web Sites, select Default Web Site, as shown in Figure 16, and then click Next.

    Figure 16. The Web Server page

  8. On the Virtual Directory Information page, in the Alias text box, type Sync; in the Path text box, type C:\Inetpub\wwwroot, as shown in Figure 17; and then click Next. The Alias value of Sync is both the virtual directory name and the name of the physical directory that is created under the directory name that is specified in the Path text box, C:\Inetpub\wwwroot.

    Figure 17. The Virtual Directory Information page

  9. If you are prompted with a message that says The folder does not exist, click Yes to create the folder.

  10. If you are prompted with a message that says This folder does not contain a copy of the SQL Compact Edition Server Agent (or SQL Mobile Server Agent), click Yes to copy and register the agent. This action copies the appropriate synchronization DLLs to the new virtual directory.

  11. On the Secure Communications page, leave the Do not require secure channel (SSL) option selected, as shown in Figure 18, and then click Next. If your Web server had an SSL certificate installed, you would have the option of using SSL for synchronization communication.

    Figure 18. The Secure Communications page

  12. On the Client Authentication page, select the Clients will be authenticated. A user name and password will be required option, as shown in Figure 19, and then click Next. While anonymous connection is possible, it is not recommended for security purposes.

    Figure 19. The Client Authentication page

  13. On the Authenticated Access page, make sure that Integrated Windows authentication is checked, as shown in Figure 20, and then click Next. This setting specifies that the user who is currently logged on will be used when the client connects to the Web server for synchronization. The Basic authentication option can be used when you want a specific user name and password to be used to access the synchronization Web site.

    Figure 20. The Authenticated Access page

  14. On the Directory Access page, click the Add button to add accounts that will require access to the virtual directory.

  15. In the Select Users or Groups dialog box that appears, type the name of the Windows account on which you are currently logged. If you are using a domain account, be sure to include the domain name when typing the account name. Click the Check Names button to ensure that the account is found.

    The dialog box then displays the fully qualified account name and underlines it, as shown in Figure 21.

    Figure 21. Selecting your Windows user account for Web synchronization

  16. Click OK.

  17. Make sure that your user name appears in the Group or user names list and that the check box at the bottom of the page is checked, and then click Next, as shown in Figure 22.

    Figure 22. Adding your Windows user account for Web synchronization

  18. On the Snapshot Share Access page, in the Share box, type \\your_machine_name\ReplicationData, where your computer's name replaces the italicized text, as shown in Figure 23. The share name, ReplicationData, corresponds to the merge-replication share that you created in the previous task (To create a merge-replication snapshot share). The wizard sets the appropriate access permissions on the snapshot directory to limit directory access to those users and groups that you selected in the previous wizard screen. Click Next.

    Figure 23. The Snapshot Share Access page

    **Note   **If you receive an error message that the share does not exist, make sure that you properly performed the previous task (To create a merge-replication snapshot share) to create the share, and make sure that your computer is properly connected to the network.

  19. When you are prompted with a warning that the snapshot share is empty, click Yes to continue. Normally, when configuring the server for merge replication, you initially publish the merge-replication data into the directory prior to sharing the directory on the Web. The wizard gives the warning that no files are found in the directory as a safety check that you are actually sharing the directory that you expect. If you had already created a merge-replication publication and populated it with an initial snapshot in the shared folder location, you would not receive this warning. Because the focus of this tutorial is on the steps related to remote data access, you can ignore this warning.

  20. On the Complete the Wizard page, click Finish to complete the configuration, as shown in Figure 24.

    Figure 24. Completing the wizard

  21. After the Configure Web Synchronization Wizard completes, a page that shows the results of all of the configuration actions is displayed, as shown in Figure 25. All actions should display Success at this point. Click Close to exit the wizard.

    Figure 25. The Configure Web Synchronization wizard successfully finishes.

To test that SQL Server Compact Edition synchronization is configured

  1. Click Start | Run.

  2. In the Open drop-down list of the Windows Run dialog box, type https://localhost/Sync/sqlcesa30.dll, and then click OK. This URL corresponds to the Web share that you created by using the wizard in the previous task.

    Your default Web browser should appear with the text Microsoft SQL Server Compact Edition Server Agent, as shown in Figure 26, indicating that replication has been properly configured. This can be a helpful debugging tool as a first step to ensure that you can connect to the replication Web share and that it is responding as expected.

    Figure 26. The replication agent responds.

    When you are finished, close the Internet browser and any Windows Explorer windows that are still open.

Conclusion

In this tutorial, you performed the following exercise:

  • Configuring IIS for SQL Server Compact Edition remote data access and merge replication

In this tutorial, you have learned how to configure IIS for both remote data access (RDA) and merge replication, two technologies that can be used to synchronize data between a SQL Server 2005 back-end database and SQL Server 2005 Compact Edition.

If you are interested in learning how to create an application that synchronizes SQL Server Compact Edition data, see the "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual Basic.NET" and "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual C#.NET" tutorials.

Appendix A: Setting Up Your Computer

Before you begin this tutorial, you must be sure that the following procedures are completed.

To install Internet Information Services

  1. On the desktop computer, click Start | Control Panel | Add or Remove Programs.
  2. In the Add or Remove Programs dialog box, click Add/Remove Windows Components.
  3. In the Windows Components list, select Internet Information Services (IIS), if it is not already selected, and then continue to click Next until the Windows Components wizard is complete.
  4. Close the Add or Remove Programs dialog box.

© Microsoft Corporation. All rights reserved.