Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Replication across non-shared servers

Author  Topic 

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-03-14 : 13:13:53
Distribution = SQL Server 2005
Subscriber = SQL Server 2008

The initial transactional replication was set up 2 years ago to replicate 6 tables between two databases on the same instance. It has worked fine. We are moving the subscriber database to its own server which will not be on our domain, but still in our facility. I changed the distribution to on the Snapshot Tab to both ‘Put files in the default folder’ and to ‘Put files in the following folder’

For the second choice, ‘Put files in the following folder’, I typed in the path as \\192.168.1.35\C$\Program Files\Microsoft SQL Server\Mssql10_50.mssqlserver\Mssql\repldata

I saved and reinitialized and I can see that the folders are created and the distribution files are in the sub folders of the path specified on the subscriber server. However, the subscriber gets an error on the distribution side that says the following

• The process could not read file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\PECSYS-DB4_AICU_AICUPATIENTS\20140314093206\UMRequest_8.pre' due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)
Get help: http://help/MSSQL_REPL20024

I’m not sure if that C: drive is referring to the local C: drive on the distribution server or if the subscriber is looking for the distribution in its C: drive.

On the subscriber side there are no errors, but the replication is not being updated.

Any help is appreciated.

Greg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-14 : 15:11:34
The path should be for the distribution server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-03-14 : 16:11:20
Thank you for you help.

So then 'Put files in the following folder' is for the distribution server and the subscriber will look for them there? Is that correct. So there is really no reason to have both of them checked in this instance. Is that correct? I should just use default folder and the subscriber will look for them there.

This seems to be a permissions issue then. The files are in the Default Folder, which is the folder in the Default Location on the Distribution server. If so, what Windows accounts need rights to this folder.

Greg
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-14 : 16:13:36
From my recollection, the distributor is the one that processes the files. The subscriber doesn't need any permissions to that folder. We upgraded to using readable secondaries with AlwaysOn Availability Groups a few months ago, so I am going from memory. I am pretty sure the distributor runs bulk insert and passes the subscriber info in that command.


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-03-14 : 16:33:36
I got it. On the distribution server I created a share to the local replication folder and gave full admin rights to it. I put that share in the 'Put files in the following folder' box and unchecked the 'Put files in the default folder' selection. It is the same folder, but now it is reference only by the network share (\\server\folder) and not the local file system path.

Thanks,

Greg
Go to Top of Page
   

- Advertisement -