>Why shouldn’t I shrink my Microsoft Dynamics GP databases?

February 13, 2011

>A client recently approached me with the question of whether they should shrink their Microsoft Dynamics GP databases to reclaim hard disk space, but instead of telling you what I think, I will demonstrate some of the issues arising from shrinking your databases:

Now to what I think…I have never been a big fan of shrinking databases to reclaim hard disk space — though, if you are running a dev environment where space is critical, then this may only be the one time. The problem arises from the way the shrink process occurs, and applies to DBCC SHRINKFILE, DBCC SHRINKDATABASE and the Auto Shrink setting in the database properties.

In summary, SQL Server goes to the end of a dabatabase file, picks up each individual page, then moves them to the first available empty space in the file. This process may reverse the order of your pages, turning perfectly defragmented indexes into perfectly fragmented ones.

So, let’s take a look with a test database in one of my client’s environments:

1. The first thing we will do is take a look at the stats on the GL00100 table by running the Microsoft SQL Server sys.dm_db_index_physical_stats function:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.
SELECT * FROM sys.dm_db_index_physical_stats

The following are the results from those stats:

Original database stats

 Now, in particular, I have highlighted the Average Fragmentation in Percent and the Average Fragment Size in Pages columns. Also, note that I have executed the function in ‘LIMITED’ mode, which combines information from all the columns that form part of each index. So, while these fragmentation levels would indicate low defragmentation rates — which is always desirable — it means there is still room for improvement. So, let’s see what happens after rebuilding the indexes on GL00100 for our test database, then rerunning the stats:

Stats after rebuilding indexes

As you can tell now, we have no fragmentation and our page size utilization went up – this is what we would expect after rebuilding indexes. So let’s see what happens when we run the shrink process on the database:


You can now tell that perfectly defragmented indexes now appear fragmented and even to a higher degree than what we started out with. These levels of fragmentation can cause serious performance issues in a production envrionment where database maintenance procedures are not properly planned and executed.

If you must reclaim hard disk space in your Microsoft Dynamics GP environment, please consult with your database administrator, but also keep in mind that storage is dirt cheap.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC


Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM

August 16, 2010

I have been involved for over the past 6 months with an extensive project requiring complex integrations between Microsoft Dynamics GP 10.0, Microsoft CRM 4.0 and other custom operational systems. In the process of designing and implementing these integrations the client requested a very easy to use interface that could be maintained without having to hire an army of developers or even specialized resources.

The mission: insert/update customer addresses and inventory items from Microsoft Dynamics GP into Microsoft CRM’s Product and Customer Address entities. The client also requested the integration be done using the Microsoft CRM web services in order to ensure upgrade support.


Beginning with SQL Server 2005, the components required to develop basic CLR database objects are installed with SQL Server. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:


You can find more information on SQL Server CLR integration over at MSDN. Be sure to check the following articles:

Overview of CLR Integration
CLR Stored Procedures


The solution can be broken down into two parts:

1. Creating the assembly with the CLR stored procedures that would in turn instantiate the CRM web methods to open a connection and insert or update the Product and Customer Address entity records.

2. Configuring Microsoft SQL Server and registering the assembly, creating the triggers on the RM Customer Address Master (RM00102) and Item Master (IV00101) tables that would invoke the CLR stored procedures to pass the Microsoft Dynamics GP records.

This week’s series will outline the solution with the code to achieve this. The following topics will become available on the day of their release:

08/18/2010 – Creating a CLR assembly and working with CRM web methods

08/20/2010 – Configuring SQL Server and creating table triggers

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Dex – Ctree temp tables versus SQL Server temp tables

July 30, 2010

For many of you who have been in the channel long enough, whether as developers, partners, or customers and have been using GP for that long, you may still be able to remember the days of c-tree and Btrieve.

Just recently, a developer approached my good friend David Musgrave with a question on a product they had created and been using since the days of GP 6.0. This product makes extensive use of c-tree temp tables and the developer wanted to know whether it was still ok to continue using c-tree tables or convert those to SQL Server temp tables. He received the following answer:

There is nothing wrong with c-tree temp tables. They are faster. SQL Temp tables take a bit of work to create so ctree has less overhead.

The only reasons to change them back to Default or SQL are:

1) You are using them on a report. This allows SQL optimization when generating the report with a single SQL Joined query.
2) You want to use SQL optimized functions such as range where clauses or range copy.
3) The number of records being placed in the temp table is huge and it could cause a local workstation’s hard disk to fill up.

