SQL 2014 std does not support AlWays on Availability Group , You need Enterprise Edition, But SQL 2016 std edition supports basic availability group features
you can check the state of the mirror by using the query below.
this will give you a starting point for further investigations.
--==============================================================================
-- query that shows the current state of each database in the mirroring
--==============================================================================
SELECT db_name(sd.[database_id]) AS [Database Name]
,sd.mirroring_state AS [Mirror State]
,sd.mirroring_state_desc AS [Mirror State]
,sd.mirroring_partner_name AS [Partner Name]
,sd.mirroring_role_desc AS [Mirror Role]
,sd.mirroring_safety_level_desc AS [Safety Level]
,sd.mirroring_witness_name AS [Witness]
,sd.mirroring_connection_timeout AS [Timeout(sec)]
FROM sys.database_mirroring AS sd
WHERE mirroring_guid IS NOT null
ORDER BY [Database Name];
make sure the sql server service account has all the relevant permissions on both servers.
I am not sure of your environment, but depending on the query below, and what I could find in the logs, on a convenient time, plan a restarting of the sql server service (which will cause a failover) and back.
Another thing that has worked for me in the past, is scripting the whole thing, then dropping it, and then setting it up again through the script, one database a time.
that way you can try to narrow down the problem.
there is a very similar question:
Database Mirroring error, but everything looks OK
the answer recommends looking at the permissions. Do that first.
another thing is to do a failover following the example below:
(the original had many databases, I left only one DB per server, but you can get the idea)
--============================================================================
--instructions:
--for each server,
--1) get the list of the alter database set safety full
--2) paste that script below
--3) run it
--4) get the list of the alter database set partner failover
--5) run the first line make sure it goes through
--6) run the other lines - failover all the databases
--version 20140624 Anne and Marcello
--============================================================================
--============================================================================
-- before failing over
-- the safety mode should be changed to FULL (SYNCHRONOUS)
--============================================================================
select 'ALTER DATABASE '+quotename(db_name(database_id))+
' SET SAFETY FULL'
from sys.database_mirroring
where mirroring_role_desc='PRINCIPAL'
and (substring(db_name(database_id),1,2) IN ('US')
or db_name(database_id) LIKE '%ROW')
order by db_name(database_id)
*
-- sqlweblon3
ALTER DATABASE [US14AUTPProduct] SET SAFETY FULL
*
--sqlweblon4
ALTER DATABASE [USAccount] SET SAFETY FULL
--============================================================================
-- generate the failover scripts
--============================================================================
select 'ALTER DATABASE '+quotename(db_name(database_id))+' SET PARTNER FAILOVER'
from sys.database_mirroring
where mirroring_role_desc='PRINCIPAL'
and (substring(db_name(database_id),1,2) IN ('US')
or db_name(database_id) LIKE '%ROW')
order by db_name(database_id)
*
--sqlweblon3 (principal)
ALTER DATABASE [US14AUTPProduct] SET PARTNER FAILOVER
*
--sqlWeblon4 (principal)
ALTER DATABASE [ProductOfferROW] SET PARTNER FAILOVER
ALTER DATABASE [USAccount] SET PARTNER FAILOVER
ALTER DATABASE [USProductOffer] SET PARTNER FAILOVER
ALTER DATABASE [USStock] SET PARTNER FAILOVER
ALTER DATABASE [ProductOfferROW] SET PARTNER FAILOVER
/** IF YOU ARE FAILING FROM DR TO LIVE CHECK THE LITESPEED
ACTIVITY SERVICE IS RUNNING ON THE SERVER. IF IT IS NOT LITESPEED
SQL AGENT JOBS WILL FAIL **/
--============================================================================
-- after the failover change the safety mode back to ASYNCHRONOUS
-- this is to be done on what is now the principal
--============================================================================
-- DR-sqlweblon3
ALTER DATABASE [US14AUTPProduct] SET SAFETY OFF
