locked
Database Mirroring hangs on ALTER DATABASE SET PARTNER RRS feed

  • Question

  • Hi,

    Here is the scenario.  I currently have a single XP Professional machine with three SQL Server instances installed.  I'm trying to set up a database mirroring session.

    The mirror instance was created with an endpoint listening on port 5151.  The principal instance endpoint listens on port 5150.  The witness listens on port 5152.

    After creating the endpoints, I back up the database I wish to mirror on the principal instance, and then restore with NORECOVERY on the mirror instance.

    I then execute the following code on the mirror instance:

    ALTER DATABASE BookStore

    SET PARTNER = 'TCP://192.168.0.2:5151'

    GO

    This works fine.  I then go to the principal to add it as a partner too:

    ALTER DATABASE BookStore

    SET PARTNER = 'TCP://192.168.0.2:5150'

    GO

    The query runs indefinitely.  I finally stop it after several minutes, and I see in the object browser that the database has the following in parenthesis "(Principal, Synchronizing)".  I then cannot drop, modify, or do anything with the principal database.

    Ideas?

    Sunday, June 26, 2005 7:57 PM

Answers

  • we aren't testing using the direct TCP-IP address for setting up database mirroring, so you need to use the FQDN.

    there is a section on troubleshooting database mirroring in BOL specifically designed for this error message.  please look at that.

    specifically,
    1. for the FQDN use the output described in the troubleshooting giude to use the output from ipconfig to set the name.
    2. you should be able to run sqlservr.exe as LocalSystem as long as the instances are on the same machine.

    mark

    Wednesday, June 29, 2005 6:32 PM

All replies

  • hmmmm....

    the first ALTER DATABASE should always work (as long as the db has been restored with NORECOVERY) because it puts the database in a "waiting to be contacted" mode.

    you should be using the FQDN instead of the IP.  i think you are getting away with that because you are on the same server.

    your port mappings are not right.  if the mirror is listening on 5151, then the principal should be issuing its ALTER DATABASE with the port 5151 on it, not 5150 as you have indicated.

    thanks,
    mark
    Tuesday, June 28, 2005 10:09 PM
  • Thanks for the response, Mark.

    I went ahead and tried switching the ALTER DATABASE to point to the mirror port, and get the following message:

    Msg 1418, Level 16, State 1, Line 3

    The server instance "TCP://192.168.0.2:5022" is not running or does not exist. Check the name and reissue the command.

    You said I should be on FQDN (but unfortunately this the only machine where I can test this - and it isn't on a domain).  What's more, I used the GUI to set up mirroring, and it worked. 

    Right now my goal is to see if I can get it to work using just Transact-SQL . (I thought to capture what SQL Server was doing via the Wizard with Profiler, but I'm getting an "Cannot retrieve trace definition for SQL Server version
    9.0.1187." error on that).

    Thanks for any thoughts you may have on this...

    Tuesday, June 28, 2005 11:57 PM
  • we aren't testing using the direct TCP-IP address for setting up database mirroring, so you need to use the FQDN.

    there is a section on troubleshooting database mirroring in BOL specifically designed for this error message.  please look at that.

    specifically,
    1. for the FQDN use the output described in the troubleshooting giude to use the output from ipconfig to set the name.
    2. you should be able to run sqlservr.exe as LocalSystem as long as the instances are on the same machine.

    mark

    Wednesday, June 29, 2005 6:32 PM
  • Thank you Mark.
    Wednesday, June 29, 2005 8:01 PM
  • I use this script to setup test mirrored db

     

     

    -- On Principal
    IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
      CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022)  FOR DATABASE_MIRRORING (ROLE = ALL)

     

    create database testMirror
    --sp_helpdb testmirror

     

    alter database testmirror
    modify file (name=testmirror_log, size=1,maxsize=2,filegrowth=1)

     

    alter database testmirror
    modify file (name=testmirror, Maxsize=3,filegrowth=2)

     

    alter database testMirror set recovery full

     

    -- We use SQL lite speed if you don't have then you are missing out, however you can use SQL backup Smile

    exec master..xp_backup_database @database='testMirror', @filename='\\SERVERA\sqlbak\testMirror.lsb',@init=1
    exec master..xp_backup_log @database='testMirror', @filename='\\SERVERA\\sqlbak\testMirror_log.lsb',@init=1

     

     

    -- On mirror
    IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
      CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022)  FOR DATABASE_MIRRORING (ROLE = ALL)

     

    exec master..xp_restore_database @database='testMirror', @filename='\\SERVERA\sqlbak\testMirror.lsb',@With='NoRecovery'


    exec master..xp_restore_log @database='testMirror', @filename='\\SERVERA\sqlbak\testMirror_log.lsb',@With='NoRecovery'
    ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERA:5022'


    -- On principal
    ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERB:5022'

     

    -- testing failing over, run on principal
    ALTER DATABASE testMirror SET PARTNER FAILOVER

     

    -- once testing is completed, break the mirror and drop the database
    ALTER DATABASE testMirror SET PARTNER off
    drop database testMirror

     

    --NOTE: I have set this up not using FQDN and it works OK, also this script is for between servers

    -- I have tried setting this up on a VISTA machine running 2 instances of SQL (dev edition) however I can get mirroring to work YET, I think I'm hitting a firewall issue (could be wrong), my ports are open correctly and I can see then via netstat - a

    -- I'm getting errors like The Network address XYZ can't be reached or does not exist

    -- Oh The joys of firewalls and ports....

    Thursday, June 28, 2007 11:22 PM