Database replication

I'm new in the area of data replication.
I hope someone is willing to help me on this little thing I'm trying to accomplish.

Problem description
--
There is one central SQL Express database. And one (or more) SQL Express database(s) on remote locations. This approach of databases remote and central is mandatory due to some application features later-on.
The local database(s) on the remote location(s) will act as a sort of backup in case there is no network connection. Meaning that the data is stored locally at all times. And moved to the central database once the network connection is on.

Updates to the local database(s) should trigger a process where all data is moved to the central database if there is a network connection. So for now, it’s a one way stream: remote to central.
This approach gives us the option to delay the moves - if needed. For example, the daily data is kept local and moved to the central site over night. Which is a good thing to have if network resources are limited during office hours.

Solution
To make this happen, I was thinking allong the lines of using triggered stored procedures.
Where a local database update triggers a stored procedure that checks the network connection and performs the move to the central database.
If there is more than one record in the remote database, all records are moved to the central database. Also, an entry should be added to a log-file.
If the network connection is not there for whatever reason, an entry should be written in a log-file stating that there was no connection to the central database.

Options
-
Another way of solving this is using RMO-based publications and subscribers.
Where the database on the remote locations are publishers. And the central database are subscribers. However, according to Microsoft:
- SQL Express can only act as a subscriber.
- Data is copied. And not moved.
According to the SQL-BOL, the first limitation doesn’t exist when using programmatic, transactional replication.
However, the second needs to be solved as well. And in the examples of SQL-BOL, this is not mentioned. At least not that I’m aware of.

There are some advantages using RMO. For example deploying a new version of the application. Or deploying an updated database schema. Please let me know if this is proven to be really usefull. If so, this would be the preferred method – provided that the limitations mentioned earlier are solved. And if using this method, I would prefer stored procedures or Visual Basic for programming this.

Help wanted
--
Like I stated earlier, I’m a newbie on these replication matters.
So I really would like some help with this.
Who is willing to help with suggestions and examples?
Sofar, I’ve been able to:
- Install SQL Express with network connections enabled
- Running the Management Studio and create the data model I would like to use
- Add data manually and via an ODBC connection (using a system wide DSN).
- Run SQL select commands against the added data.
So the infrastructure is up and running….

Best regards,

Will Moonen

The major issue you face with your proposal is that you just can't do 'push' Replication with SQL Express.

So, you will be limited in approaches. Perhaps using stored procedures will work for you. I would suggest that you consider using a combination of Stored Procedures, SQLCmd.exe, and Windows scheduler; then schedule the stored procedures to run at a certain interval. In your proposal, you indicate that data will be 'pushed' up to the central server upon change, or stored locally if there is no connection, and then 'pushed' up on the next change. What if there isn't a 'next change' for quite some time -how would the local data be pushed to central?

|||

Many thanks for your feedback!

> The major issue you face with your proposal is that you just
> can't do 'push' Replication with SQL Express.

Agree - i.e. when you want to start things from the management studio.
But what about a 'push' by using the stored procedures? That is supported
according to the Books-On-Line.

> So, you will be limited in approaches. Perhaps using stored procedures will work for you.
> I would suggest that you consider using a combination of Stored Procedures, SQLCmd.exe,
> and Windows scheduler; then schedule the stored procedures to run at a certain interval.

Mmm, scheduling via Windows is an option. But only as a last resort.
There are to many things that can go wrong when using the Windows scheduler.

> In your proposal, you indicate that data will be 'pushed' up to the central server upon change,
> or stored locally if there is no connection, and then 'pushed' up on the next change.
> What if there isn't a 'next change' for quite some time -
> how would the local data be pushed to central?

The data model is rather small. It could hold the data for weeks.
There should be a push at least once per 24 hrs.
If not, the central site of the application will signal this.