This is the fifth article in the #GPPT GP Power Tools: What’s in each module series? of articles.

It covers what is included in the Database Tools module. This module provides tools to backup and restore specific data as well as tools to ensure that your system is working perfectly with your SQL Server.

Introduction

The Database Tools module contains features for the database administrator, developer or consultant to transfer data between systems and to make sure the system is correctly setup after migrating to a new server, copying to a test server or before and after an upgrade. It also includes other tools to help ensure the smooth running of the Microsoft Dynamics GP system.

Benefits

Here is a quick summary of the benefits:

• Synchronize SQL Server settings to match Dynamics GP Companies and Users.
• Update Logins after moving to a new SQL Server or creating test environments.
• Minimize upgrade issues by validating system data and database table structures.
• Identify and fix issues with company record notes and note index values.
• Recover unused space and apply SQL compression to tables in SQL Databases.
• Generate friendly views and optimize Auto Procedures to maintain performance.

Features

Below is a list of the features for this module.

The following key is used to denote whether the feature is a Standard Mode (User level) feature (unmarked) or an Advanced Mode (Administrator level) feature and to what build of GP Power Tools the feature was added in parenthesis.

• (A) Advanced Mode Feature

XML Export (A)

This feature can be used to copy relatively small amounts of data between companies or systems. It is account framework independent and can be used to select specific tables (with optional SQL where clauses) to copy the exact data desired into an XML file. Use this feature to copy security settings from UAT to Live systems during new installs, or to backup data when testing code so you can restore back. Support engineers can also use this to request specific data instead of huge gigabyte sized backup files.

XML Import (A)

This feature is used to import data backed up with XML Export. You have the options to select which tables to import as well as defining whether the table should be cleared beforehand and whether to import duplicate records.

Database Validation (20) (A)

Checks Dynamics GP setup for users, companies and company access and makes sure that the appropriate settings in SQL Server for logins, databases and database users matches. It can clean up references to unwanted users and companies as well as ensuring all the security and access rights are granted correctly. It can look for and fix issues with incorrect references in the tables used by Dynamics Utilities during upgrades. It can find and fix account framework differences between the application, system database and company databases. Finally, it can validate and fix any differences in the table structures between the Dexterity Table Definitions and SQL Server. You can also use this tool to force users to reset logins or change Advanced SQL Server password options.

Build 28 added checks for the user’s schema to ensure it is set to dbo.

Build 29 added fixing for user access to non GP database if they is an issue with their database user records.

SQL Login Maintenance (27) (A)

Use this window to reset passwords and check or change password policy settings. New passwords can be entered or generated and emailed to users.

Password Reset Email Settings (28) (A)

Use this window to enable/disable the sending of Password Reset emails when the passwords are changed in Database Validation, SQL Login Maintenance or from the standard User Setup window.

Copy User Settings (28) (A)

This window can be used to duplicate user settings in the system database from one user to another user. It is very useful when a new employee starts and you want them to have all the same settings as the predecessor.

SQL Trigger Control (28) (A)

Use this window to check for and enable or disable SQL triggers for maintenance or troubleshooting. Disabled triggers can also be deleted if they are no longer needed.

Note Fix Utility (30) (A)

Use this window to identify and fix issues relating to the Note Indexes used for Record Notes.

Database Space Recovery (31) (A)

Use this window to reclaim unused space from Heap Tables in your Microsoft Dynamics GP database. It can also be used to apply SQL Server page compression on supported systems to significantly reduce the size of the database and improve overall performance.

Build 32 adds SQL Compression support for more versions of SQL Server as well as allowing selection between row and page compression. It even uses statistics and table types to make recommendations as to which type of compression would work best.

GenView SQL View Generator (32) (A)

This tool can be used to create SQL Views with user friendly names for the Tables and Columns as well as removing padded spaces from strings and formatting Date and Time fields to your regional setting preference.

Auto Procedure Optimizer (32) (A)

This tool can be used to update the existing zDP Auto Stored Procedures to avoid the known performance issue that can sometimes occur with the SQL SELECT TOP command. See this article for more information the issue and the fix used.

Login Control (33) (A)

The Login Control window added in Build 33 can be used to enable logging of failed login attempts, Lockout Periods after a number of failed login attempts, Multi Factor Authentication using an Authenticator application with One Time Passwords and even Single Sign On for domain users.

I hope this helps your understanding of GP Power Tools and what it can do for you. If you want more details than provided in this series, please download the GP Power Tools User Guide manual, which contains 919 pages of detailed description of every feature in the product.

You can also download and install the product, NFR (Not for Resale) Keys are available for partners and a free 30 day trial is available for customers (just install and follow the on screen instructions). GP Power Tools can also work with no registration keys if only the sample company is installed.

I firmly believe that all Microsoft Dynamics GP sites globally should have GP Power Tools installed, and the articles in this series will explain why.

David

12-Feb-2019: Updated after the release of build 26 of GP Power Tools.
24-May-2019: Updated after the release of build 27 of GP Power Tools.
11-Aug-2020: Updated after the release of build 28 of GP Power Tools.
13-Aug-2022: Updated after the release of build 29 of GP Power Tools.
22-Aug-2023: Updated after the release of build 30.1 of GP Power Tools.
19-Aug-2024: Updated after the release of build 31.1 of GP Power Tools.
23-Jun-2025: Updated after the release of build 32.1 of GP Power Tools.
01-May-2026: Updated after the release of build 33.1 of GP Power Tools.
02-Jun-2026: Updated Screenshots for Login Control with new wording about mapped Active Directory Names for Login Single Sign On and SQL Login Maintenance with Show Inactive Users checkbox.

This article was originally posted on https://www.winthropdc.com/blog.