Use merge replication for this.
Post by Edwin Smith
Thanks for the hint. I set up Merge replication but it fails and the links
in the error message go nowhere. It seems to be failing on the snapshot yet
it did get at least one snapshot. During the night.
I created a user called repl_user.
I made the repl_user a db_owner on the database at the publication and at
the subscriber. This is a domain system so the repl_user is common on both
I gave repl_user access to the publication.
I shared the snapshot folder and gave repl_user full permissions on the
share and security tabs.
I tested the Publication to make sure it created the snapshot. I set the
merge intervals for 1 hour to minimize latency.
I then subscribed the target DB but when I check the status it says it is
not a subscriber???
IN the monitor it says the subscriber is synchronizing but maybe it's timing
out. This runs for about 45 minutes. Then it gets another error.
CAn I use transactional publication with SQL 2005 Express as a subscriber?
The documentation says I can but isn't really specific if SQL Express can do
Again I'm using a VPN link through a SonicWall TZ180 which is rather slow. I
was trying to use replication to give the remote site users faster read
access but still allow updates from the subscriber. When I tried this with
Virtual Server to test I got a different error saying I need to run
sp_link_publication on the subscriber to set a password for the sa account.
I thought the sa account PW could be set with SQL management Studio.
So far the error messages have been very unhelpful. Is there a good
reference that shows how to do this step by step. The Tutorials are somewhat
dated as is the book you linked to which is for SQL2K.
Post by Edwin Smith
Thanks again. I am currently updating both ends to SP2 after upgrading both
ends to SQL2005WE. The error is about needing to run sp_link_publication to
set the credentials of the Server-Server link. However I remember seeing a
KB article or maybe in Technet library that if this message happens to check
the build numbers. Unfortunately I didn't save the link but it said the
wizard is supposed to configure everything. The way the Technet SQL BOL is
arranged it is difficult to know just how to setup the required permissions
to do replication which is the major stumbling block.
In a little while I'll have both ends fully updates and WE on both then I'll
be trying again. I have a sample database so I can do multiple tries without
worrying about blowing things up. If I fail again I'll gather more stuff and
logs to post back.
I have searched in vain for a simple to understand step-by-step procedure to
do this but haven't found much. From scanning this group it seems like this
is a difficult thing to get right so it's worth the experience of getting it
wrong multiple times. I'm having loads of fun but the customer is surely
getting impatient. I'm a week behind on this project.
Thanks for your help.
Post by Hilary Cotter
I need to know the exact error messages to diagnose this problem
If you can enable logging that will help with the diagnosis and
You can replication from SQL Server 2005 Workgroup edition and above
to a SQL Server Express subscriber using transactional replication. If
you are using a pull subscription you will have to code the pull using
tsql, ActiveX controls, or RMO. You can schedule the pulls using task
scheduler, or manually pull them with code, or Windows Synchronization