Sql Server Job Parameters
Dynamics GP Updating to Version 2010 Case Sharing Notes
This was interesting cases study, as we moved over two versions from 8.0 to 2010. Also new environment was deployed on the new Windows 2008 Server with SQL Server 2008 both 64 bit alternatives. There were about seventeen production companies and five test copies. There was also VBA script to export data from external database, form text file and launch Integration Manager in command line with parameters, specifying integration. This script was deployed as Shortcut. Plus, we had FRx Balance Sheet and P&L for each company with consolidated versions for the whole group, where some business entities were running another accounting platform and Excel worksheets were used in FRx reports. Release 8.0 was running on SQL Server 2005 with database compatibility level set to 2000 (as it was probably migrated from SQL 2000 somewhere in the past). Let's move over to details:
1. Migration 8.0 -> 10.0 Service Pack 5. Here we had first test migration, where we set in Dex.ini logs to be created. One of the companies was created with direct Customers migration from QuickBooks and we did this job. In order to migrate customers we imported them into Customer table in SQL 2005 Data Import Wizard. It looks like GP uses Customer table (by creating it in upgrade scripts and then removing). Conversion stopped and we were able to catch the problem in Dex log file. Our customer table was renamed and upgrade re-launched and all the companies were upgraded successfully without the need to do annoying data repair on SQL scripting level. Performance considerations: company databases were relatively small (below one GB) but the upgrade time was about 20 minutes in average for each company and even the smallest one took about the same time
2. Final Step 10.0 SP5 -> 2010 SP1. No issues at all, again each company consumed about 20 minutes. Both steps smoothness was a surprise for us, as we did some custom SQL scripting. For example we had template company database, which we loaded when new company was created. Then we had to delete History, Open, Work and some of the master tables (preserving Chart of Accounts). When you are doing such actions, you may expect the possibility of non-critical data inconsistency (which should not be a problem while you are exploiting the current version, but they might become an issue when you are conducting release update)
3. FRx. Customer made a decision to try migration to Microsoft Management Reporter. Version 8.0 was using FRx 6.7 Service Pack 5. As FRx is likely the candidate to be replaced by MMR in a matter of few weeks or couple of months, we decided to try the same FRx release (6.7 SP5) to be deployed for Dynamics GP 2010/11.0, and the approach worked. We are trying to be a bit conservative in deploying FRx in the new environment – we recommend to export Specification Set, install FRx with default setting, enter registration keys and then Import Specification Set. This method worked fine, however it requires some overhead – you have to create each FRx company manually (we usually create the first one and then clone and rename its ID, name and System Settings: Dynamics db name stays, you just need to change Company database). Our user workstations had Windows XP SP3 32 bit edition (so we were not exposed to FRx discussions of not being compatible with 64 bit Windows XP, Vista or Windows 7)
4. Some practical common sense advises. We scheduled update to begin on Thursday late afternoon. Test update finished on Friday, when system was frozen for new transactions and only open for reports and inquiries. Then Dynamics and companies database backups were copied on the new server and our consultants initiated production update at 5AM in the Saturday morning. Each step took four and the half hours and we installed GP 2010 user workstations in the client office Saturday afternoon. We recommend our customers to schedule version update project to be initiated at the second half of the week – in this case we have weekend time to fix possible issues
5. FRx versus Microsoft Management Reporter. Well, old good days FRx is still doing its job in generating Consolidated Financial Statements. However it's under laying technology is quite outdated. For example Specification Sets are Microsoft Access Databases (where .frl extension might be misleading, by you can open this file with MS Access, assuming that you know its encryption password). MMR naturally supports 64 bit Windows Server and Workstation (Vista, Windows 7, XP 64, plus you can deploy its client on Windows 2008 Server, including Small Business Server 2008 64 bit). FRx seems to be also installable on these OS versions, but Microsoft doesn't guarantee that and it doesn't support these installs. We are kind of in hesitation to recommend FRx to MMR conversion to each customer, as we do not usually like the software application with just one or two versions released. We rather recommend test migration, try all your reports to be printed out in FRx and compared with printed out versions from Microsoft Management Reporter. This approach has more chances to validate all the features you need in your Financial Reporting and if new reports design tool does the job (in this case Microsoft Management Reporter) – you are good to do switch in production environment
6. Integration Management Integrations. Some of them (SOP Transaction) were set to use eConnect adapter for IM 8.0. Looks like version 8.0 IM eConnect adapter was using direct SQL user login and password, while in 2010 eConnect user workstation you have to deal with COM object interface
7. Report Writer Reports. Be sure that you upgrade Reports.dic based modified reports – typically SOP Blank Invoice with your company logo. It looks like MBS is concentrating on such new technologies as SSRS and Word Templates, so the 8.0 SOP reports should be compatible with 2010 (you can try either upgrade or just import reports from old Reports.dic)
About the Author
Andrew Karasev is Great Plains Certified Master, MVP, [email protected] 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project http://www.efaru.com and founder of Alba Spectrum information space
Sql Server Job Parameters
New Features in GoAnywhere Director 3.5
Eenterprise – Old Great Plains Software ERP Application Reactivation
If you remember the XXI century story about Microsoft purchase of Great Plains Software – this is where eEnterprise, that was the last GPS ERP name invention ceased to exist in its MRP name consistency. Great Plains eEnterprise was renamed into Microsoft Business Solutions Great Plains Professional, later on in September 2005, Great Plains was renamed into Microsoft Dynamics GP. But anyway, if you or your company had hard time back in earlier 2000, then you might be on Great Plains eEnterprise or Great Plains Dynamics for Microsoft SQL Server versions 6.0 or 7.0. Let’s review eEnterprise upgrade and walk away paths:
1. DB Platform. eEnterprise was available on Microsoft SQL Server only, so more likely you are on Microsoft SQL Server 7.0 or even MS SQL Server 2000. So, considering the fact that high-end version of Microsoft Dynamics GP 10.0 or 9.0 is available on MS SQL Server 2005 and 2000 only – from DB perspective there are no challenges
2. GP Licenses and MBS Great Plains annual enhancement program status. Well, considering the fact that you are supposedly still on GPS eEnterprise ERP version, chances are high that you lapsed in Microsoft Business Solutions GP annual enhancement program. If our assumption is correct, then you will have to contact you Microsoft VAR to understand your annual enhancement reactivation options. If you have no idea on what we are talking about – call us
3. eEnterprise upgrade scenarios. If you are on Great Plains Software eEnterprise, more likely you have more than 10 Great Plains system manager users and so, you are valuable customer (meaning that you are large or mid-size business from the ERP perspective). If you didn’t downsize in 2001-2004, then your upgrade path is to Microsoft Dynamics GP Professional
4. Microsoft Dynamics C/S+. This is the predecessor of eEnterprise, C/S+ versions were 5.5, 5.0, 4.0 and 3.2. Those days Microsoft SQL platform was in the formation stage, so your version might be MS SQL Server 6.0 or alternative DB platforms: Btrieve or Ctree/Faircom. Your migration path is likely to be to Microsoft Dynamics GP Professional as well. If you are on non Microsoft SQL Server DB platform – you will need to migrate to MS SQL Server. The migration tool is available fro Microsoft Business Solutions, however it is not trivial and we recommend you to deploy GP technical consulting firm to do migration and version upgrade job.
5. Great Plains Dexterity customizations for eEnterprise. Dex programming was very popular in late 1990th, and this is not a surprise if you have Dex altered Dynamics business logic. Please review you Dynamics.set file to figure out if you have Dexterity custom logic and if so, try to find Dynamics.dic with your Sanscript scripts in not stripped out
6. eEnterprise custom reporting. The most likely you have GP ReportWriter modified reports: SOP Long Invoice or Blank Form, POP Purchase Order. RW is in fact Dexterity module and reports migration and upgrade require Dex architecture expertise and software development experience.
7. Dinosaur versions of Great Plains. GPA for DOS, MS Windows or Macintosh were discontinued to support in earlier XXI Century. If you are still on one of those, you got to consider walk away migration option: to Microsoft Dynamics GP would be the natural way, however you may theoretically migrate to Oracle, Sage, PeopleSoft, JDEdwards, Scala, or other ERP application
8. GP Licenses. You are probably familiar with ERP industry rules – besides initial MRP application purchase you have to be current and pay annual fee for ERP enhancement program. You are lapsed in enhancement program, you should expect re-enrollment fee. The GP re-enrollment fee depends on the number of lapsing years, and in some cases it is more beneficial to do new GP software licenses purchase, please contact your Microsoft Dynamics GP Great Plains reseller for details.
9. Dex Customization upgrade. If you have custom Dex logic in your eEnterprise installation version, you should not assume that modified logic update will be automatic. Typically Dexterity developers and programmers need to be involved to estimate upgrade efforts
10. Crystal Reports and Microsoft SQL Server Reporting Services SRS vs. Great Plains ReportWriter. RW is Microsoft Dexterity module, so you should not expect miracle from Report Writer interface. RW has access to GP Dex reports, stored in Reports.dic file, plus these mentioned reports have Dexterity parameters entry screens in regular GP logic. CR is Dex-independent tool and you should try to base Crystal Reports on SQL Stored Procedures and Views logic. The same should be said about SRS
11. Dexterity sanscript advise. Dex historically deploys cursor base logic in its Sanscript scripting language. This logic might not be optimal for MS SQL Server 2005 and 2000 platforms. Please consider calling MS SQL Server stored procedures from Dex sanscripts directly
12. eConnect programming. Again, eConnect was not available in 1999, eConnect was introduced for eCommerce developers in earlier 2000. eConnect is the set of encrypted stored procedures, assuming that you have already upgraded to GP 7.5, 8.0, 9.0 or 10.0
13. eEnterprise and Great Plains Software Dexterity traces. You should know that Dynamics.dic for eEnterprise is similar to the one for Dynamics for MS SQL Server. Dexterity places the following rules on MS SQL Server DB design: DEX_ROW_ID – this column is use in Dex internal logic, Dex.ini – here you place the instructions to Dexterity to log certain events
14. If Upgrade stalls. Typically this is due to inconsistent Company DB records. First consider check links rescue, however it doesn’t work in 100% of the cases. If you don’t have a luck with check links, then you will have to do manual data repair SQL scripting
15. Most Typical Pitfalls. GP Upgrade process is not easy and this is not because Microsoft Business Solutions has inefficient upgrade path, in the opposite – GP upgrade fails, due to the data inconsistency in your GP tables, typically in Purchase Order Processing: POP10110, POP30110 and IV10200
16. Manufacturing, Project Accounting, Fixed Assets special considerations. These mentioned modules were integrated with Great Plains Dynamics in late 1990th. PA was the result of MatchData purchase, Philippines base Dexterity development partner, GP Manufacturing was Icontrol Purchase, Fixed Assets module is the successor of Forestar FA application for GPS Dynamics.
17. Intellisol Advanced Purchase Order Processing and Project Accounting upgrade to GP PO Processing. To have you informed, Intellisol International, Australian based company tried to relocate to Fargo, ND in 1998. Then GPS negotiated with MatchData and Icontrol to purchase and incorporate their respective modules over Intellisol PA and Advanced POP. Intellisol APOP upgrade path is still available to GP 7.5, please contact us for further details
18. Mekorma Modules, This Los Angeles based software vendor was famous in GP secured check printing. Please contact Mekorma if your new intended for upgrade version of Mekorma GP is available
19. Local and State Tax calculation and filing partners, such as Avalara. Alba Spectrum initially helped Alalara to develop Avatax GP 7.0 and 7.5, later on Avalara decided to outsource Avatax development to India Great Plains Dexterity developers, where Alba Spectrum provided Avalara with Dex source code
20. French Canadian and European Dynamics GP ERP market. While French Canadian GP version 10.0 as well as Latin American Spanish version, German GP 10.0 version is scheduled to be phased out – in Germany and many of European countries Microsoft Axapta and Navision will be supported instead
If you like us to help you with GP upgrade, integration, software licenses, reporting, please give us a call
About the Author
Andrew Karasev, Alba Spectrum Group, http://www.albaspectrum.com [email protected] 1-866-528-0577, 1-630-961-5918, serving customers USA/Canada nationwide: Illinois, California, New York, Quebec, Ontario, Colorado, Utah, Wisconsin, Florida, Texas. Local service is available in Houston & Dallas: Richmond, Sugar Land, Katy, Rosenberg, Missouri City, Pearland, Friendswood, Meadows, Mission Bend, Jersey Village, Fort Worth; serving GP customers in Chicago, IL: Naperville, Aurora, Joliet, Wheaton, Bolingbrook, Romeoville, Lyons, Niles, Downers Grove, Lisle, West Chicago, Barrington, Schaumburg, Elk Grove Village, Lombard, Morris, Ottawa, Marseilles, Seneca, Oswego, Plainfield, Darien, Winchester, Hinsdale