none
sql server 2012 mirroring error: alter failed

    Question

  • We have upgraded from MSSQL 2008 R2 to 2012 and I am
    encountering a problem trying to set up the mirrors again. I know the basic
    setup (no witness) and network connectivity is correct because I can mirror one
    of my databaes OK.  But for 3 other dbs, when I click Start Mirroring in the
    last step of the wizard, I always get the error message below.
    I am doing the backup on the principal and restore to the mirror in exactly the same way as on
    the successful mirror, and I can not see a difference ibetween the dbs in
    Properties/Options.

    I have banged my head and searched on this all day.
    Any ideas, anyone?

    ---------------------------------------------------------------------------------------------------------------
    Alter failed for Database 'MyDatabase>" '.  (Microsoft.SqlServer.Smo)

    An exception occurred while executing a Transact-SQL statement or batch.
    (Microsoft.SqlServer.ConnectionInfo)

    Database "<MyDatabase>" is not configured for database mirroring. (Microsoft SQL Server, Error:
    1416)
    ---------------------------------------------------------------------------------------------------------------

    Doug MacGregor MacGregor Computer Services Fairfield, Iowa

    Tuesday, July 17, 2012 3:13 AM

Answers

  • A few possibilities... Can you check your endpoint configuration is correct (i.e. tcp port number is not 1433 for mirror endpoint) via...

    SELECT 
      me.[name], 
      me.endpoint_id, 
      me.[state], 
      is_encryption_enabled, 
      connection_auth_desc, 
      port 
    FROM
      sys.database_mirroring_endpoints me 
      Inner Join 
        sys.tcp_endpoints te
      On 
        te.endpoint_id = me.endpoint_id

    ...also can you confirm on the principle that you don't have settings such as database auto-close or Transparent Data Encryption turned on, i.e run....

    select 
    	*
    from 
    	sys.databases
    where 
    	is_auto_close_on = 1 Or
    	is_encrypted = 1

    ...and of course double check that you have restored the full backup and log backup with norecovery.

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem

    Tuesday, July 17, 2012 3:37 AM

All replies