April 21, 2010 at 10:53 am
I an trying to enable transactional replication on some of my databases. I try to create the publication and get the following error message when it is trying to creat it.
TITLE: Publisher Properties
------------------------------
An error occurred while saving publication database.
Do you want to continue saving other changes?
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Named Pipes Provider: Could not open a connection to SQL Server [53].
Changed database context to 'FB_Catman'.
OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005,
this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
After looking into what I could find it sugggested that I had to enable my databases to allow for the replication. When I try to do that under the publisher properties and select the databases I want to enable I get a very similar error.
Seems like it is a problem with the "repl_distributor" linked server or something, but I have yet to figure it out.
Any ideas or suggestions would be great.
Thanks
Shawn
April 21, 2010 at 1:22 pm
Is your distribution database on the same server or on a remote server? I set up my distribution database with t-sql commands. Doing it this way might help you isolate the problem.
April 21, 2010 at 1:27 pm
The Server that is the publisher is a different server from that one that will be the subscriber. Not sure if this is what you meant by distributor?
Shawn
April 21, 2010 at 2:05 pm
No, it's not. You should use a 3rd database called the distribution database that takes care of all the metadata and transactions. I use a 3rd server for this, but the database can be configured on the publishing or subscribing server. Do you have this database called "Distribution" on either server?
April 21, 2010 at 2:33 pm
There is one on the server that will be the subscriber, but not on the publisher. Is that required?
Thx
Shawn
April 21, 2010 at 3:19 pm
You only need one distribution database, and it can be on any server. The next thing to do is test the communication between your publishing server and distributing server, which in your case is the same as the subscribing server. The next thing to do is configure your distribution database on your publishing server. Under replication right click on Local Publications and configure the distributor which is a remote server. You will need the password you selected when you created your distribution database. If you have already done this then you should have a linked server defined for your publishing server that likely has the name "repl_distributor", you may also have a linked server defined for the name of your subscribing server (which in your case would both be the same server). Do you have these linked servers defined?
April 22, 2010 at 8:35 am
Thanks for the info, I currently have linked servers for repl_distributor on both servers using the same account. I went into the server with the distribution DB and added the other server as a distribution database. I think this is what you were saying to do.
When I try to create the replication I get the following error message
TITLE: New Publication Wizard
------------------------------
SQL Server could not create publication 'FBInvoice'.
------------------------------
ADDITIONAL INFORMATION:
A log reader agent can only be installed for a database enabled for transactional/snapshot replication. (Microsoft.SqlServer.Rmo)
----------------------------
When I try again to enable it as before I still get an error.
Shawn
April 22, 2010 at 8:52 am
On the server that has the database you want to replication (publish) execute the following:
use master
exec sp_replicationdboption @dbname = N'YOUR_DB_NAME', @optname = N'publish', @value = N'true'
April 22, 2010 at 12:01 pm
Thanks Toby, help is much appriciated. When I try that command it gives me the following message
OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 50000, Level 16, State 2, Line 10
Unable to connect to server. This operation will be tried later!
I try running the following to test my linked server and get the same message
declare @srvr nvarchar(128), @retval int;
set @srvr = 'repl_distributor';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
For all the other linked servers I use the remote login and password for an account on that remote server and things work fine. Not sure what I am doing wrong on this one.
Thanks again
Shawn
April 22, 2010 at 12:15 pm
Make sure both named pipes and tcp\ip are enabled on both servers. You can do this through the surface area configuration tool. I think you have to restart sql server if you end up enabling a protocol. Do the same thing for the client side connectivity through configuration manager.
Next try telnet from the publication server and do the following from the cmd:
TELNET subscribername 1433 (or port sql is listening)
TELNET subscribername 445
April 23, 2010 at 1:21 am
Yes,
1. As said above, check TCP/IP and Namedpipes enabled on both the servers. Check service accounts as well for SQL Services.
2. Drop Replication.
3. Follow rules to replicate the DB from one server to another.
4. Use Publisher - server1, Distributor - server1 or server2 , Subscriber - server2 or server3.
5. Configuring Distrubutor on Publisher will give good results, as i tested earlier.
Follow replication configuration steps.
Share the folder ReplData (default), or the replication location where you prefer to be on any disk. Replication creates the folder 'unc' which holds the transactions to be replicated to subscriber through network.
Cheers,
- Win.
" Have a great day "
May 20, 2010 at 12:09 pm
What do you mean by check service accounts? What am I meant to be checking?
Thanks
Shawn
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply