Using the Named System Database Feature for Microsoft Dynamics GP 2013

This is a reposting of an article Aaron Donat originally wrote on my Developing for Dynamics GP blog.

After 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: 878449How 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

GPDatabaseUpdates.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.