AX 2012 R2 - Minor issue (Startup Procedures SQL Server)

Most technical resources working with AX 2012 R2 are probably aware of the fact that AX deploys a Stored Procedure to the Master database to grant permissions to TempDb to the Service Account hosting tht AOS Service (instance). This is necessary since TempDb is created at startup, but be aware that the setup routine (AX AOS) adds one procedure for each AOS instance installed. Since these Stored Procedures are launched each time the SQL instance is started, it makes no sense to do this for each AOS instance. Let's say you have 10 AOS instances in Your environment utilizing the same Service Account. SQL Server will then execute the same statement 10 times during the startup sequence for the SQL Server instance. I discovered this by enabling Trace Flag 1204 and 1222 (records Dead Lock situations in the SQL Log).

Example (4 AOS instances, 1 on SQL Server used for full X++ compilation):

Stored Procedures created in the Master db:

 



Note that these Stored Procedures are identical if you use one Service Account for all AOS instances (would make sense to have one for each Service Account used).

Inside the Stored Procedure we find the following check:

if (@dbaccesscount <> 0) 

The variable @dbaccesscount holds the number of logins matching the Service Account found in TempDb. And it then grants the required roles for the Service Account if @dbaccesscount is not equal to 0... In my mind the criteria should simply be if (@dbaccesscount = 0).

You have the possibility to either change the logic (breaks the support rule?) or you could do like me and disable all except one of them. By disable I mean ALTER the procedure from launching as a Startup Procedure by running sp_procoption.

Again remember that You should have one Startup Procedure for EACH Service Account used, but it's not required to execute the same grant statement more than ONCE for each Service Account used.

Finally - Why bother? I like to have the systems clean and as efficent as possible, and it's not worth spending extra CPU cycles especially in virtual environments.

So long