While I have not heard a single complaint about not being able to support Dynamics GP on ctree and btrieve anymore, there are some legitimate complaints about Dynamics GP not taking full advantage of Microsoft SQL Server. Understanding the evolution of an application helps explain the reasons for this and with every new version, Microsoft has been enhancing Dynamics GP to make more use of SQL Server functionality. However, it is important for implementers to have an understanding of the aspects of Dynamics GP behavior that do not always take full advantage of Microsoft SQL Server.

One key aspect that you may find surprising if this is the first time you are working with Dynamics GP is that it only uses SQL Server authentication. User logins created in Dynamics GP are automatically created in SQL Server and the passwords are encrypted. Security for all Dynamics GP functionality is handled inside the application itself and, as the SQL Server passwords are encrypted by Dynamics GP, you are not easily able to use the same SQL Server logins for any other purpose. While good for security, this makes it more difficult when integrating other applications and is important to keep in mind when planning your infrastructure.

Some tasks within Dynamics GP must be performed while logged in as the SQL Server sa (system administrator) user. Examples of these tasks are creating new Dynamics GP users, installing additional components and third-party add-ons, and running various tools provided by Microsoft for Dynamics GP. There are workarounds available for some of these, but they do not completely take away the need for using the SQL Server sa user in Dynamics GP.

Another remnant of the older database platforms is a SQL Server and Dynamics GP user called DYNSA that gets created automatically by the Dynamics GP installation process. This user does not need to have any rights within the application, but it is critical for this user to be the database owner of all the Dynamics GP databases. Even though day-to-day operations do not typically rely on the database owner, installation of new modules, creation of new companies, and installation of upgrades or service packs may fail if the database owner is not DYNSA.

When you install Dynamics GP, a global system database called DYNAMICS will be created. This database holds all system-wide settings such as users, companies, security, multicurrency settings, exchange rate tables, intercompany setup, and any other information that needs to be shared globally inside Dynamics GP. Active processes and logins are also held in the DYNAMICS database.

There is no limit on how many companies can be created in Dynamics GP. Every new company you create will be a new SQL Server database. The only limitation on this is for the database ID to be five characters or less and not to start with a number.

A sample company is available to be installed with sample data for many of the Dynamics GP modules. The database ID for the sample company is TWO and it is called Fabrikam. For anyone wondering about the strange database ID, in older versions of Dynamics GP the sample company was called The World Online.

Only two Microsoft SQL Server collation types are supported by Dynamics GP:

The recommendation for new installations is to use a DOCI collation. It will make Dynamics GP easier to work with both for users and administrators, it will also remove some limitations on integrating products.

Dynamics GP is a client/server application. All the data is centrally stored in Microsoft SQL Server databases and the SQL Server must be running and accessible to all client machines running Dynamics GP. The Dynamics GP application itself does not need to be installed or running on a server and administrative functions can be performed from any client machine where the application is installed.