Otherwise, just leave them as is. My policy is to use ctree unless one of the points above says that SQL would be better.

To add to this response, c-tree temp tables are still supported by Microsoft and are used extensively throughout the Microsoft Dynamics GP application. In the end, this may be one of those cases of “if it ain’t broken don’t fix it“.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Enforcing Password Policy with Microsoft Dynamics GP

July 16, 2010

The ability to enforce password policies was introduced since version 9 of Microsot Dynamics GP (see Why does Microsoft Dynamics GP encrypt passwords? over at Developing for Dynamics GP for more information).

Surprisingly, still many system administrators are not taking advantage of this feature, because they have found it difficult to manage without certain reporting necessary to follow up on Microsoft Dynamics GP logins activity. The typical complaints revolve around the lack of visibility on when a user password will expire or whether the account has been locked or not.

To make administrative tasks even more difficult, Dynamics GP systems administrators must rely on database administrators and Windows Server administrators to resolve any issues arising from a user being locked out the system, typically working their way through a helpdesk on a relatively simple issue.

With that said, I set out to create a query that could provide systems administrators with an insight into Microsoft Dynamics GP logins and their password expiration settings:


use master;
set nocount on;
declare @loginname varchar(200);

declare @logintbl table (
 LoginName varchar(20)
   ,IsLocked char(5)
   ,DaysUntilExpiration int

declare c_logins cursor for
  select [name] from sys.syslogins where name in
    (select USERID from DYNAMICS..SY01400);
open c_logins;

fetch next from c_logins into @loginname;
while @@FETCH_STATUS = 0
 insert @logintbl(LoginName, IsLocked, DaysUntilExpiration)
  ,case convert(smallint, LOGINPROPERTY(@loginname, 'IsLocked')) when 0 then 'No' when 1 then 'Yes' end
  ,convert(int, LOGINPROPERTY(@loginname, 'DaysUntilExpiration'));

 fetch next from c_logins into @loginname;

close c_logins;
deallocate c_logins;

select * from @logintbl;
set nocount off;

When the above query is executed in Microsoft SQL Server Management studio, it produces the following results:

LoginName            IsLocked DaysUntilExpiration
-------------------- -------- -------------------
sa                   No       NULL
DYNSA                No       NULL
LESSONUSER1          No       0
LESSONUSER2          No       NULL

Note that this query uses a table variable. If you are looking for a more permanent solution, you can replace the table variable for an actual table.

You may also use Support Debugging Tool’s SQL Execute option to run the above query — I have attached the configuration file for the script to be imported into Support Debugging Tool using the Configuration Import/Export option.

Many system administrators would also want to know when was the last time a user logged into GP, but unfortunately, SQL Server does not keep track of login activity, unless you enable some of the auditing functions. Another alternative is to enable Activity Tracking in GP and track all successful login attempts sorted from the most recent. You may then incorporate this information in the above query for a cohesive result.

Related Resources

Microsoft Dynamics GP Application Level Security Series @ Developing for Dynamics GP
The Scoope on Dynamics GP’s Application Password System @ Inside Dynamics GP


Support Debugging Tool XML configuration file – LoginPolicies.dbg.xml

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC


July 14, 2010

There are a number of good blog posts in the community explaining the differences between sa, Microsoft SQL Server’s defacto administrative user; and DYNSA, Microsoft Dynamics GP defacto administrative user. In reading a number of these articles I realized one thing: they address little about the DYNSA user.

So what’s DYNSA?

You only have to read back a few lines to obtain this answer, however, I want take another direction. Let’s start by saying that in Microsoft SQL Server, every database is created with a dbo user. Take for example this blank database created using SQL Server Management Studio:

The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role (like sa for example) who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

For example, if user DYNSA is a member of the sysadmin fixed server role and creates a table RM00101, RM00101 belongs to dbo and is qualified as dbo.RM00101, not as DYNSA.RM00101. Conversely, if DYNSA is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table RM00101, RM00101 belongs to DYNSA and is qualified as DYNSA.RM00101. The table belongs to DYNSA because he did not qualify the table as dbo.RM00101… and hear is the catch! Because DYNSA is not created as a member of the sysadmin fixed role (otherwise we would have a lot of angry database administrators), it must be associated to fixed database role db_owner, hence all object creation operations performed from Dynamics Utilities must qualify the object creation preceeded by “dbo.”.

The dbo user cannot be deleted and is always present in every database.

Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

•Belong to the user creating the object, not dbo.

•Are qualified with the name of the user who created the object.

This explains two things:

1) Only sa can perform the first time installation of Microsoft Dynamics GP, since this is when all databases, database objects, and the DYNSA user are created. During installation, DYNSA is made a member of the db_owner fixed database role.

