SQL Server CE Remote Data Access and Replication

(1)

F

O

U

R

134

SQL Server CE Remote Data Access

and Replication

Remote Data Access (RDA) and replication are two techniques that allow you to take data from an SQL Server running on Windows NT or Windows 2000 and use the data in an SSCE database on a Pocket PC or HPC 2000 device. The data can be modified locally, and then changes can be sent back to the SQL Server database. Data conflicts (such as when a single record is modified in both the SSCE and SQL Server database) can be tracked and resolved. With both techniques, the Pocket PC or HPC 2000 device connects to the network through an Internet Information Server (IIS) using HTTP, and IIS can be used to apply login authorization and secure the transport of data.

In general, RDA is easier to setup and is most appropriate when devices are infrequently connected to the network, and where your Pocket PC or HPC 2000 applications need control on when data will be transferred and complete control over conﬂict resolution. RDA will work with SQL Server versions 6.5, 7.0, and 2000.

Replication can be more difﬁcult to setup and requires more SQL Server administration knowledge. It is ideal where a device is nearly always con-nected (e.g., through a wireless connection such as GPRS), but it can still al-low data changes to be made when the device is not connected. Replication only works with SQL Server 2000.

For the sake of clarity, SQL Server for Windows CE will be referred to as SSCE in this chapter, and SQL Server 6.5, 7.0, or 2000 running on Win-dows NT or WinWin-dows 2000 will be referred to as SQL Server.

Some typical uses of RDA and replication include

(2)

● Pulling down up-to-date price lists from a central server

● Recording orders locally on an SSCE database and then sending them to

a central SQL Server

● Pulling down updated customer details, making changes to the customer

lists in the SSCE database, and sending changes back to the SQL Server Because IIS is used to connect to the SQL Server, Windows NT authenti-cation can be used to secure access. Since HTTP is used to transfer data, Se-cure Socket Layer (SSL) HTTP requests can be used to seSe-cure the data in tran-sit. Using HTTP makes deployment easier — most firewalls and proxy servers are configured to allow HTTP requests from outside the organization, so addi-tional ports and protocols do not need to be configured. Finally, the data is compressed in transit, making transmission efficient even over low bandwidth connections.

You will need to plan carefully the amount and nature of the data you will be transferring using RDA or replication. Storage on Pocket PC and HPC 2000 devices is limited (your entire customer database may not ﬁt!), and the more data you involve in RDA or replication, the slower the transmission will be. In most cases, it is possible to identify subsets of the data, either by se-lecting rows (“select customers assigned to a particular salesperson”) or col-umns of data (“only these colcol-umns of data are relevant in the mobile situa-tion”). Ensure that you test your RDA or replication setup across the typical connection speeds your users will operate over, rather than the fast network connection you may use in development.

Remote Data Access

RDA allows you to pull the entire contents of a table, or selected columns and rows of data, from a Microsoft SQL Server 6.5, 7.0, or 2000 database and store the contents in an SSCE database table. The table in SSCE will automatically be created by the pull operation, and the pull process will convert SQL Server data types to appropriate SSCE data types. Primary key and other constraints will also be created in the local SSCE database table, but relationships with other tables will not be created.

Once a table is pulled down into an SSCE database the data can be ac-cessed and manipulated using ADOCE. You can add relationships with other tables, such as declaring foreign keys using ADOXCE. When a table is pulled you can turn on tracking, and this allows you to push the changes you have made to the SSCE table back up to the SQL Server database. You can specify a table name that will contain a list of conﬂicts detected when a push is made.

RDA also allows you to execute SQL statements from a Pocket PC or HPC 2000 on an SQL Server. Any SQL statement can be executed, including stored

(3)

136

Chapter 4

_•

SQL Server CE Remote Data Access and Replication

procedures, INSERT, DELETE, and UPDATE statements, so long as the SQL statement does not return a result set.

RDA operates through Internet Information Server (IIS) running on Win-dows NT 4 or WinWin-dows 2000 and can use an SQL Server located on the same server as IIS or on a different server. The conﬁguration of RDA can be a little tricky, as you need to conﬁgure IIS, SQL Server, and Windows NT or Windows 2000 security, so you should follow the instructions in the next section carefully.

RDA Installation and Configuration

Figure 4.1 shows the RDA architecture, the key elements of which are

● SQL Server CE Client Agent ● SQL Server CE Server Agent

The Client Agent implements the “SSCE.RemoteDataAccess.1.0” object that you program against to push and pull tables and to execute SQL state-ments on an SQL Server database. This functionality is implemented in the ﬁle

ssceca10.dll, and this should be installed in the \windows directory on the Pocket PC or HPC 2000 device.

The Server Agent receives push, pull, and SQL execute requests from the client agent and passes these requests on to the SQL Server. It manages the

re-Remote Data Access architecture

Figure 4.1

(4)

turned data and conﬂict resolution. This functionality is implemented in the ﬁle

sscesa10.dll, which is an ISAPI (Internet Server API) application that is in-stalled in a Web virtual directory and is managed by IIS.

REQUIREMENTS

To install and use RDA you will need

● A Pocket PC or HPC 2000 with SSCE installed (as described in the last

chapter), together with the ﬁlessceca10.dllcopied into the\windows

directory on the device

● A Windows NT or Windows 2000 server running IIS

● An SQL Server 6.5, 7.0, or 2000 server running on either the same server

as IIS or a different one

The Pocket PC or HPC 2000 should be capable of connecting to the IIS server. This can be a direct network connection using a network card or a dial-up RAS (Remote Access Server) connection. Making RAS connections is de-scribed in Chapter 8. Alternatively, you can use the SQL Server Relay through an ActiveSync connection using a serial or USB connection, as described later in this chapter.

While you can use Windows NT or Windows 2000, and either SQL Server version 6.5, 7.0, or 2000, the instructions in this chapter describe setting up RDA on a Windows 2000 Server with SQL Server 2000 run-ning on the same server. Instructions for Windows NT and other versions of SQL Server can be found in the on-line documentation.

The examples in this section will show how to setup RDA to the pubs

sample database shipped with SQL Server. The same principles can be applied to any database. The example here uses a Windows 2000 Server called “malahide” with an IP address of “190.100.100.120”. You should change these where appropriate to your server name and address as you follow through these instructions.

INSTALLING SSCE SERVER TOOLS

You will need to install the SSCE Server Tools on your server running IIS. Note that if you have IIS and SQL Server on separate machines, you need only run the SSCE Server Tools setup on the IIS machine.

● Run the ssce11.exe setup application.

● On the opening screen, ensure that “Server Tools” is selected and

“De-velopment Tools” is unselected.

● Click Next.

You can now follow the wizard steps to complete the SSCE Server Tools installation.

(5)

138

Chapter 4

_•

CONFIGURING IIS

The best place to start conﬁguring RDA is with the IIS server. You will need to

● Create a virtual directory

● Copy the ﬁle sscesa10.dllinto the directory

● Set ﬁle and directory access permissions in this directory ● Specify the authentication control used on the website.

First, run the “Internet Information Services” application and create the virtual directory:

● Select the Start Programs Administrative Tools Internet Ser-vices Manager menu command.

Alternatively, on Windows 2000 Professional you can

● Run the Control Panel

● Double-click the “Administrative Tools” icon ● Double-click the “Internet Services Manager” icon

