Solved

Downgrade Great Plains 9.0 DB from SQL 2008 to SQL 2005

Posted on 2012-04-13
17
1,064 Views
Last Modified: 2012-06-04
I have a GreatPlains 9.0 installation on SQL 2008R2. On that platform, I am unable to install SP4 for GP 9.0, which is required to be installed prior to being able to upgrade to GP10.0 or GP 2010.

Therefore, I have been trying to find ways to downgrade the GP databases to SQL 2005. I have a separate server setup that has SQL 2005 w/ SP4 installed. The two databases in question are DYNAMICS (the GP config and app DB) and KUTR (the actual company data).

I tried the script creation wizard in SQL 2008 to create a script that would create not only the schema, but also data on the SQL 2005 server. I selected options to continue on error, Script for SQL 2005, script schema and data. That worked except for one stored procedure that is encrypted. Then I executed that script on the SQL 2005 instance and it worked except the parts where it referenced the DYNAMICS database. At that point, I realized that the DYNAMICS database has to be migrated first.

Based upon the success of the previous technique, I presumed that I would have no issues scripting the schema and data in the DYNAMICS database into a SQL 2005 compatible script by using the script creation wizard.
However, that is not the case. The script creation fails right away with the error in the attached file.

Would you please look at the attached file and let me know what you think about why it is failing and what I can do about it?
I don't understand why using the script creation wizard would work for the KUTR database, but not the DYNAMICS database.
DYNAMICS-Failed1.html
0
Comment
Question by:Felicia King
  • 10
  • 4
  • 2
  • +1
17 Comments
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
I am trying to understand how GreatPlains allowed a 9.0 installation on SQL 2008R2 but cannot accept a predicate upgrade required for version 10.  

Are you sure this is correct?  I am not a Great Plains expert and I apologize for that.  But I was involved in upgrading our Sql Server supporting Great Plains to 2008 and assisted on the applciation upgrade to version 10.  The instructions for the version 10 upgrade included a number of options if we were coming from other versions.

Are you absolutely sure that you have gotten yourself in a catch 22 here and that Great plains does not have an alternative?  I would never downgrade my database unless explicitly recommended by the software vendor.  And specifying the ignoring of errors is not something that I would expect to cause anything but problems.  Rather than use database utilities, I would be more inclined to look for a Great Plains export/inport feature as that would be more likely to honor RI relationships and application requirements that you are unaware of. Using database utilities normally requires the person executing them to provide the knowledge necessary to load the tables in the sequence needed. Great Plains is a complex package that comes with detailed instructions for installs that any deviation from causes application problems.

Always, always call the technical support for the vendor of a third party product before you deviate from standard support practices for their product!  I really believe they are there to help you from making your situation worse.
0
 
LVL 4

Author Comment

by:Felicia King
Comment Utility
Microsoft does not support Great Plains 9.0 anymore. We are trying to get upgraded to GP 2010, but the GP utilities will not allow an update to even GP10.0 without SP4 for GP 9.0 installed. GP 9.0 functions just fine on SQL 2008R2. It has been that way for months.

Microsoft wants $10,000 to downgrade the database. They claim it will take 80 hours of labor, but refuse to state what it is that is actually the big deal. I have found a bunch of articles on the internet talking about ease of downgrade of a database from SQL 2008 to SQL 2005. If I could simply get the DBs downgraded, then I would be able to install SP4 on GP9.0, which would then allow immediate upgrade to GP 10 and then upgrade to GP2010. Then the databases could easily be put back onto SQL 2008R2.

Believe me, I have tried working with application support on this. Shelling out $10,000 is not an option. If that is the only technical option available, then we will use a logistical option and will start off our new fiscal year with GP 2010 new install and make general journal entries to set the balances in accounts.
0
 
LVL 18

Expert Comment

by:Steve Endow
Comment Utility
Hi,

Sounds like a tricky situation.

To your specific error, it looks like it is somehow trying to create the Dynamics SY01500 table twice.  You might want to check your scripts and see if it is listed twice for some reason.

Can you post the script?

My concern is that even IF you could get your GP 9 database re-created in SQL 2005, it may not be 100% 'perfect', and the GP 9 SP4 upgrade might fail.

What error are you getting when you try and apply GP 9 SP4?  

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 
LVL 4

Author Comment

by:Felicia King
Comment Utility
Error on GP 9.0 SP4 attempt to apply is that the service pack cannot be applied on that level of SQL server. Heck, GP 9.0 SP4 doesn't even understand the level of SQL 2008R2. It claims that the SQL server level version has to be higher.

I can't post the script because I am trying to use the script creation wizard in SQL 2008R2 to create a script that will create the database schema and data. If you right-click on the database, and then go to tasks, there is an option to create script. That opens up the wizard. I have been selecting the options to Script for SQL 2005 and script schema and data. Also selected to continue on Error. This worked great for the KUTR database, but will not work for the DYNAMICS database. The error generated by the script creation attempt using the wizard is the file that I attached.

I have tried dropping the database and attaching it to a SQL 2005 server where the GP 9.0 SP4 would understand the SQL server level. But of course, that doesn't work. The attach function claims that the database has been upgraded to a SQL version that is too new and cannot be attached.

This is what led to the entire approach to downgrade the database.

Do you think it would be possible to push the data using SSIS, BCP, or the import wizard? Would I have to have the schema created first?
I have considered those options and though that I could at least use the script creation wizard to create scripts that would at least create the database structure. Then I could push the data using SSIS or the other methods.

What do you think?
0
 
LVL 18

Expert Comment

by:Steve Endow
Comment Utility
Hi,

I did a quick check and I also found that it is apparently not possible to transfer or restore an upgraded DB to a prior version of SQL Server.

I guess one alternative would be to install GP 9 from scratch on your 2005 server and bring it up to your current service pack level (SP3?).  Then create new empty company databases with the same names as your production DBs.  At that point, you will theoretically have a matching environment on 2005, but with empty databases.  Make sure to make backups of all of those databases first.

Once you have backups, you can try to SSIS all of your data from your 2008 DBs/tables to you 2005 DBs/tables.  It will probably be incredibly tedious, but I would think it would be theoretically possible.

I essentially did this once for a client--they needed to create a new company database and wanted all of the same setup as their existing company, but needed a clean empty DB and didn't want any of the transaction data.  I created a new empty company, then manually transferred the setup tables and master tables for each module.  Very tedious, but it turned out well.  I think there might even be a KB article on Customer Source on this approach.

Let me know how that sounds.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 
LVL 4

Author Comment

by:Felicia King
Comment Utility
Steve, thanks for the idea. I did some more research based upon your suggestion. I found this as a basic guide for the SSIS data transfer: http://www.katieandemil.com/create-ssis-package-find-an-example-how-to-create-an-ssis-package-a-simple-one

I think getting the company data to transfer isn't going to be an issue. I actually think that the script creation wizard could handle that given the fact that it didn't error for me the way that the DYNAMICS database script creation wizard task did.

I really like your idea of installing GP9.0 to the same SP level as production on the SQL2005 server. Question: With SSIS will I need to create a separate package for each table, stored procedure, view, etc, or is there a way to simply create one comprehensive package to grab the whole kit and kaboodle and transfer it?
I'd like to try to do this for the DYNAMICS database as I think I can get the company data with the script creation wizard.

-looking forward to your feedback
0
 
LVL 4

Author Comment

by:Felicia King
Comment Utility
I have tried over a dozen different techniques this afternoon. None of them are working. I am also having issues getting GP 9.0 server install to actually generate a new Dynamics database. When I launch GP utils, it is looking for an existing Dynamics database. My only option is to create an empty DB and setup the ODBC connection to it. Then when I open GP utils, it connects, but then tells me that data is missing. Of course it is. I cannot lay hands on a GP 9.0 fresh install manual and seem to be unable to find articles on the internet that describe the process. Everyone refers to the GP 9.0 installation documentation, which I do not have.

