Microsoft Dynamics GP & SQL Server Together Equals – Security, Reliability , Scalability

Microsoft Dynamics GP is built around Microsoft’s SQL Server database platform.  What does that really mean and why is it significant?    

Microsoft Dynamics GP implements multiple levels of security.  First, the application itself requires a SQL Server login.  This is a user account created with the GP application with an encrypted password that can only be decrypted by the GP application.  What this means is that this user id / password combination cannot be used outside the GP application.  In addition, all the GP database objects are accessible and manipulated only by the DYNGRP role.  So unless a DBA has made another SQL user a member of this role or explicitly granted permission to specific objects, the GP data is not accessible to just anyone – even if they have access to the database in a guest or public role.  In using SQL Server, you not only have the security built into the GP application, you also have the security built into the SQL Server database. 

A main reason Microsoft GP (and many other applications) use SQL Server is for the reliability, fault tolerance and high availability of SQL Server.  Besides the need for an application, such as Microsoft Dynamics GP, to be developed with very few crashes coming out of the released product, the database that manages all that data for the application must be highly stable and reliable – which SQL Server is.  SQL Server is a “mature” software product that very rarely crashes.  So users expect the database to be up and running and available for use.  While everyone understands the need for backing up their data, it is possible (and may be required in environments that operate around-the-clock) to back up SQL Server while users are in the application.  Although this is not the most desirable scenario, there are many situations where it’s the only option.  In addition,  the SQL concept of “transaction logging” (a complete topic by itself) ensures that all “units of work” complete successfully together.  For example, when posting a Payables voucher, not only must the open payables be updated, but the outstanding vendor totals along with the General Ledger account distributions must be updated as well.  All these together are considered a single “unit of work”.   If for some reason they all do not update successfully, this “transaction” is “rolled back” to its unposted state as if the posting never started.  In addition, between database backups and transaction log backups, it is possible to restore data to a specific point in time.  For example, if a database is backed up at midnight and the transaction log files backed up every hour, should the system crash at 3:15pm, it would be possible to restore the database backup from midnight and apply each of the hourly logs to bring the database back to the way it appeared at 3pm.  This reliability and high availability is obviously very desirable in a critical “run-your-business” application such as Microsoft Dynamics GP. 

Another advantage of using a SQL Server database for an application such as Microsoft Dynamics GP lies in the fact it’s a scalable backend.  Microsoft SQL Server is still the backend database for GP whether it’s licensed as a single-user application or for a couple of hundred users.   And Microsoft SQL Server databases are used to house many terabytes of data; we now see Microsoft Dynamics GP databases of over 100 gigabytes.  So whether the number of users to start with is small or the number of transactions is small, the application and database are capable of significant growth without requiring a change to the application or a change to the database. 

What all this really means to an organization is the choice of the Microsoft Dynamics GP application and Microsoft SQL Server database can fit a company for a long period of time since the platform is stable, reliable and highly scalable as a business grows in both the number of users processing data and the volume of data being processed.

If you’re looking for specific Microsoft Dynamics GP or CRM information, you can also check out the resources on the Sherwood Systems website. And if you’re located in Arizona or New Mexico area, contact Sherwood Systems for assistance in determining if a technology solution can help your organization better serve your business needs. (877) 943-9696

by Ed Bonaski, Sherwood Systems an Arizona Microsoft Dynamics GP Partner

Related Posts

1. Microsoft Dynamics GP Achieves 18% Performance Increase Using Microsoft SQL Server 2008
2. Are You Part Of The Microsoft Dynamics “Community”?
3. Software vs. Services- What You DON’T Have To Give Up With Microsoft Dynamics