To create the virtual directory

● Expand out your server name (“malahide” in Figure 4.2), and “Default

Web Site”

● Right-click “Default Web Site” and select the New Virtual Web Direc-torymenu

Selecting this menu command starts the “Virtual Directory Creation Wizard.”

● Click Nextin the opening screen.

● Enter the alias name for the virtual directory (see Figure 4.3). In this ex-ample you should use sscePubs. Click Next.

The virtual directory needs a folder that it will map to for content.

● Use File Explorer to create a new folder \Inetpub\wwwroot\ssce Pubs.

● Select this folder in the “Web Site Content Directory” wizard page (see

Figure 4.4). Click Next.

The next screen allows you to specify the access permissions for this website. RDA is implemented through an ISAPI DLL, and so Execute permis-sions must be enabled. Other permispermis-sions are not required.

● Ensure only “Execute (such as ISAPI applications or CGI)” is selected in

the “Access Permissions” wizard page (see Figure 4.5). Click Next and then Finish.

The next stage is conﬁguring Directory Security options for this new vir-tual directory. It is crucial to get this right; otherwise, you will end up with an insecure website or will not be able to access the website at all.

(6)

● Right-click the new sscePubsvirtual directory in the “Default Web Site” list in the “Internet Information Services” application.

● Select the Propertiesmenu command.

● Select the Directory Security tab in this dialog and click Edit.

This displays the “Authentication Methods” dialog (see Figure 4.6). By de-fault, anonymous login will be enabled (unless this option is changed in “De-fault Web Site” within Internet Information Services), and this speciﬁes a login that will be used if the application making the HTTP request does not sup-ply a Windows NT or Windows 2000 username and password. By default, the username is IUSR_<servername>where <servername>is replaced with the name of the service IIS is running on (for example, IUSR_Malahide). Click-ing Editallows you to change the login name used for anonymous access.

“Integrated Windows authentication” allows the client application to specify a username and password that will be used to authenticate access to IIS and is more secure than using anonymous login. You should avoid using “Basic authentication.”

Internet Information Services

(7)

140

Chapter 4

_•

In this example, anonymous login will be used; however, you may wish to use “Integrated Windows authentication” in a production system.

● Click the Editbox in the “Authentication Methods” dialog and record the

name of the anonymous username.

● Close all the open dialogs.

Note that the “Directory Security” page allows you to setup certificates for enabling Secure Socket Layer (SSL) for encrypting data in transit. This al-lows you to use HTTPS rather than HTTP when using RDA. You should en-able SSL when transmitting data across public networks such as the Internet. You now need to copy the ISAPI DLL into the folder used by the virtual directory you have just created and to set the correct permissions on the directory.

● Run File Explorer and copy the ﬁle sscesa10.dllfrom the install loca-tion (which is, by default, in the folder \Program files\Microsoft SQL Server CE\Server) to \InetPub\wwwroot\sscepubs.

Selecting the virtual alias name

Figure 4.3

(8)

● Run a command shell (cmd.exe), navigate to the \InetPub\wwwroot \sscepubsfolder, and run regsvr32on this DLL:

Regsvr32 sscesa10.dll

This registers the COM components in this DLL. This DLL needs to read and write ﬁles in the \InetPub\wwwroot\sscepubs folder, so the anony-mous user (or other users if you are using Integrated Windows authentication) must be granted access. Note that this is not done though the Internet Infor-mation Server administration program, since remote client does not need to perform the reads and writes — it is the DLL itself that does.

● Run File Explorer, and select the \InetPub\wwwroot\sscepubsfolder.

● Right-click the folder sscepubsand select Properties. ● Click the Securitytab (see Figure 4.7).

● Click the Addbutton to add another user to list of users granted

permis-sions on the directory.

● Select your IUSR_<servername> user (e.g., IUSR_MALAHIDE) from the

list of users, click the Addbutton and click OK. Selecting the Web site content directory

(9)

142

Chapter 4

_•

● Select the newly added user in the “Name” list, and click “Full Control”

in the “Permissions” list.

● Click OKto accept the change to the security settings.

TESTING THE IIS INSTALLATION

You can now test the IIS installation at a basic level by attempting to execute

sscesa10.dll through Internet Explorer.

● Run Internet Explorer on your server PC.

● Enter http://localhost/sscePubs/sscesa10.dll in the address line and press Enter.

If IIS is conﬁgured correctly you should see the word “Body” being dis-played. This indicates that IIS could connect and execute the ISAPI function in

sscesa10.dll. If you get an error 500 returned, check that you have copied

sscesa10.dllinto the virtual directory folder and that you have successfully registered the DLL with regsvr32.dll. If you get a permissions problem, check that you have assigned execute permissions to the virtual directory and read /write permissions on the folder.

Selecting the access permissions

Figure 4.5

(10)

Directory security

Figure 4.6

You can check the username being used to access IIS through the logs:

● Navigate to the folder \winnt\system32\logfiles\W3SVC1

● Open the most recently accessed log ﬁle with Notepad and scroll to

the end.

You will see lines like the following:

2001-04-06 12:28:37 190.100.100.98 - 190.100.100.120 80 POST /sscePubs/sscesa10.dll - 200 SSCEReplicationClient

This shows the date and time of the HTTP request followed by the IP dress of the requester and the IP address of IIS. The “-” between the two IP ad-dresses indicates that an anonymous login was used to access IIS. If Windows authentication was used, the domain name and username would be shown:

2001-04-05 18:30:40 190.100.100.98 MALAHIDE\Administrator 190.100.100.120

80 POST /sscePubs/sscesa10.dll - 200 SSCEReplicationClient

TESTING THE POCKET PC OR HPC 2000 CONNECTION

You can now test that your Pocket PC or HPC 2000 can connect to your IIS server by using Pocket Internet Explorer.

(11)

144

Chapter 4

_•

Folder security

Figure 4.7

● Run Pocket Internet Explorer on your Pocket PC or HPC 2000 device. ● Enter the address http://Malahide/sscepubs/sscesa10.dll in

the address line, replacing “Malahide” with the name of your server. Pocket Internet Explorer should display the text “Body” if it can connect to sscesa10.dll.

Note that the screen will be blank if you have “Fit to Screen” turned on, so use the View Fit to Screenmenu to turn off this option and en-sure that the text “Body” appears.

If you cannot connect, try using the IP address of your server rather than the server name. For example, enterhttp://190.100.100.120/sscepubs/

(12)

sscesa10.dllin the address line, replacing “190.100.100.120” with the IP ad-dress of your server. If this solves the problem it is likely that the Pocket PC or HPC 2000 cannot connect to a Domain Name Server (DNS), or the DNS is not configured properly. You can either fix the DNS problem or continue to use an IP address in place of the server name. You will need to be careful, though, if your server has a server-assigned IP address rather than a fixed IP address — the next time the server reboots, the IP address may change, and you will no longer be able to connect.

Once you have IIS conﬁgured, you can move on to conﬁguring SQL Server.

CONFIGURING SQL SERVER 2000

When conﬁguring SQL Server 2000 there are two types of security you can use:

● Mixed security (“SQL Server and Windows”):Users can login either

using an SQL Server login identiﬁer or using integrated security with an automatic login using a Windows username.

● Windows only:Integrated security only with a Windows username. This

is sometimes known as “trusted security.”

