Optimize the Inbox Accounting Database

Applies To: Windows Server 2012, Windows Server 2012 R2

You can use this topic to reconfigure your DirectAccess Inbox Accounting database to improve database performance.

The Inbox Accounting feature utilizes a local database that is installed on the DirectAccess server. If you are using the Inbox Accounting database with your Remote Access deployment, it is possible that the database is consuming too many CPU cycles and too much memory, diminishing database performance.

This topic provides the solution of adding a new index to a table in the accounting database to help improve the operations performance. This is accomplished by reducing the number of disk read and write operations that are conducted by several stored procedures that are called by the Remote Access Management Service during logging operations.

If you have a DirectAccess server and you are using Inbox Accounting, you can investigate database performance by reviewing the following symptoms against your server's performance.

• On the DirectAccess server, the process SQLSERVR.EXE is consuming a steady level of CPU cycles, on most occasions the majority of the CPU time for a particular core.

• The memory usage of the process SVCHOST.EXE hosting the RAMGMTSVC service is growing to very high levels and only settling back down during off-hours.

• Reports lag behind when querying for recent dates, and data from the last few days is missing from the reports.

You can use the following procedures to resolve these issues. In addition, you must perform these procedures on all DirectAccess servers in your deployment.

Membership in Administrators, or equivalent, is the minimum required to perform these procedures.

To Backup the Inbox Accounting Database

1. Select a disk location where you want to store your database backup, and then, if necessary, change the following information in the code below to include your database path and file name: C:\Windows\DirectAccess\db\RaAcctDb.bak

2. Open Windows PowerShell with Administrator privileges.

3. Copy and paste each of the following commands into the Windows PowerShell console, and press ENTER after each command.

   $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
   $connection.ConnectionString =  'Server=np:\\.\pipe\Microsoft##WID\tsql\query;Database=RaAcctDb;Trusted_Connection=True;'
   $command = $connection.CreateCommand()
   $command.CommandText = " BACKUP database RaAcctDb TO DISK='C:\Windows\DirectAccess\db\RaAcctDb.bak' WITH DESCRIPTION = 'Full backup before optimisation changes'"
   $connection.Open()
   $command.ExecuteNonQuery()
   $connection.close()
   

   After the $command.ExecuteNonQuery() is run, Windows PowerShell displays a value of -1.

To Apply Optimizations to the Inbox Accounting Database

1. Open Windows PowerShell with Administrator privileges.

2. Copy and paste each of the following commands into the Windows PowerShell console, and press ENTER after each command.

   $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
   $connection.ConnectionString =  'Server=np:\\.\pipe\Microsoft##WID\tsql\query;Database=RaAcctDb;Trusted_Connection=True;'
   $command = $connection.CreateCommand()
   $command.CommandText = "CREATE INDEX IdxSessionTblState ON [RaAcctDb].[dbo].[SessionTable] ([SessionState]) INCLUDE ([ConnectionId])"
   $connection.Open()
   $command.ExecuteNonQuery()
   $connection.close()
   

   After the $command.ExecuteNonQuery() is run, Windows PowerShell displays a value of -1.

You can use the following procedure to verify the previous procedure.

To Verify Inbox Accounting Database Configuration

1. Open Windows PowerShell with Administrator privileges.

2. Copy and paste each of the following commands into the Windows PowerShell console, and then press ENTER.

   $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
   $connection.ConnectionString =  'Server=np:\\.\pipe\Microsoft##WID\tsql\query;Database=RaAcctDb;Trusted_Connection=True;'
   $command = $connection.CreateCommand()
   $command.CommandText = "SELECT name from sys.indexes where name like 'IdxSessionTblState'"
   $adapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command
   $dataset = New-Object -TypeName System.Data.DataSet
   $adapter.Fill($dataset)
   $dataset.Tables[0]
   

   After you run these commands, Windows PowerShell displays the following output.

   name
   ----
   IdxSessionTblState
   

You can use the following procedure to optionally remove the optimizations that you made to the Inbox Accounting database using the previous procedures.

To Remove Optimizations to the Inbox Accounting Database (Optional)

1. Open Windows PowerShell with Administrator privileges.

2. Copy and paste each of the following commands into the Windows PowerShell console, and then press ENTER.

   $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
   $connection.ConnectionString =  'Server=np:\\.\pipe\Microsoft##WID\tsql\query;Database=RaAcctDb;Trusted_Connection=True;'
   $command = $connection.CreateCommand()
   $command.CommandText = "DROP INDEX IdxSessionTblState ON [RaAcctDb].[dbo].[SessionTable]"
   $connection.Open()
   $command.ExecuteNonQuery()
   $connection.close()
   

   After the $command.ExecuteNonQuery() is run, Windows PowerShell displays a value of -1.

You can also review the following list for additional updates.

• KB 2883952. Recommended hotfixes and updates for DirectAccess in Windows Server 2012 R2 and Windows Server 2012.