Replication tables cleanup

Hi group,
I want to know if the system tables added by replication (MS_%) are
automatically purged in any way?
Or is my task to clean them regularly? If the former is true, what is the
recommended cleanup procedure?
Thanks,
Renato
Renato,
if you're referring to merge metadata, what you needed to do is actually
quiesce your entire enterprise, make sure everybody synchronizes all their
changes, and then pick a quiet time for a maintenance period and run the
stored procedure sp_cleanupmergemetadata. This changed in SP1 as now merge
is enabled to do automatic trimming of metadata based on the publication
retention period, which you can define (there is a profile parameter called
MetadataRetentionCleanup. It's just a Boolean, so you can set it to true or
false, 1 or 0, and turn retention-based cleanup on or off.).
If you're referring to system tables retained after you have removed a
subscriber, as long as the database is no longer involved in publication or
subscription, you can run sp_removedbreplication to reset the database.
HTH,
Paul Ibison
|||Fine! That explain all... Thank you!
Renato
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uiqOTuvIEHA.580@.TK2MSFTNGP12.phx.gbl...
> Renato,
> if you're referring to merge metadata, what you needed to do is actually
> quiesce your entire enterprise, make sure everybody synchronizes all their
> changes, and then pick a quiet time for a maintenance period and run the
> stored procedure sp_cleanupmergemetadata. This changed in SP1 as now merge
> is enabled to do automatic trimming of metadata based on the publication
> retention period, which you can define (there is a profile parameter
called
> MetadataRetentionCleanup. It's just a Boolean, so you can set it to true
or
> false, 1 or 0, and turn retention-based cleanup on or off.).
> If you're referring to system tables retained after you have removed a
> subscriber, as long as the database is no longer involved in publication
or
> subscription, you can run sp_removedbreplication to reset the database.
> HTH,
> Paul Ibison
>
|||replication maintains the replication tables automatically.
With merge replication you might want to run sp_mergecleanupmetadata every
once in a while paying attention to the caveats in
http://msdn.microsoft.com/library/de...repl3_7k89.asp
"Renato Aranghelovici" <renatoa@.rdslink.ro> wrote in message
news:OSs2TRvIEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I want to know if the system tables added by replication (MS_%) are
> automatically purged in any way?
> Or is my task to clean them regularly? If the former is true, what is the
> recommended cleanup procedure?
> Thanks,
> Renato
>
sql

replication system stored procedures parameter defaults ?

Hi there

This is a pretty straight forward question.

When using sp_droparticle or sp_changepublication etc, basically any replication system stored procedure.

There are many parameters for these sp's basically all i want to know is if i provide the relavant paramaters,that is publication name, subscriber name , specific parameter i wish to change etc, are all the other paramters defaulted to the current publication/subscriber properties.

In other words sometimes i really dont know what to provide for all the parameters i am pretty sure as long as i provide the necessary ones the other ones are defaulted correctly, BOL is not 100% clear on all the parameters ?

ThanxHi Sean,

You are right. If you dont specify a parameter value in a stored procedure, it will get set to the default value.

But when you call a sp_change*** procedure, only parameters you have specified will be changed. Others will continue to be set to what they were (They will not be reset to the default values)

For Eg: you call:

sp_addpublication @.publication='testPub', @.allow_push='true', @.allow_pull='true', @.allow_anonymous='true'

[Note that defaults for @.allow_pull and @.allow_anonymous are false]

And now if you call:

sp_changepublication @.publication='testPub', @.allow_pull='false'

this call will only set @.allow_pull='false' and will not touch @.allow_anonymous. It has already been set to 'true' and it will continue to be true.|||Hi Mahesh

Yes that helps thanx.
I had a problem with sp_addarticle i did not specify push or pull because i thought it would look at the subscriber and stay at pull, but it automatically defaulted to push, which i found strange as the subscriber/publication relationship was pull ?

Thanx|||Hi Sean, subscription-type has nothing to do with sp_addarticle, did you reference the wrong stored procedure name in your post?

Assuming you meant to say sp_addsubscription, if you look it up in Books Online, for parameter @.subscription_type, the default is PUSH. This is the only place where you can assign a subscription to be push or pull.
|||Hi Greg

Yes sorry i meant sp_addsubscription.

Replication system disk performance issue after 1 month

Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.

Performance was good. Latency was ~ 5-7 seconds.

May 10 we noticed that the DB was behind (latency was 12 hours).

All performance counters seem good with the exception of the disk.

. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.

. During this period, Disk % Busy (1 - Disk % Idle) is 100%

The publisher DB publishes about 50-52 xacts/sec.

Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.

I know this because several weeks ago, the network went down, we were 24 hours behind.

When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.

What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.

We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).

I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.

My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.

Thanks for any help on this!

Jack Griffith

Configuration:

Subscribing System:

SQL Server: 2000, SP4 - 8.0.2039

CPU - 2.8GHZ Xeon, Quad Dual-core

Memory - 3.5GB RAM

Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:

C: 39GB System and Programs

D: 97GB Log space

E: 982 GB Data space

Replication configuration:

- nosynch, continuous Transactional Replication

- Distribution db is on Subscription system

- distribution - Publication of approx. 50 transactions / second

Subscriber DB configuration:

DB size: 64458 MB

Logging: Simple (at this point)

distribution

DB size: 3111 MB

Logging: Simple (at this point)

Is the whole topology (pub,dist and sub) SQL 2000 SP2? Do you know if logreader agent or distribution agent is the bottleneck? Did you add any new indexes or triggers at the subscriber db?

Replication system disk performance issue after 1 month

Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.

Performance was good. Latency was ~ 5-7 seconds.

May 10 we noticed that the DB was behind (latency was 12 hours).

All performance counters seem good with the exception of the disk.

. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.

. During this period, Disk % Busy (1 - Disk % Idle) is 100%

The publisher DB publishes about 50-52 xacts/sec.

Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.

I know this because several weeks ago, the network went down, we were 24 hours behind.

When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.

What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.

We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).

I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.

My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.

Thanks for any help on this!

Jack Griffith

Configuration:

Subscribing System:

SQL Server: 2000, SP4 - 8.0.2039

CPU - 2.8GHZ Xeon, Quad Dual-core

Memory - 3.5GB RAM

Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:

C: 39GB System and Programs

D: 97GB Log space

E: 982 GB Data space

Replication configuration:

- nosynch, continuous Transactional Replication

- Distribution db is on Subscription system

- distribution - Publication of approx. 50 transactions / second

Subscriber DB configuration:

DB size: 64458 MB

Logging: Simple (at this point)

distribution

DB size: 3111 MB

Logging: Simple (at this point)

Is the whole topology (pub,dist and sub) SQL 2000 SP2? Do you know if logreader agent or distribution agent is the bottleneck? Did you add any new indexes or triggers at the subscriber db?sql

Replication stored procedures

Is there a list anywhere of the system stored procedures that are used
strictly in replication? Thanks.
David
David,
if you search in BOL for system stored procedures, the procedures are
categorized (or here:
http://msdn.microsoft.com/library/de...sp_00_519s.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication solution?

We have Epicor Financials as our accounting system which uses SQL Server as
its backend DBMS and a powerbuilder front-end. For the longest time, we've
had the accounting system only in one office (Vancouver) and had our
accountants able to periodically work from home using remote desktop. Now
we've opened a 2nd office located on the other side of the contenient
(Ottawa) and access from Ottawa is really slow. It has been proposed that an
identical SQL Server in the Ottawa would make it faster for the Ottawa
accountants. And a manual coordination of emails and nightly database
restores on either server will keep information synchronized. However, this
could be very impractical and prone to human error. As well, it would be
very doubtful that requests for database restores would be kept only at
night. We have looked at options of SQL Server active-active clustering but
it is unclear how SQL Server can deal with conflict resolutions due to
network failures over a WAN. We suspect that a cheaper option might be
peer-to-peer replication. I'm not sure if it works the same way as log
shipping for redo-ing operations that are queued up before a network failure.
I don't think log shipping would do us any good since we need two active
servers. Any recommendations?
Clustering is a high-availability solution, not performance or distributed
transaction processing since the nodes are generally sitting right next to
each other.
Log Shipping would allow you read-only access to the destination
server...not write. It is a disaster recovery/poor mans reporting server
option.
Replication is your solution for distributed transaction processing...but
you may be limited by the fact that you are using 3rd party software, as in
some cases replication makes (or forces you to make) changes to the schema.
For example, you cannot replicate a table in SQL 2000 Transactional
replication that doesn't have a PK.
I'm not as familiar with PtP replication (new in 2005 I think?), but you are
most likely going to want to look into Merge (good for slow or inconsistent
connectivity situations) or transactional (good for almost always connected
scenarios), with updating subscribers.
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Alvin" <Alvin@.discussions.microsoft.com> wrote in message
news:5BB4190B-A62B-4800-850E-4DDA5C132341@.microsoft.com...
> We have Epicor Financials as our accounting system which uses SQL Server
> as
> its backend DBMS and a powerbuilder front-end. For the longest time,
> we've
> had the accounting system only in one office (Vancouver) and had our
> accountants able to periodically work from home using remote desktop. Now
> we've opened a 2nd office located on the other side of the contenient
> (Ottawa) and access from Ottawa is really slow. It has been proposed that
> an
> identical SQL Server in the Ottawa would make it faster for the Ottawa
> accountants. And a manual coordination of emails and nightly database
> restores on either server will keep information synchronized. However,
> this
> could be very impractical and prone to human error. As well, it would be
> very doubtful that requests for database restores would be kept only at
> night. We have looked at options of SQL Server active-active clustering
> but
> it is unclear how SQL Server can deal with conflict resolutions due to
> network failures over a WAN. We suspect that a cheaper option might be
> peer-to-peer replication. I'm not sure if it works the same way as log
> shipping for redo-ing operations that are queued up before a network
> failure.
> I don't think log shipping would do us any good since we need two active
> servers. Any recommendations?
|||Hi Kevin,
Interesting thought regarding your idea for merge replication but doesn't
that mean that articles are modified/published uni-directionally? I assume
that merge replication is more suited for when you are combining data from of
a hub and spoke deployment rather than everybody modifying everything
(bi-directional).
I'm not sure the procedure in either a clustered scenario or replication
scenario what is the procedure if connectivity between two offices is
disrupted.
"Kevin3NF" wrote:

> Clustering is a high-availability solution, not performance or distributed
> transaction processing since the nodes are generally sitting right next to
> each other.
> Log Shipping would allow you read-only access to the destination
> server...not write. It is a disaster recovery/poor mans reporting server
> option.
> Replication is your solution for distributed transaction processing...but
> you may be limited by the fact that you are using 3rd party software, as in
> some cases replication makes (or forces you to make) changes to the schema.
> For example, you cannot replicate a table in SQL 2000 Transactional
> replication that doesn't have a PK.
> I'm not as familiar with PtP replication (new in 2005 I think?), but you are
> most likely going to want to look into Merge (good for slow or inconsistent
> connectivity situations) or transactional (good for almost always connected
> scenarios), with updating subscribers.
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Alvin" <Alvin@.discussions.microsoft.com> wrote in message
> news:5BB4190B-A62B-4800-850E-4DDA5C132341@.microsoft.com...
>
>
|||I would use bi-directional transactional replication over peer to peer
as it is faster and you don't need the EE edition of SQL Server 2005
for this.
It is more difficult to implement and you have to tear it down and
bring it up again each time you make a schema change.
As Kevin mentions clustering is a HA solution and does not work over
the distance that you have.
If you architect your solution so that each site has a location
identifier on all tables which might encounter conflicts you will not
have a problem with being offline for any appreciable amount of time.
On Jan 8, 5:52 pm, Alvin <Al...@.discussions.microsoft.com> wrote:
> We have Epicor Financials as our accounting system which uses SQL Server as
> its backend DBMS and a powerbuilder front-end. For the longest time, we've
> had the accounting system only in one office (Vancouver) and had our
> accountants able to periodically work from home using remote desktop. Now
> we've opened a 2nd office located on the other side of the contenient
> (Ottawa) and access from Ottawa is really slow. It has been proposed that an
> identical SQL Server in the Ottawa would make it faster for the Ottawa
> accountants. And a manual coordination of emails and nightly database
> restores on either server will keep information synchronized. However, this
> could be very impractical and prone to human error. As well, it would be
> very doubtful that requests for database restores would be kept only at
> night. We have looked at options of SQL Server active-active clustering but
> it is unclear how SQL Server can deal with conflict resolutions due to
> network failures over a WAN. We suspect that a cheaper option might be
> peer-to-peer replication. I'm not sure if it works the same way as log
> shipping for redo-ing operations that are queued up before a network failure.
> I don't think log shipping would do us any good since we need two active
> servers. Any recommendations?
|||Sorry for conflict resolution inquiry, I meant the transactional replication.
"Kevin3NF" wrote:

> Clustering is a high-availability solution, not performance or distributed
> transaction processing since the nodes are generally sitting right next to
> each other.
> Log Shipping would allow you read-only access to the destination
> server...not write. It is a disaster recovery/poor mans reporting server
> option.
> Replication is your solution for distributed transaction processing...but
> you may be limited by the fact that you are using 3rd party software, as in
> some cases replication makes (or forces you to make) changes to the schema.
> For example, you cannot replicate a table in SQL 2000 Transactional
> replication that doesn't have a PK.
> I'm not as familiar with PtP replication (new in 2005 I think?), but you are
> most likely going to want to look into Merge (good for slow or inconsistent
> connectivity situations) or transactional (good for almost always connected
> scenarios), with updating subscribers.
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Alvin" <Alvin@.discussions.microsoft.com> wrote in message
> news:5BB4190B-A62B-4800-850E-4DDA5C132341@.microsoft.com...
>
>
|||The best example I can give for what merge was intended for is a Central
server with a mobile workforce in the field (like sales force) running SQL
Express/MSDE on their laptops. They are usually disconnected and synch on
the next opportunity.
Transactional is better (as Hilary mentions) for a very stable link between
two offices.
MS Clustering does not come into play in the scenario you describe, as the
nodes must be near each other an only one of them can read-write to the
database
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Alvin" <Alvin@.discussions.microsoft.com> wrote in message
news:7C9F5146-9105-4FC0-9688-9384DB1A802F@.microsoft.com...[vbcol=seagreen]
> Hi Kevin,
> Interesting thought regarding your idea for merge replication but doesn't
> that mean that articles are modified/published uni-directionally? I assume
> that merge replication is more suited for when you are combining data from
> of
> a hub and spoke deployment rather than everybody modifying everything
> (bi-directional).
> I'm not sure the procedure in either a clustered scenario or replication
> scenario what is the procedure if connectivity between two offices is
> disrupted.
>
> "Kevin3NF" wrote:
|||Hi Hilary,
Wow I just checked the Microsoft site for the implementation of
bi-directional transactional replication. It imples that you'd need to
create at least 3 stored procedures (insert/update/delete) per table. For
our case, the current accounting system has 12 databases with identical
schemas. Each database has about 1200 tables. So working out the math, if
takes me about 10 mins per stored proc, it would take me about 7 yrs?
(12 databases) * (1200 tables) * (3 sp) * (2 nodes) * (10mins) *
(1hr/60mins) * (1wk/40hrs) * (1yr/52wks)
Unless I missed something, it looks like I better start young.
"Hilary Cotter" wrote:

> I would use bi-directional transactional replication over peer to peer
> as it is faster and you don't need the EE edition of SQL Server 2005
> for this.
> It is more difficult to implement and you have to tear it down and
> bring it up again each time you make a schema change.
> As Kevin mentions clustering is a HA solution and does not work over
> the distance that you have.
> If you architect your solution so that each site has a location
> identifier on all tables which might encounter conflicts you will not
> have a problem with being offline for any appreciable amount of time.
> On Jan 8, 5:52 pm, Alvin <Al...@.discussions.microsoft.com> wrote:
>

replication related questions

I have a couple questions about replication (for both 2000 and 2005 servers):

1. which system tables/dmvs/system sprocs can I look at to determine which columns of a table are being replicated?

2. which system tables/dmvs/sprocs can I call to get metadata about publishers and subscribers?

Thanks!1. None of them, since Microsoft changes the table structures and internal details from time to time, and emphatically does NOT guarantee anything about those tables. The sp_helparticlecolumns (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl2_1d6b.asp) stored procedure will give you this information though, in a supported fashion too!

2. Again, none, for the same reason. There are more choices for stored procedures here, can you give an example of what information you're trying to retrieve?

-PatP|||I think I need to do some reading myself first.

These questions are actually straight from a customer of mine who wants me to add documentation of replication related stuff to sqlspec. I've never used replication before so I thought I'd ask here. Premature.

thanks though. I'll come back when I have done my homework!

Replication problems

I am trying to get replication to work on my system and keep getting the
following problem trying to start up the Push to my servers.
All the schemas seem to be generated on all the servers, but not the tables.
After telling it to initialize the subscriptions I get the following
message:
***********************************************************************
Subscriptions were created successfully at the following Subscribers:
PTERADON
SQL Server Enterprise Manager could not start the service
'SQLServerAgent' on server 'DINO'.
5 - (Access is denied)
The subscription at Subscriber 'PTERADON' cannot be initialized
immediately because the snapshot for this publication is not yet
available. To initialize the subscription, start the Distribution Agent
after the snapshot is available.
***************************************************************************
SQLServerAgent is running on the Server "Dino", so I don't know why I am
getting that message.
Also, in the Replication Monitor, almost all the folders have a red
circle with an X in it.
In the "Snapshot agent" folder I have an item "northwind" (which is what
I am trying to publish), with the following message:
The process could not bulk copy out of table
'[dbo].[syncobj_0x4630333239354338]'.
In the "log reader agents", I get the following:
The license for this installation of SQL Server does not permit Log
Reader Agent to run at the Publisher.
Could not get license information correctly.
I also get the License message for the distribution agents.
What License are they talking about?
Thanks,
Tom.Is your sql service running as user with administrative rights or is it
running as local system. Try to use user with admin rights.
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:3FC462B7.6080805@.deltanet.com...
> I am trying to get replication to work on my system and keep getting the
> following problem trying to start up the Push to my servers.
> All the schemas seem to be generated on all the servers, but not the
tables.
> After telling it to initialize the subscriptions I get the following
> message:
> ***********************************************************************
> Subscriptions were created successfully at the following Subscribers:
> PTERADON
> SQL Server Enterprise Manager could not start the service
> 'SQLServerAgent' on server 'DINO'.
> 5 - (Access is denied)
> The subscription at Subscriber 'PTERADON' cannot be initialized
> immediately because the snapshot for this publication is not yet
> available. To initialize the subscription, start the Distribution Agent
> after the snapshot is available.
>
***************************************************************************
> SQLServerAgent is running on the Server "Dino", so I don't know why I am
> getting that message.
> Also, in the Replication Monitor, almost all the folders have a red
> circle with an X in it.
> In the "Snapshot agent" folder I have an item "northwind" (which is what
> I am trying to publish), with the following message:
> The process could not bulk copy out of table
> '[dbo].[syncobj_0x4630333239354338]'.
> In the "log reader agents", I get the following:
> The license for this installation of SQL Server does not permit Log
> Reader Agent to run at the Publisher.
> Could not get license information correctly.
> I also get the License message for the distribution agents.
> What License are they talking about?
> Thanks,
> Tom.
>|||Dalibor Cvijetinovic wrote:
> Is your sql service running as user with administrative rights or is it
> running as local system. Try to use user with admin rights.
>
No. I have both set up as SqlAgentCmdExec and it has executive priviledges.
All the tasks have these license errors as shown below and may be why I
can't get this to work correctly.
Where do they come from and why am I getting them. Did I not load
something correctly when I set up Sql Server 7'
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:3FC462B7.6080805@.deltanet.com...
>>I am trying to get replication to work on my system and keep getting the
>>following problem trying to start up the Push to my servers.
>>All the schemas seem to be generated on all the servers, but not the
> tables.
>>After telling it to initialize the subscriptions I get the following
>>message:
>>***********************************************************************
>>Subscriptions were created successfully at the following Subscribers:
>>PTERADON
>>SQL Server Enterprise Manager could not start the service
>>'SQLServerAgent' on server 'DINO'.
>>5 - (Access is denied)
>>The subscription at Subscriber 'PTERADON' cannot be initialized
>>immediately because the snapshot for this publication is not yet
>>available. To initialize the subscription, start the Distribution Agent
>>after the snapshot is available.
>>
> ***************************************************************************
>>SQLServerAgent is running on the Server "Dino", so I don't know why I am
>>getting that message.
>>Also, in the Replication Monitor, almost all the folders have a red
>>circle with an X in it.
>>In the "Snapshot agent" folder I have an item "northwind" (which is what
>>I am trying to publish), with the following message:
>>The process could not bulk copy out of table
>>'[dbo].[syncobj_0x4630333239354338]'.
>>In the "log reader agents", I get the following:
>>The license for this installation of SQL Server does not permit Log
>>Reader Agent to run at the Publisher.
>>Could not get license information correctly.
>>I also get the License message for the distribution agents.
>>What License are they talking about?
>>Thanks,
>>Tom.
>>
>

Replication problem - system cannot find the specified path

Hi,

I'm trying to set up transactional replication from one server to another (I've done this successfully a few times). I've set up the publications and pushed a subscription to the subscriber. But as soon as the snapshot agent starts, I get the following error

system cannot find the specified path

The path for the snapshot is valid, and I'm using a domain account which has acces to this folder to run the SQL Server Agent on both boxes.

Could anyone tell me where I'm going wrong?

IanFixed it!

For some reason, the path for certain executables used in replication is stored in the regisry in DOS format, so SQL Server was looking in microso~2 instead of microso~4. I changed this in the registry and everything was fine!sql

Replication or Update via Trigger ?

Hi SQL Gurus,
I have an application where the end user insist that there should be nine(9)
databases, same server within the system.
There are 'shared tables' on one of the database where any
insert/update/delete on those 'shared tables' must appear immediately on
other databases.
In this case, should I use replication or doing updates via Trigger ?
Thanks for your comments,
KristI am not sure why he/she is insisting for identical databases? Perhaps,
instead of creating tables in all these databases, you can opt to create
views which simply SELECT from a table in a single a database. You don't
have to worry about the data being in sync either.
Replication is not a solution for addressing such requirements. You can opt
for a trigger, but with the information from your post, I rather would
conclude it is an overkill to support redundant data for no apparent
reasons.
--
- Anith
( Please reply to newsgroups only )|||Hi Anith,
I used to think of View.
But I need to create FK from transaction table to these 'shared tables'
e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
and Area are 'shared tables'
How can I do this with view ?
Thanks,
Krist
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I am not sure why he/she is insisting for identical databases? Perhaps,
> instead of creating tables in all these databases, you can opt to create
> views which simply SELECT from a table in a single a database. You don't
> have to worry about the data being in sync either.
> Replication is not a solution for addressing such requirements. You can
opt
> for a trigger, but with the information from your post, I rather would
> conclude it is an overkill to support redundant data for no apparent
> reasons.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||Krist, first find out the reason that your user needs 9 databases.
1. If is for security then perhaps create 9 different users or roles instead
and assign permissions accordingly.
2. If it's a limitation in the front end application then perhaps you'll
need to stick with several databases
3. If the user later wants to distribute the 9 databases to 9 different
servers then cross-database views become more tricky.
It's a rather strange requirement, and the underlying reason for it probably
constrains your options even further.
"tristant" <krislioe@.cbn.net.id> wrote in message
news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I used to think of View.
> But I need to create FK from transaction table to these 'shared tables'
> e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
> and Area are 'shared tables'
> How can I do this with view ?
> Thanks,
> Krist
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I am not sure why he/she is insisting for identical databases? Perhaps,
> > instead of creating tables in all these databases, you can opt to create
> > views which simply SELECT from a table in a single a database. You don't
> > have to worry about the data being in sync either.
> >
> > Replication is not a solution for addressing such requirements. You can
> opt
> > for a trigger, but with the information from your post, I rather would
> > conclude it is an overkill to support redundant data for no apparent
> > reasons.
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>|||Hi Anthony,
You are absolutely right : the reason the number (3)
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
So, that is my reason, what options do I have now ?
Thanks,
Krist
"Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> Krist, first find out the reason that your user needs 9 databases.
> 1. If is for security then perhaps create 9 different users or roles
instead
> and assign permissions accordingly.
> 2. If it's a limitation in the front end application then perhaps you'll
> need to stick with several databases
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
> It's a rather strange requirement, and the underlying reason for it
probably
> constrains your options even further.
> "tristant" <krislioe@.cbn.net.id> wrote in message
> news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > Hi Anith,
> > I used to think of View.
> > But I need to create FK from transaction table to these 'shared tables'
> > e.g : SalesOrder must have FK to Salesman and Area table, where
Salesman
> > and Area are 'shared tables'
> >
> > How can I do this with view ?
> >
> > Thanks,
> > Krist
> >
> > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > I am not sure why he/she is insisting for identical databases?
Perhaps,
> > > instead of creating tables in all these databases, you can opt to
create
> > > views which simply SELECT from a table in a single a database. You
don't
> > > have to worry about the data being in sync either.
> > >
> > > Replication is not a solution for addressing such requirements. You
can
> > opt
> > > for a trigger, but with the information from your post, I rather would
> > > conclude it is an overkill to support redundant data for no apparent
> > > reasons.
> > >
> > > --
> > > - Anith
> > > ( Please reply to newsgroups only )
> > >
> > >
> >
> >
>|||Your options:
1. Partitioned views
2. Replication
3. Log shipping (backup & restore)
"tristant" <krislioe@.cbn.net.id> wrote in message
news:OQxaWDgxDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi Anthony,
> You are absolutely right : the reason the number (3)
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> So, that is my reason, what options do I have now ?
> Thanks,
> Krist
> "Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
> news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> > Krist, first find out the reason that your user needs 9 databases.
> > 1. If is for security then perhaps create 9 different users or roles
> instead
> > and assign permissions accordingly.
> > 2. If it's a limitation in the front end application then perhaps you'll
> > need to stick with several databases
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> >
> > It's a rather strange requirement, and the underlying reason for it
> probably
> > constrains your options even further.
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> > news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > > Hi Anith,
> > > I used to think of View.
> > > But I need to create FK from transaction table to these 'shared
tables'
> > > e.g : SalesOrder must have FK to Salesman and Area table, where
> Salesman
> > > and Area are 'shared tables'
> > >
> > > How can I do this with view ?
> > >
> > > Thanks,
> > > Krist
> > >
> > > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > > I am not sure why he/she is insisting for identical databases?
> Perhaps,
> > > > instead of creating tables in all these databases, you can opt to
> create
> > > > views which simply SELECT from a table in a single a database. You
> don't
> > > > have to worry about the data being in sync either.
> > > >
> > > > Replication is not a solution for addressing such requirements. You
> can
> > > opt
> > > > for a trigger, but with the information from your post, I rather
would
> > > > conclude it is an overkill to support redundant data for no apparent
> > > > reasons.
> > > >
> > > > --
> > > > - Anith
> > > > ( Please reply to newsgroups only )
> > > >
> > > >
> > >
> > >
> >
> >
>

replication not working

I set up a transactional, pull publisher, distributor and subscriber last
week on a development system and all worked fine. Then, I did the same set
up on our production system and it is not working. When this was started on
Thursday night the distributor would not start. I was not here Friday so my
boss stopped replication and I did not see the error messages. I set up a
new replication subscriber last night and it looks like it did not
synchronize though I saw transactions going from the publisher to
distributor, but nothing going from the distributor.
I look in the log file viewer and see no error messages. My guess is that
the distributor is not running but I don't know how to see if it is.
I thought that I did everything the same on the production set up that I did
on development, that worked.
TIA - nick
Nick - can you run the distribution agent from EM/SSMS and then post up the
messages you get. Also, what version of sql server are you using and are you
using updatable subscribers - it's not entirely clear from the description.
If updatable subscribers, is data being prevented from flowing in either
direction?
Just a simple check would be to see if the tables are there on the
subscriber (assuming they weren't there originally!) - this means
initalization has worked and the distribution agent has worked at least once.
If SQL Server 2005 you can use a trace flag to test the throughput is working.
HTH,
Paul Ibison
|||Paul,
Thanks for the quick response.
I don't know how to "run distribution from SSMS and then post up the
messages". I'm new to all this.
My publisher is SS 2000; the distributor and subscriber are 2005. I am not
doing updatable subscribers.
I starting from a subscriber database that is a previous backup of the
publisher. I look to see if the record counts, dates, etc. have changed as
they should and they have not.
How do I use this trace flag?
thanks - nick
"Paul Ibison" wrote:

> Nick - can you run the distribution agent from EM/SSMS and then post up the
> messages you get. Also, what version of sql server are you using and are you
> using updatable subscribers - it's not entirely clear from the description.
> If updatable subscribers, is data being prevented from flowing in either
> direction?
> Just a simple check would be to see if the tables are there on the
> subscriber (assuming they weren't there originally!) - this means
> initalization has worked and the distribution agent has worked at least once.
> If SQL Server 2005 you can use a trace flag to test the throughput is working.
> HTH,
> Paul Ibison
|||One thing to add that does look suspicious ... If I right-click on the
subscriber and select View Sync Status, the status window comes up and the
status message reads "The job succeeded ..." nad Start button is enabled. If
I click on View Job History, when the Log Viewer comes up, under Progress it
says Login failed for user 'sa'.
- nick
"Paul Ibison" wrote:

> Nick - can you run the distribution agent from EM/SSMS and then post up the
> messages you get. Also, what version of sql server are you using and are you
> using updatable subscribers - it's not entirely clear from the description.
> If updatable subscribers, is data being prevented from flowing in either
> direction?
> Just a simple check would be to see if the tables are there on the
> subscriber (assuming they weren't there originally!) - this means
> initalization has worked and the distribution agent has worked at least once.
> If SQL Server 2005 you can use a trace flag to test the throughput is working.
> HTH,
> Paul Ibison

Replication Issue

When Trying to Replicate I got an issue with the Snapshot Agent.

Please be kind enough to help me I am developing a system using SQL server Mobile for a Construction Site. This is for my final Project in Bachelors Degree.

Please reffer the Error that I got. bellow.

Creating Publication

- Creating Publication 'SQLMobile' (Success)

· SQL Server created publication 'SQLMobile'.

- Adding article 3 of 3 (Success)

· Article 'FlightData' was added.

· Article 'MembershipData' was added.

· Article 'sysdiagrams' was added.

- Starting the Snapshot Agent (Warning)

Messages

SQL Server could not start the Snapshot Agent.(New Publication Wizard)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantStartSnapshotAgent&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQLServerAgent is not currently running so it cannot be notified of this action.
Changed database context to 'SQLMobile'. (Microsoft SQL Server, Error: 22022)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476

Thanks

Mark.

The error is:

- Starting the Snapshot Agent (Warning)

Messages

SQL Server could not start the Snapshot Agent. (New Publication Wizard)

You need to see why the snapshot agent could not be started. You can start by drilling down into the history of the snapshot agent in Replication Monitor, you can also query the distribution database for table MSsnapshot_history.

Replication in XP home

Please help!!

I have recently installed ms sql on my system and I am trying to replicate my machine which happens to be a compaq laptop which is running xp home edition. The publisher machine can see my server however gives me an invalid subscription error. I have been told that it might be due to the fact the the laptop is on xp home, would this be a problem???

please get back to me, anyone.,.much appreciated!!!!

JaysunJaysun,
What version of SQL Server did you install? Check http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp for version/OS compability.

If your version of SQL Server is compatible with XP home I would also suggest looking at the account that SQL Server is using to login. If it is using the local system account then I will not be able to talk to other servers on the domain.

Regards,|||hi thanks for coming back to me, ive checked the version and its ok. plus i can connect to the publisher and also to other servers. esp to another laptop that is running xp prof, and that does not seem to have a problem replicating. however i cannot connect to other servers running windows 2k. ive checked the sql account and username and password is the same as the other laptop./

the error i get when i try and connect to another server:

login failed for user 'sa' not associated with a trusted sql server

would it be because i this one is not logged into a domain??