When using RDA, the type of security is selected through a connection string from within your program. However, you will need to ensure that the connection string you choose uses a security type configured on SQL Server. In this example, “Mixed Security” will be used so that code using both SQL Server login identifiers and integrated security can be shown. To configure security

● Run the “SQL Server Enterprise Manager” by selecting the Start Pro-gramsMicrosoft SQL ServerEnterprise Managermenu command.

● Expand out “Microsoft SQL Servers” under “Console Root” in the “Tree” pane until you locate your server (e.g., “Malahide”).

If you cannot locate your server, right-click “SQL Server Group” in the “Tree” pane, select the New SQL Server Registration menu command, and follow the “Register SQL Server Wizard” instructions. Next, you will set the se-curity options for your server.

● Right-click your server name (e.g., “Malahide”) in the “Tree” pane, and

select the Propertiesmenu command.

● Select the Securitytab (see Figure 4.8).

● Ensure that “SQL Server and Windows” security is selected. ● Ensure that “All” is selected for “Audit level”.

Selecting “All” for “Audit level” causes SQL Server to write an event into the application event log for all attempts at logging into the server, regardless of whether they succeed or fail. Using this option when setting up RDA is use-ful, as you can quickly determine the username being used by IIS to access SQL Server. However, you should remember to reset this option at some stage

(13)

146

Chapter 4

_•

SQL Server security options

Figure 4.8

to ensure your event log does not become full. Figure 4.9 shows a typical event log for a successful login using integrated (trusted) security for the anonymous login user IUSR_MALAHIDE.

You will now need to check that users have the correct access rights to the database being accessed through RDA. In this example the user in ques-tion is IUSR_MALAHIDE and the database is pubs.

● Expand out your server (e.g., “Malahide”) in the “Tree” pane so that

“Se-curity” is visible, and then expand out “Se“Se-curity” so “Logins” is visible.

● Single-click “Logins” to display the current list of logins (see Figure 4.10). 04-P1914 8/24/2001 11:26 AM Page 146

(14)

SQL Server login

Figure 4.9

Notice that there is not currently an IUSR_MALAHIDE user added to this list. If you were to access the SQL Server through IIS with anonymous login, you would get an error explaining that IUSR_MALAHIDE was not a trusted user of the SQL Server. You now need to add a trusted user.

● Right-click “Logins” in the “Tree” pane and select the New Loginmenu

command.

● In the “SQL Server Login Properties —New Login” dialog (see

Fig-ure 4.11), click the button with the caption “. . .” to the right of the “Name” edit box.

● Double-click IUSR_MALAHIDE from the list of users and click OK. ● Click the “Database Access” tab in the “SQL Server Login Properties —

(15)

148

Chapter 4

_•

SQL Server logins

Figure 4.10

● Select the “Permit” check box for the “pubs” database.

● Select the “db_owner” role for the “pubs” database (see Figure 4.12). ● Click on OKto create the login.

Note that the user IUSR_MALAHIDE has had the “db_owner” role se-lected — this grants full access to all objects in this database. You would nor-mally not make a user a “db_owner” unless the user was a developer. Instead, you should set the permissions on all objects appropriately through the data-base’s user permission settings.

That completes the SQL Server conﬁguration and the RDA setup —you now need to execute some RDA code to test the conﬁguration. Remember, if you have trouble logging in to SQL Server or need to check the actual login being used, use the Event Viewer to look at the application event log.

Programming RDA

RDA is programmed through the RemoteDataAccessobject’s methods and properties. This object is created using the progid “SSCE.RemoteDataAc-cess.1.0”. A reference to the library “Microsoft SQL Server Control 1.0” should be added to the project using the eVB Project Add Referencesmenu com-mand. All the code shown in this section can be found in the project \Ex amples\PocketPC\RDA\RDA.ebp or \Examples\HPC2000\RDA\RDA.ebp

on the CDROM. The code to use RDA is the same for Pocket PC and HPC 2000.

(16)

Creating a new SQL login

Figure 4.11

PULLING DATA

The RemoteDataAccess object’s Pull method allows a table to be down-loaded from an SQL Server database and the data down-loaded into an SSCE table. The Pullmethod automatically creates the table in the SSCE database and pro-vides suitable data type conversions where the SQL Server database uses data types that are unsupported in SSCE.

The Pull method is passed an SQL statement that will be executed on the SQL Server, and this defines the data to be downloaded. In the simplest case, you can specify a SELECT * FROM tabletype statement to download all fields and all records from the table. Alternatively, you can specify a list of fields in place of “*” or add a WHEREclause to filter only particular records.

(17)

Be-150

Chapter 4

_•

Database access for an SQL Server login

Figure 4.12

cause almost any SQL statement producing a result set can be used, stored pro-cedures, views, or SELECTstatements joining several tables can be used. How-ever, if you later want to push data back up onto the server, you should en-sure that the recordset resulting from executing the SQL statement is updateable.

There are some limitations to be aware of when using the RDA Pull

method.

● With SSCE databases, object names are not case sensitive, whereas SQL

Server databases can optionally be conﬁgured as case sensitive. You can-not use RDA against a case-sensitive SQL Server database.

● You cannot pull data from a table having a primary key of type CHAR,

NCHAR, VARCHAR, or NVARCHAR where the length of the ﬁeld is greater

(18)

than 255 characters. In a Pull, ﬁelds of this type with a length greater than 255 are mapped to NTEXT, and an NTEXT ﬁeld cannot be a pri-mary key.

● When using RDA to pull data from a table that also participates in

repli-cation on the server, you must exclude the system column with the

ROWGUIDCOL attribute. By default, this column is named RowGuid.

● The number of columns pulled must be 253 or less. RDA adds two extra

columns for tracking changes made to pulled data.

● You need to apply service pack 6 to SQL Server 6.5 when tracking data

changes.

Pull can be used with two different data change tracking options:

● Tracking Off:The data is pulled from the SQL Server and SSCE does not monitor changes made to the data locally. Changes cannot be automati-cally pushed back to the server.

● Tracking On:Data changes made locally will be tracked. At a later stage, the changes can be pushed back to the server. Optionally, a table can be speciﬁed into which information on conﬂicts will be added.

In the ﬁrst RDA example, a new database will be created called

\pubs.sdf, and the three tables authors, titles, and titleauthor will be pulled from the pubs database without tracking. The relationships will not automatically be created between these tables during the Pulloperation, so they will be added using ADOXCE techniques described in the previous chapter. The code for creating the database and pulling the tables is shown in Listing 4.1. This code is associated with a form called frmGetTables in the project \Examples\PocketPC\RDA\RDA.ebpor \Examples\HPC2000\ RDA\RDA.ebpon the CDROM. The form is illustrated in Figure 4.13.

The form prompts the user for the name or IP address of the IIS server and allows the SQL Server username and password to be speciﬁed — the code uses an SQL login id rather than integrated security in this example. Clicking the “OK” button proceeds with creating the database (deleting the old database if one exists) and then pulling the three tables. A label on the form notiﬁes the user of progress. The form has a File System object called FileSystem1, and this is used to delete an existing database.

Listing 4.1 Pulling tables with RDA

Option Explicit Private sServer Private sUser Private sPassword

(19)

152

Chapter 4

_•

The form frmGetTables

Figure 4.13

Me.Hide End Sub

