I'm new to replication, but have a challenging requirement...
I have multiple MSDE 2K instances that will be used by a surveying crew that
may be distributed across many miles - sometimes in jungle or desert
environments where they never even see each other.
All/any data in any instance is updateable; all changes must be propagated -
at irregular intervals (daily or?) to all other instances. There is no
designated "master" (or perhaps all instances could be considered "masters").
We can live with a hub-and-spoke replication topology (ie, all replication
occurs via a hub instance at the field camp, which then, eventually,
replicates to all the other spokes).
===> The instances are NOT (and are NEVER) connected over a network! (not
even wireless - although we should be able to at least keep the system clocks
synchronized, if that's required for reliable synchronization, by having
each system have a GPS receiver).
Therefore, all synchronization must be via files (flash drive, etc). Even
the definition/setup of the synchronization (subscriptions) must not require
a network connection.
Can Merge replication be implemented between MSDE instances, given these
network constraints?
Thanks!
David
Unless you provide at least an occasional network connectivity between
publisher and subscribers merge replication cannot be implemented.
Yury
"David" <David@.discussions.microsoft.com> wrote in message
news:32C0446B-5D11-4FAF-AB03-70F4096642BC@.microsoft.com...
> I'm new to replication, but have a challenging requirement...
> I have multiple MSDE 2K instances that will be used by a surveying crew
> that
> may be distributed across many miles - sometimes in jungle or desert
> environments where they never even see each other.
> All/any data in any instance is updateable; all changes must be
> propagated -
> at irregular intervals (daily or?) to all other instances. There is no
> designated "master" (or perhaps all instances could be considered
> "masters").
>
> We can live with a hub-and-spoke replication topology (ie, all replication
> occurs via a hub instance at the field camp, which then, eventually,
> replicates to all the other spokes).
> ===> The instances are NOT (and are NEVER) connected over a network! (not
> even wireless - although we should be able to at least keep the system
> clocks
> synchronized, if that's required for reliable synchronization, by having
> each system have a GPS receiver).
> Therefore, all synchronization must be via files (flash drive, etc). Even
> the definition/setup of the synchronization (subscriptions) must not
> require
> a network connection.
> Can Merge replication be implemented between MSDE instances, given these
> network constraints?
> Thanks!
> David
|||its probably better to use transactional replication with a local dummy
subscriber. Collect the contents of msrepl_commands and then mail them back
and forth. This will be a list of all database activity which you can run on
each node for synchronization purposes.
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
"David" <David@.discussions.microsoft.com> wrote in message
news:32C0446B-5D11-4FAF-AB03-70F4096642BC@.microsoft.com...
> I'm new to replication, but have a challenging requirement...
> I have multiple MSDE 2K instances that will be used by a surveying crew
that
> may be distributed across many miles - sometimes in jungle or desert
> environments where they never even see each other.
> All/any data in any instance is updateable; all changes must be
propagated -
> at irregular intervals (daily or?) to all other instances. There is no
> designated "master" (or perhaps all instances could be considered
"masters").
>
> We can live with a hub-and-spoke replication topology (ie, all replication
> occurs via a hub instance at the field camp, which then, eventually,
> replicates to all the other spokes).
> ===> The instances are NOT (and are NEVER) connected over a network! (not
> even wireless - although we should be able to at least keep the system
clocks
> synchronized, if that's required for reliable synchronization, by having
> each system have a GPS receiver).
> Therefore, all synchronization must be via files (flash drive, etc). Even
> the definition/setup of the synchronization (subscriptions) must not
require
> a network connection.
> Can Merge replication be implemented between MSDE instances, given these
> network constraints?
> Thanks!
> David
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Wednesday, March 28, 2012
MSDE 2000A Upgrade to SQL Express SP1 with Merge Replication
Hello,
I have the following scenario. An application using SQL Server 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on the client machine. Since the client is offline quite often merge replication is used to keep the clients in sync.
Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor upgrade (on the same box) worked fine and all clients could still synchronize. Fine :-)
Now we try to upgrade the clients to SQL Express SP1. Now the problems start :-(
1) After the upgrade the entry within the Synchronization Manager is gone (we can overcome this by using sp_MSregistersubscription or by manually disable and enable Synchronization manager on the subscription properties)
2) Initial Synchronization (takes a long time but) works fine, if I apply changes to the subscriber or force a reinitialize all following synchronization will fail. With the following messages:
Error messages:
The merge process could not clean up the subscription to 'tstvmw23':'TestBase:'TestBase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200965)
Get help: http://help/MSSQL_REPL-2147200965
New request is not allowed to start because it should come with valid transaction descriptor. (Source: MSSQLServer, Error number: 3989)
Get help: http://help/3989
Remark the test system I use is a nearly empty db (only with full schema and a few lookup tables) with 15MB. The same error occures if I create a new database an subscribe to the same publication.
Please help otherwise I'm forced to step back to MSDE.
Thanks in advance,
Thomas Hotz
Hi,just to make sure you do not want SQL Server Express to the a merge replicant...
http://msdn2.microsoft.com/en-us/library/ms165616.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||What this table on the BOL means is that SQL Server Express cannot act as a Distributor/Publisher on a merge replication and the main cause is that it does not have an Agent.
http://technet.microsoft.com/en-us/library/ms143470.aspx
See the topic SQL Server does not include an agent on that reference.
SQL Server Express can act as a subscriber on Merge Replication though. If you have a type of subscription Pull, as the Agent runs on the subscribers you have to use RMO on your code to instantiate an agent or use Windows Synchronization Manager (Start Menu-> Accessories -> Synchronize)
We have SQLE running as subscribers on a pull merge replication topology...sql
MSDE 2000A Upgrade to SQL Express SP1 with Merge Replication
Hello,
I have the following scenario. An application using SQL Server 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on the client machine. Since the client is offline quite often merge replication is used to keep the clients in sync.
Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor upgrade (on the same box) worked fine and all clients could still synchronize. Fine :-)
Now we try to upgrade the clients to SQL Express SP1. Now the problems start :-(
1) After the upgrade the entry within the Synchronization Manager is gone (we can overcome this by using sp_MSregistersubscription or by manually disable and enable Synchronization manager on the subscription properties)
2) Initial Synchronization (takes a long time but) works fine, if I apply changes to the subscriber or force a reinitialize all following synchronization will fail. With the following messages:
Error messages:
The merge process could not clean up the subscription to 'tstvmw23':'TestBase:'TestBase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200965)
Get help: http://help/MSSQL_REPL-2147200965
New request is not allowed to start because it should come with valid transaction descriptor. (Source: MSSQLServer, Error number: 3989)
Get help: http://help/3989
Remark the test system I use is a nearly empty db (only with full schema and a few lookup tables) with 15MB. The same error occures if I create a new database an subscribe to the same publication.
Please help otherwise I'm forced to step back to MSDE.
Thanks in advance,
Thomas Hotz
Hi,just to make sure you do not want SQL Server Express to the a merge replicant...
http://msdn2.microsoft.com/en-us/library/ms165616.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||What this table on the BOL means is that SQL Server Express cannot act as a Distributor/Publisher on a merge replication and the main cause is that it does not have an Agent.
http://technet.microsoft.com/en-us/library/ms143470.aspx
See the topic SQL Server does not include an agent on that reference.
SQL Server Express can act as a subscriber on Merge Replication though. If you have a type of subscription Pull, as the Agent runs on the subscribers you have to use RMO on your code to instantiate an agent or use Windows Synchronization Manager (Start Menu-> Accessories -> Synchronize)
We have SQLE running as subscribers on a pull merge replication topology...
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
>
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
>
Monday, March 26, 2012
MSDE 2000 Replication to SQL Server 2000
i went through the documentation but i was not clear on following, here is the scenario :-
a Central server is having SQL Server 2000
3 Remote Locations :- Each having 4-5 no of computers, connected on a Lan, and on one of the machines MSDE 2000 will be running.
My Questions are :-
1. Can remote locations, update data locally and send changes (say in every one hour) to central server. If yes, then how ?
2. Same way they can receive updates from Central Server on whatever was updated on Centra Server or on the 3 remote locations. If yes, then How ?
any help will be highly appreciable.
Merge replication should be able to do this for you.|||Thanks for your answer.
Kindly note that if in this same scenario, can we do the same thing, if following combinations are used :-
1. SQL Express 2005 on Remote locations & on Central Server SQL Server 2005 is used ?
2. If Sql Express 2005 on Remote Locations & Central Database SQL Server 2000 is used ?
3. If MSDE 2000 on Remote Locations & Central Database SQL Server 2005 is used ?
|||#2 is not supported, this is documented in Books Online. #3 is supported, but you'll get better performance, scalability and functionality if #1 is used.
|||Thanks for reply... sorry for asking again n again...
For # 2 Option, if only features of SQL Server 2000 are used, still it is not possible ?
For #1 option, even i will prefer to use that, my only concern was in documentation they are mentioning that SQL Express server can only act as subscriber, so can it receive updates as well as send local updates, pls cfm ?
|||YOu can access SQL 2005 Books Online at http://msdn2.microsoft.com/en-us/library/ms203721.aspx. You can learn more about replication at http://msdn2.microsoft.com/en-us/library/ms151198.aspx.
A merge subscriber can upload and download changes.
MSDE 2000 Replication Conflict
I am using MSDE 2000 for my replication. the problem is that althought at time creating publication it inserts GUID into every table but if one table in 2 nodes has same primary key, it inserts only one row ( accorrding to prority ). there are some Conflict Reslover methods that can be used for this purpose. i wanted to ask that is there any other way for me to resolve this conflict. i am asking for a new way because my database schema has been created and a lot of coding behalf of that schema has been done.
I'd be thankful if you guide me.
Regards,
Are you trying to insert the same primary key at both nodes? In your case, conflict resolver won't work for you because although you have inserted at both nodes, they are treated as different rows with different rowguid. What are you trying to achieve in this scenario? Do rows with same primary key stand for the same thing in your business scenario?
You can also refer to this post to see if you have the same problem: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=509453&SiteID=1
Hope it helps
Wanwen
|||Dear Wanwen.
i have a scenario as follows.
I have one publisher and two subscribers. a table called "Companies" is my article. That "Companies" table has a primary key column called CompanyId which is an Identity Row. Now when i insert data into subscribers i will have two rows with same Primary key. For Example two rows with CompanyId 1. Now at time of merging data, i want both companyIds ( both rows with Pk 1 ) be present in publisher. But at reality it does not happen.
What should i do?! because i need both rows at publisher .
Note: i have to mention that at time of creating Subscription GUID is included by SQL Server. So why is it not working?!
Regards,|||
In your case, you need to use identity range management. Please refer to BOL topic: Replicating Identity Columns. Basically, to use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur. You could use auto for Identity Range Management Option and specify @.pub_identity_range, @.identity_range and @.threshold.
Rowguid column added by replication is for change tracking, conflict detecting and resolving, not for failure to apply changes due to constraint.
Hope it helps.
Wanwen
Monday, March 19, 2012
MSDE & SQL Server 2005 Mobile Server Tools problem
After installing MSDE(with SP4) and SQL Server 2000 SP4 Replication Components, the SQL Server 2005 Mobile Server Tools installation fails when running the System Configuration Check with a SQL Server requirement Error.
"You must first install the Replication Components for SQL Server 2000 SP 3a or higher or the SQL Server 2005 Replication Components"
Am I getting this error because I'm using the Desktop Engine version of the SQL Server 2000?
We have NOT tested SQL CE/Mobile Merge Replication with MSDE. And, we dont know the problems you may face.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||I've been using MSDE with SQL CE 2.0 without any problems.
With the release of Windows Mobile 5.0 I had to upgrade my applications using VS 2005 and start using SQL Server 2005 Mobile Edition instead of SQL CE2.0.
With SQL Server 2005 Express Edition things changed a little bit, because this version only works as a Subscriber when using Replication (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp).
The scenario is quite simple. After installing MSDE(SP4) in Windows XP Professional(SP2) and installing Microsoft SQL Server 2000 SP4 Replication Components and Microsoft .NET Framework 2.0 I try to install the SQL Server 2005 Mobile Server Tools, but during the System Configuration Check that error is displayed.
|||Any tips on how it can be achieved? MSDE - SqlCE merge replication I mean.There is a problem which I'm trying to solve for quite some time:
SQL Server 2000 - SqlCE merge replication works just fine, but in MSDE -SqlCE scenario it seems to be some trouble.
I always get the same error: 29045 - reconciler has failed. Basically it means, that SqlCE server agent cannot start Reconciler under MSDE. What can cause such a disaster? Incompatibility of components used? This isnt connectivity or access rights issue, i'm pretty sure of it.
I've tried to use different versions of MSDE: MSDE SP4 and MSDE SP3a. Searching in different newsgroups for a couple of hours makes me think that older versions of MSDE are more suitable for my task because few posts stated that merge replication works on MSDE SP3a, but this subject isnt covered in detail. Can you provide any help?
|||
READ MY POST YESTERDAY
we are all waiting for Microsoft to bring this function
I will pay for it as you probably will as well
I dont understand why we are the only developers putting pressure on this matter
We need help. The other case is that we can begin referring other 3rd party non microsoft providers....
|||You need to get onto my post and tell them you want it too
|||If you are looking for SQL Mobile/SQL Ev to SQL Server synchronization you need to have either SQL Server 2000 or SQL Server 2005 Standard Edition and above.
Thanks
Laxmi Narsimha Rao ORUGNATI, MSFT, SQL Everywhere (SQL Ev), Microsoft Corp.
MSDE & SQL Server 2005 Mobile Server Tools problem
After installing MSDE(with SP4) and SQL Server 2000 SP4 Replication Components, the SQL Server 2005 Mobile Server Tools installation fails when running the System Configuration Check with a SQL Server requirement Error.
"You must first install the Replication Components for SQL Server 2000 SP 3a or higher or the SQL Server 2005 Replication Components"
Am I getting this error because I'm using the Desktop Engine version of the SQL Server 2000?
We have NOT tested SQL CE/Mobile Merge Replication with MSDE. And, we dont know the problems you may face.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||I've been using MSDE with SQL CE 2.0 without any problems.
With the release of Windows Mobile 5.0 I had to upgrade my applications using VS 2005 and start using SQL Server 2005 Mobile Edition instead of SQL CE2.0.
With SQL Server 2005 Express Edition things changed a little bit, because this version only works as a Subscriber when using Replication (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp).
The scenario is quite simple. After installing MSDE(SP4) in Windows XP Professional(SP2) and installing Microsoft SQL Server 2000 SP4 Replication Components and Microsoft .NET Framework 2.0 I try to install the SQL Server 2005 Mobile Server Tools, but during the System Configuration Check that error is displayed.
|||Any tips on how it can be achieved? MSDE - SqlCE merge replication I mean.There is a problem which I'm trying to solve for quite some time:
SQL Server 2000 - SqlCE merge replication works just fine, but in MSDE -SqlCE scenario it seems to be some trouble.
I always get the same error: 29045 - reconciler has failed. Basically it means, that SqlCE server agent cannot start Reconciler under MSDE. What can cause such a disaster? Incompatibility of components used? This isnt connectivity or access rights issue, i'm pretty sure of it.
I've tried to use different versions of MSDE: MSDE SP4 and MSDE SP3a. Searching in different newsgroups for a couple of hours makes me think that older versions of MSDE are more suitable for my task because few posts stated that merge replication works on MSDE SP3a, but this subject isnt covered in detail. Can you provide any help?|||
READ MY POST YESTERDAY
we are all waiting for Microsoft to bring this function
I will pay for it as you probably will as well
I dont understand why we are the only developers putting pressure on this matter
We need help. The other case is that we can begin referring other 3rd party non microsoft providers....
|||You need to get onto my post and tell them you want it too
|||If you are looking for SQL Mobile/SQL Ev to SQL Server synchronization you need to have either SQL Server 2000 or SQL Server 2005 Standard Edition and above.
Thanks
Laxmi Narsimha Rao ORUGNATI, MSFT, SQL Everywhere (SQL Ev), Microsoft Corp.
Saturday, February 25, 2012
msdb recreate cause replication problems
We just went through a nasty experience whereby we had to recreate the
MSDB database.
System is now back, but replication is completely messed up. Is there a
way to get all the MS* tables back, in the MSDB? I tried to de-install
and install Replication, but that doesn't seem to generate all the
required tables and procs
SQL2005, SP1.
Any help much appreciated!
I take it you mean you want your jobs to pick up where they left off.
Dropping and recreating the publications and subscriptions should do that.
If you mean the replication table in msdb, they are created there when you
install sql server.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<renedevries@.gmail.com> wrote in message
news:1159994716.013545.158270@.i3g2000cwc.googlegro ups.com...
> We just went through a nasty experience whereby we had to recreate the
> MSDB database.
> System is now back, but replication is completely messed up. Is there a
> way to get all the MS* tables back, in the MSDB? I tried to de-install
> and install Replication, but that doesn't seem to generate all the
> required tables and procs
> SQL2005, SP1.
> Any help much appreciated!
>
|||Eventually called MS Tech Support. The goal was to het replication
working at all, never mind the jobs or the previous state. But since
all relevant tables in msdb where gone, the whole thing was in limbo
The only REAL option is indeed a fresh install of SQL. However, they
where able to help me out, though a number of steps which involved
removing repliation from the database, dropping the distribution db,
and some other assorted trickery. But eventually we got it to work.
Turns out that if you re-create the distribution database all the
associated msdb tables and jobs are also recreated. Fortunately ;)
Lesson: make sure you keep good, validated backups of msdb at all
times. Bitten once again
Rene
Hilary Cotter schreef:
> I take it you mean you want your jobs to pick up where they left off.
> Dropping and recreating the publications and subscriptions should do that.
> If you mean the replication table in msdb, they are created there when you
> install sql server.
> --
MSDB database.
System is now back, but replication is completely messed up. Is there a
way to get all the MS* tables back, in the MSDB? I tried to de-install
and install Replication, but that doesn't seem to generate all the
required tables and procs
SQL2005, SP1.
Any help much appreciated!
I take it you mean you want your jobs to pick up where they left off.
Dropping and recreating the publications and subscriptions should do that.
If you mean the replication table in msdb, they are created there when you
install sql server.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<renedevries@.gmail.com> wrote in message
news:1159994716.013545.158270@.i3g2000cwc.googlegro ups.com...
> We just went through a nasty experience whereby we had to recreate the
> MSDB database.
> System is now back, but replication is completely messed up. Is there a
> way to get all the MS* tables back, in the MSDB? I tried to de-install
> and install Replication, but that doesn't seem to generate all the
> required tables and procs
> SQL2005, SP1.
> Any help much appreciated!
>
|||Eventually called MS Tech Support. The goal was to het replication
working at all, never mind the jobs or the previous state. But since
all relevant tables in msdb where gone, the whole thing was in limbo
The only REAL option is indeed a fresh install of SQL. However, they
where able to help me out, though a number of steps which involved
removing repliation from the database, dropping the distribution db,
and some other assorted trickery. But eventually we got it to work.
Turns out that if you re-create the distribution database all the
associated msdb tables and jobs are also recreated. Fortunately ;)
Lesson: make sure you keep good, validated backups of msdb at all
times. Bitten once again
Rene
Hilary Cotter schreef:
> I take it you mean you want your jobs to pick up where they left off.
> Dropping and recreating the publications and subscriptions should do that.
> If you mean the replication table in msdb, they are created there when you
> install sql server.
> --
Monday, February 20, 2012
MSDB Database SUSPECT - RESET PROCEDURE ?
I have a regional server running SQL2000 and Merge replication, the MSDB, NORTHWIND and 2 User DB's have been marked suspect. I cannot get into query analyzer etc to run the necessary scripts as it is all suspect.
Any ideas on how I can reset the status on the MSDB database??
Unfortunately there is no backup of either of the 2 system databases :(
** ANY ASSISTANCE WOULD BE GREATLY APPRECIATED **
JReset database with "suspect" status:
Do the following steps from the Query Analyzer:
1) Use Master
GO
EXEC sp_resetstatus <dbname>
2) Stop & Start the SQL Server
3) Run DBCC CHECKDB
4) Do the Full database backup (Master DB included)
If the above steps didn't resolve the problem, run the query below:
Use Master
GO
EXEC sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Do the step 1 ~ 4 above, then:
Use Master
GO
EXEC sp_configure 'allow updates', 0 -- to turn off
GO
Reconfigure with override
GO
NOTE: If the database become "suspect" because it ran out of disk space, you have to do the step #1 above, then use the "ALTER DATABASE ..." to add more data file (or log file), Stop & start the SQL Server and finish with step #3 & #4 as above.
Good luck.|||Valid reponse by SVT and make sure to have regular backups for SYSTEM Databases too in future.
Any ideas on how I can reset the status on the MSDB database??
Unfortunately there is no backup of either of the 2 system databases :(
** ANY ASSISTANCE WOULD BE GREATLY APPRECIATED **
JReset database with "suspect" status:
Do the following steps from the Query Analyzer:
1) Use Master
GO
EXEC sp_resetstatus <dbname>
2) Stop & Start the SQL Server
3) Run DBCC CHECKDB
4) Do the Full database backup (Master DB included)
If the above steps didn't resolve the problem, run the query below:
Use Master
GO
EXEC sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Do the step 1 ~ 4 above, then:
Use Master
GO
EXEC sp_configure 'allow updates', 0 -- to turn off
GO
Reconfigure with override
GO
NOTE: If the database become "suspect" because it ran out of disk space, you have to do the step #1 above, then use the "ALTER DATABASE ..." to add more data file (or log file), Stop & start the SQL Server and finish with step #3 & #4 as above.
Good luck.|||Valid reponse by SVT and make sure to have regular backups for SYSTEM Databases too in future.
MSDB and MASTER DB
Im using MS SQL2000 and have and replication scheme with log
shipping. Is posible replicate by log shipping or transactional
replication schemes this DBs
Thanks
I'm not sure what the question is really here - the subject seems so
different to the body text
If you want to know the pros and cons of transactional replication vs log
shipping, please see this article:
http://www.replicationanswers.com/Standby.asp
If you are looking to set up a combination of log shipping and replication,
then please tell us a little more...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Both system db's are in simple mode. Microsoft recommends backing the msdb
database up and shipping it. While it is possible to restore a master
database to another server, Microsoft recommends you transfer objects to the
standby server.
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
<gnp1977@.gmail.com> wrote in message
news:1173280868.971366.142840@.c51g2000cwc.googlegr oups.com...
Im using MS SQL2000 and have and replication scheme with log
shipping. Is posible replicate by log shipping or transactional
replication schemes this DBs
Thanks
|||Hi, thanks for the answer, I need transfer the content of two DB to a
standby server...One of this have 35mb and the other have 13GB.
What method is the better? Transactional or Log Shipping?
And other thing, de DBs MASTER and MSDB can be replicated to the
stand by server, I need this because is a Continuity of Bussines site.
Thanks
|||For these volumes I would use transactional replication if possible. For the
master and msdb databases, what sort of objects are you moving?
In master your linked servers and logins live. Do these change much? There
is a SQL DTS/SSIS job which will transfer the logins. Your msdb databsae
will contain DTS packages and jobs. These can be scripted/exported and sent
to the standby server.
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
<gnp1977@.gmail.com> wrote in message
news:1173296916.452161.294790@.n33g2000cwc.googlegr oups.com...
Hi, thanks for the answer, I need transfer the content of two DB to a
standby server...One of this have 35mb and the other have 13GB.
What method is the better? Transactional or Log Shipping?
And other thing, de DBs MASTER and MSDB can be replicated to the
stand by server, I need this because is a Continuity of Bussines site.
Thanks
shipping. Is posible replicate by log shipping or transactional
replication schemes this DBs
Thanks
I'm not sure what the question is really here - the subject seems so
different to the body text
If you want to know the pros and cons of transactional replication vs log
shipping, please see this article:
http://www.replicationanswers.com/Standby.asp
If you are looking to set up a combination of log shipping and replication,
then please tell us a little more...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Both system db's are in simple mode. Microsoft recommends backing the msdb
database up and shipping it. While it is possible to restore a master
database to another server, Microsoft recommends you transfer objects to the
standby server.
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
<gnp1977@.gmail.com> wrote in message
news:1173280868.971366.142840@.c51g2000cwc.googlegr oups.com...
Im using MS SQL2000 and have and replication scheme with log
shipping. Is posible replicate by log shipping or transactional
replication schemes this DBs
Thanks
|||Hi, thanks for the answer, I need transfer the content of two DB to a
standby server...One of this have 35mb and the other have 13GB.
What method is the better? Transactional or Log Shipping?
And other thing, de DBs MASTER and MSDB can be replicated to the
stand by server, I need this because is a Continuity of Bussines site.
Thanks
|||For these volumes I would use transactional replication if possible. For the
master and msdb databases, what sort of objects are you moving?
In master your linked servers and logins live. Do these change much? There
is a SQL DTS/SSIS job which will transfer the logins. Your msdb databsae
will contain DTS packages and jobs. These can be scripted/exported and sent
to the standby server.
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
<gnp1977@.gmail.com> wrote in message
news:1173296916.452161.294790@.n33g2000cwc.googlegr oups.com...
Hi, thanks for the answer, I need transfer the content of two DB to a
standby server...One of this have 35mb and the other have 13GB.
What method is the better? Transactional or Log Shipping?
And other thing, de DBs MASTER and MSDB can be replicated to the
stand by server, I need this because is a Continuity of Bussines site.
Thanks
Subscribe to:
Posts (Atom)