We have tasked to set up a scheduler to synchronize data between a publisher and subscriber on daily basis. The subscription was set using windows sync manager and it runs just fine when run manually. The subscriber is on the same system as the sync manager whereas the publisher has been set by the client (we do not have access to the database). It is pull type subscription.
The issue is I tried to set the scheduler through the Sync Manager but it does not run, this is happening because the subscription property is set to use windows authentication and I need to log off after every system usage. I am not able to use the SQL Server authentication property as windows does not let me save the username and password.
SQL Server Agent is not an option in this scenario.
I also tried using the replmerg.exe (hoping if it runs fine, I can set up the scheduler by calling the script through a batch file ), however the script also throws me some permission issue. I have added the user connecting to the subscriber database as the db_owner, but still get the error message as follows:
On trying to run replmerg.exe through command prompt, I get a permission error saying :
CReconcilerTask , 2013/11/19 15:49:05.059, 9356, 409, S1, INFO: =============== STARTING OF MERGE =============== ............ replrec!FillErrorInfo , 2013/11/19 15:49:05.195, 9356, 20097, S1, ERROR: ErrNo = 0x80045901, ErrSrc = , ErrType = 9, ErrStr = You do not have sufficient permission to run this command. Contact your system administrator. DatabaseReconciler , 2013/11/19 15:49:05.232, 9356, 20210, S2, :T:,110,0,2,,,,,,, DatabaseReconciler , 2013/11/19 15:49:05.232, 9356, 20217, S2, INFO: Session Highlights: FAIL, CReconcilerTask , 2013/11/19 15:49:05.239, 9356, 645, S1, INFO: =============== ENDING OF MERGE ================= I have added a log and the log reads like : 2013-11-19 15:49:05.141 OLE DB Distributor 'RMS': {call sp_MSgetversion } 2013-11-19 15:49:05.148 OLE DB Distributor 'RMS': {call sp_helpdistpublisher (N'RMS') } 2013-11-19 15:49:05.191 OLE DB Distributor 'RMS': {call sp_MShelp_repl_agent (N'RMS', N'rms', N'RMS1', N'D-RMS1', N'rms', 1)} 2013-11-19 15:49:05.195 You do not have sufficient permission to run this command. Contact your system administrator. 2013-11-19 15:49:05.201 Percent Complete: 0 2013-11-19 15:49:05.203 You do not have sufficient permission to run this command. Contact your system administrator. 2013-11-19 15:49:05.221 Percent Complete: 0 2013-11-19 15:49:05.224 Category:SQLSERVER Source: Merge Process Number: 14260 Message: You do not have sufficient permission to run this command. Contact your system administrator. 2013-11-19 15:49:05.227 Percent Complete: 0 2013-11-19 15:49:05.229 Category:COMMAND Source: Failed Command Number: 0
I am not sure what should I do make this work? I cannot manually synchronize daily and need to have some sort of scheduler set up.
The issue mentioned is with a client system and it was inaccessible to us for most part of the day today.
You are right, the user running the replmerg.exe does not have access to the publisher and distributor. They are set up on the client side with no access to us. I did set up the user to be the db_owner of the subscriber database.
The replmerg command looks like :
C:\Program Files\Microsoft SQL Server\100\COM\replmerg.exe" -Publisher [DB1] -PublisherDB [rms] -Publication [SYS1] -Subscriber [DB2] -SubscriberDB [rms] -SubscriberSecurityMode [0] -SubscriberLogin [test] -SubscriberPassword [test] -Distributor [DB1] -DistributorSecurityMode [0] -DistributorLogin [test1] -DistributorPassword [test1] -OutputVerboseLevel [2] -Output C:\ MergeAgent.log
I am not quite sure what the SQL Server version is for the Publisher and Distributor as they are on a different machine.
The Subscriber is SQL Server 2008 R2.
The sync process used to work fine through the Windows Sync Manager before when we had a centralized user to the system and we did not log it off. But now with new requirements, each of us have a unique username (admin rights) and have to log off the system after every usage.