create 7.5 test environment

[jpotucek]

Currently, I have a Production Great Plains 7.5 environment with some Custom Code, an EFT Module and a Third Party add-on for the Project Accounting Piece.

I need to build out a TEST environment so that I can test a 7.5 to 9.0 upgrade.

I'm not sure how exactly to go about it.

What I was THINKING that I need to do is perform an out-of-the-box install for Great Plains and the Thrid Party PA Module then create my Companies in Great Plains and then replace the GREATPLAINS program folder on my test box
with the one from the Production box and then restore my Production Dynamics and Company Databases to the test sql server instance and I should be good to go !!!

Can anyone please comment or make recommendations????

Also, I'm not sure how to give the users a second GreatPlains Install on their machine so that they can do testing and compare Production to test?????

 

[jpotucek]

any suggestions?

 

[Dukester0122]

we're preparing for a 7.5 to 9.0 upgrade in production after 2 months of testing 9.0 in SQL 2005. What you need to do on the test server is install SQL Server then GP then all customizations & third-parties. Upgrade GP7.5 to 9.0 using the guide provided by GP. You don't need to be in SQL 2005 to run GP9.0.

For the 2 versions of GP on the users terminal, we don't have any problems because we're using a mix environment of Citrix & Terminal servers. You need to tell us which environment you're in then we can provide you with options. Remember that the test environment should be part of the network/domain for users to have access to the application.

 

[Sniper187]

You are basically on the right track. Here is a document from the MBS website on how to transfer Great Plains from one server to another. This will work if you are running SQL 2K.




1. Load Microsoft SQL Server with the same sort order as the older server and Great Plains on the new computer.

2. Execute the Capture_Logins_70_2000 script against the DYNAMICS database on the old server.




/*
** Capture_Logins_70_2000.sql
**
** Creates a table called RecoveryMaster that stores the username and password.
** Insert the name and password from the master database for all Dynamics logins.
** This table will be used for recovery on the new server.
**
**
*/

/* Always recreate the RecoveryMaster table. */

SET QUOTED_IDENTIFIER OFF

if exists (select * from DYNAMICS..sysobjects
where id = Object_id('DYNAMICS..RecoveryMaster') and type = 'U')
begin drop table DYNAMICS..RecoveryMaster end
go
CREATE TABLE DYNAMICS..RecoveryMaster (
name char(15) not null unique,
password varbinary(256) null,
DEX_ROW_ID int IDENTITY(1,1) not null )
go


/* Now insert the information */
insert DYNAMICS..RecoveryMaster (name, password)
select name, password from master..sysxlogins
where name in (select USERID from DYNAMICS..SY01400)
and name not in ('sa', 'DYNSA')




3. Make a backup of the DYNAMICS database and Company databases on the old server.

4. Copy the backup file from the old server to a local drive on the new server where Microsoft SQL Server is installed.

5. Set up an ODBC connection to point to the new server.

6. Create a new DYNAMICS database by using Great Plains Utilities - be sure to use the same number of Account segments and digits as before (if not sure, query the SY003001 and SY00302 tables). Create the new Company database(s) on the new server using Great Plains Utilities.

7. In Enterprise Manager, restore the DYNAMICS database and each Company database backup file created above in Step 3. To do this, you must follow the instructions below:


/*
** Drop_Users_Dynamics.sql
** Drops all users from the DYNAMICS database.
** Drops the DYNGRP and recreates it.
*/
declare @cStatement varchar(255) /* Misc exec string */
declare @DynDB varchar(15) /* DB Name exec string */
declare @DYNGRPgid int /* Id of DYNGRP group */



/*
** Remove all users from the DYNAMICS DYNGRP group and delete the group.
*/
SET QUOTED_IDENTIFIER OFF

use DYNAMICS
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')

declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/
use DYNAMICS
if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end



/*
** Recreate the DYNGRP group in DYNAMICS.
*/
use DYNAMICS
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec ("sp_addgroup DYNGRP")
end




10. Execute the Drop_User_Company against each of your COMPANY databases on the new server. This script is for both SQL 7.0 and SQL 2000.

Note - You will need to replace %Companydb% with your Company database name for this script.


