Stopping Idle Microsoft Dynamics NAV Services

As a Microsoft Dynamics NAV partner we do a lot of development and testing. To support that we have a lot of NAV services running on a dedicated server. Even though the server is dedicated to that task and has reasonable resources it can still feel the strain under 30+ different NAV server instances.

I wondered if I could create a PowerShell script that would stop any idle services to free up system resources for the others. Since NAV 2013 we have had PowerShell cmdlets to find, start and stop services – so this would be a walk in the park, surely? Two problems:

  1. How to define ‘idle’ in this context. How should the script determine whether to stop a service that is running?
  2. Each version of the PowerShell cmdlets for NAV only works with service tiers of the same build.

Solution 1:

NAV has a “Session Event” table which records login and logout times for user sessions to the database. I am considering a service to be idle if it is connected to a database that has not been logged into for the last 3 days.

We can use PowerShell to fire a query to the database to retrieve that information, like this:

SELECT COUNT(*) AS SessionCount FROM [Session Event] WHERE [Event Datetime] >= DATEADD(d,-3,GETDATE())

If that query returns a 0 then no users have logged in to the database over the last 3 days and it is probably safe to stop the service that is connected to it.

Solution 2:

PowerShell has a powerful set of commands that allow you to start new sessions and execute commands in those sessions within the context of your main script.

New-PSSession creates the new session and allows you to save it into a variable. Invoke-Command can then be used with that session variable like this:

$childSession = New-PSSession –ComputerName COMPNAME

Invoke-Command –Session $childSession –ScriptBlock { “Hello, World!” }

We can make use of sessions to import different versions of the cmdlets in order to control different versions of NAV services.

The Script: yes, yes. Enough with the explanation, just give us the script so that we can copy and paste it. Here it is:

$modules = "C:\Program Files\Microsoft Dynamics NAV\70\Service\Microsoft.Dynamics.Nav.Management.dll", 
           "C:\Program Files\Microsoft Dynamics NAV\71\Service\Microsoft.Dynamics.Nav.Management.dll",
            "C:\Program Files\Microsoft Dynamics NAV\80\Service - CU3\Microsoft.Dynamics.Nav.Management.psm1",
            "C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.psm1"           

foreach ($module in $modules)
{  
    $childSession = New-PSSession -ComputerName <name of your server>

    Invoke-Command -Session $childSession -ScriptBlock {
        $query = "SELECT COUNT(*) AS SessionCount FROM [Session Event] WHERE [Event Datetime] >= DATEADD(d,-3,GETDATE())"       

        Import-Module $using:module       
        $serverPath = Split-Path -Path $using:module -Parent
        $serverPath = Join-Path -Path $serverPath -ChildPath 'Microsoft.Dynamics.Nav.Server.exe'   

        $serverExe = Get-ChildItem -Path $serverPath
        $productVersion = $serverExe.VersionInfo.ProductVersion   
        $services = Get-NAVServerInstance | Where-Object {$_.State -eq 'Running' -and $_.Version -eq $productVersion }
        foreach ($service in $services)
        {
            if ($service.Version -eq $productVersion -and $service.State -eq 'Running')
            {         
                if (Get-Member -InputObject $service -Name ServerInstance -MemberType Properties)             
                {
                    $instanceName = $service.ServerInstance
                }
                else
                {
                    $instanceName = $service.Name
                }

                $serviceConfig = Get-NAVServerConfiguration $instanceName -AsXml
                $result = Invoke-Sqlcmd -ServerInstance $serviceConfig.SelectSingleNode('configuration/appSettings/add[@key="DatabaseServer"]').value -Database $serviceConfig.SelectSingleNode('configuration/appSettings/add[@key="DatabaseName"]').value -Query $query -Username <SQL authentication login> -Password <SQL authentication password>

                if ($result.SessionCount -eq 0)
                {
                    Set-NavServerInstance -ServerInstance $instanceName -Stop
                    "{0} {1} Stopped" -f $instanceName, $result.SessionCount
                }
                else
                {
                    "{0} {1}" -f $instanceName, $result.SessionCount
                }
            }
        }         Remove-Module 'Microsoft.Dynamics.Nav.Management'
    }

    Remove-PSSession -Session $childSession
}

How it Works

  1. A comma separated list of NAV PowerShell cmdlet files is built at the start. Enter as many different files as you have different versions of NAV that you need to work with.
  2. For each different file in this list:
    1. Load the cmdlets with the Import-Module cmdlet.
    2. Find the Microsoft.Dynamics.Nav.Server.exe file in the same folder and determine the version attribute of that file, this is the build number that the cmdlets will work with.
    3. Find NAV services that are running and match the version of the Server.exe file found above.
    4. For each service found above:
      1. Read the SQL server and database name from the NAV server configuration.
      2. Execute the SQL query against that database to establish if the database is ‘idle’. I’m using SQL authentication for the connection to SQL, you could create the PowerShell session with Windows authentication and use that instead if you prefer.
      3. If there are no session event records for the last three days send a stop command to the server.
      4. Output the instance name and the number of session events. Include the word “Stopped” if the instance has been stopped.

image

I’ve created a Windows scheduled task to call this script once a day. When we have a service running on a new cumulative update I just add that build to the comma separated at the head of the file.

Leave a Reply