none
Replication does not work

    Question

  • I am not a SQL guy and with only with limit knowledge of SQL. I have two servers with MS SQL 2008 R2 installed. I need to replicate:

          table X                TO         table X           

          database I                        database J

          server A                           server B

    I created distribution database and a publication in server A. Then created a subscription in server B. Use the same AD account with "sysadmin" role in both servers for both publication and subscription. There is no error in all processes. In the "View Synchronization Status" window in server B, there is a message "Job ........ started successfully". However, it is 0% in the "Synchronization in Progress" bar after about a hour? There is no table X created in database J in server B. I know there is an option to create a new database instead of select a existing database during the subscription set up. Do I need to manually create table X in the existing database J or select "Create new database" to create a new database I in server B?

    I really appreciate any help. Many thanks.

    Friday, November 15, 2013 3:35 PM

Answers

  • If this is a pull subscription then you are correct, this snapshot folder needs to be a UNC network share.

    However, the error: The process could not read file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData\unc\QASP-SQL01_TEST1_PUB_COMPANY\20131115094503\Company_2.pre' due to OS error 21.

    If you notice, it is trying to read a local path, not a UNC network share.

    You need to update your Distributor Properties.  This can be done from the Distributor Properties -> Publishers page -> Publisher Properties -> Default Snapshot Folder.


    You can also change the default snapshot folder location using sp_changedispublisher and setting the @working_directory.

    After changing the default snapshot folder to a UNC network share, generate a new snapshot, then synchronize your subscription.


    Brandon Williams (blog | linkedin)

    • Marked as answer by eg10013 Friday, November 15, 2013 8:38 PM
    Friday, November 15, 2013 5:19 PM
    Moderator

All replies

  • Did you run the Snapshot Agent to generate the inital snapshot?

    Friday, November 15, 2013 3:41 PM
  • Thank you for your quick response.

    Yes, it did. I just checked the publication "replication report" and see the following error:

    The process could not read file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData\unc\QASP-SQL01_TEST1_PUB_COMPANY\20131115094503\Company_2.pre' due to OS error 21. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)
    Get help: http://help/MSSQL_REPL20024
    The device is not ready.
     (Source: MSSQL_REPL, Error number: MSSQL_REPL21)
    Get help: http://help/MSSQL_REPL21

    It sounds the AD account does not have access to the repldata folder? However, the same account can created the snapshot in the publication process and cannot read data from the same folder in subscription process? 

    Friday, November 15, 2013 4:14 PM
  • I did some research on the error. I can confirm

    (1) The AD account has full access to the folder and the file.

    (2) The repldata is a network share \\server\repldata.

    (3) Here is the content of Company_2.pre

    SET QUOTED_IDENTIFIER ON

    go

    if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec sys.sp_MSdropfkreferencingarticle @destination_object_name = N'Company', @destination_owner_name = N'dbo'

    go

    drop Table [dbo].[Company]

    go

    Please let me know if you see any problem.

    Friday, November 15, 2013 4:57 PM
  • If this is a pull subscription then you are correct, this snapshot folder needs to be a UNC network share.

    However, the error: The process could not read file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData\unc\QASP-SQL01_TEST1_PUB_COMPANY\20131115094503\Company_2.pre' due to OS error 21.

    If you notice, it is trying to read a local path, not a UNC network share.

    You need to update your Distributor Properties.  This can be done from the Distributor Properties -> Publishers page -> Publisher Properties -> Default Snapshot Folder.


    You can also change the default snapshot folder location using sp_changedispublisher and setting the @working_directory.

    After changing the default snapshot folder to a UNC network share, generate a new snapshot, then synchronize your subscription.


    Brandon Williams (blog | linkedin)

    • Marked as answer by eg10013 Friday, November 15, 2013 8:38 PM
    Friday, November 15, 2013 5:19 PM
    Moderator
  • Thanks Brandon. With your instruction, I feel I am closing.

    Yes, this is a "pull" operation. I modified the folder path with the unc in contributor. There is no error now. However,  it is still 0% in the "Synchronization in Progress" bar and no table creaed in database J. Here are the actions in the replication report:

    initializing

    applied script 'Company_2.pre'

    applied script 'Company_2.sch'

    Bulk copying data into table 'Company'

    bulk copied data into table 'Company' (3 rows)

    delievered snapshot from the 'unc\qasp-sql01_test1_pub_test1_company\20131115133020

    no replicated transactions are available

    Please let me know what can be the problem. Thanks again.

    Friday, November 15, 2013 6:52 PM
  • Interesting.  The report indicates the table was created and the data was copied to the subscriber.

    Can you check if the table was created again?

    Note that if you're checking in Object Explorer that may have to right-click on the Tables folder -> Refresh.


    Brandon Williams (blog | linkedin)

    Friday, November 15, 2013 7:04 PM
    Moderator
  • No there is no table created.

    I do not know how to upload a screen capture to this forum (which seems require url address), otherwise, I can show you the screen shot.

    Friday, November 15, 2013 7:44 PM
  • Check Replication Monitor and drill down to your subscriber and double-click to check the Distributor to Subscriber History.  Verify you are checking the correct server for the database and that there are no other errors that occurred on sync.

    Brandon Williams (blog | linkedin)

    Friday, November 15, 2013 8:28 PM
    Moderator
  • It works!

    Refresh did not show the new table. I just close and reopen the management studio and see the new table.

    Thank you very much for your help.

    Friday, November 15, 2013 8:38 PM