D365 F&O DBSync Error – ##[error]SYNCENGINE(0,0): Error Number: -2,State:0,Class:11

Summary:

 
While working with the D365 F&O build server, we suddenly started to get the timeout error on the database synchronization step. Checking the logs on the azure dev ops pointed out that, db sync was not able to complete the login phase for the sql server. 
 
This was odd, as the builds have been working fine until this point. This was a wild goose chase to check the settings of the sql server, hosts file, testing pings etc …
 

Build failed:

2020-02-23_14-54-38
 
This started happening after we took 10.0.8 quality update, or it was co-incidence – we are not sure yet.
 
Update 2/23/2020 10:15 PM CST:
 
  • Update a development machine (not a build) with the same updates and the auto close was false – so hopefully it was an one off. This is good !!! 🙂

Resolution:

 
It came down to the “Auto close” was set to “True”, after changing that to “False”, we can see that now builds are getting done successfully.
 
Some readings/ view on auto close feature in sql server
 
 
The hints pointed to to look at auto close were as:
 
  • DBCC Check was running again and again the AxDB, which normally shoudn’t happen.
  • It was seen that AxDB start event was coming in the Application logs too frequently. 
 
Hopefully it will help other folks as well, if such error is seen in the logs.
 
You can do this by SSMS UI or using following command on a database:
 
ALTER DATABASE [AxDB] SET AUTO_CLOSE OFF;
 
2020-02-23_15-04-32
 

Full error:

 
Build started 2/23/2020 4:06:12 PM.
Project “C:\DynamicsSDK\Metadata\SyncEngine.proj” on node 1 (default targets).
SyncEngine:
Database synchronization…
– Metadata binaries: K:\AosService\PackagesLocalDirectory
– Synchronization mode: fullall
K:\AosService\PackagesLocalDirectory\Bin\SyncEngine.exe -syncmode=fullall -metadatabinaries=K:\AosService\PackagesLocalDirectory -connect=”Data Source=BUILD-1;Initial Catalog=AxDB;Integrated Security=True;Enlist=True;Application Name=SyncEngine” -fallbacktonative=False -raiseDataEntityViewSyncNotification
System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=3; handshake=5; [Login] initialization=0; authentication=2; [Post-Login] complete=14746; —> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
##[error]SYNCENGINE(0,0): Error Number: -2,State:0,Class:11
SYNCENGINE : error Number: -2,State:0,Class:11 [C:\DynamicsSDK\Metadata\SyncEngine.proj]
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.b__0()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.OpenConnection(SqlConnection conn)
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.HandleException(ExceptionDispatchInfo edi, SqlExecutionInfo execInfo)
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.ConfigureSqlDatabaseSettings()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options)
ClientConnectionId:c49c708e-c380-48e9-b81a-b41cca10ad10
##[error]SYNCENGINE(0,0): Error Number: -2,State:0,Class:11
SYNCENGINE : error Number: -2,State:0,Class:11 [C:\DynamicsSDK\Metadata\SyncEngine.proj]
Inner Exception: System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
Database synchronization failed with exit code: -1.
Done Building Project “C:\DynamicsSDK\Metadata\SyncEngine.proj” (default targets) — FAILED.
 

D365 -Publishing Entities (BYOD)

Microsoft released the Dynamics 365 on November 01, 2016. With that Microsoft also release the VM for the solution as well – you can download it from https://connect.microsoft.com/site1321/Downloads

(You need to be registered in the feedback program to download)

After setting it all up, I tried to publish the entities to external database. It all looked easy, but while trying to do faced some exceptions as it was not able to find assemblies to actually execute the publish commands.

Below is the first error received, the SMO assembly for SQL Server was not loaded. error-loading-assemly

Now to resolve this follow below steps:

  1. You can find this and other related assemblies in the C:\AOSService\PackagesLocalDirectory\Bin folder for your local VM.
  2. After locating the assembly – drag it to c:\windows\assembly folder on your local VM. It will register it in the GAC. Don’t get confused by the assembly version in the properties of it. It will correctly display as 13.100.0.0  in the GAC.
  3. Restart the “Microsoft Dynamics 365 for Operations – Data Import/Export Framework Service” from windows services
  4. Reset IIS from Command prompt running in Administrator mode
  5. Refresh the Dynamics AX, and publish the entities again after selecting

publish-done

When you will refresh the page – you will see “published” column indicating the successful operation.

publish-indicators

The database chosen for this will have the Schema published for the selected entities. Please note the name of the columns will be different than what actually are in the actual AX tables. I am sure you can change them – but that’s for some next article.

Example: SalesTable.SalesId == SalesOrderHeaderEntityStaging.SalesOrderNumber … (In Staging)

sql-published

Next, you can use “Export” framework to export data to this database. Will find some time soon to go through further details on it. In the mean time please see Microsoft wiki on the topic. It’s a great help.

Until next time … Enjoy!

 

 

 

Handy Script for Checking Breakages in SQL Server Objects (Views, Stored Procedures etc …)

Hi,

Today i was working with some column removal and there were lot of stored procedures that i had to verify if they are fine or not.

So, wrote the following script to check each and every stored procedure for any breakages.

Copy and Execute on your database and enjoy!!!

 

DECLARE @spName nvarchar(1000)

DECLARE product_cursor CURSOR FOR

SELECT CONVERT(nvarchar(20), sch.name) + ‘.’ +  CONVERT(nvarchar(500), obj.Name)

FROM sys.sql_modules modu

INNER JOIN sys.objects obj

INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id

ON modu.object_id = obj.object_id

WHERE obj.type = ‘P’ — for stored prcedures… 

OPEN product_cursor

FETCH NEXT FROM product_cursor INTO @spName

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @spName

exec sp_refreshsqlmodule @spName

FETCH NEXT FROM product_cursor INTO @spName

END

CLOSE product_cursor

DEALLOCATE product_cursor