New table added to p-t-p transactional publication does not replicate - why?

Hello,

We are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."

This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @.immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.

Can someone point me in the right direction?

Thanks,

BCB

Hi,

P2P replication should not be initialized using snapshot agent. The subscription must be initialized either manually or using backup/restore.

To add a new article into an existing P2P topology, following the instruction in this link (http://msdn2.microsoft.com/en-us/library/ms146867.aspx). Basically you need to ensure all nodes have the same schema/data for the table you would llike to add. And then call sp_addarticle at each node. P2P topology should be quiesced before it.

Thanks,

Peng

New Publication Error

Hi All,
A couple of months ago, I setup a transactional replication between 2
servers. In the last week, the publisher stopped replicating to the
subscriber for some unknown reason. When I was troubleshooting the problem,
I tried to add a new publication and I received the error attached in the
screenshot.
I ran the DBCC CHECKDB on that DB and the results were ok.
Any help would be greatly appreciated.
Regards,
JN
Can't see the screenshot using the web interface to the newsgroup. Can you
post up the error message for us...
Cheers,
Paul Ibison
|||Sure thing...Error as follows:
"New Publication Wizard encountered one or more errors while retrieving the
list of databases on the 'servername'. The list of databases may not be
complete.
Additional Information:
An exception occured while executing a T-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 21 (The device is not ready) to SQL
server during a read at offset 0x000000614d2000 in
file F:\TST\TSTDAT.mdf (not sure what this DB is?). Addtional messages
in the SQL Server error log and system event log my provide more detail.
This is a severe system level error condition that threatens database
integrity and must be corrected
immediatly. Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for
more information, SQL Server Books Online (MS SQL Error: 823)"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:D538F184-0AC3-4CD2-AC4D-0029B4E0ED7E@.microsoft.com...
> Can't see the screenshot using the web interface to the newsgroup. Can you
> post up the error message for us...
> Cheers,
> Paul Ibison
>
|||I'd look to see what database the file F:\TST\TSTDAT.mdf corresponds to, then
run DBCC CHECKDB on this database. The info should be in sysaltfiles.
HTH,
Paul Ibison

new publication deleted before snapshot completed

I just created a new publication and subscription... And was in the middle
of the synchronization when I dropped the publication...
I am now receiving a 'Could not find stored procedure 'sp_MSins_tablename''
for all of the agents replicating to this server/db.
The error code is 2812.
Anyone encounter this and have an easy solution?
Thanks in advance!
~lb
Hi,
You can remove all the entries manually or use sp_removedbreplication
'databaseName' to start all over again.
Hope this helps.
cheers
"Lonnye Blake Bower" wrote:

> I just created a new publication and subscription... And was in the middle
> of the synchronization when I dropped the publication...
> I am now receiving a 'Could not find stored procedure 'sp_MSins_tablename''
> for all of the agents replicating to this server/db.
> The error code is 2812.
> Anyone encounter this and have an easy solution?
> Thanks in advance!
> ~lb
>
>

New Publication - Snapshot Error

Help!
Some genius cancelled a replication job that was very close to completing the Initial Snapshot of a database being replicated (Transactional) from one server to another.
Is the only thing necessary to do a 'Reinitialize' of this publication?
Is there any way around having the tables locked when the Snapshot is being generated?
(This is why the doofus killed the job)
Any help is greatly appreciated!
You might be able to get away with a concurrent snapshot if you are using transactional replication. On your publication properties, clcik the snapshot tab, and select concurrent access during snapshot generation.
Another Snapshot will be generated but there will be far less locking.
If you can kick all your users off the database you can DTS the data over. This is faster than a snapshot.
To get this to work you should
1) create a new database.
2) script out the tables you are replicating, and any other objects you are replicating, and run this script in the new database.
3) publish this database and objects to a second new database on the same server.
4) script out the objects and the stored procedures and then run this in the subscriber.
This way your objects will be the way replication wants them and you will have the necessary replication stored procedures. Then do your DTS and then do a no-sync subscription.
DTS can be twice as fast as a snapshot generation and distribution.
It is critical that you be able to prevent any updates from happening to your publication database while you do this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" wrote:

> Help!
> Some genius cancelled a replication job that was very close to completing the Initial Snapshot of a database being replicated (Transactional) from one server to another.
> Is the only thing necessary to do a 'Reinitialize' of this publication?
> Is there any way around having the tables locked when the Snapshot is being generated?
> (This is why the doofus killed the job)
> Any help is greatly appreciated!
|||Thanx!
Any way to buy a copy of your book before August 15?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ED801206-889D-42F6-A590-663BC63F3BBA@.microsoft.com...
> You might be able to get away with a concurrent snapshot if you are using
transactional replication. On your publication properties, clcik the
snapshot tab, and select concurrent access during snapshot generation.
> Another Snapshot will be generated but there will be far less locking.
> If you can kick all your users off the database you can DTS the data over.
This is faster than a snapshot.
> To get this to work you should
> 1) create a new database.
> 2) script out the tables you are replicating, and any other objects you
are replicating, and run this script in the new database.
> 3) publish this database and objects to a second new database on the same
server.
> 4) script out the objects and the stored procedures and then run this in
the subscriber.
> This way your objects will be the way replication wants them and you will
have the necessary replication stored procedures. Then do your DTS and then
do a no-sync subscription.
> DTS can be twice as fast as a snapshot generation and distribution.
> It is critical that you be able to prevent any updates from happening to
your publication database while you do this.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "JLS" wrote:
completing the Initial Snapshot of a database being replicated
(Transactional) from one server to another.[vbcol=seagreen]
being generated?[vbcol=seagreen]
|||no. There may be some proofs out in a month.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%23qw9qmIWEHA.1380@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanx!
> Any way to buy a copy of your book before August 15?
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:ED801206-889D-42F6-A590-663BC63F3BBA@.microsoft.com...
using[vbcol=seagreen]
> transactional replication. On your publication properties, clcik the
> snapshot tab, and select concurrent access during snapshot generation.
over.[vbcol=seagreen]
> This is faster than a snapshot.
> are replicating, and run this script in the new database.
same[vbcol=seagreen]
> server.
> the subscriber.
will
> have the necessary replication stored procedures. Then do your DTS and
then[vbcol=seagreen]
> do a no-sync subscription.
> your publication database while you do this.
> completing the Initial Snapshot of a database being replicated
> (Transactional) from one server to another.
publication?
> being generated?
>

New Publication

Hi,
I'm trying to set up a new publication on a Veritas Cluster/SQL Server 2005.
The problem i'm having is that sql server reports that in order to do that
"SQL Server replication rquires the actual server name to make a connection
to the server. Connections through a server alias, IP address or any other
alternate name are not supported"
When you run sql on a cluster you virtual names set up. How do i get aroud
this?
Regards
Michael
do this
xp_cmdshell 'hostname'
then do this
select @.@.servername
these should be the same. If not you must do the following
sp_dropserver @.server='OldServerName'
sp_addserver @.server='CorrectServerName', @.local='LOCAL'
As you are on a cluster I am not exactly sure how this is going to work.
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
"Michael_Sergel" <MichaelSergel@.discussions.microsoft.com> wrote in message
news:26053401-B617-4E56-A359-80CB8CDD67D8@.microsoft.com...
> Hi,
> I'm trying to set up a new publication on a Veritas Cluster/SQL Server
> 2005.
> The problem i'm having is that sql server reports that in order to do that
> "SQL Server replication rquires the actual server name to make a
> connection
> to the server. Connections through a server alias, IP address or any other
> alternate name are not supported"
> When you run sql on a cluster you virtual names set up. How do i get aroud
> this?
> Regards
> Michael