Replication and database mirroring (failover) support for merge replication between SQL Server and SQL Server Compact 3.5 is supported. For database mirroring, you must be running SQL Server services under a domain account. By default, the domain account is under the local system account.
Setting up SQL Server for Database Mirroring
To set up SQL Server for database mirroring, follow the instructions in the SQL Server 2008 R2 Books Online topic Replication and Database Mirroring.
Setting up Internet Information Services (IIS) Server
To set up IIS for database mirroring, see the following SQL Server Compact 3.5 Books Online topics:
Configuring the IIS Server Environment
Configuring the SQL Server Environment for Replication
SQL Server Compact 3.5 as the Client for Database Mirroring
The following scenarios are supported when you use SQL Server Compact 3.5 as the client for database mirroring.
For descriptions of principal (P), mirror (M), or witness (W), see the SQL Server 2008 R2 Books Online topic Replication and Database Mirroring. |
Test case description |
Initial sync |
Incremental sync |
|---|---|---|
Synchronize with principal – Both are UP |
Pass |
Pass |
Synchronize with principal – Mirror is DOWN |
Pass |
Pass |
Failover - Principal went down, mirror became a principal, and the principal became a mirror.
Synchronize with old principal (now mirror) – Both are UP |
Fail |
Pass |
Synchronize with mirror – principal is DOWN |
Fail |
Pass |
Key points are as follows:
After a merge subscription is created, SqlCeReplication.Publisher always remains the same, regardless of the failover. That is, SqlCeReplication.Publisher always points to the principal of merge subscription at creation time, even if the principal is now the mirror.
Synchronization always occurs with the principal. If the principal is down, the failover of merge replication occurs automatically to the new principal.
When there is a failover, you do not have to change subscription properties.
SQL Server Compact 3.5 can synchronize with multiple SQL Server versions. SQL Server Compact 3.5 connects to SQL Server to find its version. After the version is determined, SQL Server Compact 3.5 identifies the version-specific replication client components in order to create the merge subscription. After the subscription is created, the publisher details are saved, and incremental sync does not require a direct connection.
Mirror does not allow for a direct OLEDB connection.
When there is a failover, you must specify either a new principal or the current principal as publisher when creating the subscription. If you specify the publisher as old principal (now mirror), regardless of whether it is UP or DOWN, the initial subscription fails. The reason for this failure is the previous points 4 and 5.
The information in this topic applies to supported versions of SQL Server; for more information, see Hardware and Software Requirements.