Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Using the Named System Database Feature for Microsoft Dynamics GP 2013

Using the Named System Database Feature for Microsoft Dynamics GP 2013

Rate This
  • Comments 13

Aaront Donat - Click for blog homepageAfter visiting with a group of Partners who attended the Technical Airlift this past week in Fargo, it became clear that the design intent behind the Named System Database feature and the perception of how it could be used in the field were not on the same page. With that, I decided that we really need to clear this misconception up and walk though the details of what the Named System Database feature can and can't do for you.

Bottom-line, the Named Database feature essentially allows you to now have a different database name for the DYNAMICS database.  Since our inception onto the SQL Platform, the system database has always been called DYNAMICS.  With the Microsoft GP 2013 release, NEW INSTALLATIONS now have an option to name their system database anything they wish up to a length of ten characters and so long as it conforms to SQL Server's naming requirements, ie...no special characters and doesn't start with a number. With that stated, please go back and read that last sentence completely again to ensure you understand what it says! 

I want to point out two key words there:  NEW INSTALLATIONS.  This would mean that if you are a current user of Microsoft Dynamics GP, there is not upgrade path or support for changing your system database name to something other than DYNAMICS and in reality, there is no benefit in doing so either.

The primary reason that Microsoft implemented this feature was to enable Hosting Partners the ability to host multiple customer data sets on a single instance of SQL Server. No longer would those Hosting Partners have to install a second or third instance of SQL Server on a physical host consuming resources with no apparent benefit. The secondary assumption made with this feature is that the Hosting Partner would responsible for maintaining and providing the system level admin work as well.  This essentially translates into:

  • Controlling access to the "sa" password
  • Controlling access the DYNSA login as there is only one DYNSA login that will be used for the instance of SQL Server
  • Providing additional SQL Server rights using a standard GP account by managing server and database security role membership.
  • Controlling access to SQL Management Studio and the query tool.

At this point, I hope that you are starting to get a better picture of what this feature enables and what it doesn't.  So if you are a single on-premise Dynamics GP customer, there really is no reason for you to move to this feature.  In addition, when you are planning your upgrade to Dynamics GP 2013, the upgrade is supported using the DYNAMICS system database. So at a minimum, you have to be ON Dynamics GP 2013 in order to take advantage of the feature, not upgrading to Dynamics GP 2013.

With that said, the business reasons as to why you would need to move to Named Database functionality are:

  • You currently have an on-premise deployment and want to move to a hosted environment
  • You have multiple physical installs of Dynamics GP and you want to consolidate down to a single install on one SQL Server (not company combining)
  • Hosting Partners needing to consolidate their current hosted deployments

and because these reasons do have merit, we needed to find a method of how to "migrate" an existing Dynamics GP 2013 customer into a Named Instance of Dynamics GP.  The steps documented below enable this possibility.  As of right now, there are no plans to build out a tool to enable such a process as the cost benefit of building and testing a tool when compared with performing what we consider, mostly SQL Admin work, is really difficult to justify. In addition, our impression is that these steps mostly apply to Hosting Partners and not the general Dynamics GP community. Either way, providing this information to everyone does have it benefits and I hope that you take the time to understand and dig into these steps.  It will only help you better understand the database to GP client relationship with each other.

Before we begin, there are several planning aspects that need to be in place prior to starting this exercise and those are:

  • Database name conflicts
  • GP login name conflicts
  • Administering Dynamics GP within the application
  • Granting access to SQL Management Studio

Below is more detail regarding each planning point listed above.  If you don't spend the time walking through each component, you will only have to deal with them once you attempt to perform the LIVE migration.

Database Name Conflicts:

For obvious reasons, we know that it isn't possible to have the same database name within a single instance of SQL Server. So you first will need to analyze the existing database names that you plan on migrating to ensure there are no conflicts.  If the TWO database is present, that database will have to be sequenced in order to restore it.  For example, TWO01 or TWO02.  In fact, DynUtils will auto increment the Lesson Company up to 99 times, ie...TWO99.  After the 99 instances of the Lesson Company, the installer isn't able to move forward and requires the end user to enter a NEW database name from the Lesson Company. The best/recommended choice in this case would be to delete the Sample Company from the existing system and then re-add it after migration so the re-sequencing occurs through DynUtils. But if you are understanding what I am really saying here and reading between the lines, I am mentioning the action of "renaming" of databases. The reason I point that out is because once you start getting a number of databases deployed on the SQL Server, overall management of those databases can become challenging.  Because of that, I would consider identifying a naming convention for each customer AND their company databases.  While the naming restrictions hasn't changed (still limited to 5 characters), changing the name of the database is something to consider. You might have to take action if there are duplicate company database names! One strategy of name consolidation would be if Company ABC has database XYZ, why not have all of their databases begin with the letter A and end up with a database name of AXYZ? From a SQL Management perspective, all of those customer databases are in order and grouped together.  Something to think about here!

