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

Answer 1

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

Answer 2

Could be an issue with SSMS

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/31/setting-up-database-mirroring-in-sql-server-2008-using-t-sql-when-the-database-is-encrypted-using-transparent-data-encryption.aspx

BTW,now that  you have upgraded to SQL Server 2012 why not trying AlwaysOn Availability Group

http://msdn.microsoft.com/en-us/sqlserver/gg508768.aspx

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 3

Thanks for the solution.  I was restoring the database with norecovery, but not the log.

---

Doug MacGregor MacGregor Computer Services Fairfield, Iowa