SQL Server Performance

DBA Configuring Replication Error help

Discussion in 'Performance Tuning for SQL Server Replication' started by brianswestra, Nov 3, 2005.

  1. brianswestra New Member

    Hello,

    I have just started to configure my replication. My distributor and Publisher are the same machines. I have received an error after ensuring that the services were not using the local account, etc.

    The error I'm receiving comes at the point when the wizard is trying to configure the distributor. The error is: SQL Server Enterprise Manager could not configure 'servername' as the distributor for 'servername'. Error 21007: cannot add the remote distributor. Make sure that the local server is configured as a publisher at the distributor.

    I have search the archives and multiple search engines and followed all the tips. Nothing has worked yet...

    Please HELP!
    Brian
  2. satya Moderator

    Check whether any of the servername has been dropped and added previously.

    To resolve the problem the server name needs to be updated. Use the following:

    sp_addserver <real-server-name>, LOCAL

    if this gives an error complaining that the name already exists then use the following sequence:

    sp_dropserver <real-server-name>
    go
    sp_addserver <real-server-name>, LOCAL
    go

    If instead the error reported is 'There is already a local server.' then use the following sequence:

    sp_dropserver <old-server-name>
    go
    sp_addserver <real-server-name>, LOCAL
    go

    Stop and restart SQL Server.


    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. brianswestra New Member

    Hi Satya,<br /><br />Thanks for the reply (again!). I have gotten past that error and now am receiving the error 18456 Login failed for user 'sa'.<br /><br />I am at a complete loss - I know this is security related, but have done everything I've read and tried every combination you can imagine.<br /><br />Right now, there are 2 servers listed in the remote servers window and I have granted access to both my local server and the repl_distributor to map all remote logins to SA...<br /><br />You don't have a support number I could call do ya? <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />If you have any thoughts about this login failed issue, I'd sure appreciate them!<br /><br />Brian
  4. satya Moderator

    Check to make sure that the computer you are trying to access which has the group
    'distributor_admin' actually has the administrative access it requires, both in the security file for the server and in the user security for the individual database.

    If you are on a network and the network administrator has not enabled the administrator rights for the 'distributor_admin' group, you may need to ask the administrator to grant the permission over the network.

    KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;Q225129 to troubleshoot 18456 error and linkhttp://vyaskn.tripod.com/repl_ans4.htm#linkpubl for information.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. brianswestra New Member

    That has been one sticking point for me with the network access stuff...

    So I need to have a 'distributor_admin' group with admin rights? Not a user with admin rights?

    Also, in SQL Server, I have a user called 'distributor_admin' with sysadmin role. This is correct?

    Lastly, there is a remote computer created when I run the Replication Wizard called 'repl_distributor'. For this remote computer, the RPC is enabled and I have set all mappings to the sa account. Is this also correct?

    Ok, think that's it for now! Thanks again for all your help! Hopefully others will find this info worthwhile...Isn't there a step-by-step guide available? Maybe someone should create one!!!

    Cheers!
    Brian
  6. satya Moderator

    True, you need to workout with network admin to check on those privileges on OS.

    Bunch of articles on replication setup for your joy:
    http://www.databasejournal.com/features/mssql/article.php/1438231
    http://www.databasejournal.com/features/mssql/article.php/1438201
    http://www.codeproject.com/database/MergeReplication.asp
    http://www.devarticles.com/c/a/SQL-Server/Replication-SQL-Server-2000-Part-2/
    http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm
    http://ouriran.com/snapshot.cfm

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. brianswestra New Member

    Hello,

    Ok...I'm now past the login error as mentioned below and am now encountering a 20028 error. Details follow:

    While using the Replication wizard, my first error encountered was the 18456
    - the original error message was sa login failed...while messing with the
    remote login mapping the error changed to distributor_admin login failed. I
    resolved this by using the sp_changedistributor_password and this then
    allowed me to run through the wizard to set up the distributor. At this
    point, I did not set up the publisher or publications or subscribers.

    Ok, so now I can go into the confiuration screen and set up the publisher to
    use the non-trusted SQL Server authentication using distributor_admin (also
    tried sa). When I specify a merge publication and click ok (only trying one
    table at this point) I get the 20028 error saying that the distributor is not
    configured correctly.

    In reading through multiple forums and asking questions, I'm told to use the
    sp_dropserver dropping the logins in the process of course and then the
    sp_addserver with the local param.

    This has not resolved the issue...There are 3 servers right now in the
    sysservers table. S13 is the local server name id=0, DALSQL is the
    distributor with id=2 and repl_distributor with id=3.

    Any suggestions or thoughts would be greatly appreciated!

    Thanks in advance,
    Brian

    PS: here's the output of the @@servername and the server name property along with the records in the sysservers table...

    SELECT @@SERVERNAME
    SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
    SELECT * FROM SYSSERVERS

    S13
    S13
    01089S13SQL ServerSQLOLEDBS13NULLNULL2005-11-08 08:03:09.880NULLNULLNULLNULL00S13 010000100010NULL
    11609repl_distributorSQL ServerSQLOLEDBS13NULLNULL2005-11-08 07:04:25.793NULLNULLNULLNULL00S13 010010100110NULL
    21089ORDSQL01SQL ServerSQLOLEDBORDSQL01NULLNULL2005-11-08 05:26:16.060NULLNULLNULLNULL00ORDSQL01 010000100010NULL
    31089DALSQL01.SPAREBACKUP.COMSQL ServerSQLOLEDBDALSQL01.SPAREBACKUP.COMNULLNULL2005-11-08 05:41:31.450NULLNULLNULLNULL00DALSQL01.SPAREBACKUP.COM 010000100010NULL
  8. satya Moderator

    BTW can you check whether any issues with WINS and naming resolution issues on network.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. brianswestra New Member

    Here is the output from the sp_helpserver. You had asked me to post this if after getting the error that the distributor was not installed correctly and disabling and reenabling distributor/publisher. I still receive the same message. I also disabled again and genereated a script and ran that...still the same error.

    01089S13SQL ServerSQLOLEDBS13NULLNULL2005-11-08 08:03:09.880NULLNULLNULLNULL00S13 010000100010NULL
    11609repl_distributorSQL ServerSQLOLEDBS13NULLNULL2005-11-10 00:23:09.450NULLNULLNULLNULL00S13 010010100110NULL
    21089ORDSQL01SQL ServerSQLOLEDBORDSQL01NULLNULL2005-11-08 05:26:16.060NULLNULLNULLNULL00ORDSQL01 010000100010NULL
    31089DALSQL01.SPAREBACKUP.COMSQL ServerSQLOLEDBDALSQL01.SPAREBACKUP.COMNULLNULL2005-11-08 05:41:31.450NULLNULLNULLNULL00DALSQL01.SPAREBACKUP.COM 010000100010NULL


    Thanks! Brian
  10. satya Moderator

    All the information is correct from SQL Server perspective and I have run out of options to checklist the options.

    Are you using Firewall between the SQL servers, if so check KBAhttp://support.microsoft.com/kb/287932/EN-US/ to reconfigure the ports to allow the traffic.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. brianswestra New Member

    Good day...

    In regards to the naming/WINs; there doesn't appear to be any issues.

    Is there a way for me to tell or test this?

    Thanks,
    Brian
  12. satya Moderator

  13. brianswestra New Member

    Dear Satya,

    I can't thank you enough for all the effort you have put into helping me. I have now configured the distributor/publisher and subscriber!

    It all came down to the entries in the sysservers table. I had to update it with the appropriate server name for the dist/pub and also the subscriber server name. The whole time I was wondering if that was my problem.

    Again, thank you for your help and patience! Now all I need to do is script my user defined data types, make sure I'm using full column lists and update a couple of identity fields.

    By the way, do I need to set the NOT FOR REPLICATION on the identity fields if I'm going to use identity ranges? And if so, is there a property setting in SEM? I can't seem to find it. Also, where are the identity ranges set in replication?

    Thanks again,
    Brian
  14. satya Moderator

Share This Page