I have attached a script located in the GPDatabaseUpdates.zip named GPCompanyUpdateDBName.sql and GPSystemDBCompanyRename.sql  These statements will traverse through the available tables within the company database and update INTERID value for the columns:

  • DB_NAME
  • DBNAME
  • db_name

Please be sure to read the instructions at the top of each script. If there are additional products that carry the INTERID value within a different column, the attached script will need to be modified or manual updates will need to take place.  Again, this script is used for renaming a company database to something different. You will need to make a change in the script and provide the current system database.  There is a WILDCARD that needs to change:  [%SYSTEMDB%]

**Note: Making this change will  have an impact on other items such as existing SQL Jobs, Management Reporter, Web Services, eConnect and so on.  Be sure to perform this action in a test environment first before executing a production move.

GP Login Name Conflicts:

Identify the GP User name conflicts prior to any system database renaming. If action isn't taken prior to the movement of the system database, dealing with the fall out on the destination SQL Server isn't going to be easy. In thinking through this process, we recommend if conflicts are found regarding user accounts, the following steps should be taken:

  • Create a new user account that is distinct on the existing system and not on the destination SQL Server
  • Grant the same application security access as the duplicate user account.
  • Delete the duplicate user account from with Dynamics GP.
  • Delete the SQL Login from the SQL Server to ensure it isn't present.

When considering the Lesson User accounts, if those are present within the source databases, those should also be removed entirely from the source SQL Server by deleting them through the application.  There can only be ONE SET of Lesson User accounts and the Dynamics GP 2013 Dyn Utilities doesn't create multiple versions of these accounts.  So in all likelihood, what needs to happen in this case is create normal users as you would and use another new feature in Dynamics GP 2013. This new feature allows you the option to mark a user as a LESSON USER instead of a Full or Light user and won't impact your registered user count.  These new users would then be granted access to the Lesson Company by default.

Administering Dynamics GP within the application:

This topic does become a bit more black and white in terms of what you are able to do within the Dynamics GP 2013 application. But with a focus on separation of duties with regards to what is considered an application focused task and what is system focused task can be different from company to company.  Because of this, breaking these duties down in writing so there is a clearly defined path for who is responsible for what definitely helps identify how best to manage the application and its supporting systems. There are many system level actions that can be managed within the Dynamics GP application today that don't require access to the 'sa' login or SQL Management Studio, to include the management of user accounts. Taking advantage of the SQL Server System and Database Roles can enable specific GP users accounts the ability to perform user management focused tasks within the Dynamics GP application. Unfortunately, this is an option not used by many as we have all grown up to utilize the revered 'sa' account.  In addition, even though the core product can use database roles for security access to specific tasks, there are still products out there today that require the 'sa' login, and PSTL (Professional Services Tools Library) is one of those.

With that, I am happy to announce that with Dynamics GP 2013, PSTL will no longer require access to the 'sa' login to perform functions.  The GP user accounts that performs tasks within PSTL today simply need to become a member of the database owner (db_owner) database role for the given companies and system database. YEAH!!! If you are still using applications that require the use of 'sa' (with the exception of DynUtils), please consider opening a support case with the owner of the product and inquire as to when those changes can be included. From a hosting perspective, this change in business logic is going to need to change otherwise, the hosting partner will have to be involved in these types of application focused actions that still require 'sa'.

Granting access to SQL Management Studio:

For customers wanting to get access to their data through SQL Management Studio, this is an option that can be accommodated. By using the built-in Database roles within SQL, it is possible to create an account and grant the correct level of permissions needed to access the appropriate databases. With that said, what would be needed to accomplish this is:

  • A new SQL Server login (Windows or Standard account).  Can't use their GP login as the password is encrypted and SQL Management Studio doesn't recognize the encryption.
  • Take the new SQL login and make a member of the db_owner database role for the system database and the associated company databases.
  • If backup permissions are needed for owned databases, add the SQL Login to the db_backupoperator database role. 

**Note: When taking these steps above, there is an implicit action taken called: VIEW ANY DATABASE. What this enables is the ability to SEE the actual databases being hosted in the instance of SQL. However, seeing the name of the database is the only action available since actual permissions are only granted to the specific databases needed.

