January 25, 2016 at 8:08 am
Hi All,
There is a job which has failed with below error message:
Description: "Login failed for user 'domain\accnt'.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Cannot open database 'xxxx'. requested by the login. The login failed.".
Then I checked the below messages in the errorlog at the time of the issue. Later after further investigation, found that this instance is Secondary AlwaysON replica, which job connects to.
Question is why I am seeing this Recovery messages for 'xxxx' database and because of which we are getting login failures. How does AlwaysON work? After sometime when we try to connect, we are able to connect to the instance. What is ALwaysON doing in the background on Secondary replica? Can anyone explain?
2016-01-24 14:05:11.840 Logon Login failed for user 'domain\accnt'. Reason: Failed to open the explicitly specified database 'xxxx'. [CLIENT: 10.xxx.xx.xx]
2016-01-24 14:10:15.200 Logon Error: 18456, Severity: 14, State: 38.
2016-01-24 14:10:15.200 Logon Login failed for user 'domain\accnt''. Reason: Failed to open the explicitly specified database 'xxxx'. [CLIENT: 10.xxx.xx.xx]
:
2016-01-24 14:10:50.390 spid27s Recovery of database 'xxxx' (5) is 0% complete (approximately 103090 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2016-01-24 14:11:10.390 spid27s Recovery of database 'xxxx' (5) is 0% complete (approximately 102843 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2016-01-24 14:11:30.400 spid27s Recovery of database 'xxxx' (5) is 0% complete (approximately 102978 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2016-01-24 14:11:50.400 spid27s Recovery of database 'xxxx' (5) is 0% complete (approximately 102967 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2016-01-24 14:12:10.400 spid27s Recovery of database 'xxxx' (5) is 0% complete (approximately 103295 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2016-01-24 14:12:30.410 spid27s Recovery of database 'xxxx' (5) is 0% complete (approximately 103605 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
:
SQL version : Microsoft SQL Server 2012 (SP1) - 11.0.3460.0 (X64)
Thanks,
Sam
January 25, 2016 at 8:37 am
Availability Groups is mirroring with new features.
Just because the server is a secondary replica does not mean the database in question is part of the AG, unless you verified that it is?
Once a database has been added to the AG you would not see any activity of the database being restored, as described by the example messages you have shown. You would likely only see this if the database was being added to the AG.
If you want to know more about Availability Groups you can see the Stairway Series on this site on the subject.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 25, 2016 at 9:35 am
Yes, the database is part AG.
January 25, 2016 at 11:10 am
its not a permission issue. the account has db_owner on the database. But something is missing here. Alwayson is a db mirroring which sends log records from Principal to Mirror(i.e. secondary replica) and during the application of log recs, it might take db to single user and perform the recovery and once recovery is done, db is online for user access. Thats what my understanding goes. Experts, could someone explain in detail whats happening here?
January 26, 2016 at 7:02 am
Can anybody share their thoughts or comment above understanding?
January 26, 2016 at 7:19 am
Sounds like you have a job that is trying to access a database participating in an AlwaysOn AG while that database is a secondary replica and not otherwise available. You have to manage this manually. Jobs with steps setup to run against databases in an AG need to be aware if its OK for them to execute. They also should not have T-SQL steps setup with a default database that is in an AG to avoid problems where the job step immediately tries to change contexts into a secondary replica and fails with an error message very similar to what you showed. Instead the job step should start in master or tempdb and use 3-part naming to reference database objects.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 26, 2016 at 7:42 am
Hi Orlando,
Thanks for the response. I am basically looking for 2 clarifications here.
In AG, does Primary replica will be sending log records constantly to secondary and applied on secondary replica database log file ? And if it applies log records constantly, does db recovery kicks in on secondary replica and any user connections to database is not allowed irrespective of if it is Readonly online secondary replica? Is my statement correct ? pl correct if my understanding is wrong?
or Secondly, is it a Failover could have happened and recovery might have been kicked in and so any connections to that db is not allowed during the process of secondary being primary?
January 26, 2016 at 2:57 pm
vsamantha35 (1/26/2016)
Hi Orlando,Thanks for the response. I am basically looking for 2 clarifications here.
In AG, does Primary replica will be sending log records constantly to secondary and applied on secondary replica database log file ?
Yes.
And if it applies log records constantly, does db recovery kicks in on secondary replica and any user connections to database is not allowed irrespective of if it is Readonly online secondary replica? Is my statement correct ? pl correct if my understanding is wrong?
AG/Mirroring sessions are not exactly like Log Shipping in terms of it needing to sever all connections before applying log records. By default user connections are not allowed against a secondary replica for the same reason one cannot connect to a database getting restored from a backup, namely that the database is in a state of restoring (although in AG-terms it is called Synchronizing). We can configure a readable secondary which puts special emphasis on the secondary replica to offer a transactionally-consistent version of itself at all times so it can be queried. It does this through the use of snapshot isolation. If the secondary replica is not configured as readable then no connections are allowed to the database, ever, until it becomes the primary or is setup as readable.
or Secondly, is it a Failover could have happened and recovery might have been kicked in and so any connections to that db is not allowed during the process of secondary being primary?
I think you are asking about when a readable secondary transitions to become the primary replica. I suspect, but am not 100% sure, that when the failover process starts the secondary replica would have to run recovery and therefore would not be available to existing or new connections, i.e. existing connections would be severed and new connetions would be blocked until failover completed and the replica became the primary. If the existing connections were from .NET clients with multi-subnet failover enabled then re-connections should be transparently re-initiated.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 26, 2016 at 10:27 pm
Thanks Orlando. That clears a lot of doubts.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply