Let me start by saying that I'm not a SQL guru, I'm not a DBA, I'm a lowly application programmer who just happens to (begrudgingly) need to use SQL server. I thought I was fairly adept and figuring out weird combinations of software until I tried to install SQL Server CE and set it up to replication with my SQL Server on the desktop.
SQLCE and SQL Server, they are supposed to work together, right? They do but there are a few gotchas along the way that you might like to know about. I look forward to hearing other tips or advice on this process.
It's pretty simple actually but you have to do it exactly as follows.
- SQLCE (Server Tools) will not install without SP1 it will not install with SP3. You must install SQL2k -> SQL2KSP1 --> SQLCE --> SQL2KSP3
- SQL Replication will not work if the SQL service is running under the system account. You MUST configure the SQL services to use a DOMAIN account with 'run as service' privilege
- Run SQL 2K Installer.
- Change the Instance Name
- Do NOT use the System account. It does not work for replication.
- The account you use must run as a service
- The account must have a password
- change the sa password
- Install SP1. DO NOT install SP3 yet. SQLCE will not install over SP3
- Now Install SQLCE
- Now Install SP3