/*
** Drop_Users_Company.sql
**
** This script will remove all users from the DYNGRP in the company database
** specified. It will then drop the DYNGRP and readd the DYNGRP to the company.
** It will then add all users back to the DYNGRP based on the SY60100 table.
** NOTE: You will need to replace %Companydb% with the company database
** name.
*/



declare @cStatement varchar(255) /* Misc exec string */
declare @DynDB varchar(15) /* DB Name exec string */
declare @DYNGRPgid int /* Id of DYNGRP group */


/*
** Loop through all company databases, emptying the DYNGRP group.
*/
SET QUOTED_IDENTIFIER OFF

use %Companydb%
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')

declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/

if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end




/*
** Recreate the DYNGRP group in all company databases.
*/
use %Companydb%
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec ("sp_addgroup DYNGRP")
end







11. Execute the Create_SQL_Logins_70_2000.sql script against the DYNAMICS database on the new server.


/*
** SM_Create_SQL_Logins_70_2000.sql
**
** Drop and Recreates all Dynamics logins based off of the RecoveryMaster table.
** Adds users to the DYNGRP for the Dynamics database.
**
*/

declare @cStatement varchar(255) /* Misc exec string */
declare @DynDB varchar(15) /* DB Name exec string */
declare @DYNGRPgid int /* Id of DYNGRP group */



/* drops all Dynamics logins. */
SET QUOTED_IDENTIFIER OFF

use master

declare G_cursor CURSOR for select "if exists (select name from master..syslogins
where name = '" + USERID + "') begin exec('sp_droplogin ["
+ USERID + "]') end" from DYNAMICS..SY01400
where USERID in (select name from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

/*
** Recreate all Dynamics logins.
*/

use master
declare G_cursor CURSOR for select "sp_addlogin [" + USERID + "], NULL, DYNAMICS"
from DYNAMICS..SY01400
where USERID in (select name from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor


/* Adds users to the DYNGRP in the Dynamics database. */

use DYNAMICS
declare G_cursor CURSOR for select "sp_adduser " + USERID + ", [" + USERID + "], 'DYNGRP'"
from DYNAMICS..SY01400
where USERID in (select name from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

/*
** Update the login passwords with the backed up values from the production server.
** This has to be done in 'allow updates' mode because the syslogins system table
** is being directly updated.
*/

exec sp_configure 'allow updates', 1
exec ("reconfigure with override")

exec ("update m
set m.password = rm.password
from master..sysxlogins m, DYNAMICS..RecoveryMaster rm
where m.name = rm.name")

exec sp_configure 'allow updates', 0
exec ("reconfigure with override")



12. Execute the Add_Users_Company against each of your COMPANY databases on the new server. This script is for both SQL 7.0 and SQL 2000.

Note - You will need to replace %Companydb% with your Company database name for this script.


/*
** Add_Users_Company.sql
**
** Adds users to the DYNGRP for the Company database.
**
** NOTE: will need to replace %Companydb% with the company database name.
**
*/



declare @cStatement varchar(255) /* Misc exec string */

/*
** Add users back to the appropriate DYNGRP groups for the company database.
*/

SET QUOTED_IDENTIFIER OFF

use %Companydb%
declare G_cursor CURSOR for select "if exists(select * from
DYNAMICS..SY60100 a,DYNAMICS..SY01500 c,DYNAMICS..RecoveryMaster u
where u.name=a.USERID and a.CMPANYID=c.CMPANYID and c.INTERID=""%Companydb%""
and u.name='"+rtrim(USERID)+"')
exec(""sp_adduser ["+rtrim(USERID)+"],["+rtrim(USERID)+"],'DYNGRP'"")"
from DYNAMICS..SY01400
where USERID in (select USERID from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor



13. Execute the GRANT70.SQL (SQL 7.0 & SQL 2000) script against the DYNAMICS and ALL COMPANY databases on the new server.


/*
** Grant70.sql
**
*/

/*Count : 1 */



declare @cStatement varchar(255)



declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where (type = 'U' or type = 'V') and uid = 1



set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor



declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where type = 'P'



set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor




14. Execute LOCKS.SQL in Query Analyzer to recreate the dex_lock and dex_session tables in the Tempdb database. You can find this script in Windows Explorer in Great Plains folder | SQL folder | Utils folder | LOCKS.SQL.

15. Load any third party products or additional products (such as Human Resources and Fixed Assets, etc.) that are needed at the new server.