How do you stop Replication?

• jsterrett

  SSC-Addicted

  Points: 459

  More actions

  July 1, 2015 at 8:09 am

  #1809498

  The snapshot is just going to BCP data into files on the path specified to the distributer. Can you verify if the snapshot has been deployed to the subscribers? You should see this in the replication monitor under the subscribers.

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 1, 2015 at 8:14 am

  #1809501

  Justin Manning SA (7/1/2015)

  ---

  Welsh Corgi (7/1/2015)

  ---

  I reinitialized and created a new snapshot.

  I got messages that the Data was being bulked copied.

  There is no data in any of the tables. :sick:

  Are you sure its not going to a different schema?

  Publisher being "dbo" subscriber might have a different schema, and now its created the tables as "dbo" on the subscriber?

  They are the same schema.

  While the publication was being generated I got a message that tblClientOffice was missing. I do not know why.

  So I added it and ran the agent to publish it.

  After the Generate Publication completed I go and view that Messages.

  I get a Bulk copying snapshot for article tblClientOffice

  The following message is Bulk copied snapshot data for article 'tblClientOffice (717) Rows.

  I check for the table and it is not in the Database.

  I get the message Bulk Copied for every article.

  What am I missing?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 1, 2015 at 8:23 am

  #1809510

  jsterrett (7/1/2015)

  ---

  The snapshot is just going to BCP data into files on the path specified to the distributer. Can you verify if the snapshot has been deployed to the subscribers? You should see this in the replication monitor under the subscribers.

  It is not creating any files.

  Could you please elaborate on the verification please?

  Thanks!:-)

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 1, 2015 at 8:34 am

  #1809518

  I get an error that the Replication Monitor could not reinitialize one or more subscriptions that support automatic initialization.

  The current operation was aborted because it would deactivate an article in a publication for which a snapshot was generated.

  Could not update the distribution database subscription table.

  The subscription status could not be changed.

  Change database to ExactData. (Microsoft SQL Server Error 2861)

  Does anyone recognize this problem?

  Thanks.:-)

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• andreas.kreuzberg

  SSCertifiable

  Points: 6191

  More actions

  July 1, 2015 at 8:40 am

  #1809519

  Hi,

  are you sure, that you, ore the used service account, had all rights. We have a special user called SQLREPL, who had db_owner rights on the publisher, and subscriber database. Further the user need rights to the unc path, where the snapshots are stored.

  And, I think, the use needed access to the distribution database.

  Regards,

  Andreas

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 1, 2015 at 8:50 am

  #1809527

  andreas.kreuzberg (7/1/2015)

  ---

  Hi,

  are you sure, that you, ore the used service account, had all rights. We have a special user called SQLREPL, who had db_owner rights on the publisher, and subscriber database. Further the user need rights to the unc path, where the snapshots are stored.

  And, I think, the use needed access to the distribution database.

  Regards,

  Andreas

  Permissions were setup correctly.

  It was working up to Friday.

  I turned on the Agents that I had stopped Monday because of performance issues..

  Yesterday a Developer said that it had data in the tables and it was replicating.

  2 hours later he tells me that there is no data.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 1, 2015 at 9:29 am

  #1809559

  I clicked on Publisher to Distributor and it list 2 seconds.

  Distributer to Subscriber is Pending.

  Total latency is Pending as well.

  What do I need to turn on?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 1, 2015 at 10:18 am

  #1809590

  I believe this will turn the Agent back on?

  [/

  if exists (select 1 from distribution..MSsubscriptions where status = 0)

  begin

  UPDATE distribution..MSsubscriptions

  SET STATUS = 2

  WHERE publisher_id = '--publisher_id -- will be integer --'

  AND publisher_db = '--publisher db name ---'

  AND publication_id = '--publication_id -- will be integer --'

  AND subscriber_id = '--subscriber_id -- will be integer ---'

  AND subscriber_db = '-- subscriber_db ---'

  end

  else

  begin

  print 'The subscription is not INACTIVE ... you are good for now .... !!'

  end

  code]

  Edit: No that's not it. The Status is set to 2.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 1, 2015 at 10:41 am

  #1809602

  Replication is working. 😎

  Thank you to everyone for your positive feedback.:-)

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Justin Manning SA

  Hall of Fame

  Points: 3190

  More actions

  July 2, 2015 at 12:53 am

  #1809723

  Great 🙂

  what did you eventually do to fix it?

  Thanks,

  Justin

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 2, 2015 at 6:07 am

  #1809816

  Justin Manning SA (7/2/2015)

  ---

  Great 🙂

  what did you eventually do to fix it?

  Thanks,

  Justin

  I keep trying to create a snapshot but I got error that various objects did not exist.

  So I kept adding articles that came up as missing each time that I tried to generate a snapshot.

  I was able to sync which when I previously attempted I got a response that there was nothing to sync.

  I kept adding them and finally the snapshot got picked up from the Distributer to the Subscriber.

  It was weird.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/