SQL Server login timeout on first attempt, second immediate attempt success

Was having a login issue today with SQL Server after the installation.
There're 2 servers, one for AOS and another one for SQL Server.
Both server running Windows Server 2008 R2.
The SQL Server has 2 named instance (one SQL Server 2005 and another one SQL Server 2008 R2), firewall enabled, Named Pipes & TCP/IP has been enabled, port for SQL Server has been opened.
(Ref: http://support.microsoft.com/kb/968872)
SQL Server Browser service is running.


When starting up the AOS, it failed with the error message below: 
Object Server 12: Fatal SQL condition during login. Error message: "[Microsoft][ODBC SQL Server Driver]Timeout expired"


Trying to connect to the SQL Server using SSMS (from another server) gives this error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)


First attempt to connect using SSMS failed with the above message, but on second attempt, it immediately connected, login success. It seems weird the login only timeout or failed on first attempt. This error can be consistently replicated, just close the SSMS, reopen and try login again, the error occur, then second login works.


Tried changed the "Remote Login Timeout" settings (Under SQL Server instance properties, on the "Advanced" page) to a higher number, but it doesn't help, the first login attempt still failed.


After further investigation, the reason was actually on the SQL Server Network Configuration.
The SQL Server is listen to dynamic ports.


Resolution: Change it to fixed port number
- Open SQL Server Configuration Manager
- Expand SQL Server Network Configuration
- Select the SQL Server instance you wanted to connect to
- Double click on TCP/IP
- Select the IP Addresses tab
- Under the IPAll group, remove the value in "TCP Dynamic Ports"
- Enter "1433" into "TCP" port (you can change the port according to your needs)


That resolve the issue.

SQL Server port settings

Standard AX data import & deletion

When using standard Dynamics AX 2009 import & export functionality for data migration, there's one thing that needs to be careful of - the default settings is deleting data before importing new data.

The deletion happens to the last two setup options:
> Clear table before inserting records
> Use definition group settings - "Delete and import"

This (deletion) is the default value on the table setup and during the import, unless it has been changed once and saved into usage data.

Unfortunately standard AX do the delete and import in two separate transaction.
Any error on import won’t rollback the transaction for the deletion.

Screenshot showing the transaction level, the deletion is in its own transaction.
DeleteAction is not executed as well, so no other table is affected.