Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Angry Unanswered: Standby server does not allows update

    I have two SQLServer, DBS1(main) and DBS2 (stand by). We are using the transactional replication with updating subscriber. The distribution
    database is on DBS2. When DBS1 was down, DBS2 does not allows me to update records. I able to do lookup records just fine! When DBS1 is backup and running, I removed all replications from DBS1 and DBS2 and still cannot update records on DBS2. I test update using sa account, system administrators account,... and also using the Query Analyzer login as Window authetication. The error I am getting is "Server: Msg18456, Level 14, State 1, Line 5 Login failed for user 'sa'."
    even when I am not using the sa account.

    I also try to update record using Access program linked to DBS2 under a different user (not sa) and I received the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'. {#18456}

    I able to update records on other database on DBS2 that is not setup for replication with DBS1.

    Thank you.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What happens when you login as 'sa' in query analyzer ?

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    For the transactional replication - did you have immediate updating selected ?

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Try the following on the subscriber that is giving the error:

    sp_link_publication
    @publisher = 'Publisher_Server_Name',
    @publisher_db = 'Publisher_Database_Name',
    @publication = 'Publication_Name',
    @security_mode = 0,
    @login = 'sa',
    @password = 'Publisher_SA_Password'

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    Originally posted by rnealejr
    What happens when you login as 'sa' in query analyzer ?
    I also received error "Server: Msg18456, Level 14, State 1, Line 5 Login failed for user 'sa'."

    Kev

  6. #6
    Join Date
    Nov 2003
    Posts
    7
    Originally posted by rnealejr
    For the transactional replication - did you have immediate updating selected ?
    No, The replication is setup every 15min

    Kev.

  7. #7
    Join Date
    Nov 2003
    Posts
    7
    Originally posted by rnealejr
    Try the following on the subscriber that is giving the error:

    sp_link_publication
    @publisher = 'Publisher_Server_Name',
    @publisher_db = 'Publisher_Database_Name',
    @publication = 'Publication_Name',
    @security_mode = 0,
    @login = 'sa',
    @password = 'Publisher_SA_Password'
    The replication is working fine, I don't get any error on the subscriber. The problem is I cannot update records on DBS2, the standby server.

    I dropped the replication from DBS1 (main server) and DBS2 (standby server) and still unable to update records on DBS2. I able to update records on a different database on DBS2 but not the one that had replication.

    Thank you,
    Kev.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Did you have any problems removing replication from the server ? Correctly removing replication is tricky - if you do not do it correctly some of the tables will still be treated as though replication is still enabled even though it appears replication is gone. Have you tried to recreate the replication process - when you do this, verify about the immediate updating ( I have seen several sites that had it enabled not really knowing that they had enabled or what it does ).

    Let me know what happens.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    "No, The replication is setup every 15min"

    This does not mean that you did not have immediate updating selected. Immediate updating allows changes on the subscriber to be pushed back to the publisher. That is potentially why you are receiving the login failed for user 'sa' because it needs the sa userid/password to update records back to the publisher (from the subscriber).

  10. #10
    Join Date
    Nov 2003
    Posts
    7
    Originally posted by rnealejr
    Did you have any problems removing replication from the server ? Correctly removing replication is tricky - if you do not do it correctly some of the tables will still be treated as though replication is still enabled even though it appears replication is gone. Have you tried to recreate the replication process - when you do this, verify about the immediate updating ( I have seen several sites that had it enabled not really knowing that they had enabled or what it does ).

    Let me know what happens.
    Thank you for all your replies. I able to re-create the replicaiton again and chosed "Queued Updating". When the replication finished, I able to update records on DBS2. I don't know what cause DBS2 to able to do update again. You are right about removing replication, after the removal, and re-create the replication, now I have 2 Subsrciptions in the subscriptions folder to the same database and I don't know how to remove the old one. When I right click on the Subriptions, It only has "Set Update Method..." and Help. The "Set Update Method" is grayed out.

    Thank you,
    Kevin.

  11. #11
    Join Date
    Nov 2003
    Posts
    7
    Originally posted by rnealejr
    "No, The replication is setup every 15min"

    This does not mean that you did not have immediate updating selected. Immediate updating allows changes on the subscriber to be pushed back to the publisher. That is potentially why you are receiving the login failed for user 'sa' because it needs the sa userid/password to update records back to the publisher (from the subscriber).
    Does "Queued Updating" also push the update back to publisher? Is It means that when the Main server is down, it will queues the update until the main server is backup?

    Thank you,
    Kev.

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    Does "Queued Updating" also push the update back to publisher? Is It means that when the Main server is down, it will queues the update until the main server is backup?
    Yes - and you need to have a conflict resolution policy to handle the potential conflicts.

    In case you are thinking about removing the other replication - Removing the other defunct replication is very difficult and requires an intimate understanding of the system tables with replication.

  13. #13
    Join Date
    Nov 2003
    Posts
    7

    Thumbs up

    Originally posted by rnealejr
    Yes - and you need to have a conflict resolution policy to handle the potential conflicts.

    In case you are thinking about removing the other replication - Removing the other defunct replication is very difficult and requires an intimate understanding of the system tables with replication.
    Thank you for your help!
    Kevin.

  14. #14
    Join Date
    Feb 2002
    Posts
    2,232
    Happy to help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •