Production Replication and Linked Server Failure

Replication between remote offices, along with linked servers, began failing. Network and server admins indicate that nothing has been changed. The DBA of the remote server also says nothing has been changed. Replication failes with the following message.

The process could not connect to Subscriber 'ProdServer1\Production1'.

SQL Server does not exist or access denied.
(Source: CDB0202\PRODUCTIONLFG (Data source); Error number: 17)

I am able to connect to the remote server using EM and Query Analyzer. I am also able to create linked servers from the local production server to a remote test server.

Any tips on how to troubleshoot this problem?

Thanks, DaveThe problem has been resolved. WINS had expired on the clustered remote server. The network admins don't understand why it happened and can't explain why DNS didn't didn't resolve the name, but at least it is fixed. I'm a bit confused why EM and Query Analyzer did not experience any problems.

Thanks, Dave

Process to create db copy on another server

Hello
(duplicated post in sqlserver.replication)
I have a production database that i would like to have mirrored on another
server. I have never worked with database replication, but from what I
understand, changes made to any of the replicated DBs get reflected in all
other DBs. What I need is to have the one production database copied daily
(or weekly) to another box.
Can I somehow set this up through replication so that the replicated
database cannot be updated? ie is static and just syncs to the production db?
Should I be using replication or another tool/process?
Thanks for the help!
Replication is usually more for real time updates. But you can use Snapshot
replication and set the time to how ever often you wish. But if you are
really talking about a daily or even weekly copy I would just set up a job
to copy the full backup file to the other servers and restore it. You can
put the restored copy in ReadOnly mode so it doesn't have to use locks.
Andrew J. Kelly SQL MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:01C58D3A-8F33-4C09-9326-A8D8BB754062@.microsoft.com...
> Hello
> (duplicated post in sqlserver.replication)
> I have a production database that i would like to have mirrored on another
> server. I have never worked with database replication, but from what I
> understand, changes made to any of the replicated DBs get reflected in all
> other DBs. What I need is to have the one production database copied
> daily
> (or weekly) to another box.
> Can I somehow set this up through replication so that the replicated
> database cannot be updated? ie is static and just syncs to the production
> db?
> Should I be using replication or another tool/process?
> Thanks for the help!

Process could note deliver updates (s) at the publisher

I have created a publisher with row filtering using merge replication .I configured 5-6 subscribers for it.It was working fine for 2 months.But now for two subscribers its showing an error Process could not deliver update (s) at the publisher

Ive checked the job history..its showing

Merge process encountered an unexpected network error. The connection to tublisher 'Publishername' is no longer available

can any one help me?

thanks and regards

Dhanya

Sounds like a connection issue. Does it happen intermittently or persistently? If persistently, you can try to use osql.exe to connect to server "Publishername" from the machine that merge process is launched.

Thanks,

Peng

|||

Ive increased the query time out both at the publisher and subscriber...

now i am getting another message "The process is running and is wainting for a response from one of the backend connections"

Process could note deliver updates (s) at the publisher

I have created a publisher with row filtering using merge replication .I configured 5-6 subscribers for it.It was working fine for 2 months.But now for two subscribers its showing an error Process could not deliver update (s) at the publisher

Ive checked the job history..its showing

Merge process encountered an unexpected network error. The connection to tublisher 'Publishername' is no longer available

can any one help me?

thanks and regards

Dhanya

Sounds like a connection issue. Does it happen intermittently or persistently? If persistently, you can try to use osql.exe to connect to server "Publishername" from the machine that merge process is launched.

Thanks,

Peng

|||

Ive increased the query time out both at the publisher and subscriber...

now i am getting another message "The process is running and is wainting for a response from one of the backend connections"

process could not execute 'sp_repldone/sp_replcounters'

