MSDE 2000 to MSDE 2000 Replication?

Hi,
I would like to know if I can do Replication from a MSDE 2000 to another
MSDE 2000 system (one way).
All I need to do is Sync Server database changes on to a Workstation
database(Real-Time).
Let me give an example.
We have Server and Workstation product. Our Server will have a LiveConfig db
and each workstation will have a localLookupDB(basically same as
LiveConfig). I know If I have the Server as Std SQL Server 2000 I can use
Replication to propogate changes onto workstation what if the Server is
MSDE?
Are there any ways to Replicate data From a Server database onto Workstation
database (Real-Time).
Your advice is appreciated.
Thanks
Arun
hi Arun,
Arun Kumar wrote:
> Hi,
> I would like to know if I can do Replication from a MSDE 2000 to
> another MSDE 2000 system (one way).
> All I need to do is Sync Server database changes on to a Workstation
> database(Real-Time).
> Let me give an example.
> We have Server and Workstation product. Our Server will have a
> LiveConfig db and each workstation will have a
> localLookupDB(basically same as LiveConfig). I know If I have the
> Server as Std SQL Server 2000 I can use Replication to propogate
> changes onto workstation what if the Server is MSDE?
> Are there any ways to Replicate data From a Server database onto
> Workstation database (Real-Time).
>
MSDE supports both snapshot and merge replication both as publisher and as
subcriber, so your potential design could be supported... but remember that
replication is non just one way (publisher to subscriber(s)) but the sync
goes both ways ( subscriber to publisher to other subscriber(s)) to
propagate modification at all levels..
I'm not a replication guy but I do not think "real-time" replication is that
suitable as a certain degree of latency should be expected in order not to
overhelm all the servers with replication schedules and related activity,
that, depending on the amount of the involved data, can be time consuming..
merge replication can be indicated if the data load per scheduled timeframe
is not very very high, but it supports simultaneous data entry on all
servers (per server autonomy, so tthat each individual instance can continue
it's own work in case of net problems), continuos data transfer.. you can
even take care of conflicts providing your own preferred conflicts
resolution policy, but you have to do it your own via DMO and/or scripts as
MSDE is not provided with Enterprise Manager which has built-in tools and
wizards for configuring and troubleshooting replication... anyway, merge
replication, provides an out of the box mechanism for resolving conflicts at
the column level and high transactional consistency of operations in case of
low conflicts rates and few sites involved.. the more autonoms servers are
involved, the more heavy load will the scenario introduce...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks for the reply. I agree with the latency, but Can you suggest me any
other way.
Here's my idea
Workstations user enters the data and the Business layer on the Server will
insert/update those records on the LiveConfig Server database using Stored
procedures.
My idea is use add insert/Update statement into the same stored procedure to
insert/update other Workstation Databases. When we configure the Workstation
Database we add the Workstation Server/Database name onto one of the tables
in LiveConfig Server Database table.
Here's how i envision, correct me if I am wrong.
User enters data -> Business Layer ->
->Stored Procedure -- >(Inside str proc)
Call StoredProcedure Insert/Update, MainServer,MainDatabase
Select WorkstationServer,WorkstationDBName From
MainServer.MainDatabase.DBTable
If found THEN
Begin
Use Cursors
While Not MainServer.MainDatabase.DBTable.Eof
Begin
StoredProcedure Insert/Update,
WorkstationServer,Database
End
End
Do you think this method will work? any drawbacks on this?
I know the above statement is not a real SQL Statements its just a concept.
Let me know your comments.
Thanks
Arun
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3n0q38F18covhU1@.individual.net...
> hi Arun,
> Arun Kumar wrote:
> MSDE supports both snapshot and merge replication both as publisher and as
> subcriber, so your potential design could be supported... but remember
> that replication is non just one way (publisher to subscriber(s)) but the
> sync goes both ways ( subscriber to publisher to other subscriber(s)) to
> propagate modification at all levels..
> I'm not a replication guy but I do not think "real-time" replication is
> that suitable as a certain degree of latency should be expected in order
> not to overhelm all the servers with replication schedules and related
> activity, that, depending on the amount of the involved data, can be time
> consuming..
> merge replication can be indicated if the data load per scheduled
> timeframe is not very very high, but it supports simultaneous data entry
> on all servers (per server autonomy, so tthat each individual instance can
> continue it's own work in case of net problems), continuos data transfer..
> you can even take care of conflicts providing your own preferred conflicts
> resolution policy, but you have to do it your own via DMO and/or scripts
> as MSDE is not provided with Enterprise Manager which has built-in tools
> and wizards for configuring and troubleshooting replication... anyway,
> merge replication, provides an out of the box mechanism for resolving
> conflicts at the column level and high transactional consistency of
> operations in case of low conflicts rates and few sites involved.. the
> more autonoms servers are involved, the more heavy load will the scenario
> introduce...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Arun,
Arun Kumar wrote:
> Andrea,
> Thanks for the reply. I agree with the latency, but Can you suggest
> me any other way.
> Here's my idea
> Workstations user enters the data and the Business layer on the
> Server will insert/update those records on the LiveConfig Server
> database using Stored procedures.
> My idea is use add insert/Update statement into the same stored
> procedure to insert/update other Workstation Databases. When we
> configure the Workstation Database we add the Workstation
> Server/Database name onto one of the tables in LiveConfig Server
> Database table. Here's how i envision, correct me if I am wrong.
> User enters data -> Business Layer ->
> ->Stored Procedure -- >(Inside str proc)
> Call StoredProcedure Insert/Update, MainServer,MainDatabase
> Select WorkstationServer,WorkstationDBName From
> MainServer.MainDatabase.DBTable
> If found THEN
> Begin
> Use Cursors
> While Not MainServer.MainDatabase.DBTable.Eof
> Begin
> StoredProcedure Insert/Update,
> WorkstationServer,Database
> End
> End
> Do you think this method will work? any drawbacks on this?
> I know the above statement is not a real SQL Statements its just a
> concept.
ok, this pseudo code will actually be in your business layer and not in
Transact-SQL stored procedure code...
so you cycle in the "pseudo Registered servers" table you populate from your
SELECT s.WorkstationServer, s.WorkstationDBName FROM dbo.DBTable s
connect to that specified instance (and appropriate database) in the loop
and re-execute the stored procedure...
this is a lot of work and can be very time consuming for a large number of
servers... more, I hope that a rollback in an inner loop server will not
break the whole transaction requiring to rollback on all other servers :D
(how do you handle later sync in case of out of sync situations will
probably become a nightmare)
but if all your clients connects to the master , why do you need local sync?
can you not rely on scheduled (weekly, dayly, twice a day, ...) sync via a
set of SQL Server Agent jobs to send all workstation a sort of sync via
standard INSERT INTO ... DDL scripts you can create via simple queryes on
your live db?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Well, the problem is our product is a Real-Time Application talking to
hardware. Depending on the time of the day, the transactions from the
hardware will vary, not all workstations need to be synced. Our Host
App(Real-Time) talking to hardware can installed on Workstations, so it
needs a local lookup database of names, configurations and settings which
should go down the hardware. I only need a Local Lookup database of that
application. Users will update or add new hardware records from their
station which will be inserted into the Main Server DB and it should be
picked up by the Host App within few seconds or max 10-20 seconds.
Typical setup will be:
Server --> Master DB
- Workstation 1
- Workstation 2 with Host App (need a Local LookUp db)
- Workstation 3 with Host App (need a Local LookUp db)
I hope I explained my situation.
Thanks
Arun
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3n12msF194jhfU1@.individual.net...
> hi Arun,
> Arun Kumar wrote:
> ok, this pseudo code will actually be in your business layer and not in
> Transact-SQL stored procedure code...
> so you cycle in the "pseudo Registered servers" table you populate from
> your
> SELECT s.WorkstationServer, s.WorkstationDBName FROM dbo.DBTable s
> connect to that specified instance (and appropriate database) in the loop
> and re-execute the stored procedure...
> this is a lot of work and can be very time consuming for a large number of
> servers... more, I hope that a rollback in an inner loop server will not
> break the whole transaction requiring to rollback on all other servers :D
> (how do you handle later sync in case of out of sync situations will
> probably become a nightmare)
> but if all your clients connects to the master , why do you need local
> sync?
> can you not rely on scheduled (weekly, dayly, twice a day, ...) sync via a
> set of SQL Server Agent jobs to send all workstation a sort of sync via
> standard INSERT INTO ... DDL scripts you can create via simple queryes on
> your live db?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
Arun Kumar wrote:
> Well, the problem is our product is a Real-Time Application talking to
> hardware. Depending on the time of the day, the transactions from the
> hardware will vary, not all workstations need to be synced. Our Host
> App(Real-Time) talking to hardware can installed on Workstations, so
> it needs a local lookup database of names, configurations and
> settings which should go down the hardware. I only need a Local
> Lookup database of that application. Users will update or add new
> hardware records from their station which will be inserted into the
> Main Server DB and it should be picked up by the Host App within few
> seconds or max 10-20 seconds.
> Typical setup will be:
> Server --> Master DB
> - Workstation 1
> - Workstation 2 with Host App (need a Local LookUp db)
> - Workstation 3 with Host App (need a Local LookUp db)
> I hope I explained my situation.
real time applications are hard to manage by them selves, and you are adding
a layer of complexity that's not that little..
replicating data across a sytem requires time and cpu, and real time
requirements do usually not provide them...
plan carefully and test your scenario... with lot of active subscriber you
can get deep in trouble...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
I agree that for real-time its tough to maintain. I have come up with
another idea I don't if its the best idea let me know your comments on it.
Server -> Main Database
Workstation ->Lookup database
I thought of creating a utility NT Service Synchronizer (Btw Main<-->Lookup)
which will be installed on each workstation and will poll the main server
for updates on a timely basis(adjustable).
What do you think of this?
Thanks
Arun
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3n3fp1F10cej4U1@.individual.net...
> hi,
> Arun Kumar wrote:
> real time applications are hard to manage by them selves, and you are
> adding a layer of complexity that's not that little..
> replicating data across a sytem requires time and cpu, and real time
> requirements do usually not provide them...
> plan carefully and test your scenario... with lot of active subscriber you
> can get deep in trouble...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Arun
Arun Kumar wrote:
> Andrea,
> I agree that for real-time its tough to maintain. I have come up with
> another idea I don't if its the best idea let me know your comments
> on it. Server -> Main Database
> Workstation ->Lookup database
> I thought of creating a utility NT Service Synchronizer (Btw
> Main<-->Lookup) which will be installed on each workstation and will
> poll the main server for updates on a timely basis(adjustable).
> What do you think of this?
just another home made replication... perhaps you've better use a
traditional supported one and adjust (as required/supported) your
scheduling...
my $0.02
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks for your comment.
I will see what's best way to go keeping all options open.
Thanks
Arun
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3n63etF1sb8U1@.individual.net...
> hi Arun
> Arun Kumar wrote:
> just another home made replication... perhaps you've better use a
> traditional supported one and adjust (as required/supported) your
> scheduling...
> my $0.02
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>