Logo

RecIds, Database Log and Just-In-Time SQL access

In this blog post Maciej would like to share with you 3 aspects that we have learned during implementation of D365 Finance and Operations. All of these touch SQL server and are significantly different from AX 2012.

RecIds

RecId is a column added to every table in Dynamics F&O. Data type is 64-bit integer – values identify a record uniquely in a table. Since the RecId values have little meaning to us humans, we call a SQL index based on this column a Surrogate Key (AX 2012 documentation can be found here – it has not changed in D365).

Ever since I remember, RecId assignment has been handled by kernel – AOS was using SystemSequences table for that purpose. Among other information, the table would store the TableId and next RecId value; AOS would cache next 250 values for performance reasons:

 

When doing direct SQL data imports in the past, developers would generate RecIds based on this table and adjust the ‘NextVal’ value accordingly after the process was finished.

RecIds – the D365 way

The concept has changed in Fin & Ops. RecId generation is now handled by SQL using concept of Sequences, which were introduced in SQL Server 2012. There is a separate sequence for every table in the database (using TableId as suffix):

 

 

 

The sequence is then used in every RecId column definition in any table. Example from CustGroup table

 

 

Why Microsoft decided to use Sequences? As it usually is the case, performance is key: […]the next number for the column value will be retrieved from memory rather than from the disk – this makes Sequence significantly faster[…] (source)

The table SystemSequences is still there, but it looks like it is for backward compatibility only:

Database Log

This feature enables user to log inserts, deletes and updates to any of the tables in the system:

 

 

 

It has a Wizard which is extremely non-intuitive to use, as it requires you to know under which Configuration Key exactly has your table of interest been placed – and if you do not turn on System table names, then you might end up with such results:

 

 

After locating your table, you select which events are of interest to you. You get to choose insert, delete or update on any field:

 

 

In AX 2012 this would instruct the kernel to monitor the table during database operations. This could have performance impact on the system.

Once again in F&O engineers decided to harness the power of SQL and adding a table to Database Log adds a trigger in the underlying table itself:

 

The trigger will insert records directly into SysDatabaseLog table, whilst respecting any call to ‘skipDatabaseLog’ method. Note that RecId is not specified in INSERT INTO statement as it is handled by SQL using aforementioned Sequences.

Just-In-Time SQL access

You might be aware that Microsoft has been communicating for some time about restricting Remote Desktop access to machines hosted in Microsoft-manged subscription. Those are your Tier-2 (and higher tiers) machines seen in implementation project under Environments section:

 

 

 

In order to restrict the access, Microsoft will have to supply customers and partners with right tools to diagnose and fix problems from LCS itself, otherwise they will see a huge spike in Support requests and face very unhappy community.

As a step towards that, Microsoft introduces Just-In-Time SQL access, where you will be able to access underlying Azure SQL database for 8 hours (source)

[…]if you need to connect to the database on your Standard Acceptance Test environments for troubleshooting, create a support ticket and provide the following details:

 

Project ID: ID of the LCS project.

Environment ID: GUID available in the URL (EnvironmentID) for the environment.

Query that needs to be executed: Query that you want to execute.

This functionality will be self-service through Lifecycle Services (LCS) in early 2019. The access to the Azure SQL database will not be persistent; however, you can request access to the database directly through the Lifecycle Services environment details page. The process of getting access will be as follows:

  • Enable access to Azure SQL through LCS by whitelisting the IP address of the machine that you will use to connect to the Azure SQL database using SQL Management Studio.
  • After this is done, you can request access to see the database credentials through LCS by entering a reason for requesting access.
  • As soon as you submit the request, it gets auto-approved. You will be able to see the database access credentials within a few minutes, on the LCS environment details page.
  • Note that the credentials are valid for 8 hours and will expire after that duration. After 8 hours the access expires and you will need to request access again. Additional information on how to perform the above steps will be published as soon as this feature is made available in January, through Lifecycle Services.