I have had to fully restored my publisher database. Since then my
transaactional replication no longer works with the 'Log Reader' failing to
start and reporting the error message "The process could not execute
'sp_repldone/sp_replcounters' on ServerName".
I have run via Enterprise Manager 'Validate Subscriptions' and
'Re-initialize All Subscriptions' but the Log Reader still fails. Should I
simply re-start the server or run sp_replrestart?
Any assistance would be greatly appreciated.
Kind regards
Robert
the fastest way to fix this is to drop and recreate your subscription.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert Paynter" <Robert Paynter@.discussions.microsoft.com> wrote in message
news:AD6E580D-02D3-4722-8AA0-E33979A1F0FD@.microsoft.com...
> I have had to fully restored my publisher database. Since then my
> transaactional replication no longer works with the 'Log Reader' failing
to
> start and reporting the error message "The process could not execute
> 'sp_repldone/sp_replcounters' on ServerName".
> I have run via Enterprise Manager 'Validate Subscriptions' and
> 'Re-initialize All Subscriptions' but the Log Reader still fails. Should
I
> simply re-start the server or run sp_replrestart?
> Any assistance would be greatly appreciated.
> Kind regards
> Robert
|||Hi Hilary
Many thanks for your reply. Just so I am clear in Enterprise Manager I
would delete the existing pulication and create a 'New Publication'
Also could I ask a related question ..... On the destination server the
existing Subcription (Enterprise Manager\Replication\Subscriptions\....)
does not delete when a publication is deleted via Enterprise Manager. In the
past I have investiigated how to delete old subscriptions without success.
Is there a way of doing this of which you are aware?
Again thank you for your kind assistance.
Kind regards
Rob
"Hilary Cotter" wrote:

> the fastest way to fix this is to drop and recreate your subscription.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Robert Paynter" <Robert Paynter@.discussions.microsoft.com> wrote in message
> news:AD6E580D-02D3-4722-8AA0-E33979A1F0FD@.microsoft.com...
> to
> I
>
>
|||Yes, if you script out your publication first it will be easier for you to
rebuild it.
Yes, there are two reasons why you get these orphaned subscribers, well
three actually, the third involves Paul Ibison and a big bottle of Scotch,
but he has sworn me to secrecy on this one.
If you restore an unpublished database onto a published database you can
orphan your subscribers. If you drop a pull subscription through a script on
the publisher only you can orphan them.
I can normally right click on the subscription and then delete it. If not I
manually remove the entry from mssubscription_properties.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert Paynter" <RobertPaynter@.discussions.microsoft.com> wrote in message
news:E433EA9D-8239-4256-908C-9CA841D24DF1@.microsoft.com...
> Hi Hilary
> Many thanks for your reply. Just so I am clear in Enterprise Manager I
> would delete the existing pulication and create a 'New Publication'
> Also could I ask a related question ..... On the destination server the
> existing Subcription (Enterprise Manager\Replication\Subscriptions\....)
> does not delete when a publication is deleted via Enterprise Manager. In
the[vbcol=seagreen]
> past I have investiigated how to delete old subscriptions without success.
> Is there a way of doing this of which you are aware?
> Again thank you for your kind assistance.
> Kind regards
> Rob
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
failing[vbcol=seagreen]
Should[vbcol=seagreen]
|||Hi Hilary
Again I would like to thank you for your reply and the time you give to do
this. I shall proceed with fixing the problem as advised and we'll be able
to get the subscription database functioning again.
Again many thanks.
Kind regards
Rob
"Hilary Cotter" wrote:

> Yes, if you script out your publication first it will be easier for you to
> rebuild it.
> Yes, there are two reasons why you get these orphaned subscribers, well
> three actually, the third involves Paul Ibison and a big bottle of Scotch,
> but he has sworn me to secrecy on this one.
> If you restore an unpublished database onto a published database you can
> orphan your subscribers. If you drop a pull subscription through a script on
> the publisher only you can orphan them.
> I can normally right click on the subscription and then delete it. If not I
> manually remove the entry from mssubscription_properties.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Robert Paynter" <RobertPaynter@.discussions.microsoft.com> wrote in message
> news:E433EA9D-8239-4256-908C-9CA841D24DF1@.microsoft.com...
> the
> message
> failing
> Should
>
>

Process cannot connect to distributor error !!!

Hey guys. I am creating an app that uses the RMO Merge Replication objects.
So far it can subscribe and unsubscribe just fine but when it comes to the syncing process it gets a little weird, here's why.

- If I run the SQL GUI Sync tool, it sinks fine. So that setup is working.

- When I run the C# App it blows up witht the error: "Process cannot connect to Distributor"

- Here's the weird part: When I set a breakpoint on my custom method SyncData (this method sets up the connection info and properties and then calls Syncronize( ) ) and step through the code when it gets to Syncronize( ) it runs perfectly fine, no execeptions.

I thought that maybe the Syncronize( ) method was being called too fast before the connection properties got to fully setup and connect, so I added a Thread.Sleep( ) method for 10 secs after each connection call and just before calling Syncronize( ). It still didn't work. My class is a static class by design, however, I changed it into an ordinary class and then placed the connection info in the constructor hoping it would do it's connection when the object gets initialized, that didn't work either.