I tried the copy database wizard. I got around four errors by continually creating accounts it claimed that it needed on the destination server. After getting through those errors, I believe I have run into another version incompatibility problem.

"errorCode=0 description=Warning: The SQL Server client assembly 'microsoft.sqlserver.types, version=10.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment."

So it appears I am unable to create a new installation of GP 9.0 on the SQL 2005 server even though I installed GP9.0 server software there.

I tried the SSIS transfer items, but it requires the schema and all the objects to already exist in order to transfer the data into, but you knew that.

I think I'm back to trying to conquer the issue as to why I am getting the error message in the file attached to this post. If I could figure out how to get past that, then I could script the schema and data creation no problem.

I don't understand why it is stuck on a dependency issue and how to get it past that issue.
Any suggestions are definitely appreciated.
0
 
LVL 4

Author Comment

by:Felicia King
Comment Utility
Discover dependencies failed regardless of whether or not I execute the generate script command using a domain admin account, sa, or DYNSA. Also it doesn't matter whether or not I select FALSE on "Generate Scripts for Dependent Objects".

With that set to FALSE, it should not be trying to check for dependencies.

I think I have eliminated a problem with table SY01500. It had a row that referenced a company database that no longer exists. I tried using GP Utils to delete the company, but it wouldn't do it because that old database no longer exists. I was able to delete the row in the SY01500 table that referred to the old company file. I tested GP application after that and am still able to get into both of the prod company files no issue.

Despite that, the generate script function on DYNAMICS database is still failing.

I tried a different tact. I tried an Export data function. This process looks very promising and does all the tables successfully except one. That table is SY01400. This is the database that contains all the user IDs, their perms and passwords. I get the following error:

Error 0xc002f210: Preparation SQL Task 12: Executing the query "CREATE TABLE [dbo].[SY01400] (
      [USERID] char(15) ..." failed with the following error: "A table can only have one timestamp column. Because table 'SY01400' already has one, the column 'SECACCS' cannot be added.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I find that error interesting as it suggests some progress. If this one error can be resolved, then the export data function would likely work. I reviewed the SY01400 table, and it looks like the SECACCS column is the only with one with type binary length 4.

LATER:
I figured out the problem with the SECACCS column. There were two columns in the database that were showing up as DATESTAMP type during the transfer, but were not really set that way.

Anyhow, the database export isn't going to work because despite getting past that error, I have run into some other errors that I cannot get past.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Author Comment

by:Felicia King
Comment Utility
A new question:
Is it possible that the best approach here might be to install a new installation of GP 2010, create a new company data file that is empty and then import the old company data into that new company data database? That is a variation of what you mentioned before.
I only have three users that need access to the database, so it's no big deal to recreate their accounts and security setup.
0
 
LVL 18

Expert Comment

by:Steve Endow
Comment Utility
Hi,

After you install GP 9 on the SQL 2005 server, you need to make sure that you have a 32-bit ODBC DSN that is pointing to a SQL 2005 instance that does NOT have a DYNAMICS database.

When GP Utilities is run the first time, it should tell you that the System database needs to be setup, and should create the DB.  Do not create an empty Dynamics DB.  If it does not prompt you to setup the system database, then something is wrong.  It should be a very simple automated process.

I don't think that trying to migrate your GP 9 data to GP 2010 would work.  Some tables would migrate, but others would not due to schema changes.  And even if the data physically transferred, it may not have correct logical values.  The GP 2010 upgrade performs hundreds (thousands?) of processes on the database and data to get everything upgraded, so simply pushing GP 9 data into GP 2010 tables isn't worth trying, IMHO.

I would recommend the clean GP 9 install on SQL 2005.

Screen shot attached of the GP utilities window that should appear when it is pointed to a SQL instance without a Dynamics database.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
gp9system.jpg
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
Ok, it sounds like you might really need to try and get a downgraded dataabse going.  if it were me, I would run the wizard to script out everything for the structures, procedures, triggers, users, etc..

BUT you do NOT run the parts to create the RI constraints until AFTER you have reloaded all the data.

AND you do NOT ignore any errors that come up when you try to bring the constraints back up. EVER!

Since you may need to do this quickly and multiple times if you have problems, my choice would be to use BCP for copying the data after you ahve verified the structures are ready..  

AND I would have scripts prepared to compare metadata for tables, indexes and and constraints.  (I have a few very basic ones I use and would be willing to share..  but you may already have a package that is much more robust.)  This is run after the 2005 creation, but before you try to bring up dynamics against the instance.

Remember to rebuild all views after the reload.  Full text indexes too.  Don't trust anything you have not done yourself.

Anyway thats my advice. And probably why MS said 80 hours for a guaranteed perfect conversion. They are estimating worst case and follow-up for any errors, I expect.  With care, using a checklist and not ignoring any warning signs along the way this works. Don't work when you are tired, or I expect you'll miss something and need to start from scratch. Its a boring painstakinig activity...
0
 
LVL 4

Author Comment

by:Felicia King
Comment Utility
Now I know why I couldn't get GP Utilities to create a new Dynamics database. I setup the ODBC connection, but presumed it needed to be connected to a specific database rather than just the master DB. I really appreciate your feedback regarding the impossibility of the import data into GP2010. I will continue to pursue the downgrade option. I think I'm going to have to get a GP expert to work with me via TeamViewer session at this point.

I would really like to find a solution as to why the generate scripts function on the production Dynamics database is producing errors even after I found zombie data and cleaned it up. I restored the database back to what it was prior to my cleanup operations when that didn't work.

Do you know of any GP experts that would be willing to consult on this project? No onsite visits would be required. It can all be done through TeamViewer sessions.
0
 
LVL 18

Expert Comment

by:Steve Endow
Comment Utility
Hi,

Your first point of contact should be your Dynamics GP partner (VAR).  They should be able to assist you with the process.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 
LVL 4

Author Comment

by:Felicia King
Comment Utility
I went to the VAR before. They were quoting 80 hours for the project, which is insane. I have since gotten in contact with a local GP expert who is estimating 16-20 hours for the project. We are working with him now.
0
 
LVL 1

Expert Comment

by:HLF_David
Comment Utility
How did your project work out with the local GP expert. We are currently experiencing the issues with downgrading an 2008 R2 DB. Thanks.
0
 
LVL 4

Accepted Solution

by:
Felicia King earned 0 total points
Comment Utility
We had great success with our local GP expert. I wouldn't call him a SQL management or database expert, but he is for sure a Great Plains expert. Total project took him about 16 hours. Essentially, he never downgraded the database. He left it on SQL 2008R2, but knew what fields to edit to *trick* Great Plains upgrader to believe that the database was on the correct server version and that the database was the correct service pack level.

I think there was a bit more to it, but the tricking was the general idea. I believe he also made the formatting changes to the database that were the equivalent of the changes that the service pack install would have done.
The current production version of Great Plains is 2010.

The migration was March 23, 2012. It is now May 30th, 2012. There have been no issues whatsoever with Great Plains at the customer site and they use it every day. Furthermore, the local GP expert was able to correct some zombie object issues we had also.
Below is the website for the company that he works for. I'm sure that he could actually do a project completely remotely and would be an asset to any GP migration.

I do think you should have a separate SQL administrator that is in-house planning and managing backups as well as regularized transaction log shrink scripts.

http://www.dblaze.com
0
 
LVL 4

Author Closing Comment

by:Felicia King
Comment Utility
The solution of tricking the databases to get around the service pack level requirements took a total of 16 hours and worked perfectly. This was absolutely the best option available because it cost only 16 hours of consultant time instead of the ridiculous sums of money that Microsoft wanted for the same work. Microsoft wanted nearly $10,000 for the database fix.
Instead, a single local Great Plains expert was able to do it in 16 hours costing less than $2000.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now