I need to create an SQL log shipping between 2 databases,
The source is on an AWS EC2 instance running Windows Server 2008 R2 and SQL Server 2008
The destination is on an Azure VM running Windows datacenter 2012 and SQL Server 2012
This process requires the following:
A. source server write a file
B. destination server copy the file to a local folder on it
C. the destination server uses the file
all these tasks are done by the sql server process
Now i have problem of creating some shared folder between the instances, i tried to do the following :
step A is done to a local folder on the machine,
On step b the sql server tries to access that folder using this format (and that copy the files from it to a local folder):
after granting access to everyone to that folder in the source server and enabling sharing and enabling the guest user
Yet i keep having access problems and the copy process fails.
What should one do ?
I ended up to no success trying any of the solutions that were suggested, some how the sql server could not gain access to folders that the user did had access to,
So instead of digging in to that i have chosen to use 3rd party software to sync information between the 2 servers such as dropbox/svn ect.. and by using the same location for the shared folder on both servers i could in a very simple way use the network location:
and both computers would act as if it is on the network but the folder was synced and shared on the local instance so there was no permission problem on it.
Not the ideal solution but it works.