Multiple Publisher (Changed default port in SQL local and Client)
Remote Distributor (Changed default port in SQL local and Client)
Multiple Subscriber (Changed default port in SQL local and Client)
When I set up Publisher and Distributor as one server (same SQL Instance), then the agent will succeed.
But when I set up Publisher as an Individual SQL Server and a Remote Distributor at the other SQL server. The SQL Replication will fail.
The message I received below:
Message: Named Pipes Provider: Could not open a connection to SQL Server [5].
Message: Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "repl_distributor".
Error messages:
Message: Named Pipes Provider: Could not open a connection to SQL Server [5].Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowConnectionFailureException(CConnection* pNativeConnectionWrapper)at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.InitializeNativeBcpOutProvider(LogMessageCallback logMessageCallback, String strName, String strApplication, String strHost, String strServer, String strNetwork, Int32 encryptionLevel, String strDatabase, SecurityMode securityMode, String strLogin, SecureString strPassword, Int32 bcpBatchSize, Int32 loginTimeout, Int32 queryTimeout, Int32 packetSize, DeadlockPriority deadlockPriority, String strRowDelimiter, String strFieldDelimiter, Byte syncMethod, Boolean forceOdbcBcp, Boolean forceOleDbBcp, Boolean enableMultipleActiveResultSets, Boolean useReadPastHint, Boolean usePageLockHint)at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.InstantiateBcpOutProviderWorker(String strBcpConnectionName)at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.InstantiateBcpOutProvider(String strBcpConnectionName)at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.Initialize()at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()at Microsoft.SqlServer.Replication.MainWorkerThread.AgentThreadProc()at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 5)Get help: http://help/5Message: Named Pipes Provider: Could not open a connection to SQL Server [5].Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowConnectionFailureException(CConnection* pNativeConnectionWrapper) (Source: MSSQLServer, Error number: 5)Get help: http://help/5Message: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Stack: (Source: MSSQLServer, Error number: 5)Get help: http://help/5Message: Login timeout expiredStack: (Source: MSSQLServer, Error number: 0)Get help: http://help/0
=========================
Error messages:
Message: Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "repl_distributor".
Command Text: sp_MSactivate_auto_sub
Parameters: @publication = Recruiter_Test_FourTables_10152015
@article = %
@status = initiated
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteReader(SqlCommand command, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.SetPublisherTranSequenceNumViaAutoSub(PublicationActivationState publicationActivationState, SqlConnection connection)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ActivateCSSPublicationAndSetTranSequenceNums(PublicationActivationState publicationActivationState, SqlConnection connection)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ConcurrentPreArticleFilesGenerationTransaction(SqlConnection connection)
at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoConcurrentPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 7303)
Get help: http://help/7303
Server FLDVT-SQLRUT, Level 16, State 1, Procedure sp_MSrepl_getdistributorinfo, Line 93
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "repl_distributor". (Source: MSSQLServer, Error number: 7303)
Get help: http://help/7303
The snapshot folder will produce "*.idx", "sch","pre" files, but always failed to produce "*.bcp"
I tried to assign the SQL Replication account as Windows Local and SQL Admin, but it's still failed.
After several hours troubleshooting, I found the solution
Whatever your topology of SQL Replication, 3 level, 2 level, 1 level. The DISTRIBUTOR role server must be the DEFAULT PORT, 1433
Especially the "CLIENT PROTOCOLS"
Afterward, I still changed the port to non-default port(ex: 2015), but keep the client protocols as 1433 and allow firewall go through 1433 and 2015
The port is only for Snapshot need and needs to open at distributor server.
Distributor should be the default port
Reference:
Reference:
http://serverfault.com/questions/329321/sql-server-2008-r2-transactional-replication-ports
No comments
Post a Comment