cannot replicate to subscriber... complicated...
-
I am going to try and explain this the best I can.
I have a server the have SQL 2000 (default instance) and SQL 2005 (named instance). Both are set up for replicate as their own publisher and distributer. The 2005 has just been setup so it has no subscribers. I can create publications and create snapshots without any problem. When I try to add a subscriber -- subscriber is SQL 2005 only -- I get an error... 'cannot connect to subscriber'. I can connect through SMS with no issue. I tried all kinds of combinations of security and nothing works. Same errors.
SO I decided to test by making the subscriber be a publisher/Distributer and replicate to the box that has both versions on it...
Well... it worked flawlessly...
I can't find any information on having 2 version on the same server except how to install it and that is no help...
Any suggestions??
-
Check SAC to make sure you have enable remote connections...
For Express, developer and Eval. edition by default it is configured to local connections only...
MohammedU
Microsoft SQL Server MVP -
I did check that and it is enabled. I am running standard edition.
-
You may need to go into the client configuration and configure an alias for your subscriber with the appropriate port number. Multiple instances varies the port number and sometimes this does not play nice with replication.
-
Please explain how you are trying to create the subscription. Are you using the wizard or stored procedures? If using stored procedures, please post the code.
Also, if you are using stored procedures that were scripted from an existing subscription/publication, bear in mind that it won't script out the logins and passwords.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
I created the subscriptions via the wizards...
I found out some more information and now am really stuck...
I tried to connect through osql to the subscriber... it worked... but then looked at my path statement and it was pointing to the 2000 osql...
I manually went into the 80/tools/binn and was able to connect
I manually went into the 90/tools/binn and got an error stating the connection was actively refused.
I then loaded up profiler on the subscriber to see if anything was coming through.. and nothing came up in profiler when I tried to log in from the publisher..
now I am not sure what to look at. I made sure TCP/IP and namepipes were enabled on the subscriber along with allowing remote connections... I restarted the DB engine a couple time with the changed of the configurations..
-
Are you using the instance name for the suscriber?
Check the error log to see if there was anything logged about the login attempt.
Can you script out what you have done and post the part relative to the subscription?
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
The subscriber is the default instance...
Here is the script as generated by the wizard
-----------------BEGIN: Script to be run at Publisher 'ALLFLDB11\BLUENILE'-----------------
use [Music_store]
exec sp_addsubscription @publication = N'Products', @subscriber = N'allfldb99', @destination_db = N'music_store', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'Products', @subscriber = N'allfldb99', @subscriber_db = N'music_store', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20070129, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'ALLFLDB11\BLUENILE'-----------------
-
I didn't realize that the subscriber was the SQL 2000 instance on the same machine. Didn't you say that you had set both of them up to replicate themselves and acting as their own distributor? If you've configured the Music_store db on the SQL 2000 machine to be a publisher, remove it's status as a publisher. I'd even go ahead and remove the distributor from the server unless you need it for somethign else.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
The subscriber is not 2000....
here is the set setup
server 1 -- SQL 2000 pub/dist for current production enviroment. Also has SQL 2005 pub/dist for conversion... we didn't have any extra servers so we has to install side by side. The plan is to shut off the 2000 instance once 2005 is complete.
server 2 -- SQL 2005 only as subscriber to 2005 pub.
The 2000 instance on server one has 8 SQL 2000 subscribers so I cannot alter the situation.... I remembered today that SQL 2000 client tools were reinstalled on server 1 because when 2005 was installed it messed up the ability to save DTS in 2000... this was the fix as posted by microsoft... however I am thinking this also is causing my issue...
-
I see that you are using Windows authentication. Are you using a domain account or a local account?
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
it is a local account but it is on both servers... I would use a domain account but we do not have a domain on that segmant of the network.
-
A local account, even a local admin, does not have access rights to other machines. I would suggest setting up a SQL account specifically for replication. Make the same account on all machines involved in replication. This is what I do.
Then make sure you give the account access to the publication:
exec
sp_grant_publication_access @publication = N'Music_store', @login = N'ReplicationUser'
I have every step of replication in stored separate stored procedures for automation purposes. My scripts are for merge replication, so obviously they wouldn't work for you as written, but maybe it will help you troubleshoot.
This is where I configure the publisher (the procedure is located in the publisher db):
Create
Procedure dbo.USP_ReplicationConfigurePublisher
As
Declare
@descrip nvarchar(255),
@ServerName
sysname,
@Login1
nvarchar(255),
@Login2
nvarchar(255),
@publisher
sysname
Set
@publisher = db_name()
Set
@descrip = N'Merge publication of database ''' + @publisher + ''' from Publisher ''' + @@ServerName + '''.'
If
Not Exists (Select 1 From sys.databases
Where is_merge_published = 1
And [name] = db_name())
Begin
Exec master..sp_replicationdboption @dbname = @publisher, @optname = N'merge publish', @value = N'true'
Exec sp_addmergepublication @publication = @publisher, @description = @descrip, @sync_mode = N'native', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'false', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = N'false', @publication_compatibility_level = N'90RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0
Select @Login1 = name
From sys.server_principals
Where Type = 'G'
And is_disabled = 0
And name Like Cast(SERVERPROPERTY('MachineName') as varchar) + '\SQLServer2005SQLAgentUser$' + Replace(@@ServerName, '\', '$') + '%'
Select @Login2 = name
From sys.server_principals
Where Type = 'G'
And is_disabled = 0
And name Like Cast(SERVERPROPERTY('MachineName') as varchar) + '\SQLServer2005MSSQLUser$' + Replace(@@ServerName, '\', '$') + '%'
exec sp_addpublication_snapshot @publication = @publisher, @frequency_type = 4, @frequency_interval = 14, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'ReplicationUser', @publisher_password = N'Censored'
exec sp_grant_publication_access @publication = @publisher, @login = N'ReplicationUser'
exec sp_grant_publication_access @publication = @publisher, @login = N'NT AUTHORITY\SYSTEM'
exec sp_grant_publication_access @publication = @publisher, @login = N'BUILTIN\Administrators'
exec sp_grant_publication_access @publication = @publisher, @login = @Login1
exec sp_grant_publication_access @publication = @publisher, @login = @Login2
exec sp_grant_publication_access @publication = @publisher, @login = N'distributor_admin'
End
And then for adding subscribers, I have the following procedure in the publisher database:
Create
Procedure dbo.USP_ReplicationAddSubscribers
@ServerName sysname = Null
As
Declare
@Date int,
@Publisher
sysname,
sysname
Set
@Publisher = db_name()
-- My publisher is named with Master added to the end, i.e. DBName is subscriber and DBNameMaster is publisher. Publisher has a subscriber on it as well.
Set
@subscriber = Replace(@Publisher, 'Master', '')
If
@ServerName Is Null Set @ServerName = @@ServerName
Select
@Date = Cast(convert(varchar, getdate(), 112) as int)
exec
sp_addmergesubscription @publication = @Publisher, @subscriber = @ServerName, @subscriber_db = @subscriber, @subscription_type = N'Push', @sync_type = N'None', @subscriber_type = N'Global', @subscription_priority = 75, @description = null, @use_interactive_resolver = N'False'
exec
sp_addmergepushsubscription_agent @publication = @Publisher, @subscriber = @ServerName, @subscriber_db = @subscriber, @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'ReplicationUser', @subscriber_password = N'Censored', @publisher_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = @Date, @active_end_date = 99991231, @enabled_for_syncmgr = N'False'
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
That definitely is a good plan....
However if I can't connect then I will not be able to create anything....
I have tried connecting with sa but that didn't work either...
I truely think something is messed up with my files for client connectivity.
-
Could definitely be the case. Did you check the SQL error log to see if there were any login errors? That will tell you if it is at least getting to the server.
Execute this on the subscriber server:
Declare
@ErrorLog Table (LogID int identity(1, 1) not null primary key,
LogDate
datetime null,
ProcessInfo
nvarchar(100) null,
LogText
nvarchar(max) null)
Insert
Into @ErrorLog (LogDate, ProcessInfo, LogText)
Exec
master..xp_readerrorlog
Select
*
From
@ErrorLog
Where
CharIndex('sa', LogText) > 0
Order
By LogID Desc
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply