SQL Server CE RDA replication configuration –zz

SQL Server CE RDA replication configuration

What needs be installed before this tutorial

To complete this tutorial I am using the following configuration:

• Win 2000
• IIS 5 running
• eVB (eMbedded Visual Basic)
• SQL Server 2000 Developer Edition (this is what I use on my development Desktop PC)
• SQL Server 2000 Windows CE Edition
• lastly but not least your Pocket PC docked or attached via a wireless LAN

In my next tutorial you will be able to use this configuration together with the RemoteDataAccess object of SSCE (SQL Server CE Edition) to 'Pull' data from a remote SQL 2000 database populating a local SSCE table. The actual code to do this is pretty straight forward, however the tasks of setting up both the remote SQL Server and IIS are critical. It's a bit like a house of cards, missing one of these steps will cause the whole thing to fail with some cryptic message which will have you hankering after those DBASE days! Configuration is always a pain but in the immortal words of 'Tony Soprano' - "What you gonna do?'

Setting up SQL Server for CE

All the information in this section is contained in the SQL Server help file ("C:\Program Files\Microsoft SQL Server CE\sqlce.chm") - I will attempt to workflow it in as simple a fashion as possible - if all else fails you may have to RTFM - "read the manual".

To get it all hanging together just right we need to configure both SQL Server and Internet Information Server (IIS). What's IIS got to do with all of this you ask incredulously? Communication between the SQL Server (SS) CE Client Agent (residing on your Pocket PC) and SS CE Server Manager (residing on the SQL Server) is handled using the HTTP protocol. If you stop to contemplate this it's a very logical if not ingenious approach. SS CE uses IIS to enforce access authentication and permissions in addition to then using HTTP to transmit the data between the WINCE device and the SQL Server.

I would encourage you if possible to set up a development environment with Win 2000, IIS 5 and SQL Server 2000 all on the same system. I have done this and find that it makes trouble shooting connectivity issues and permissions very easy. I then installed MS ActiveSync, eVb and SS CE on this same PC for a complete SS CE test box. Since RDA supports 802.11b I threw in an Orinoco PC Card to make the whole thing wireless - but that's a topic for another rainy day.

Configuring SQL Server 2000 - the shortcut

A shortcut to configuring the SS access permissions is to use the 'SA' username to access the remote server from your WINCE device. Make sure you have the SA user name and password at hand. Since this is a test box - I'm not worried about restricting permissions just yet and doing it this way skips some of the potential permissions issues. You can always revisit this section and 'lock it down'.

Now let's configure IIS

We begin by configuring the SS CE server agent which is an ISAPI DLL.

1) create a directory which IIS will use for the SS CE server agent, e.g.
c:\inetpub\wwwroot\northwind

2) grab the sscesa10.dll file from it's default install directory:
c:\Program Files\Microsoft SQL Server CE\Server
and copy it to the directory you created in step 1:
c:\inetpub\wwwroot\northwind

3) Open a command prompt and register the DLL:
regsvr32 C:\Inetpub\wwwroot\NorthWind\sscesa10.dll

4) Now open the Internet Services Manager to create the virtual directory:
Right click the web site you want to create the new virtual directory in, in most cases using the 'Default Web Site' will be the right place.

Choose New then Virtual Directory. Click Next.
Choose an alias name, since we are using the Northwind data call it 'Northwind'. Click Next and navigate to the directory you created in #1: i.e. 'C:\Inetpub\wwwroot\northwind'
In the Access Permissions list check 'Execute' and disable all the rest.
Click Next then Finish.