Hands On With the GP Power Tools – System Module: Configuring SQL Profile Tracing With Recommended Settings

This post is part of the Hands On With the GP Power Tools (GPPT) – System Module series in which I am taking a hands on look at the varioues tools offered by GPPT.

In the last post, I gave an overview of the Manual Logging Mode and mentioned that the SQL Profile Tracing tool needs to be configured before it can be used.

There are recommend settings for the SQL Profile Tracing, which are configured as follows in this post.

The first step is to create a folder on a local drive of the SQL Server to store the traces as they are being produced:

I have created a folder called Dynamics Central and then a sub folder called SQL Profile Tracing as there are other files which will be shared with users.

The folder needs to be shared so all Microsoft Dynamics GP users have Full Control of the folder. Do this by right clicking on the folder in Windows Explorer and clicking on Properties.

Click the Advanced Sharing button:

Click Share this folder and then on the Permissions button:

Grant Full Control to the AD group containing the Microsoft Dynamics GP users or, as I have Everyone if appropriate:

Click OK twice to save the settings and return to the folder properties dialog. Make a note of the Network Path nad click OK:

A user account is required, which can be either a local user on the SQL Server or a Domain account, but in either case needs to have local administrator permissions on the SQL Server; it is also recommended, to have the account configured so that the password does not expire.

I have created a local user called sql.trace and set it as a local administrator.

Launch Microsoft Dynamics GP and log in using the sa account, or other account which has escalated privileges on the SQL Server. Open the Logging Settings window (GP Power Tools area page >> Setup >> Logging Settings). Click the Edit SQL Profile Trace Settings button:

Enter the user with local administrator permissions in the Windows Administrator User ID field (this is the fully qualified domain name; for a local user this would be the computer name\username):

When this field loses focus, you will be asked if you want to process the SQL Server actions to enable the Authenticatiom Mode for the current User ID; clikc Yes to proceed:

Enter the password for the entered account (please note, that the password is not validated at this stage, so be careful entering it:

Enter the name of the local folder created on the SQL Server and assigned permissions earlier:

When you tab from the field, you will be prompted to confirm the SQL Profile Trace Components should be created in the system database:

Enter the UNC path of the folder entered above in the UNC Network shared path to the above Folder field:

The Copy SQL Profile Trace files to the Logs and Export files location checkbox should automatically default to marked; if it isn’t do so yourself.

Click the OK button to save the changes and close the SQL Server Profile Trace Settings window.

Mark the Capture SQL Profile Trace checkbox and set the Trace Mode:

Click OK to save the changes and close the Logging Settings window.