Sql Express 2005 Synchronization --> Asp.net 2.0 Framework

I have to develop a web application and I have the following questions:

Technology thinking of using
ASP.net Framework 2.0
MSSql express edition 2005

In the architecture design we decided to go with an offline approach. Meaning that we are going to have a server with an IIS at the Local Offices and one remote server in a web hosting  environment. Both Server are going to have MSSql Server Express Edition 2005 and are going to get synchronize with each other. So the user can choose either to work with the remote server or the local server.

1.My question is whether you have some ideas of how to synchronize the 2 sql express servers and how to use the locking of certain records in order to do that.
2.Is it better to go with PHP aand Mysql technology?
3.How can I connect a remote server with the local server. What do i need ? What do i need to ask the Web Hosting Provider in order to be able to connect to the remote sql express server and syncrhonize it with my local server?

Who is Participating?
We have this sort of set up in our office, albeit with MS SQL Server 2000:  We have a web server with its own database server out on the DMZ which synchronizes with the internal database server.

We synchronize using custom replication process, but the SQL Server replication service should work well (our problem is that due to braindead firewall policies, replication is blocked from communicating between both servers).

If you need to create your custom replication solution, then you can follow our approach.  Its simple, if somewhat convoluted (I did not design it!):  All tables have an extra bit column flagging them as modified.  Triggers set these flags.  We also have a separate table marking which tables have been updated.  Our replication application periodically checks these flags and syncronizes both ways as necessary, taking into account any conflicts (e.g. the same record was modified on both sides).  You can build this as complex as you need.

Ted BouskillSenior Software DeveloperCommented:
If it is a thin-client solution why can't the local office connect to the web front end at the hosting site?

Replicating databases (mirroring) creates risk and complexity that is often not worth the trouble.

MySQL is NOT a enterprise solution.   I wouldn't recommend it if you require replication.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.