We have about 76 company databases that we will be upgrading from GP 10 SP5 to GP 2013. My question is, can we do this as a gradual process by standing up a new SQL instance and moving the databases over as we coordinate with the business.
*This post is locked for comments
Jonathon -- while yes, you are correct, it is extreme to delete the database, and normally that would not be an option, but after discussions with the business and them questioning why it was created and what it was used for and the fact that there were only 2 transactions, they were fine with removing it all together. In our case, it worked out for the better. Our partner sent me the clear companies script that took care of this -- I was able to launch GP Utilities and the company in question was gone. Thanks so much for your response(s).
First I would say that deleting the database is going to the extreme but you want to do that then yes you would deleted it in SQL and then wrong the following script to remove all orphaned records in the Dynamics database for that company.
/*Revised May 2013 to work with Microsoft Dynamics GP 2013*/
/*This script is supported with Microsoft Dynamics GP 10.0, 2010 and 2013*/
/* Remove all references in the company master (SY01500)
for databases that do not exist on the SQL Server */
set nocount on
use master
declare @dbNames char(5), @GPSystem char(15), @Statement char(3400)
create table ##GPSystem (GPSystem char(15))
declare FindGPSystem cursor for
select name from sysdatabases where len(name) <= 5 and name not in ('model','msdb') order by name
open FindGPSystem
fetch next from FindGPSystem into @dbNames
while (@@fetch_status <> -1) begin
set @Statement = 'if exists (select 1 from ' + rtrim(@dbNames) + '..sysobjects
where type = ''U'' and name = ''SY00100'')
insert into ##GPSystem (GPSystem)
select DBNAME from ' + rtrim(@dbNames) + '..SY00100'
exec (@Statement)
fetch next from FindGPSystem into @dbNames
end
close FindGPSystem
deallocate FindGPSystem
if not exists (select 1 from ##GPSystem)
insert into ##GPSystem (GPSystem) select 'DYNAMICS'
delete from ##GPSystem where GPSystem not in (select name from sysdatabases)
declare CleanGPSystem cursor for
select GPSystem from ##GPSystem order by GPSystem
open CleanGPSystem
fetch next from CleanGPSystem into @GPSystem
while (@@fetch_status <> -1) begin
set @Statement = 'use ' + rtrim(@GPSystem) + '
declare @statement char(150)
delete SY01500 where INTERID not in (select name from master..sysdatabases)
declare CMPANYID_Cleanup CURSOR for
select ''delete '' + o.name + '' where CMPANYID not in (0,-32767)
and CMPANYID not in (select CMPANYID from SY01500)''
from sysobjects o join syscolumns c on o.id = c.id and o.type = ''U''
where c.name = ''CMPANYID'' and o.name <> ''SY01500''
order by o.name
open CMPANYID_Cleanup
fetch next from CMPANYID_Cleanup into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from CMPANYID_Cleanup into @statement
end
close CMPANYID_Cleanup
deallocate CMPANYID_Cleanup
declare companyID_Cleanup1 CURSOR for
select ''delete '' + rtrim(o.name) + '' where companyID not in (0,-32767)
and companyID not in (select CMPANYID from SY01500)''
from sysobjects o join syscolumns c on o.id = c.id and o.type = ''U''
where c.name = ''companyID'' and o.name <> ''SY01500'' and o.name <> ''syDeployedReports''
order by o.name
open companyID_Cleanup1
fetch next from companyID_Cleanup1 into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from companyID_Cleanup1 into @statement
end
close companyID_Cleanup1
deallocate companyID_Cleanup1
declare companyID_Cleanup2 CURSOR for
select ''delete '' + rtrim(o.name) + '' where companyID <> ''''' + rtrim(@GPSystem) + '''''
and companyID <>'''''''' and companyID not in (select INTERID from SY01500)''
from sysobjects o join syscolumns c on o.id = c.id and o.type = ''U''
where c.name = ''companyID'' and o.name <> ''SY01500'' and o.name = ''syDeployedReports''
order by o.name
open companyID_Cleanup2
fetch next from companyID_Cleanup2 into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from companyID_Cleanup2 into @statement
end
close companyID_Cleanup2
deallocate companyID_Cleanup2
declare db_name_Cleanup CURSOR for
select ''delete '' + rtrim(o.name) + '' where db_name <> ''''' + rtrim(@GPSystem) + '''''
and db_name <> '''''''' and db_name not in (select INTERID from SY01500)''
from sysobjects o join syscolumns c on o.id = c.id and o.type = ''U''
where c.name = ''db_name''
order by o.name
open db_name_Cleanup
fetch next from db_name_Cleanup into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from db_name_Cleanup into @statement
end
close db_name_Cleanup
deallocate db_name_Cleanup
declare dbname_Cleanup CURSOR for
select ''delete '' + rtrim(o.name) + '' where DBNAME <> ''''' + rtrim(@GPSystem) + '''''
and DBNAME <> '''''''' and DBNAME not in (select INTERID from SY01500)''
from sysobjects o join syscolumns c on o.id = c.id and o.type = ''U''
where c.name = ''DBNAME'' and o.name not in (''SLB10100'',''ERB10100'',''NLB10100'')
order by o.name
open dbname_Cleanup
fetch next from dbname_Cleanup into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from dbname_Cleanup into @statement
end
close dbname_Cleanup
deallocate dbname_Cleanup
delete SY40502 where BARULEID not in (select BARULEID from SY40500)
delete SY40503 where BARULEID not in (select BARULEID from SY40500)
delete SY40504 where BARULEID not in (select BARULEID from SY40500)
delete SY40505 where BARULEID not in (select BARULEID from SY40500)
delete SY40506 where BARULEID not in (select BARULEID from SY40500)'
exec (@Statement)
fetch next from CleanGPSystem into @GPSystem
end
close CleanGPSystem
deallocate CleanGPSystem
drop table ##GPSystem
set nocount off
go
OK, so I just talked to the business unit and apparently this troublesome database only has 2 transactions in it. They're fine with deleting this database so we can move forward with the migration. Aside from deleting the company in GP and deleting the database -- where else do I have to remove entries related to this database so that GP Utilities doesn't think it still needs to be updated?
Jonathon --
Well, for testing, we ended up standing up multiple servers and split the upgrade between the servers. The upgrade finished within about 25 hours with the exception of 1 company where I have the following errors:
Table (CLSF2.dbo.GL70500) did not have the correct structure prior to the conversion of this table.
Table (CLSF2.dbo.GL70501) did not have the correct structure prior to the conversion of this table.
Table (CLSF2.dbo.GL10111) did not have the correct structure prior to the conversion of this table.
Table (CLSF2.dbo.GL10110) did not have the correct structure prior to the conversion of this table.
Table (CLSF2.dbo.IV70500) did not have the correct structure prior to the conversion of this table.
This is the same company that had issues when I ran the pre-upgrade Account_framework_validation script. I was able to set the dex.ini file to synchronization=True, run GP Utilities and then rerun the script and it didn't return any results -- so I moved on with the upgrade and then ran into the failed upgrade on that company.
Hi Richard,
Just seeing how things are going. Let us know if you have any other questions.
Actually you can split these up. What you would need is multiple machines. What you could do is have GP installed on multiple machines and select say 5 or 10 companies to upgrade on the first machine and kick those off. Next log onto another machine and choose another 5 or 10 databases (not any of the ones you selected on the first machine). This way you would be upgrading multiple companies ant the same time.
Yes, create a duplicate of your live environment on a test server. Run the complete upgrade and make note of each database size after the upgrade. Go back to the live environment and extend each database size for log and data to be at least as large as the finished test size. When the company list to upgrade comes up in the live upgrade select the top 10 highest priority companies and leave the others unchecked. Let the upgrade run on these 10 only. When these 10 complete then run the upgrade again and pick the next 10 companies in the priority list. After those complete you might be able to select all the remaining lower priority companies and let them run on their own, or continue to upgrade in chunks. You do not need to upgrade only one company at a time. You can select several to upgrade and those will run one at a time as a group.
Keith McConnell
Thanks for your reply Keith. So you're saying copy all the databases over to do a test upgrade, note the size of each of the databases after the upgrade and then increase the production databases to accommodate the upgrade. The last time we upgraded GP9 to GP10, it took about 5 days to upgrade all of the companies -- that was with me checking every 1.5 hrs to see where the current company was at in the upgrade process and then starting the next company. Perhaps you are correct that the slowness may have been with the continuous expanding of the databases -- but regardless, I don't have another 5 day window to get this upgrade done this time.
Keith,
I have used this method as well. Expanding the databases is absolutely the way to go - it made a huge difference. I ran into trouble when the current installation was on an incompatible build for the upgrade. I had to upgrade all of the companies to a compatible build and then went ahead with the final upgrade prioritizing the companies.
Kind regards,
Leslie
We have about 100 companies and also experienced long upgrade times. I recommend you do a complete test upgrade and look at the database sizes after the upgrade is complete. If the live databases are increased in size enough to handle the upgrade before pushing the button then the upgrade runs much faster. I think much of the time involved is the server expanding the database size in pieces as the upgrade progresses. After we expanded the databases then we used a priority order for the companies upgrading the ones used most often first and the ones used least often near the end. As long as the intercompany entries are restricted to completed companies people can work in a company that has completed the upgrade process even while other companies are still being upgraded if that becomes absolutely necessary.
Keith McConnell
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (