Question about replication

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Question about replication

Question about replication

Rate Topic

Display Mode

Topic Options

Author

Message

SQL Guy 1

Posted Friday, November 09, 2012 2:05 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 258, Visits: 796

We have transactional replication setup some time ago. Recently we created a new table, it has a PK, and I added it to articles.

However, it did not show up at the subscribing database. So I created it with the script taken from the publisher. But it's not getting populated with data.

What other steps should be taken to insure that a new table is really replicated ?

Thanks

Post #1383232

MyDoggieJessie

Posted Friday, November 09, 2012 6:54 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 2,037, Visits: 3,761

Just out of curiosity, how much data is being replicated? Please describe your architecture...for instance, how many articles are in the publication? When you created your publication, what options did you choose?

If you simply added articles and pretty much accepted the "defaults" then certain things happen. The main thing is after adding the "new" article you should have received a message stating that you were invalidating the existing snapshot (or similar wording) and would you like to generate a new snapshot. Again, depending on your set up this may have been a quick thing...or in a rather large environment, you probably would choose "NO" to this. The reason for this is because, by default, a new snapshot will be generated for EVERY article in the publication, and not just the new article you added. This occurs because by default immediate_sync is set to "true". In a large enterprise, or in a heavy OLTP env. this could have potentially disastrous consequences.

To check how it's setup, run this TSQL against the distribution database

SELECT publisher_id, publication_id,
     publisher_db,
     publication, allow_anonymous, immediate_sync
FROM distribution.dbo.MSpublications WHERE publication_type = 0

This ought to show you a listing of all your publications.

While IMHO, a rather poorly documented process in BOL, all you need to do to allow the adding/dropping of individual articles in a publication is to set the immediate_sync option and allow_anonymous to zero.

--On the PUBLISHER, using the PROPER database
EXEC sp_changepublication @publication = 'YOUR_PUBLICATION', @property = 'allow_anonymous', @value = 'false' 
GO
EXEC sp_changepublication @publication = 'YOUR_PUBLICATION', @property = 'immediate_sync', @value = 'false'
GO

Now to get the data back over to your subscriber and create the link needed for replicated to occur, you need to start the Snapshot Agent. You can do this either in the SQL Agent or by opening up the replication monitor in SSMS, finding the Agent Tab, and right-click the Agent choosing "start agent"

That should be all you really need to do.

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" Wink

Post #1383315

MysteryJimbo

Posted Saturday, November 10, 2012 4:43 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:28 AM
Points: 1,125, Visits: 14,511

SQL Guy 1 (11/9/2012)

Recently we created a new table, it has a PK, and I added it to articles.

How?

If you did this by TSQL using sp_addarticle then you still need to rerun sp_addsubscription to add the additional article to the subscriptions and rerun the snapshot agent to generate a partial snapshot (new table only)

Post #1383357

SQL Guy 1

Posted Monday, November 12, 2012 6:25 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 258, Visits: 796

Thank all for replies. Sorry for delay, I was busy all day today.

MyDoggieJessie:

The database size is 36 GB including log.
Totally 16 tables, only 10 of them have PKs, and all 10 are replicated.
I created replication in GUI several months ago, so I don't remember all the details. But probably I chose all suggested defaults.
I did not receive an error message when adding new article.
As a result of the query that you posted, both values were 1 and 1.
After updating with two S.P.s, they became 0 and 0.

About Replication monitor:

Sometimes it shows red circle with X inside with status "Error", sometimes it is blue "OK", while subscriber is green "Running".
Snapshot agent is Completed.
Log Reader agent is Running.
Queue reading agent is grayed-out, and I can't start it.
All 6 maintenence jobs are grayed-out as well.

I believe that my replication is seriously ill.

MysteryJimbo:

I created replication only in GUI, I did not run any scripts.
You advice me to "generate a partial snapshot". How ? The problem is that I created snapshot, but it took hours to completely synchronize. Partial snapshould would be definitely much faster.

Post #1383919

« Prev Topic | Next Topic »

Permissions