Replication between 2 or more SQL Express databases

Problem description
There is one central SQL Express database. And one (or more) SQL Express
database(s) on remote locations. This approach 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
Since I’m a newbie on these replication matters, I would like some help with
this.
Who is willing to help with examples and suggestions?
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….
firing triggers over the network is not scalable, as there is significant
latency involved for each trigger firing. For a batch operation it is
painful. Your transactions will also hang for up to 20 seconds if the
connection is down.
You might want to look at SSIS (Integration Services) or perhaps even bcp
for what you are trying to do.
You might also want to upgrade from Express to Standard so you can do
transactional replication which will do exactly what you are trying to do.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Will" <Will@.discussions.microsoft.com> wrote in message
news:474B4797-1811-4ADC-8576-0137A4FCBAF9@.microsoft.com...
> Problem description
> --
> There is one central SQL Express database. And one (or more) SQL Express
> database(s) on remote locations. This approach 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
> --
> Since I'm a newbie on these replication matters, I would like some help
> with
> this.
> Who is willing to help with examples and suggestions?
> 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..
>
|||Hi Hilary,
Thanks for your support and feedback. I really do appreciate this!!!

> firing triggers over the network is not scalable, as there is significant
> latency involved for each trigger firing. For a batch operation it is
> painful. Your transactions will also hang for up to 20 seconds if the
> connection is down.
Mmm - sounds like we have a different understanding of the term trigger.
I would like to use triggers on the remote databases - not the central one.
As a result, triggers are not travelling across the network.

> You might want to look at SSIS (Integration Services) or perhaps even bcp
> for what you are trying to do.
I looked at SSIS. That would require
I'm not familiar with BCP. But it looks like a bulk COPY tool.
How would this help in MOVING the data?

> You might also want to upgrade from Express to Standard so you can do
> transactional replication which will do exactly what you are trying to do.
Do you mean replace Express with standaard on the remote side?
On the central side? Or both?
Keep in mind that SQL Express on the remote sites needs to run on
Workstations.
Ans therefor requires a light footprint.
Will
|||Answers inline.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Will" <Will@.discussions.microsoft.com> wrote in message
news:E6201678-BFD9-4290-ABAC-1D7A5F6E1962@.microsoft.com...
> Hi Hilary,
> Thanks for your support and feedback. I really do appreciate this!!!
>
> Mmm - sounds like we have a different understanding of the term trigger.
> I would like to use triggers on the remote databases - not the central
> one.
> As a result, triggers are not travelling across the network.
>
> I looked at SSIS. That would require
> I'm not familiar with BCP. But it looks like a bulk COPY tool.
> How would this help in MOVING the data?
>
it doesn't move data, it copies it row by row. I know of no technology
which actually moves that, all copy it and you have to delete it yourself on
the source.

> Do you mean replace Express with standaard on the remote side?
> On the central side? Or both?
> Keep in mind that SQL Express on the remote sites needs to run on
> Workstations.
> Ans therefor requires a light footprint.
In retrospect you will probably need a code solution. Put a timestamp on
your tables which you can use to determine which rows have changed since the
last time you "moved" data. Anything greater than the timestamp the last
time you polled must be moved.
You will probably be able to use any version of SQL Server for this.
> --
> Will
>