SQL Server 2012 exception Issue (i)--failover cluster + mirroring environment causes job execution to fail

Original: SQL Server 2012 exception Issue (i)--failover cluster + mirroring environment causes job execution to fail

First of all, thank you. My colleagues first found this issue, thanks: Xiangfei, Zhi-Gang, Hye-young

Recently found a strange problem in the production environment;

Environment: WINDOWS 2012+sqlserver SP1, two-node failover cluster + Single-node SQL Server SP1 instance (mirrored)

The production database was migrated from SQL Server 2008r2 to 2012, and the migration process was smooth and, as a general rule of thumb, could cause the database owner to be lost, so manually modifying the database owner to SA after migration, At the same time, there is a job in the archive of this library (regular cleanup of historical data to the local history database, delete, insert operations);

After the migration attempt to run the job can be performed normally, the next day to mirror the disaster preparedness, after the completion of the mirror environment, the archive job error: Unable to log on to the server "(local)."

After the test attempt to rebuild the job is also invalid, and then create a new job, only in this image of the library to execute "SELECT 1", the same error (it is said that select 1 is not logged, so it does not affect the image)

Finally, through the query Agent error log, the following error was found:

Indeed, the Multisubnetfailover parameter must be turned on for AlwaysOn properties before it can be used. But why would the SQL agent connect to the mirror instance through this connection option in a mirror-only environment?

In addition, I did two more tests:

Test A: On a single instance (non-clustered environment) that has the AlwaysOn option turned on, first turn on the AlwaysOn option, delete and rebuild the endpoint, and set up the mirroring environment;

Test B: Set up a mirrored environment on a single instance (non-clustered environment) that has never had the AlwaysOn option turned on (a newly installed virtual machine);

The above two tests, the job can be executed smoothly, did not appear previously in the cluster environment problems;

As a result, it is possible to exclude this issue from the impact of AlwaysOn options, but only to the cluster environment such phenomenon;

The current cluster environment is inconvenient to turn on AlwaysOn options, so you cannot do further testing to verify the above view, interested children shoes can continue to test;

PS: After Chen Sang Point, this problem can be solved by curve;

When the job step is created, the connection database is selected as a non-mirror library (such as Master), and the SQL to be executed is modified, and the object name is changed to Database.schema.object format;

SQL Server 2012 exception Issue (i)--failover cluster + mirroring environment causes job execution to fail