Replication with SQL Server 2005 Express Edition and SQL Compact Edition 3.5

Question

I need some information on SQL Server 2005 Express edition. What I want to do is
have my central database servin local machine databases

IE

back office Cental database
|-------------------> Shop floor Terminal 1
|-------------------> Shop Floor Terminal 2
|-------------------> Shop Floor Terminal 3
|-------------------> Shop Floor Terminal 4
|-------------------> Shop Floor Terminal 5
|-------------------> Shop Floor Terminal 6

I want is so that Shop floor terminals would PULL down ANY changes to the database as
and when they happen (selected changes are needed change would be Add new item / Edit
Item info that is used by Shop floor terminal (ie price, description, sale group)

Is this possible with SQL 2005? I have the ability to make my own Sync Applciation but
I would need to know what to look for in the database that trigers a update

Many thanks for any advice you can give

Andy

Answer 1

Yes you can do that with SQL Express. You'll want to use SQL Server Replication to do that. You'll need to use a Workgroup or higher server as the middle server that talks to all the other ones.

You'll probably want transactional replication or merge replication. Both are near real time.

Answer 2

SQL Server replication is usually very easy to set up, but you can not publish from a SQL Server express database (please see the article below).

SQL Server books online.

If your central database is close by (network speaking) to the terminals you could just connect directly to the central database using a thin/thick client.

Merge replication works well, but there is always a bit of a delay since a merge occurs on a schedule/on demand; it sounds like transactional would be a better fit for 'instantly viewable' database operations.

That being said, I would only recommend replication when you have multiple offices, or a specific reasoning for using multiple databases (i.e. support for occasionally connected applications). It sounds like a single database with a thin/thick client would be a better option (IMO).