Older versions of Dynamics GP, when it was still called Great Plains, supported installation on three different database platforms: c-tree, Pervasive PSQL (previously called Btrieve), and Microsoft SQL Server. Starting with version 8.0, Microsoft Dynamics GP is only supported on Microsoft SQL Server. With Dynamics GP 2013 the supported versions of SQL Server are 2008, 2008 R2, and 2012.
While I have not heard a single complaint about not being able to support Dynamics GP on c-tree 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.
Note
An excellent discussion on this topic can be found on the Developing for Dynamics GP blog:
Understanding how Microsoft Dynamics GP works with Microsoft SQL Server: http://bit.ly/18fvKo3
Understanding how Microsoft Dynamics GP works with Microsoft SQL Server continued: http://bit.ly/18fvTrE
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 and initializing 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 during 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 upgrades or service packs may fail if the database owner is not DYNSA.
A Dynamics GP ISV, FastPath, has a whitepaper on minimizing the use of sa in Dynamics GP which is available at http://bit.ly/15YoCLs.
When you install Dynamics GP, a global system database is created. In prior versions of Dynamics GP this database was forced to be called DYNAMICS. Starting with Dynamics GP 2013 you can change this name for new installations. The system 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 in Dynamics GP. Active processes and logins are also stored in the system database.
There is no limit on how many companies you can create in Dynamics GP. Every new company will be a new SQL Server database. The only restriction is for the SQL Server database ID to be five characters or less and to not start with a number.
A sample company is available to be installed with sample data for many of the Dynamics GP modules. The sample company is called Fabrikam and in versions prior to 2013 the database ID used to be TWO (because in older versions of Dynamics GP the sample company was called The World Online). Starting with Dynamics GP 2013 you can change this database ID to be whatever you would like within the naming restriction of five characters or less and not starting with a number.
Only two Microsoft SQL Server sort orders are supported by Dynamics GP:
The recommendation for new installations is to use the DOCI sort order. It will make Dynamics GP easier to work with for both users and administrators, and 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, optionally, some shared files on the network) 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.