February 24, 2012 at 5:15 am
I am adding this question here as I am unable to find a dedicated board for replication.
I am struggling with setting up a merge pull subscription between SQL 2008 R2 express and SQL 2008 R2 Standard on separate servers.
My topology is as follows.
PUBLISHER/DISTRIBUTOR
Windows Server 2008 R2 Standard (x64) - Workgroup
SQL Server 2008 R2 Enterprise (x64) - Mixed Mode
Public facing with Static IP
Server Name = PPWAPPSRV01
SQL INSTANCE = PPWAPPSRV01
Publication Name = VHS
Publication DB = HBH_VHS
SUBSCRIBER
Windows 7 Professional (x64) - Workgroup
SQL Server 2008 R2 Express (x64) - Mixed Mode
Server Name = TESTSQL
SQL INSTANCE = TESTSQL\VHS
Subscription DB = VHS
I have run the following script to open all relevant ports on both machines:
@echo ================================================
@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo ================================================
@echo ========= Analysis Services Ports ============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo ================================================
@echo ========= SQL Browser Ports ==================
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo ================================================
@echo ========= Misc Applications ==================
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
I have run the following TSQL on the publisher in order to add the subscription:
use [HBH_VHS]
GO
exec sp_addmergesubscription
@publication = N'VHS'
, @subscriber = N'TESTSQL\VHS'
, @subscriber_db = N'VHS'
, @subscription_type = N'pull'
, @subscriber_type = N'local'
, @subscription_priority = 0
, @sync_type = N'Automatic'
, @hostname = N'TESTSQL'
GO
I have run the following TSQL on the subscriber in order to add the subscription:
use [VHS]
GO
exec sp_addmergepullsubscription
@publisher = N'PPWAPPSRV01'
, @publication = N'VHS'
, @publisher_db = N'HBH_VHS'
, @subscriber_type = N'Local'
, @subscription_priority = 0
, @description = N''
, @sync_type = N'Automatic'
exec sp_addmergepullsubscription_agent
@publisher = N'PPWAPPSRV01'
, @publisher_db = N'HBH_VHS'
, @publication = N'VHS'
, @distributor = N'PPWAPPSRV01'
, @distributor_security_mode = 0
, @distributor_login = N'sa'
, @distributor_password = N'*****'
, @enabled_for_syncmgr = N'False'
, @frequency_type = 1
, @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 = 0
, @active_start_date = 0
, @active_end_date = 19950101
, @alt_snapshot_folder = N'\\PublicIP\repldata'
, @working_directory = N''
, @use_ftp = N'False'
, @job_login = null
, @job_password = null
, @publisher_security_mode = 0
, @publisher_login = N'sa'
, @publisher_password = N'*****'
, @use_interactive_resolver = N'False'
, @dynamic_snapshot_location = null
, @use_web_sync = 0
GO
I run the following in a batch file on the subscriber to try and initialise the subscription:
REM -- Declare the variables.
SET Publisher=PPWAPPSRV01
SET Publication=VHS
SET PublicationDB=HBH_VHS
SET PublisherLogin=sa
SET Publisherpass=*****
SET Subscriber=TESTSQL\VHS
SET SubscriptionDB=VHS
REM -- Start the Distribution Agent.
REM -- The following command must be supplied without line breaks.
"C:\Program Files\Microsoft SQL Server\100\COM\DISTRIB.EXE" -Publisher %Publisher% -PublisherDB %PublicationDB% -Publication %Publication% -Distributor %Publisher% -DistributorLogin %PublisherLogin% -Distributorpassword %Publisherpass% -DistributorSecurityMode 0 -Subscriber %Subscriber% -SubscriberDB %SubscriptionDB% -SubscriberSecurityMode 1 -SubscriptionType 1 -OutputVerboseLevel 2 > InitVHS.log
The log file (InitVHS.log) returns the following error:
2012-02-24 11:12:28.478 OLE DB Distributor 'PPWAPPSRV01': execute sp_server_info 18
2012-02-24 11:12:28.529 ANSI codepage: 1
OLE DB Distributor 'PPWAPPSRV01': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'PPWAPPSRV01')
2012-02-24 11:12:28.632 OLE DB Distributor 'PPWAPPSRV01': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'TESTSQL\VHS')
2012-02-24 11:12:28.693 Agent message code 20053. Server 'TESTSQL\VHS' is not registered at server 'PPWAPPSRV01'.
2012-02-24 11:12:28.774 ErrorId = 20, SourceTypeId = 2
ErrorCode = '20053'
ErrorText = 'Server 'TESTSQL\VHS' is not registered at server 'PPWAPPSRV01'.'
2012-02-24 11:12:28.861 Adding alert to msdb..sysreplicationalerts: ErrorId = 20,
Transaction Seqno = , Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent (null) failed. Server 'TESTSQL\VHS' is not registered at server 'PPWAPPSRV01'.OLE DB Subscriber 'TESTSQL\VHS': exec dbo.sp_MSupdatelastsyncinfo N'PPWAPPSRV01',N'HBH_VHS', N'VHS', 1, 6, N'Server ''TESTSQL\VHS'' is not registered at server ''PPWAPPSRV01''.'
2012-02-24 11:12:28.930 Disconnecting from OLE DB Subscriber 'TESTSQL\VHS'
2012-02-24 11:12:28.931 Disconnecting from OLE DB Subscriber 'TESTSQL\VHS'
2012-02-24 11:12:28.931 Disconnecting from OLE DB Distributor 'PPWAPPSRV01'
This is driving me nuts.
I even created an alias on the subscriber machine, and get the following errors:
Protocol = TCP/IP ... PortNo = [blank]
2012-02-24 11:12:28.628 Agent message code 20084. The process could not connect to Subscriber 'HBH\VHS'.
2012-02-24 11:12:28.628 Category:NULL
Source: Microsoft SQL Server Native Client 10.0
Number: 10061
Message: TCP Provider: No connection could be made because the target machine actively refused it.
Protocol = TCP/IP ... PortNo = 0
2012-02-24 11:12:31.440 Agent message code 20084. The process could not connect to Subscriber 'HBH\VHS'.
2012-02-24 11:12:31.440 Category:NULL
Source: Microsoft SQL Server Native Client 10.0
Number: 87
Message: SQL Server Network Interfaces: Connection string is not valid [87].
Protocol = TCP/IP ... PortNo = 1433
2012-02-24 11:12:34.167 Agent message code 20084. The process could not connect to Subscriber 'HBH\VHS'.
2012-02-24 11:12:34.167 Category:NULL
Source: Microsoft SQL Server Native Client 10.0
Number: 10061
Message: TCP Provider: No connection could be made because the target machine actively refused it.
Protocol = Named Pipes ... Pipe Name = \\HBH\pipe\sql\query
2012-02-24 11:12:37.181 Agent message code 20084. The process could not connect to Subscriber 'HBH\VHS'.
2012-02-24 11:12:37.181 Category:NULL
Source: Microsoft SQL Server Native Client 10.0
Number: 2
Message: Named Pipes Provider: Could not open a connection to SQL Server [2].
February 24, 2012 at 6:40 am
Have you tried this in a push replication setup?
I did find this on technet as well which suggests its security related in someway but the wording to me suggests its a push model so it could be wrong.
February 24, 2012 at 6:50 am
MysteryJimbo (2/24/2012)
Have you tried this in a push replication setup?I did find this on technet as well which suggests its security related in someway but the wording to me suggests its a push model so it could be wrong.
Thanks
We are unable to use a push model as the subscribers dont have fixed IPs and they are also not online at any predictable times.
Will look into the blog.
February 24, 2012 at 6:52 am
MysteryJimbo (2/24/2012)
Have you tried this in a push replication setup?I did find this on technet as well which suggests its security related in someway but the wording to me suggests its a push model so it could be wrong.
The blog entry seems to be pointing to an authentication issue between the publisher and the distributor using linked servers. in my case both the publisher and distributor are the same machine, and there are no linked servers.
February 24, 2012 at 7:12 am
Is the server register in the publisher?
exec sp_helpsubscriberinfo
Also check for an entry in sys.servers as it should have one on the publisher
February 24, 2012 at 7:16 am
MysteryJimbo (2/24/2012)
Is the server register in the publisher?
exec sp_helpsubscriberinfoAlso check for an entry in sys.servers as it should have one on the publisher
exec sp_helpsubscriberinfo comes back empty
select * from sys.servers returns the publisher and distributor only
I suspect that somehow I need to get the subscriber into this lot, but the question is how?
I would expect this info to get populated when running the sp_addsubscription procs
February 24, 2012 at 7:33 am
Thats where I believe it is done. I'm wondering if by overriding the host in sp_addmergesubscription its done something different. Have you tried it without that parameter?
February 24, 2012 at 7:36 am
MysteryJimbo (2/24/2012)
Thats where I believe it is done. I'm wondering if by overriding the host in sp_addmergesubscription its done something different. Have you tried it without that parameter?
Originally did it without the host param. Will try again now.
February 24, 2012 at 7:43 am
Removed subscription at subscriber then at publisher.
Added at publisher then at subscriber.
Still no info for subscriber listed.
Gonna start following sys.sp_ trail to see what gets done. Maybe error gracefully handled ....
February 24, 2012 at 8:04 am
subscriber is listed correctly in
select subscriber_server from dbo.sysmergesubscriptions
February 24, 2012 at 8:17 am
DesNorton (2/24/2012)
I run the following in a batch file on the subscriber to try and initialise the subscription:REM -- Declare the variables.
SET Publisher=PPWAPPSRV01
SET Publication=VHS
SET PublicationDB=HBH_VHS
SET PublisherLogin=sa
SET Publisherpass=*****
SET Subscriber=TESTSQL\VHS
SET SubscriptionDB=VHS
REM -- Start the Distribution Agent.
REM -- The following command must be supplied without line breaks.
"C:\Program Files\Microsoft SQL Server\100\COM\DISTRIB.EXE" -Publisher %Publisher% -PublisherDB %PublicationDB% -Publication %Publication% -Distributor %Publisher% -DistributorLogin %PublisherLogin% -Distributorpassword %Publisherpass% -DistributorSecurityMode 0 -Subscriber %Subscriber% -SubscriberDB %SubscriptionDB% -SubscriberSecurityMode 1 -SubscriptionType 1 -OutputVerboseLevel 2 > InitVHS.log
What about this? You are using sa so this should be SQL auth.
-SubscriberSecurityMode[ 0| 1]
Specifies the security mode of the Subscriber. A value of 0 indicates SQL Server Authentication, and a value of 1 indicates Windows Authentication Mode (default).
February 26, 2012 at 11:26 pm
MysteryJimbo (2/24/2012)
DesNorton (2/24/2012)
I run the following in a batch file on the subscriber to try and initialise the subscription:REM -- Declare the variables.
SET Publisher=PPWAPPSRV01
SET Publication=VHS
SET PublicationDB=HBH_VHS
SET PublisherLogin=sa
SET Publisherpass=*****
SET Subscriber=TESTSQL\VHS
SET SubscriptionDB=VHS
REM -- Start the Distribution Agent.
REM -- The following command must be supplied without line breaks.
"C:\Program Files\Microsoft SQL Server\100\COM\DISTRIB.EXE" -Publisher %Publisher% -PublisherDB %PublicationDB% -Publication %Publication% -Distributor %Publisher% -DistributorLogin %PublisherLogin% -Distributorpassword %Publisherpass% -DistributorSecurityMode 0 -Subscriber %Subscriber% -SubscriberDB %SubscriptionDB% -SubscriberSecurityMode 1 -SubscriptionType 1 -OutputVerboseLevel 2 > InitVHS.log
What about this? You are using sa so this should be SQL auth.
-SubscriberSecurityMode[ 0| 1]
Specifies the security mode of the Subscriber. A value of 0 indicates SQL Server Authentication, and a value of 1 indicates Windows Authentication Mode (default).
Subscriber login is Windows Authentication. Logs show success at subscruber. Changed to SQL Auth, but error remains the same.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply