Downgrade Great Plains 9.0 DB from SQL 2008 to SQL 2005
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
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
ASKER
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.
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.
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
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
ASKER
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?
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?
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
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
ASKER
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
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
ASKER
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
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.
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
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.
ASKER
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.
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.
ASKER
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.
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.
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
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
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...
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...
ASKER
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.
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.
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
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
ASKER
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.
How did your project work out with the local GP expert. We are currently experiencing the issues with downgrading an 2008 R2 DB. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Instead, a single local Great Plains expert was able to do it in 16 hours costing less than $2000.
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.