SQL Server Performance

will Mirroring work in local account and workgroup

Discussion in 'SQL Server 2005 Database Mirroring' started by WingSzeto, Mar 15, 2007.

  1. WingSzeto Member

    I like to know if db mirroring will work for two SQL server 2K5 in workgroup env and each has a local account with the same name and password to start the SQL services. The local account has admin right and also has a SQL login setup. The database is restored as norecovery in the secondary server.

    Here is the command I used to set up the mirroring.

    -- on the primary ServerA
    CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7777 )
    FOR DATABASE_MIRRORING (ROLE=PARTNER);



    ALTER DATABASE db1
    SET PARTNER = 'TCP://172.16.100.1:7777'

    grant connect on endpoint::endpoint_mirroring to [ServerAAccountA]

    -- on the secondary ServerB

    CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7777 )
    FOR DATABASE_MIRRORING (ROLE=PARTNER);



    ALTER DATABASE db1
    SET PARTNER = 'TCP://172.16.100.2:7777'

    grant connect on endpoint::endpoint_mirroring to [ServerBAccountA]


    I issue the following two commands to make sure the endpoint is created and started and they are.

    SELECT name, port FROM sys.tcp_endpoints

    SELECT state_desc FROM sys.database_mirroring_endpoints

    ----------
    I have couple questions.

    1. If I do the above setup using Studio management tool instead of transact-SQL, I will get 'not able to connect to ServerB. I have checked the BOL to make sure the network address name is constructed as suggested. If I run the above commands in query analyzer, I don't get any error. However, when I right click the prinicple database and select mirroring, it said the DB mirroring is not configured yet and the Server Network address for mirror is empty. Is it a normal behavior if I set up mirroring using transact-sql?

    2. Since the command above didn't give me any error, so I went ahead and see if I can switch over to the mirror database, when I issue the below command

    ALTER DATABASE db1 SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS


    It will give me the following error:

    Msg 1404, Level 16, State 3, Line 1
    The command failed because the database mirror is busy. Reissue the command later.

    Any idea of why this happens?

    Wingman

  2. satya Moderator

    You have to ensure the SQL server services have the same name and password as they are in workgroup; also check the log on mirror server to ensure the REDO queue is completed.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page