Steps to move a Dynamics GP 2013 Deployment to a Named Database Deployment:

  • Upgrade to Dynamics GP 2013 on the existing SQL Server. Get good backups after conversion and before starting any migration work.
     
  • Remove any duplicate GP Users names that have been identified and create new GP Users along with setting security as required.
     
  • Capture the SQL logins from the source SQL Server using Knowledge Article: 878449
     
    How to transfer an existing Microsoft Dynamics GP, Microsoft Small Business Financials, or Microsoft Small Business Manager installation to a new server that is running Microsoft SQL Server (KB 878449)
     
  • Load the captured SQL logins from the source SQL Server onto the destination SQL Server. You will need to deal with any exceptions that occur during this process.
     
  • Restore the DYNAMICS database using a DIFFERENT DATABASE NAME (within the guidelines listed above) onto the destination SQL Server.  For example, when restoring the database, you can restore a backup and have the backup tool also create a new database using a new database name such as DYN02. One of the key steps during this process will be to ensure that the Physical file name (GPSDYNAMICSDAT.mdf) gets changed to match the new system database name.  An example of renaming to a system database name of DYN02 would be:  Current File Name: GPSDYNAMICSDAT.mdf   New File Name: GPSDYN02DAT.mdf   Same would also apply to the database log file as well.
     
  • Restore the company databases as required.
     
  • If database name conflicts exist or renaming of the company databases is planned, make those change at this point using the steps above at the time of restore.
     
  • Update the database compatibility level for each restored database to match the version of SQL Server you are going to.
     

SQL Server 2008/2008 R2:  sp_dbcmptlevel DYN02, 100

SQL Server 2012:  sp_dbcmptlevel DYN02, 110

 

  • Set the database owner to DYNSA using the script below for all restored databases.
         

sp_changedbowner DYNSA

 

  • Script out the following object from one of the company databases and make the required edits listed below:
    • For one of the company databases being re-assigned to a new system database, go into SQL Management Studio and locate the AccountTransactions SQL view
    • Right click on the view and choose "Script View as ALTER" to a new query window.
    • Once the syntax of the SQL view is visible, perform a FIND for the string DYNAMICS.  There are three instances of the DYNAMICS database name which is used like:  DYNAMICS.dbo.MC40200
    • For each instance of the DYNAMICS database name, you will need to change this to your new system database name.  For example: DYN02.dbo.MC40200
       
  • After modifying the SQL view, load the updated view against each of the company databases associated with the new system database.
     
  • Run the following script from the attached zip file to update the system database information to correct reflect the new system database name. Please be sure to read the instructions at the top of each script.

GPSystemDBUpdate.sql

GPSY00100Update.sql

  •  Run Database Maintenance tool against the new system database and all of the company databases regenerating all of the objects within the database.

**Note: Making this change will  have an impact on other items such as existing SQL Jobs, Management Reporter, Web Services, eConnect and so on.  Be sure to perform this action in a test environment first before executing a production move.

 At this point, all of the key pointers within the system and company database should be updated.  The next steps are to deal with the additional and integrating products that also have a dependency on the NAME of the databases. Once that is complete, log into the Dynamics GP client and begin your testing.

Looking forward to your feedback regarding this information. The first question probably will be something like: "Is this supported through the support team?"  In short, probably not. I am leaving this open primarily because as people go through these processes, if issues are encountered that forces a change to this content, we would like to know about it so the content can be updated for others.  But if the intent is to open a support case because you have a scheduled engagement that requires you to make these types of changes, getting full support for this probably won't happen. Because of that, making test runs through the entire process ensures that all required changes are made.  We are expecting the hosting partners will be doing as its supports their business directly. If you have any follow up questions or concerns, please comment on this post or feel free to email me directly and I will attempt to help the best I can.

Thanks!

Aaron

