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

Winthrop DCThis 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:

Windows Explorer

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:

Dynamics Central Properties

Click Share this folder and then on the Permissions button:

Advanced Sharing

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

Permissions for Dynamics Central

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

Dynamics Central Properties

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:

Logging Settings

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):

SQL Profile Trace Settings

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 password

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

SQL Server Profile Trace Settings

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

Microsoft Dynamics GP - Do you want to create the SQL profile Trace SQL Components?

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

SQL Server Profile Trace Settings

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.

What should we write about next?

Your Name (required) –
Your Email (required) –

Leave a Reply

Your email address will not be published. Required fields are marked *