Private Sub cmdOK_Click() sServer = Me.txtServerName sUser = Me.txtSQLUser sPassword = Me.txtPassword Call MakeDB Me.Hide End Sub

' makes the SSCE database by copying files from SQL Server ' using RDA.

Private Sub MakeDB()

Dim ceRDA As SSCE.RemoteDataAccess Dim oCatalog As adoxce.Catalog Dim sSvrCon, sLocalCon

' Delete database if it already exists, and then create On Error Resume Next

If FileSystem1.Dir("\Pubs.sdf") <> "" Then FileSystem1.Kill "\Pubs.sdf"

If Err.Number <> 0 Then

DisplayError "Deleting database: " Exit Sub

End If End If

Set oCatalog = CreateObject("ADOXCE.Catalog.3.1") If Err.Number <> 0 Then

DisplayError "Creating ADOXCE.Catalog object: "

(20)

Exit Sub End If

lblStatus.Caption = "Creating local database"

sLocalCon = "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;" sLocalCon = sLocalCon & "Data source=\Pubs.SDF"

oCatalog.Create sLocalCon ' create the database If Err.Number <> 0 Then

DisplayError "Creating Database: " Exit Sub

End If

Set oCatalog = Nothing

lblStatus.Caption = "Connecting to remote server" Set ceRDA = CreateObject("SSCE.RemoteDataAccess.1.0") ' set RDA properties

ceRDA.InternetURL = "http://" & sServer & _ "/sscePubs/sscesa10.dll"

ceRDA.LocalConnectionString = sLocalCon

sSvrCon = "Provider=sqloledb;Data Source=(local);" sSvrCon = sSvrCon & "Initial Catalog=Pubs"

sSvrCon = sSvrCon & ";user id=" & sUser sSvrCon = sSvrCon & ";password=" & sPassword lblStatus.Caption = "Pulling Authors"

ceRDA.Pull "Authors", "SELECT * FROM Authors", _ sSvrCon, _

TRACKINGOFF

ReportEngineErrors "Pulling Authors:", ceRDA Exit Sub

End If

lblStatus.Caption = "Pulling Titles"

ceRDA.Pull "Titles", "SELECT * FROM Titles", _ sSvrCon, _

TRACKINGOFF

ReportEngineErrors "Pulling Titles", ceRDA Exit Sub

End If

lblStatus.Caption = "Pulling TitleAuthor"

ceRDA.Pull "TitleAuthor", "SELECT * FROM TitleAuthor", _ sSvrCon, _

TRACKINGOFF

ReportEngineErrors "Pulling TitleAuthor", ceRDA Exit Sub

(21)

154

Chapter 4

_•

Set ceRDA = Nothing

Call AddIndexesEtc(sLocalCon) End Sub

The function MakeDB uses the FileSystem1 control’s Dir method to

determine if the ﬁle \pubs.sdfalready exists. This method returns an empty

string if the file does not exist or the fully qualified filename if it does. The

Killmethod is used to delete the ﬁle:

If FileSystem1.Dir("\Pubs.sdf") <> "" Then FileSystem1.Kill "\Pubs.sdf"

DisplayError "Deleting database: " Exit Sub

End If End If

Next, ADOXCE is used to create the new database \pubs.sdf using a connection string specifying the OLEDB SSCE provider and the name of the ﬁle, as follows:

oCatalog.Create sLocalCon ' create the database

This connection string will also be used to specify the database into which RDA will place the pulled tables. When calling RDA you will need to create an object using the progid SSCE.RemoteDataAccess.1.0 and

spec-ify the URL to the IIS server, including the virtual directory and name of the ISAPI DLL sscesa10.dll. This is assigned to the InternetURL property.

The connection string for the SSCE database is speciﬁed in the LocalConnec tionStringproperty:

Set ceRDA = CreateObject("SSCE.RemoteDataAccess.1.0") ' set RDA properties

The Pull method also needs a connection string for connecting to the

SQL Server. This connection string will be used on the IIS server, so you can use (local)for the Data Source(that is, the server name where SQL Server

is located). If your SQL Server is on another server, you will need to change this to the name of the server. In this case, the initial catalog is set to the pubs

database, and the user id and password are taken from the dialog:

sSvrCon = "Provider=sqloledb;Data Source=(local);" sSvrCon = sSvrCon & "Initial Catalog=Pubs"

sSvrCon = sSvrCon & ";user id=" & sUser sSvrCon = sSvrCon & ";password=" & sPassword

(22)

This code, when executed, results in the following connection string be-ing created:

Provider=sqloledb;Data Source=(local);Initial Catalog=Pubs; user id=sa;password=

It is important that “Initial Catalog” is used to specify the database being used; otherwise, the default database deﬁned for the SQL Server user will be used (which is “master” by default). The Pull method is now called and is passed the following parameters:

● The name for the new table in the SSCE database, Authorsin this case.

● The SQL statement that provides the recordset for the new table. All

columns and all rows are selected.

● The SQL Server connection string created above.

● The tracking option for changed data. Tracking is not enabled in this case

as the TRACKINGOFFconstant is speciﬁed:

ceRDA.Pull "Authors", "SELECT * FROM Authors", _ sSvrCon, _

TRACKINGOFF

ReportEngineErrors "Pulling Authors:", ceRDA Exit Sub

End If

Note that the table being pulled must not exist in the SSCE database. In this case the database has been re-created so the tables cannot exist. In your code you may need to drop tables before pulling them.

As you can imagine, there are many things that can (and often do) go wrong with pulling data; therefore, it is essential to provide good error-check-ing code. In the above code, the subroutine ReportEngineErrorsis called if an error is detected, and this is passed the RDA Engine object. Listing 4.2 shows this subroutine — it is located in the module utils.bas.

Listing 4.2 Reporting RDA errors

Public Sub ReportEngineErrors(sMsg, oEngine As SSCE.Engine) Dim oEngErr As SSCEError

For Each oEngErr In oEngine.ErrorRecords MsgBox sMsg & vbCrLf & _

"SSCE Error: " & vbCrLf & oEngErr.Description _ & vbCrLf & "Number: " & oEngErr.Number & _ vbCrLf & "Native: " & oEngErr.NativeError

(23)

156

Chapter 4

_•

Next End Sub

The Engineobject has an ErrorRecordscollection containing one or more SSCEError objects describing the error. The code in Listing 4.2 iter-ates through the errors, displaying each in a message box. The Description

property does not always describe the error fully, so you should look up the

NativeError in the SSCE on-line documentation for more information. Search the help ﬁle for “Client Agent Errors” for a list of error numbers gener-ated by the SSCE Client Agent and “Server Agent Errors” for error numbers gen-erated by the IIS ISAPI agent.

Similar code is executed to pull the Titles and the TitleAuthor

tables. Once this has been done, the code in Listing 4.3 is called to add pri-mary and foreign keys between the tables. The techniques used in this code are described in Chapter 3, “ADOCE and ADOXCE.”

Listing 4.3 Adding indexes and keys

Private Sub AddIndexesEtc(sLocalCon)

' add indexes, primary keys etc for these new tables Dim oT As adoxce.Table

Dim o As adoxce.Catalog Dim oK As adoxce.Key Dim oIC As adoxce.Column

Set o = CreateObject("ADOXCE.Catalog.3.1") o.ActiveConnection = sLocalCon

On Error Resume Next

' set primary key on au_id for Authors table Set oT = o.Tables("Authors")

Set oK = CreateObject("ADOXCE.Key.3.1") oK.Type = adKeyPrimary

oK.Name = "pkAuthors"

Set oIC = CreateObject("ADOXCE.Column.3.1") oIC.Name = "au_id" oK.Columns.Append oIC oT.Keys.Append oK If Err.Number <> 0 Then DisplayError "Authors PK" End If

' set primary key on title_id for titles table Set oT = o.Tables("Titles")

oK.Name = "pkTitles"

Set oIC = CreateObject("ADOXCE.Column.3.1")

(24)

oIC.Name = "title_id" oK.Columns.Append oIC oT.Keys.Append oK If Err.Number <> 0 Then DisplayError "Titles PK" End If

' set primary key on au_id, title_id ' for titleauthor table

Set oT = o.Tables("TitleAuthor")

oK.Name = "pkTitleAuthor"

Set oIC = CreateObject("ADOXCE.Column.3.1") oIC.Name = "au_id"

oK.Columns.Append oIC

Set oIC = CreateObject("ADOXCE.Column.3.1") oIC.Name = "title_id" oK.Columns.Append oIC oT.Keys.Append oK If Err.Number <> 0 Then DisplayError "TitleAuthor PK" End If

' set foreign key on titleauthor table to authors table Set oK = CreateObject("ADOXCE.Key.3.1")

oK.Type = adKeyForeign oK.Name = "fkAuthor"

Set oIC = CreateObject("ADOXCE.Column.3.1") oIC.Name = "au_id" oIC.RelatedColumn = "au_id" oK.Columns.Append oIC oK.RelatedTable = "Authors" If Err.Number <> 0 Then DisplayError "Author FK" End If oT.Keys.Append oK

' set foreign key on titleauthor table to titles table Set oK = CreateObject("ADOXCE.Key.3.1")

oK.Type = adKeyForeign oK.Name = "fkTitles"

Set oIC = CreateObject("ADOXCE.Column.3.1") oIC.Name = "title_id" oIC.RelatedColumn = "title_id" oK.Columns.Append oIC oK.RelatedTable = "Titles" oT.Keys.Append oK Set o = Nothing If Err.Number <> 0 Then DisplayError "Title FK"

(25)

158

Chapter 4

_•

List of authors from a pulled table

Figure 4.14

End If End Sub

You can use this code to test your own RDA installation.

● Copy the ﬁles from the folder \Examples\PocketPC\RDA from the CDROM into a folder on your PC’s hard drive.

● Use File Explorer to remove the read-only settings on these ﬁles.

● Open the project rda.ebp in eVB.

● Open the code associated with the form frmRDA.ebf and change the constant deﬁnition sMyServerto your own IIS server name or IP address of the IIS server:

Const sMyServer = "190.100.100.120"

● Run the application on your Pocket PC.

● Select the Operations Create Tablesmenu command to display the

“Get Server Details” dialog.

● Enter your server name (it does not use the constant sMyServerin this

case) and enter a valid SQL login and password.

● Click OKto download the tables.

● To conﬁrm that the data has been downloaded, select the Operations Show Authorsmenu command to list the contents of the “Authors” table (see Figure 4.14).

(26)

SQL PASS-THROUGH

The RDA Engine object’s SubmitSQLmethod can be used to execute any SQL

statement on an SQL Server providing the SQL statement does not return a recordset. The SQL statement could be an INSERT, DELETE, or UPDATE

state-ment or a stored procedure.

Note that you cannot determine the number of rows affected when using

SubmitSQL. Therefore, if you delete a number of rows using a WHERE

clause, you cannot determine how many, if any, records were deleted. When calling the RDA Engine object’s SubmitSQL method (see Listing 4.4), you will ﬁrst need to specify the URL of the IIS server in the Internet URL property and the SSCE connection string in the LocalConnection String property. The format of the URL and connection string is exactly the same as for the Push method. The SubmitSQL method itself is passed two parameters:

● The SQL statement to execute

● The connection string used by IIS to connect to the SQL Server

As usual, you will need to check for errors, and these can be displayed using the ReportEngineErrorsmethod.

Listing 4.4 Using SubmitSQL

Public Sub RemoteSQL(sServer, sSQL) Dim sLocalCon, sSvrCon

Dim ceRDA As SSCE.RemoteDataAccess

Set ceRDA = CreateObject("SSCE.RemoteDataAccess.1.0") On Error Resume Next

sSvrCon = "Provider=sqloledb;DataSource=(local)" sSvrCon = sSvrCon & ";Initial Catalog=Pubs;" sSvrCon = sSvrCon & "Integrated Security=SSPI" ceRDA.SubmitSQL sSQL, sSvrCon

ReportEngineErrors "Adding Author:", ceRDA Exit Sub

End If End Sub

(27)

160

Chapter 4

_•

Adding an author using RemoteSQL

Figure 4.15

In the project RDA.EBP the function RemoteSQL is used by the form frmAddAuthor.ebfto get details for each of the ﬁelds in the Authortable,

to produce an INSERT SQL statement containing these values, and to send

the INSERTstatement to the server (see Figure 4.15). The “Add Author” form

is accessed from the main form through the Operations Add Authormenu command.

The following code is executed when the Add button is clicked in the “Add Author” dialog:

Private Sub cmdAdd_Click() Dim s

s = "INSERT INTO Authors "

s = s & "(au_id, au_lname, au_fname, phone," s = s & "address, city, state, zip, contract)" s = s & "VALUES("

s = s & "'" & au_id.Text & "'," s = s & "'" & au_lname.Text & "'," s = s & "'" & au_fname.Text & "'," s = s & "'" & phone.Text & "'," s = s & "'" & address.Text & "'," s = s & "'" & city.Text & "'," s = s & "'" & state.Text & "'," s = s & "'" & zip.Text & "'," If contract.Value Then

(28)

s = s & "1)" Else s = s & "0)" End If RemoteSQL Me.txtServer, s Me.Hide End Sub

This code generates an SQL INSERT statement such as

INSERT INTO Authors

(au_id, au_lname, au_fname, phone,address, city, state, zip, contract)

VALUES('999-99-9999','First Name', 'Last Name','408 496 7224',

'1500 Bridge Street','Oakland','CA','94026',

Remember that this results in a record being added to the Authorstable

on the SQL Server, not on the local SSCE database. You will not see this new record in your Pocket PC or HPC 2000 application unless you again pull the Authorstable from SQL Server. There is no automatic update

with RDA.

TRACKING DATA WITH PULL

In the Engine object’s Push example described earlier in this chapter, the

TRACKINGOFFoption was used. The SSCE client agent does not track changes made on the Pocket PC or HPC 2000 device, so changes cannot automatically be sent back to the SQL Server.

When the TRACKINGON option is used, two new fields are added to the table being pulled, called s_BinaryKeyand s_Operation.Values are placed in these fields indicating the type of change operation (such as a record being inserted, deleted, or updated). A table with these two fields can be pushed back to the SQL Server, and the SSCE client agent will only send back to SQL Server those records that have been changed in some way.

Of course, it is possible that one particular record may be changed in SSCE and on the SQL Server database between the time the table was pulled and the time it is eventually pushed back to the server — this creates a conﬂict that must be resolved. When calling the Pull method you can specify the name of a table in the SSCE database that will contain records detailing any conﬂicts that have been detected.

In the \Examples\PocketPC\RDA\RDA.ebp project on the CDROM, the Stores table from the Pubs database is used to illustrate using the

TRACKINGON option and resolving conﬂicts. The Operations Pull Stores menu command executes the code in Listing 4.5 to pull the Stores table us-ing trackus-ing. The code is located in frmRDA.ebf.

(29)

162

Chapter 4

_•

This code uses ADOXCE to determine if the Storestable already exists in the database. If it does, the table is deleted. This uses the DoesObject Existfunction described in Chapter 3.

Listing 4.5 Pulling “Stores” table

Public Sub PullStores()

Dim ceRDA As SSCE.RemoteDataAccess Dim sLocalCon, sSvrCon

Dim oCatalog As adoxce.Catalog On Error Resume Next

' first delete the table if it exists

Set oCatalog = CreateObject("ADOXCE.Catalog.3.1") If Err.Number <> 0 Then

DisplayError "Creating ADOXCE.Catalog object: " Exit Sub

End If

oCatalog.ActiveConnection = sLocalCon

If DoesObjectExist(oCatalog.Tables, "Stores") Then oCatalog.Tables.Delete ("Stores")

DisplayError "Deleting 'Stores' table: " Exit Sub

End If End If

Set oCatalog = Nothing

ceRDA.InternetURL = "http://" & sMyServer & _ "/sscePubs/sscesa10.dll"

sSvrCon = "Provider=sqloledb;Data Source=(local)" sSvrCon = sSvrCon & ";Initial Catalog=Pubs;" sSvrCon = sSvrCon & "Integrated Security=SSPI" ceRDA.Pull "Stores", "SELECT * FROM Stores", _

sSvrCon, _

TRACKINGON, "tblError" If Err.Number <> 0 Then

ReportEngineErrors "Pulling Stores:", ceRDA Else

MsgBox "Stores Pulled"

(30)

End If End Sub

An RDA Engineobject is created as usual, and the InternetURL prop-erty is assigned the URL of the SSCE Server agent DLL, sscesa10.dll. Next, the local connection string is assigned to the LocalConnectionString prop-erty. Both these two strings are the same as the previous Pullexample. The server connection string uses integrated SQL Server security rather than speci-fying an SQL Server login and password. Because anonymous access is used for IIS, the IUSR_MALAHIDE login will be used to access SQL Server. The con-nection string will look like the following:

Provider=sqloledb;Data Source=(local);

Initial Catalog=Pubs;Integrated Security=SSPI

The call to the Pullmethod speciﬁes:

● The name of the table to create in the SSCE database, which is Stores.

● The TRACKINGONoption — this results in the two extra ﬁelds being added to the Storestable.

● The name tblErrorused for the errors table. This table will automati-cally be created when a Pushis performed.

Note that you should specify a different errors table name (tblError)

for each table being pulled. The errors table contains the same fields as the pulled table and is therefore specific to that table.

The Operations Show Storesmenu command can be used to display the pulled table and the contents of the s_BinaryKey and s_Operation

fields. When first pulled, the contents of these two fields will always be NULL— the values are set as records are changed.

CREATING A CONFLICT

To illustrate how conflicts can be detected, the Operations Create Stores Conflictmenu in the \Examples\PocketPC\RDA\RDA.ebp project adds a record to the SSCE database table Stores and another record to the SQL Server database table Stores with the same primary key value for stor_id, but different data in the other fields. When the table is finally pushed, the conflict will be detected.

The code to create the conﬂict is shown in Listing 4.6. Two INSERT state-ments are created in the strings sSQLLoc(for adding to the SSCE database) and

sSQLRem(for adding to the SQL Server database). The local record is inserted using the ADOCE’s Executemethod through the Connectionobject. The re-mote record is inserted using the RemoteSQLmethod described earlier in this chapter.

(31)

164

Chapter 4

_•

Listing 4.6 Creating a conflict in the Stores table

Public Sub CreateStoresConflict() Dim sSQLLoc, sSQLRem, sLocalCon Dim oLocADO As ADOCE.Connection On Error Resume Next

' create the SQL INSERT statements

sSQLLoc = "INSERT INTO Stores (stor_id, stor_name, " sSQLLoc = sSQLLoc & "stor_address, city, state, zip)" sSQLLoc = sSQLLoc & "VALUES ('9999', 'Store 1', " sSQLLoc = sSQLLoc & "'11 High Street', "

sSQLLoc = sSQLLoc & "'Portland', 'OR', '89076')" sSQLRem = "INSERT INTO Stores (stor_id, stor_name, " sSQLRem = sSQLRem & "stor_address, city, state, zip)" sSQLRem = sSQLRem & "VALUES ('9999', 'Store 2', " sSQLRem = sSQLRem & "'1021 Lost Creek', "

sSQLRem = sSQLRem & "'Fremont', 'CA', '90019')" ' add record locally

Set oLocADO = CreateObject("ADOCE.Connection.3.1") If Err.Number <> 0 Then

MsgBox "Error Creating ADOCE Object" & _ vbCrLf & Err.Description

Exit Sub End If

oLocADO.Open sLocalCon If Err.Number <> 0 Then

MsgBox "Error opening local database" & _ vbCrLf & Err.Description

Exit Sub End If

oLocADO.Execute sSQLLoc If Err.Number <> 0 Then

MsgBox "Error adding local record" & _ vbCrLf & Err.Description

Exit Sub End If

oLocADO.Close

Set oLocADO = Nothing ' add record remotely

Call RemoteSQL(sMyServer, sSQLRem) MsgBox "Conflict Created"

End Sub

The easiest way to look at and edit records in an SQL Server database is through the Enterprise Manager. Click on “Tables” in the “pubs” database 04-P1914 8/24/2001 11:26 AM Page 164

(32)

in the “Tree” pane, right-click the table name “Stores”, and select the Open Table Return All Rowsmenu command. This displays the records in a table and is editable.

PUSHING A TABLE

Changes made locally in the Stores table can be pushed back to the SQL Server using the RDA Engineobject’s Push method. In Listing 4.7 the Inter netURL and LocalConnection strings are initialized as before. The Push

method is passed

● The name of the table to push, which is Storesin this case.

● The connection string used to connect to the SQL Server database. Once

again, this uses integrated security.

This code can be executed through the Operations Push Stores menu command in the sample application. Executing this menu command will result in a native error 28544 being generated. This is a client – agent error that the on-line documentation describes as “One or more error rows returned from RDA_PUSH; see associated _err table”. This results because of the conﬂict cre-ated in the previous section.

Listing 4.7 Pushing a table

Public Sub PushStores()

Dim ceRDA As SSCE.RemoteDataAccess Dim sLocalCon, sSvrCon

On Error Resume Next

ceRDA.InternetURL = "http://" & sMyServer & _ "/sscePubs/sscesa10.dll"

sSvrCon = "Provider=sqloledb;Data Source=(local);" sSvrCon = sSvrCon & "Initial Catalog=Pubs;"

sSvrCon = sSvrCon & "Integrated Security=SSPI" ceRDA.Push "Stores", sSvrCon

ReportEngineErrors "Pushing Stores:", ceRDA Exit Sub

Else

(33)

166

Chapter 4

_•

End If End Sub

The following happens when a conﬂict is detected:

● The offending record is removed from the SSCE Stores table

● The conﬂicting record on the SQL Server database Stores table is not changed — the SQL Server changes take precedence

● A record is added to the tblError table describing the conﬂict

Note that a Pushdoes not result in records changed on the SQL Server

table being downloaded to the SSCE table. You must perform a Pull

again to get changes from the SQL Server table, and to do this you must first drop the SSCE table. This can make RDA inefficient when pulling large tables.

The contents of the tblErrortable can be viewed in the sample appli-cation by selecting the Operations Show Conflicts menu command. The table will contain a single record describing the conflict with the following fields:

● s_errorDate:Date and time of the Pushthat detected the conﬂict

● s_OLDEBErr:Database access error causing the conﬂict

● s_OLDEBErrorString:A string describing the error

● Fields for each of the ﬁelds in the original table Stores containing the record resulting in the conﬂict

The conﬂict in this case results in an error 2627 “Violation of PRIMARY KEY constraint ‘UPK_storeid’”, since the conﬂict created earlier used the same primary key for the local record and the record added to the SQL Server database.

SQL Server CE Relay

Generally, when you have a serial or USB connection to ActiveSync, you can-not connect to other servers on the network using TCP/IP. This means that while you can use RDA when a network or dial-up connection is present, you cannot use RDA with ActiveSync. Release 1.1 of SSCE introduced the “SQL Server CE Relay” application — this acts as a point-to-point proxy between the Pocket PC or HPC 2000 device and the IIS server (see Figure 4.16).

By using relay, all connection methods between a Pocket PC and HPC 2000 devices can use RDA. You should note the following limitations in using relay:

● Relay must have a connection using ActiveSync 3.1

● Relay does not support Secure Socket Layer (SSL) encryption or Inte-grated Windows authentication

(34)

SQL Server CE relay architecture

Figure 4.16

CONFIGURING SQL SERVER CE RELAY

Before starting to configure SSCE Relay ensure that you have your Pocket PC or HPC 2000 connected to a desktop PC using ActiveSync 3.1 using a serial or USB connection. It is best to start by manually configuring SSCE Relay as it is easy to change parameters without having to reconnect your device to Ac-tiveSync. When you have Relay working, you can then configure Relay to start automatically when an ActiveSync connection is made.

Relay acts as a proxy server, so you will need to change your RDA code to use a proxy server. You will need to add the line shown in bold to any code that makes a Push, Pull, or SubmitSQLcall through the RDA Engine object:

ceRDA.LocalConnectionString = sLocalCon ' enable this line if SSCE Relay is used

ceRDA.InternetProxyServer = "ppp_ peer:81"

sSvrCon = "Provider=sqloledb;DataSource=(local)" sSvrCon = sSvrCon & ";Initial Catalog=Pubs;"

(35)

168

Chapter 4

_•

sSvrCon = sSvrCon & "Integrated Security=SSPI"

ceRDA.SubmitSQL sSQL, sSvrCon

This line speciﬁes that Relay is being employed (the Relay is a point-to-point peer proxy) using port 81.

You can now run the Relay server on your desktop PC. Run a command shell (cmd.exe) and change directory to \Program Files\Microsoft SQL Server CE\Relayand execute the following command:

sscerelay /clientport 81 /servername 190.100.100.120 /serverport 80

This command uses the following parameters:

● Clientport: Speciﬁes the port number (81) speciﬁed in the Inter netProxyServerproperty shown in the code above.

● Servername: Server name or IP address (190.100.100.120) of the IIS server the RDA application connects to.

● Serverport: The port number on the server used by IIS. Port 80 is the default and is the most commonly used number. Sometimes port 8080 is used for security reasons.

An icon is added to the tray area on the desktop PC, and the icon ani-mates when transmission takes place. You should now be able to run the RDA application, and tables can be pulled and pushed as before. RDA errors are logged in a ﬁle called sscerelay.log in the same directory where

sscerelay.exeis located.

Once you have relay working you can close down the Relay application and then conﬁgure Relay to start automatically when an ActiveSync connection is made. To stop Relay issue the following command in the command shell:

sscerelay /stop

Now issue the following command. This is the same command as used before to start Relay, but with the additional parameter /register.

sscerelay /clientport 81 /servername 190.100.100.120 /serverport 80 /register

Note that Relay will not be available until the next time an ActiveSync connection is made and not for the current session after issuing this command.

Relay is automatically started when an ActiveSync connection is made and closed down when the connection is closed. You can unregister Relay by issuing the command

sscerelay /unregister

(36)

Security

In the examples shown above, anonymous authentication using the IUSR_ servername user has been used. In many situations you will want to

imple-ment NT authentication to secure access to your data. You can do this in IIS by

● Running the Internet Information Services administration program by

selecting the Start Programs Administrative Tools Internet Services Manageror through the Control Panel’s “Administrative Tools” icon

● Expanding out “Default Web Site” and right-clicking the RDA virtual

di-rectory name (e.g., sscePubs)

● Selecting the “Directory Security” tab and clicking the Editbutton ● Unchecking “Anonymous access” and checking “Integrated Windows

authentication”

● Clicking OK to close the dialogs

Note that you can choose “Basic authentication,” but this can be insecure as the username and password are not sent in encrypted form.

When Integrated Windows authentication is enabled you will need to supply the Windows username and password through the RDA Engine object. You can use the InternetLogin and InternetPassword properties for this.

ceRDA.InternetLogin = "MyUserName" ceRDA.InternetPassword = "MyPassword"

SQL Server Replication

SSCE can replicate data with SQL Server 2000 using merge replication. SQL Server replication provides a much more sophisticated mechanism for down-loading and updown-loading data than RDA. However, this comes at a cost.

● Careful planning is required and should involve network administration

staff and database administrators.

● Installation and conﬁguration are more difﬁcult.

● Merge replication is a large, complex subject and involves a signiﬁcant

learning curve to gain full advantage of the technology. Merge replication offers the following advantages over RDA:

● Replication is two way — changes made on the Pocket PC or HPC 2000

device are sent to the SQL Server, and changes made on the SQL Server are sent back to the Pocket PC or HPC 2000 device in a single operation.

(37)

170

Chapter 4

_•

● Conflict resolution can be automatic using rules like “first modification

wins”, or you can write custom conflict resolves that run on the server. With RDA, changes made in the SQL Server are only downloaded when a Pulloperation is made, and this involves copying down the entire contents of the table. With merge replication, only the changes are copied down, so this is much more efficient. Further, to perform a two-way transfer of data with RDA you must first perform a Push, resolve conflicts, and then perform a Pull. With merge replication these three tasks are performed by a single synchro-nization.

Note that SSCE replication only works with SQL Server 2000 using merge replication.

The examples and discussion in this chapter are designed to allow a pro-grammer to setup and explore merge replication with SQL Server 2000. Before attempting to deploy an application that uses merge replication, you will need to carefully plan the topology and security of your solution. This will involve liaising with your network administration staff.

You should ensure that you have RDA working as described in the previ-ous sections of this chapter before attempting to implement merge repli-cation. The examples in the remainder of this chapter assume that IIS and the SSCE server and client agents have been correctly installed. The code examples assume that anonymous login is used to the IIS server. There are two stages to getting merge replication up and running.

● Conﬁgure merge replication on the SQL Server.

● Write code using the Replication object to initialize merge replication on

the Pocket PC or HPC 2000 device and then to periodically synchronize the data.

Synchronization of data with SSCE merge replication is not automatic. Your eVB application will need to decide when it is appropriate to synchronize data and then make a connection to SQL Server to perform synchronization.

The following terms are used with merge replication:

● Distributor:A process that manages the distribution of data from a

pub-lication to a subscriber. This runs through the SQL Server Agent service.

● Publication: The deﬁnition of data in a database that is available for

replication.

● Subscriber: An application that consumes data from a publication and

optionally returns data to the publication.

(38)

● Articles:The data elements being published in a publication. Although

in SQL Server articles can be views, stored procedures, or tables, only tables are used for SSCE replication.

Installation and Configuration

The instructions here show how to setup merge replication for some tables in the pubs database on SQL Server 2000.

UPDATING A STORED PROCEDURE

Before attempting to use merge replication with SSCE, you will need to update the stored procedure sp_Msmakegenerationin the masterdatabase in SQL

Server.

This should only be performed on SQL Server if no service packs have been installed. Service packs will automatically update this stored procedure. To update this stored procedure,

● Run SQL Server Enterprise Manager

● Select the Tools SQL Query Analyzermenu command

● Ensure that the masterdatabase is selected from the list of databases in the toolbar

● Select the File Openmenu command

● Select the ﬁle \Program files\Microsoft SQL Server CE\Inc \sp_MSMakeGeneration.sql

● Select the Query Execute menu command to execute the script and

update the stored procedure

The file sp_MSMakeGeneration.sql is installed as part of a “Devel-oper” SSCE installation andnot part of a “Server” installation. You will there-fore need to find the file on your development PC.

CREATING A SHARE FOR THE PUBLICATION

You will need to create a folder and then create a share on the same server as SQL Server. This will be used by publication for storing ﬁles associated with replication. The default option is to use the share \\myserver\c$, but you cannot set permissions and security on this share. Therefore, you should cre-ate a folder and a share and then conﬁgure the correct security on that share.

● Run the File Explorer, and create the folder \PubReplication.

● Right-click this new folder and select the Properties menu command.

(39)

172

Chapter 4

_•

● Click the Share this folder option button and ensure that PubRepli cationis entered for the “Share Name.”

● Click the Permissionsbutton.

You will need to add the following users to have permissions on this share:

● Administrator— this is the default user for the SQL Agent service, and

this user will require full access to the share.

● IUSR_servername— The anonymous IIS user will need read-only ac-cess to this share. In this example, it is IUSR_Malahide.

If you are using Windows authentication you will need to add read-only access to the share for any users that participate in replication. You should now set the security for these two users, as follows:

● Click the Addbutton in the “Permissions for PubReplication” dialog ● Double-click “Administrator” from the list of “Users, Computers, or

Groups” and click OK

● Select “Administrator” in the list of users, and click the “Full Control”

op-tion button in the “Allow” column

● Click the Addbutton again

● Double-click IUSR_servername— in this example the user is IUSR_ Malahide, from the list of “Users, Computers, or Groups”, and click OK ● Select IUSR_servername in the list of users, and click the Read option

button in the “Allow” column

● Click OKto close the dialogs

The “Create Publication Wizard” in the next section will use this share.

SET TING UP A PUBLICATION

A publication deﬁnes the data that can be replicated. The easiest way of cre-ating a publication is to use the “Create Publication Wizard” from the SQL Server Enterprise Manager.

● Run the SQL Server Enterprise Manager by selecting the Start Pro-grams Microsoft SQL Server Enterprise Manager menu com-mand.

● Expand the “Microsoft SQL Servers” tree list in the left pane to show your server (“Malahide” in this case), and “Databases” to show the list of databases.

● Right-click the pubsdatabase and select the New Publicationmenu command.

This starts the “Create Publication Wizard.” The steps in this wizard de-pend on whether a publication has been created already on this server. If a publication has not been created the wizard will take you through the details

(40)

Create Publication Wizard—Select Distributor

Figure 4.17

of the Distributor to use for the publication. The Distributor can be the SQL Server supporting the publication or another SQL Server. In this case, the steps assume that a Distributor is yet to be deﬁned for the SQL Server and that the SQL Server itself will act as the Distributor.

● Click Next to skip the initial, opening sheet for the wizard.

● In the “Select Distributor” sheet select the ﬁrst option “Make ‘Malahide’ its

own Distributor; SQL Server will create a distribution database and log” (see Figure 4.17). Click Next.

Publication uses the SQL Server Agent to generate information relating to replication. By default, the agent is not started automatically. The “Conﬁgure SQL Server Agent” sheet allows you to conﬁgure the SQL Server Agent to run automatically when the computer is started.

● Select “Yes, Conﬁgure the SQL Server Agent service to start automatically”

(41)

174

Chapter 4

_•

Create Publication Wizard—Specify Snapshot Folder

Figure 4.18

The next page, “Specify Snapshot Folder,” is used to specify the name of the share that replication will use for holding snapshot and other information. In this example, you will use the share created in the previous section. Note that you should use a share and not a folder on a local drive.

● Enter \\server\PubReplication for the “Snapshot folder” and click Next (see Figure 4.18). In this example, \\Malahide\PubReplica tionis used.

The next sheet, “Choose Publication Database,” lists all the databases on the SQL Server and allows you to select the database to publish. In this case you will be publishing the “pubs” database.

● Select “pubs” from the list of databases and click Next.

There are three types of publication supported by SQL Server (Snapshot, Trans-actional, and Merge), but only Merge is supported by SSCE.

● Select “Merge Publication” from the list of publication types in the “Select

Publication Type” sheet, and click Next(see Figure 4.19).

(42)

Create Publication Wizard—Select Publication Type

Figure 4.19

The “Specify Subscriber Types” sheet allows you to select one or more types of subscriber. You must select at least “Devices running SQL Server CE” when using replication with SSCE.

● Select “Devices running SQL Server CE” from the list of subscriber types,

and any other subscriber types you want to test with. Click Next. The next sheet, “Specify Articles,” allows you to specify the tables that will be published through the publication. While SQL Server supports tables, stored procedures, and views as articles, you should only use tables for SSCE replication. You need to carefully deﬁne the set of tables to use, because you can end up limiting the ability to add new records to replicated tables. For ex-ample, if you set table “A” as an article, and table “B” has a foreign key rela-tionship with the primary key in “A” but is not speciﬁed as an article, then you will not be able to add records to table “A” in the SSCE database. Of course, the more tables you specify as articles, the slower replication will be. You need to plan carefully the data that needs to be updated in the SSCE database and specify articles accordingly.

(43)

176

Chapter 4

_•

Create Publication Wizard—Specify Articles

Figure 4.20

● Select the following tables in the object list: authors, publishers,

roysched, sales, titles, titleauthor (see Figure 4.20) and click Next.

To enable merge replication, SQL Server needs to add a uniqueidentifier

column with a unique index and the attribute ROWGUIDCOLto each table that does not already have such a column. The “Article Issues” sheet requests you to conﬁrm this database change.

● Select Next to conﬁrm these database changes in the “Article Issues”

sheet.

Each publication will need a name through which it is accessed. In this case you will set the name to “pubs”, that is, the same as the database name. You can also specify a description for the publication in this sheet. Note that you can have several different publications on the same database — this can be use-ful when you have groups of users with different replication requirements.