If someone could please help me out with this I would greatly appreciate it.

Here's my code:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Replication;
using Microsoft.SqlServer.Replication.BusinessLogicSupport;
using Microsoft.SqlServer.Management.Common;
using System.Windows.Forms;
using System.Threading;

namespace Emds.Briefcase.BriefcaseSubscriber.BLL
{
public class SyncDataClass
{
#region Members

private static string m_statusMessage = string.Empty;
private static byte m_percentComplete;
public delegate void StatusTextChangeHandler(int percent, string status);
public static event StatusTextChangeHandler OnStatusChange;

#endregion Members

#region Methods

public static string SyncData()
{

//Delay();

// Define the server, publication, and database names.
string publicationName = "Chart";
string publisherName = @."JSMITH\SQL2005";
string subscriberName = @."JDOE\SQL2005";
string subscriptionDbName = "DataSubscriber";
string publicationDbName = "DataPublisher";
string message = string.Empty;

// Create a connection to the Subscriber.
ServerConnection conn = new ServerConnection(subscriberName);

MergePullSubscription subscription;

try
{
// Connect to the Subscriber.
conn.Connect();
// Delay();

// Define the pull subscription.
subscription = new MergePullSubscription();
subscription.ConnectionContext = conn;

//Delay();

subscription.DistributorSecurity.WindowsAuthentication = false;
subscription.DistributorSecurity.SqlStandardLogin = "sa";
subscription.DistributorSecurity.SqlStandardPassword = "russell";

// Delay();
subscription.PublisherName = publisherName;
subscription.PublicationDBName = publicationDbName;
subscription.PublicationName = publicationName;
subscription.PublisherSecurity.SecurityMode = ReplicationSecurityMode.SqlStandard;
subscription.PublisherSecurity.SqlStandardLogin = "sa";
subscription.PublisherSecurity.SqlStandardPassword = "russell";

// Delay();
subscription.DatabaseName = subscriptionDbName;
subscription.SubscriberSecurity.WindowsAuthentication = false;
subscription.SubscriberSecurity.SqlStandardLogin = "sa";
subscription.SubscriberSecurity.SqlStandardPassword = "russell";

// Delay();

// If the pull subscription exists, then start the synchronization.
if (subscription.LoadProperties())
{

// Check that we have enough metadata to start the agent.
if (subscription.PublisherSecurity != null || subscription.DistributorSecurity != null)
{
// Synchronously start the Merge Agent for the subscription.
subscription.SynchronizationAgent.Status += new AgentCore.StatusEventHandler(SynchronizationAgent_Status);
// Delay();

subscription.SynchronizationAgent.Synchronize();
message = "Data Syncronization is a success!";
}
else
{
throw new ApplicationException("There is insufficent metadata to " +
"synchronize the subscription. Recreate the subscription with " +
"the agent job or supply the required agent properties at run time.");
}
}
else
{
// Do something here if the pull subscription does not exist.
throw new ApplicationException(String.Format(
"A subscription to '{0}' does not exist on {1}",
publicationName, subscriberName));
}
}
catch (Exception ex)
{
// Implement appropriate error handling here.
throw new ApplicationException("The subscription could not be " +
"synchronized. Verify that the subscription has " +
"been defined correctly.", ex);
}
finally
{
conn.Disconnect();
}
return message;
}

static void SynchronizationAgent_Status(object sender, StatusEventArgs e)
{
m_percentComplete = e.PercentCompleted;
m_statusMessage = e.Message;

//Fire custom event
if (OnStatusChange != null)
{
OnStatusChange(m_percentComplete, m_statusMessage);
}
}

#endregion Methods
}
}Moved to SQL Server Replication forum.|||

You may want to try setting the distributor security on the Agent object directly before calling subscription.SynchronizationAgent.Synchronize() like so:

subscription.SynchronizationAgent.DistributorSecurityMode = SecurityMode.Standard

subscription.SynchronizationAgent.DistributorLogin = "sa"

subscription.SynchronizationAgent.DistributorPassword = "russell"

Hope that helps,

-Raymond

|||Raymond,

Thanks for your response. If you look at my code listing I think you will see that I am already doing that, unless I missunderstood what you were suggesting.

Thanks again,

Chris|||

There is actually a subtle difference: I believe your code is setting the security info on the subscription object, whereas my code is setting it on the SynchronizationAgent object.

-Raymond

|||Ok, I will give it a shot. Thank you again!!|||That was it!!! Thank you very, very much!!!!!

Take care!!!