Solved

Replication trouble across domains (non-trusted)

Posted on 2004-04-30
17
580 Views
Last Modified: 2011-10-03
I have struggled for weeks now with replication.

This is my setup:
-------------------------------------------------------------------
Machine 1 (publisher+distributor):
Dell Server, brand new (single purpose DB server)
Windows 2000 Server (Stand alone)
SQL Server 2000 Std SP3a (running under special SQL Server account, not LocalSystem)

Machine 2 and 3 (subscribers):
Windows NT4 WS SP5 (not member of domains)
IE 5.5 SP2 (required by SQL Server)
MSDE 2000 SP3a (running under special SQL Server account, not LocalSystem)
-------------------------------------------------------------------

I have attempted setting up merge and transactional replication without success. Every attempt seems to boil down to the snapshot not being applied. The clients inform that 'the initial snapshot could not be applied because it is not available yet.' If I run Snapshot Agent, I cannot browse the snapshot because it says it is 'not created or has been cleaned up.'

If I type SELECT working_directory FROM msdb.dbo.MSdistpublishers I get E:\SQLRepl\ and attempts to browse that directory using NET USE X: \\(Machine1)\SQLRepl - It says 'password incorrect, enter username:'. If I enter an existing username and password on Machine1 it logs on allright.

These are my requirements to replication (all three types will need to be set up):
-------------------------------------------------------------------
Type1:
2 tables (logging)
changes happen at the subscribers
one table is being written to twice per second (production parameter logging)
no special latency requirement (can sync every 5 mins to reduce network load)
changes normally happen on Machine2. Machine3 is an online backup of Machine2.

Type2:
1 table (positioning system)
changes happen at the subscribers and publisher
long periods without activity, but must be replicated immediately when changes occur!
minimal latency (changes should be replicated <1 second)

Type3:
1 table (system variables)
changes can happen at subscriber and publisher
changes rarely happen (maybe twice per year)
must be applied at Machine2 and Machine3 exactly at the same time - db consistent across replicas. Not necessary at Machine1 (publisher).

General:
The machines are placed in a network without a DNS-server or a DC. Machine2 and 3 are exact copies of each other. The table in type3 holds the name of the machine that is the "active" machine - the one writing to the database. Machine1 is placed about 1 km away from the two others, but acceptable response times through wired ethernet. If network connection to Machine1 is lost, everything should carry on as normal, queueing transactions, and resuming (catching up) when network connection is back up.
-------------------------------------------------------------------

I hope you have a good view of my mission critical system, with good suggestions on how to solve my snapshot distribution problems and how I should set up my replication properly.

PS! I am not currently by the machines, so error messages and directories are taken from memory and thus may be slightly misspelled. My appologies.
0
Comment
Question by:risoy
  • 9
  • 8
17 Comments
 
LVL 13

Expert Comment

by:danblake
Comment Utility
Hows this one going ?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20931504.html


The clients inform that 'the initial snapshot could not be applied because it is not available yet.' If I run Snapshot Agent, I cannot browse the snapshot because it says it is 'not created or has been cleaned up.'
--> Have you created the initial snapshot ?
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
Or specified no snapshot needs to be created ?
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpatterns/html/ImpSnapshotReplication.asp

http://support.microsoft.com/default.aspx?scid=kb;en-us;195757

Has the snapshot agent completed, have you tried running it manually ?
(there is a job for this, with a line containing  snapshot -<multiple params> depending on how you setup your system...refer to BOL on starting the snapshot agent.)
0
 

Author Comment

by:risoy
Comment Utility
The other thread is regarding the first test setup I did, which was on a trusted domain - the snapshot creation worked. So it is a different problem. What is not working with the other thread is the fact that if the system goes down for longer than the 10 times trial cycle, it won't restart.

The problem described above is on a non-trusted connection - across domains, or actually no domains. Running the snapshot agent manually returns success, but I am not able to 'browse the snapshot' using the appropriate button. Browsing the directory locall works though. But it does not work from the subscribers OS. Plus I am afraid the 'not yet created' message will appear when I get that bit working...

The publication wizard has ben set to create the snapshot.
0
 
LVL 13

Accepted Solution