Attachment: GPDatabaseUpdates.zip
  • Posting from Mark Polino of DynamicAccounting.net

    msdynamicsgp.blogspot.com/.../using-named-system-database-feature-for.html

  • Fantastic article, thank you for this valuable and detailed overview. Keep up the great work.

  • Great article.  I just want to clarify that if you have multiple system databases, you will need a separate GP client install for each.  Otherwise how can the client distinguish between one and another since the ODBC doesn't specify the system database?  Either the GP client must have a separate instance for each system database, or there is a "broker" built-in to keep track of the different system databases (and I see no evidence of a broker).  In short, to have GP clients coexist, they will need to be separe named installations, or to have a single GP installation work with different systems requires separate SQL instances, each with its own ODBC, as achieved before 2013.  Please comment if I'm off-base.  Thanks!

  • Ryan,

    Yes you typically would have a separate install for each.

    The GP client knows which System database it should point to by looking at the Pathname= line in your Dex.ini and parse it to get the System Database.

    So in theory if you had multiple systems under named databases that were exactly the same, you _could_ hand edit that line before launching GP and switch between the instances.

  • Hi Ryan and Patrick

    You could have multiple dex.ini files, one for each system and control which one was used by passing it as a parameter in the shortcut.  You can also pass the Dynamics.set for your install so it is possible to have different modules installed as well and different sets of customisations.

    However, with this shared design you would not be able to have different versions or languages of a specific dictionary as they are all sharing the same files.  Also as VBA code is stored in the application folder, you would not be able to have different VBA customisations.

    Separate folders would still be best.

    David

  • Hi Dave,

    Could you elaborate on passing dex.ini file as a command-line parameter?

    One additional proviso to using the same install of GP for multiple instances:

    The Account Format must also be the same.  - or can that also be a command-line parameter where you pass-in the DYNFIB file?

  • Habib,

    Dynamics takes a number of command line params.  One that is used 100% of the time is "Dynamics.set" which it uses to load the products.

    So you'd maybe have your GP shortcut to be:

    dynamics.exe dynamics.set c:\data\patrick\dex.ini

    and not GP would use the dex.ini from the c:\data\patrick\dex.ini and not my GP\Data\Dex.ini

    There isn't anything for account format as that is a function of the dictionary data structures and not a setting.  When you choose to make your max account format 3 segments and 4, 4, 4; Dynutils physically changes certain fields in the dictionary to be those lengths and therefore the tables that uses those would be the same way.  Not something that is adjusted "on the fly".

    If you were considering having multiple GP installs but wanted to use the actual same GP client install, then you would want to set the account structure up to the max size it takes (i believe that is the default setting) and and/or add any additional sorts you think your install might require.

    Now once that is set, the actual Account Number size is set up per company up to the maximums set on the initial install of Dynutils- just like it has always been.

  • Patrick,

    It would be nice to have a document that lists the command line params that Dynamics accepts and explains how to use them.  Probably not very useful (considering no one seems to have needed it so far) be but who knows?  

    I had heard that the dictionary was changed when the account format is synchronized by Dynutils.  Glad to see it confirmed.  OK, so you can't pass in DynFib.  what is the purpose of that file anyway other than to get a quick peek at the account format?  

    From what you say, is it correct to infer that if I initially setup the account format to the max allowed -  66 long, 10 segs, max 10 then I could that dictionary in an install that has a narrower configuration?  My impression was that the account format had to match exactly or it wouldn't work.

  • Dynfib.txt is actually an error file.  I'm trying to recall what produces that but I believe it is generated when the application tries to disconnect from a datasource with files open.

    While possibly useful, it also happens when you run any process that could be sent to DPS (whether or not it DOES go to DPS or not).  

    So posting a SOP batch or printing invoices will generate it.

    The file is essentially a debug file to try to figure out why files are left open on logout.

    As for listing of command line params, I'm thinking there probably isn't a single spot - more like here & there.  I haven't seen such a list.

    the ones i recall are

    dynamics.set

    dex.ini

    macro file (which would play macro file on launch)

    /REGSERVER (register  the continuum library to this exe)

    /CNKONLY (launch runtine, include cnks w/o prompting and exit)

  • Forgot the account format question.

    Think you missed a word in there but in order to use a dictionary the dictionary framework (set in dynutils) must be the same as the framework for the tables.

    So if you install with "default framework" and I do as well, and assuming we are both the same version (ie 11.0.1752 for example) then i could give you my dictonary and you give me yours.  And my company frame can be whatever - still will work.  The reason that is so is because the account format is the same.  The display format for the company doesn't matter since that only goes to the max size chosen in Dynutils.

  • Nice job.  However when separating out system databases there are also the users to consider.  While one could go into the separated GP instance, and delete out the users which do not have access to the instance, this removes them from SQL server level also.  Any ideas or links on this?

    Thanks.

  • Hi John

    Are you saying that you have users which can access multiple GP instances on a single SQL Server Instance using the same User IDs?  That deleting a user from one instance, screws up their access to the other instance because the Login has been removed from SQL?

    I can see this can happen. However, the multiple instances were designed to have separate sets of users for each instance so they would not clash at the SQL Server level.  Maybe you should have different user IDs for each instance.

    You could log this as a product suggestion or a support case to get a bug logged.  I think the delete user code should confirm that the user has no access to any databases before removing the login.  That would then allow for the same user to be used on multiple instances without this issue occurring.

    David

  • Am working currently on a client and separating a single GP instance into multiple system databases on the same SQL platform.  We anticipate passing the responsibility for GP user Management to a user in each of the instances by making a GP user a sysadmin in SQL.  At this point, each of the separate system databases has a full list of all the original users in the original DYNAMICS database.  I was wondering if anyone has addressed trying to remove the users that do not logon to that instance without removing them from SQL.

    As I understand from the above article, separating an existing GP deployment into separate system database is not supported by MBS (?), so the subfunction of separating the users would not be supported either.  

    I simply wondered if anyone had approached this and if so, if they had a strategy to develop a script.

    Thanks!

    JC

Page 1 of 1 (13 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post