locked
Problem in mirroring when using SQLCMD RRS feed

  • Question

  • Dear all,

    I'm having a problem with configure Mirroring. When I use graphical inteface to config the Mirror, it is ok. But when using sql, there are an error that I can not understand.

    Here is the code:

    Tongue TiedETVAR PrincipalServer DEMO\PARTNERA
    Tongue TiedETVAR MirrorServer DEMO\PARTNERB
    Tongue TiedETVAR WitnessServer DEMO\WITNESS
    Tongue TiedETVAR Database2Mirror AdventureWorksDW
    go

    SurpriseN ERROR EXIT
    go

    :CONNECT $(PrincipalServer)

    -- Mirroring ONLY supports the FULL Recovery Model
    ALTER DATABASE $(Database2Mirror)
    SET RECOVERY FULL
    go

    USE $(Database2Mirror)
    go

    CREATE ENDPOINT Mirroring
    STATE=STARTED
    AS TCP (LISTENER_PORT=5022)
    FOR DATABASE_MIRRORING (ROLE=PARTNER)
    GO

    :CONNECT $(MirrorServer)

    CREATE ENDPOINT Mirroring
    STATE=STARTED
    AS TCP (LISTENER_PORT=5023)
    FOR DATABASE_MIRRORING (ROLE=PARTNER)
    GO

    :CONNECT $(WitnessServer)

    CREATE ENDPOINT Mirroring
    STATE=STARTED
    AS TCP (LISTENER_PORT=5024)
    FOR DATABASE_MIRRORING (ROLE=WITNESS)
    GO

    :CONNECT $(PrincipalServer)

    BACKUP DATABASE $(Database2Mirror)
    TO DISK = 'C:\TechReady\$(Database2Mirror).bak'
    WITH INIT
    GO

    :CONNECT $(MirrorServer)

    DECLARE @InstanceName sql_variant,
    @InstanceDir sql_variant,
    @SQLDataRoot nvarchar(512),
    @ExecStr nvarchar(max)

    SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')

    EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
    'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
    @InstanceName, @InstanceDir OUTPUT

    SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread '
    + '''HKEY_LOCAL_MACHINE'', '
    + '''SOFTWARE\Microsoft\Microsoft SQL Server\'
    + convert(varchar, @InstanceDir)
    + '\Setup'', ''SQLDataRoot'', @SQLDataRoot OUTPUT'

    EXEC master.dbo.sp_executesql @ExecStr
    , N'@SQLDataRoot nvarchar(512) OUTPUT'
    , @SQLDataRoot OUTPUT

    IF @SQLDataRoot IS NULL
    BEGIN
    RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)
    END

    CREATE TABLE #BackupFileList
    ( LogicalName sysname NULL
    , PhysicalName sysname NULL
    , [Type] char(1)
    , FileGroupName sysname NULL
    , Size bigint
    , MaxSize bigint
    , FileId smallint
    , CreateLSN numeric(25,0)
    , DropLSN numeric(25,0)
    , UniqueId uniqueidentifier
    , ReadOnlyLSN numeric(25,0)
    , ReadWriteLSN numeric(25,0)
    , BackupSizeInBytes bigint
    , SourceBlockSize bigint
    , FileGroupId smallint
    , LogGroupGUID uniqueidentifier
    , DifferentialBaseLSN numeric(25,0)
    , DifferentialBaseGUID uniqueidentifier
    , IsReadOnly bit
    , IsPresent bit
    )

    INSERT #BackupFileList
    EXEC('LOAD FILELISTONLY FROM DISK = ''C:\TechReady\$(Database2Mirror).bak''')

    UPDATE #BackupFileList
    SET PhysicalName
    = @SQLDataRoot
    + N'\Data\'
    + REVERSE(SUBSTRING(REVERSE(PhysicalName)
    , 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))

    DECLARE @LogicalName sysname
    , @PhysicalName sysname

    DECLARE FileListCursor CURSOR FAST_FORWARD FOR
    SELECT LogicalName, PhysicalName
    FROM #BackupFileList

    OPEN FileListCursor

    FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

    SELECT @ExecStr = N'RESTORE DATABASE $(Database2Mirror)' +
    N' FROM DISK = ''c:\TechReady\$(Database2Mirror).bak''' +
    N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''

    FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

    WHILE @@FETCH_STATUS <> -1
    BEGIN
    SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName
    + ''' TO ''' + @PhysicalName + ''''
    FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
    END

    -- NORECOVERY is required for Database Mirroring, replace is not.
    -- Replace is used here solely to allow repetitive use of this script.
    SELECT @ExecStr = @ExecStr + N' , NORECOVERY, REPLACE'

    -- Useful for testing
    -- Only return the string and then comment out the EXEC line below.
    -- SELECT @ExecStr

    EXEC (@ExecStr)

    DEALLOCATE FileListCursor
    GO

    :CONNECT $(PrincipalServer)
    SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns ONLINE
    SELECT db_name(sd.[database_id]) AS [Database Name],
    sd.mirroring_guid,
    sd.mirroring_state,
    sd.mirroring_state_desc,
    sd.mirroring_partner_name,
    sd.mirroring_witness_name,
    sd.mirroring_witness_state,
    sd.mirroring_witness_state_desc,
    sd.mirroring_role,
    sd.mirroring_role_desc,
    sd.mirroring_role_sequence,
    sd.mirroring_safety_level,
    sd.mirroring_safety_level_desc,
    sd.mirroring_safety_sequence,
    sd.mirroring_failover_lsn
    FROM sys.database_mirroring AS sd
    WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
    go

    :CONNECT $(MirrorServer)
    SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
    SELECT db_name(sd.[database_id]) AS [Database Name],
    sd.mirroring_guid,
    sd.mirroring_state,
    sd.mirroring_state_desc,
    sd.mirroring_partner_name,
    sd.mirroring_witness_name,
    sd.mirroring_witness_state,
    sd.mirroring_witness_state_desc,
    sd.mirroring_role,
    sd.mirroring_role_desc,
    sd.mirroring_role_sequence,
    sd.mirroring_safety_level,
    sd.mirroring_safety_level_desc,
    sd.mirroring_safety_sequence,
    sd.mirroring_failover_lsn
    FROM sys.database_mirroring AS sd
    WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
    go

    :CONNECT $(MirrorServer)

    ALTER DATABASE $(Database2Mirror)
    SET PARTNER = 'TCP://DEMO:5023'
    -- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'
    -- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5091'
    GO

    :CONNECT $(PrincipalServer)

    ALTER DATABASE $(Database2Mirror)
    SET PARTNER = 'TCP://DEMO:5022'
    -- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'
    -- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5092'
    GO

    ALTER DATABASE $(Database2Mirror)
    SET WITNESS = 'TCP://DEMO:5024'
    -- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'
    -- SET WITNESS = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5090'
    GO

    SELECT db_name(sd.[database_id]) AS [Database Name],
    sd.mirroring_guid,
    sd.mirroring_state,
    sd.mirroring_state_desc,
    sd.mirroring_partner_name,
    sd.mirroring_witness_name,
    sd.mirroring_witness_state,
    sd.mirroring_witness_state_desc,
    sd.mirroring_role,
    sd.mirroring_role_desc,
    sd.mirroring_role_sequence,
    sd.mirroring_safety_level,
    sd.mirroring_safety_level_desc,
    sd.mirroring_safety_sequence,
    sd.mirroring_failover_lsn
    FROM sys.database_mirroring AS sd
    WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')

    and the log is:

    Msg 1452, Level 16, State 6, Line 3
    The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.
    ** An error was encountered during execution of batch. Exiting.


    I Think the error is begin at:

    ALTER DATABASE $(Database2Mirror)
    SET PARTNER = 'TCP://DEMO:5023'
    -- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'
    -- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5091'
    GO

    :CONNECT $(PrincipalServer)

    ALTER DATABASE $(Database2Mirror)
    SET PARTNER = 'TCP://DEMO:5022'
    -- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'
    -- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5092'
    GO

    ALTER DATABASE $(Database2Mirror)
    SET WITNESS = 'TCP://DEMO:5024'
    -- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'
    -- SET WITNESS = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5090'
    GO


    but I can not find out the solution. Turn back to grafical interface, everything is done

    Please help!

    Friday, May 4, 2007 8:34 AM

All replies

  • Instance name is demo\partnera and when using alter you have mentioned only demo and not with instance name.
    Friday, May 4, 2007 1:48 PM
  • Thanks for your help!

     

    I also have thought as your suggestion, but searching in the web I can not find out how to add the partnera to the like (using tcp://demo/partnera did not work)

     

    Using graphical interface, the last step after everything are config there are no additional "instance name" add in, they just distinguise with the port

     

    Can you help?

    Friday, May 4, 2007 5:24 PM
  • When using mirroring everything is done via port numbers which is why the servers are reverenced as TCP://HostNameStick out tongueort instead of Server\Instance.
    • Proposed as answer by KenPearson Wednesday, June 15, 2011 7:45 PM
    • Unproposed as answer by KenPearson Wednesday, June 15, 2011 7:45 PM
    Friday, May 18, 2007 10:56 PM
  • I just spent 2 days trying to figure this out and I got it.

     On th Mirror reference the Primary server.

    ALTER DATABASE MYDB_04

    SET PATNER = 'TCP://PRINCPLESERVERNAME.DOMAIN.LOCAL:PORT#'

    On the Primary reference the Mirror server.

    ALTER DATABASE MYDB_04

    SET PATNER = 'TCP://MIRRORSERVERNAME.DOMAIN.LOCAL:PORT#'

    This is not stated correctly in the documentation the I have read.

    K9

     

     


    Its a Dog Eat Dog World
    • Proposed as answer by Bini_DBA Friday, June 29, 2012 1:31 PM
    Wednesday, June 15, 2011 7:51 PM
  • The account under this script executing from SQLCMD does it have enough permissions?
    http://uk.linkedin.com/in/ramjaddu
    Thursday, June 16, 2011 12:21 PM
  • Thanks Ken, you saved my day.   Everyone seems to miss the error message.
    Friday, June 29, 2012 1:29 PM