by:
danblake earned 500 total points
Comment Utility
But it does not work from the subscribers OS -- Due to no credentials being supplied/authorisation.
You have to allow the snapshot folder to be seen from the subscribers --
(Have you seen the article KB Number: 321822
Replicating across non trusted domains or no domains:
http://support.microsoft.com/default.aspx?scid=kb;en-us;321822)


On the other thread -- I mentioned changing the job type to always run -- so you will not suffer from the problem of 10 times trial /resets as it will always be running as you are manually overloading the process...

You need to setup a FTP Site or Establish a VPN/security (such as Trusted remote domain) mechanism between the two sites to allow the remote site to see the snapshot.

Its down to you how you put this in place.
I believe I have given you answers to both of these particular problems that you have encountered.
0
 

Author Comment

by:risoy
Comment Utility
So FTP is the only solution when in a non-trusted environment. I hoped I could just share a folder. I will try this next time I am by the machines...

What is the disadvantage of "manually" creating the schema and data on the subscriber and then setting up replication? What happens when it stops working for a while?
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
I hoped I could just share a folder -- which you can do via a VPN or other security mechansim relationship to the snapshot folder.  Would you honestly want to share a folder publically containing company data ?



What is the disadvantage of "manually" creating the schema and data on the subscriber and then setting up replication? What happens when it stops working for a while?
--> New question.  Ask in a new question please, please refer to EE guidelines.
0
 

Author Comment

by:risoy
Comment Utility
FTP is set up. But the REPL-Snapshot agent failes with the following error:

"The job failed. The job was invoked by User sa. The last setp to run was step 3 (Detect nonlogged agent shutdowm.)."
Details:
Step ID 2: "The process could not create the file 'E:\SQLRepl\ftp\SERVERNAME_DBNAME_DBNAME_REPLNAME\2004050614456'. The step failed.

I have run it a few times manually. Tried changing the user it is run as... Nothing helped.

This is a merge replication.

The FTP connection test passed.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

by:danblake
Comment Utility
Does the SQL-Server Agent logon have permissions to this folder ?
Is it running as a domain account ?
0
 

Author Comment

by:risoy
Comment Utility
More details:

"Cannot create a file when that file already exists.
(Source: (OS); Error number: 183)"

- From replication monitor: Snapshot agent
0
 

Author Comment

by:risoy
Comment Utility
> Does the SQL-Server Agent logon have permissions to this folder ?

Yes. The E:\SQLRepl\unc\... is created successfully.

>  it running as a domain account ?

Yes.
0
 

Author Comment

by:risoy
Comment Utility
Now it creates a new empty folder under E:\SQLRepl\ftp\ each time it is run - still giving above error message. The folder under ...\unc\ holds only one direct sub-folder and the snapshot seems to be complete.
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
Can you turn on verbose logging, and delete that snapshot file that already exists...
by adding : -OutputVerboseLevel 2

Sometimes you will get this error if one snapshot is still running in the background and has not completed running an initial snapshot.

The snapshot job, will contain a line with:
snapshot -<settings> -<settings> -<settings>

Also if you were creating a compressed snapshot -- turn this option off for the mo (seen some problems with big snapshots and compression)

BOL on snapshot agent command line settings:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_snapshot_3189.asp
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
Here are the full details on how to add logging to the agents:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;312292
0
 

Author Comment

by:risoy
Comment Utility
A little breakthrough. The -OutputVerboseLevel 2 switch strangely didn't return any more error messages. But by unchecking the 'Generate snapshots in the normal snapshot folder' it created the ftp one.

Running the snapshot agent now works. New problem: Creating the subscription:

"The schema script '' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------
The process could not connect to FTP site 'SERVERNAME' using port PORTNO.
(Source: SUBSCRNAME (Agent); Error number: 0)
---------------------------------------------------------------------------------
550 E:\SQLRepl: The filename, directory name, or volume label syntax is incorrect.
(Source: (OS); Error number 12003)
---------------------------------------------------------------------------------"

If a run from the command line 'ftp servername portno' I have no problem browsing the snapshot folders. I get straigh to the folder where the folders ftp and unc are.
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
Have you added verbose Level output to 2 , and added the output file for the errors to be logged to ?
Examine this for further info.

Check the following:
http://support.microsoft.com/default.aspx?scid=kb;en-us;318592&Product=sql2k -- That all your subscribers have SP2 or above with SQL Server 2000.
http://support.microsoft.com/default.aspx?scid=kb;en-us;320600&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;832902&Product=sql2k


0
 

Author Comment

by:risoy
Comment Utility
It seems to be working just now.

Firstly I wrongly added the local (server)  E:\SQLRepl on the ftp path by mistake. What it needed to be was 'ftp'. It did not work without it.

Thank you so much for your help! I will keep the thread open for a couple of days so I know that I won't run into any more related problems.

Thanks again!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now