If you have ever seen errors in your AOS Event Viewer Logs after a SQL Server Cluster or AlwaysOn Availability Groups Failover and wondered why it is happening here is a brief explanation.
Object Server 01:
[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table
'tempdb.DBO.t855_489F061A83074B41907007FFAE3B5D41', because it does not exist or you do not have permission.
DROP TABLE tempdb."DBO".t855_489F061A83074B41907007FFAE3B5D41
Dynamics AX 2012 R2 makes use of "persisted" tables in TempDB in SQL Server. This essentially means that they build Tales in TempDB that "hang around" for a long time. This is a little different than how most processes use TempDB as they usually will create a temporary object, use it, and then drop it within a single transaction. When a SQL Server has a Failover TempDB does not "come with it" to the new server node. The AOS has no idea that the SQL Server has had a failover and still assumes these temporary tables still exist and will try to query them generating these types of errors. The only way to get rid of them is to stop and start the AOS services. It means that those TempDB transactions are not safe through the failover
This was actually corrected in is a pre CU3 hotfix which worked KB 2729496. They added code to check for the TempDB table before querying it and if it is not they rebuild it on the fly (There are other actions but I want to keep this brief). This fix was then rolled into CU6 but due to a regression error it was broken.
For now the best practice on SQL Server Failover would be too manually or use a PowerShell script to automate the recycling all the AOS services after a failover. Agreed this is not a perfect workaround and removes some of the benefits of automatic failover but until a fix is released it will prevent many headaches with your users and AOS event logs filling with errors
Due to the complexity and pervasive nature of this issue we are still in the process of investigating all the potential causes. I will update this BLOG as more information on this issue becomes available.
This issue has now been corrected. The user will still see an error but AX will clean up the pool so that the user will not see any more errors after the initial error even if there are many tempdb tables in the pool. This does not ensure that the user will never see an error or that a batch job will not fail but guarantee that the AOS does not have to be restarted.
AX 2012 RTM
You will need to contact support and ask for KB 2920058
AX 2012 R2
You will need to contact support and ask for KB 2956617
I find it appauling that Microsoft's recommended high availability configuration for Dynamics AX doesn't actually serve any purpose at all because of this bug in the product. Our customer purchased an Enterprise license for SQL just to use AlwaysOn with Dynamics AX, and yet they still have to reboot their AOS's everytime a failover occurs, otherwise half the application stops working. What a joke.
Can you give us an example of this powershell script to refresh all services?
@Phil A, I completely understand your frustration as I helped a customer build an AlwaysOn Availability Group for Failover, ETL data loads for Data Warehouse, and doing back-up from only to find out that when it failed over, the AOS servers starting throwing errors left and right. The only consolation I can offer you is that it will be fixed in a later CU for AX.
@jaestevan, I will admit I am not a PowerShell guru, but if I find the time and find some commandlets I can "borrow" from I will write one and add it to the article.
get-service -name *AOS60$01* -ComputerName AOSSERVER1, AOSSERVER2, AOSSERVER3 | Restart-Service -whatif
start powershell with elevated rights. remove -whatif if you want to actually restart the aos services