2) In lieu of the sa user, Microsoft Dynamics GP system administrators can use DYNSA to perform any upgrade or maintenance activities. Since DYNSA is the defacto database owner, then it can perform any activities related to that database. This is the reason why you should ensure that DYNSA remains the database owner prior to initiating any upgrade activity. The following script should help with this:

1. Run the following making sure that DYNSA is the database owner:

sp_helpdb DYNAMICS;
sp_helpdb %COMPDB%;

2. If the above returns anything different than DYNSA, use the following script to set the database owner to DYNSA:

sp_changedbowner 'DYNSA';
use %COMPDB%;
sp_chagedbowner 'DYNSA';

In the above scripts, replace %COMPDB% for your company database name on SQL Server.

Related Articles:

Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Microsoft SQL Server 2008 Upgrade Advisor

June 2, 2010

With the release of Microsoft Dynamics GP 2010, I have been seing more and more questions regarding upgrading from earlier versions of Microsoft SQL Server — for example Microsoft SQL Server 2000 — to Microsoft SQL Server 2008. The questions usually comes in the form of “we are currently running [some prior version of SQL Server here] and they are ready to move to Microsoft SQL Server 2008. Is there a checklist in place for this upgrade? How does this affect GP?

To start, the reason you will not find much of a checklist on upgrading Microsoft SQL Server on CustomerSource or PartnerSource is because this topic does not have much to do with the Microsoft Dynamics GP application itself, but rather falls in the domain of the Microsoft SQL Server Team. As such, one of the cool tools available from the SQL Server Team is the SQL Server Upgrade Advisor.

SQL Server Upgrade Advisor is a wizard-based application that helps you prepare for upgrades to SQL Server 2008 or SQL Server 2008 R2. The Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade your database server.

Upgrade Advisor analyzes the following SQL Server components:

  • Database Engine
  • Analysis Services
  • Reporting Services
  • Integration Services
  • Data Transformation Services

While Notification Services appears in the Analysis Wizard, it is not included in Upgrade Advisor scans because it has been removed beginning in SQL Server 2008 R2.

You can find more information about Microsoft SQL Server Upgrade Advisor at the following links:

Using Upgrade Advisor to Prepare for Upgrades @ MSDN
Microsoft SQL Server Upgrade Advisor download page @ MS Download Center
Microsoft SQL Server versions and editions upgrade @ MSDN

Until next post!

Mariano Gomez, MIS, MCP, PMP, MVP
Maximum Global Business, LLC

SQL Mail to be deprecated in Microsoft SQL Server 2008 R2

May 27, 2010

If your organization is considering upgrading to Microsoft SQL Server 2008 R2 and relies on SQL Mail to generate Business Alerts notifications for Microsoft Dynamics GP, then this is for you.

With the introduction of Microsoft SQL Server 2008 R2, a number of database features will be deprecated, this is, the feature will not be available in a future release of SQL Server — SQL Mail being one of them. You can find the complete list of features that will be deprecated here:

Deprecated Database Features in SQL Server 2008 R2

A couple things about SQL Mail:

1. SQL Mail runs in-process to SQL Server service. The issue here was, if SQL Mail crashed so did your SQL Server. Certainly, not an optimal situation in a production environment.

2. Using SQL Mail requires installing an extended MAPI client component on your server. Most of you would have typically resorted to install Microsoft Outlook to overcome this situation, as without a MAPI client SQL Mail would not run.

So here comes Database Mail:

1. Unlike SQL Mail, Database Mail runs in a separate process thread. If Database Mail goes down, it will not take down SQL Server with it.

2. Database Mail does not require an extended MAPI client component for it to run.

3. Database Mail can be configued with multiple SMTP accounts and with multiple profiles. This increases the robustness of the system. In a realtime environment, if one mail server goes down, Database Mail will still be able to send emails through the other configured SMTP accounts.

4. In Database Mail, all mail events are logged keeping history of emails.

5. You can configure Database Mail to limit file sizes, disallowing large attachments and filtering of file extensions such as .exe or .bat

To configure Database Mail please check the following Microsoft video:

Video source: Technet (http://technet.microsoft.com/en-us/sqlserver/dd939167.aspx)

Be sure to include Database Mail setup as part of your SQL Server 2008 R2 implementation and to test all your Microsoft Dynamics GP Business Alerts in the process.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC