Re: Replication within SQL Express
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Mon, 19 Mar 2007 07:59:31 -0400
The trigger is not going to be fired across the network. What the op wants
to do is have some action originating on the remote locations move data to
the central location. He used the word trigger, which confused the both of
us:(
--
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
"Jacx" <Jacx@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:457FA84E-8C43-4BF6-855E-3BF5A4ECCCB0@xxxxxxxxxxxxxxxx
how busy will be ur database???
if its too busy then firing a trigger on each and every update and
checking
for the
network connection for move the database is not a good idea i guess.
How about timing the whole network checking process and moving the data at
that particular time.
regarDs,
J@cx
"Will" wrote:
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 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.
.
- Prev by Date: Re: Rollback SP4
- Next by Date: Re: Splitting of transaction during transactional replication
- Previous by thread: Re: Replication within SQL Express
- Next by thread: Re: Splitting of transaction during transactional replication
- Index(es):
Relevant Pages
|
