Archive

Archive for the ‘DynGP’ Category

Setting up a Dynamics GP 10 test system

December 10, 2010 Leave a comment

One of the systems I manage is our implementation of MS Dynamics GP.  Our GP install is a behemoth and also at the center of our enterprise.  One of the many things that draw companies to GP is the ease at which you can customize the system and the vast number of third party software vendors there are out there to extend the functionality of it.  That all makes a flexible system that can change as your business need changes.  One of the draw backs I have seen with GP is the structure of the system.  Every GP system has at least 2 databases, the Dynamics database that takes care of your system wide settings, security, and defaults; and your company database that holds all of your company specific and transactional data.   In theory both the flexibility and the structure are a boon, but in practice they create some challenges when setting up a test system.

Our setup is a large one – in sheer size (company database is just shy of 1TB), number of users for GP (~200) and number of integrated software packages (we have 40 items listed in our dynamics.set file).  We are also making changes to our system at the rate of 1 a week.  These changes could be something as simple as a new piece of VB code at the client level or as complicated as a completely new integration depending on the business need.  So, how do you test all of this and ensure that you are only pushing quality changes to production?

Thankfully Microsoft Business Solutions has laid out (customer source login required) how to create a test company, and it works great if what you need to test is process and data flow.  If you need to test new code or something that will make schema changes to Dynamics the process they describe won’t do much for you.  In order to test how code or schema changes are going to affect your system you really need to setup a secondary GP instance on a separate server for test.  If you have integrated products that require additional servers in production you will want to duplicate those as well so that you truly have a mirror of production.

Now for the fun stuff…

The first thing you will want to do is build your TEST system using the same OS, patch level, version of SQL server, etc as your production system.  The only thing I don’t duplicate in my TEST system is the hardware.  It’s just not a good ROI for us to use identical hardware in TEST, as it doesn’t take the same kind of transactional load as my production system.  Once your TEST servers are up and your SQL instance is configured to match your Production system you can restore your most recent backup of Dynamics and your Company databases.  These restores are going to leave you with all kinds of orphaned users in both Dynamics and your Company databases that will need to be removed.  Depending on the number of users you have in Dynamics you will either want to drop them all thru SSMS or with a script.  Just use caution that you don’t remove SA or DYNSA from either database.   If you have previously used this SQL instance for a test system you will also need to drop the Logins from the SQL instance and potentially schemas depending what version of sql/GP you started with and how you have upgraded your systems.

Luckily I have handy dandy scripts to do that for you:



/* Use this script to remove database users from a Dynamics GP system to prepare the environment for testing.
This script was orginally provided by Microsoft Business Systems support and later modified by Meredith Ryan-Smith*/

IF @@SERVERNAME = '<Production server name, text, ...Production server name>'
BEGIN
PRINT '*** This code will NOT execute in Production***'
RETURN
END

DECLARE @UserId CHAR(15)
DECLARE @DatabaseId CHAR(8)
DECLARE @sql VARCHAR(4000)

DECLARE DatabaseLoop CURSOR FOR
SELECT name FROM master.sys.sysdatabases WHERE name IN
(SELECT INTERID FROM DYNAMICS..SY01500 UNION SELECT 'DYNAMICS')

OPEN DatabaseLoop
FETCH NEXT FROM DatabaseLoop INTO @DatabaseId
WHILE ( @@fetch_status <> -1 )
BEGIN

/*Inner Loop for each Database*/
DECLARE UserIDLoop CURSOR FOR SELECT USERID FROM DYNAMICS..SY01400

OPEN UserIDLoop
FETCH NEXT FROM UserIDLoop INTO @UserId
WHILE ( @@fetch_status <> -1 )
BEGIN

SET @sql = 'USE Dynamics IF EXISTS (SELECT * FROM sys.schemas WHERE name = '''
+ RTRIM(@UserId) + ''') DROP SCHEMA [' + RTRIM(@UserId)
+ ']'
PRINT @sql
EXEC (@sql)

FETCH NEXT FROM UserIDLoop INTO @UserId
END

CLOSE UserIDLoop
DEALLOCATE UserIDLoop

FETCH NEXT FROM DatabaseLoop INTO @DatabaseId
END

CLOSE DatabaseLoop
DEALLOCATE DatabaseLoop




/* Use this script to remove database users from a Dynamics GP system to prepare the environment for testing.
This script was orginally provided by Microsoft Business Systems support and later modified by Meredith Ryan-Smith*/

IF @@SERVERNAME = '<Production server name, text, ...Production server name>'
BEGIN
PRINT '*** This code will NOT execute in Production***'
RETURN
END
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.

*/

use %CompanyDB%

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

declare G_cursor CURSOR for select 'sp_dropuser ' + userid from dynamics.dbo.sy01400

where userid <>'SA'

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

set nocount off

Once you have restored your databases and removed the orphaned users from the databases you can begin the data cleanup.  The easiest way to accomplish this is to run a script that makes all of the changes needed to clearly identify this new instance as TEST and not your production company.  Fortunately for you I’ve scripted that as well:


/* Use this script after restoring the Production databases to TEST or DEV.
This script will prepare the system for new Great Plains ID's.

created by: Meredith Ryan-Smith
create date: 5/27/04
lstmodby: Meredith Ryan-Smith
lstmoddate: 11/11/09
lstmodreason:  to add template parameter prompts.
USAGE:

1.  Drop logins associated to GP users from the instance
2.  Drop all schema's other than DBO from both Dyanmics and all Company databases -- dropuserschemas.sql
3.  Drop all users other than SA from both Dynamics and all Company databases -- dropusers.sql
4.  Run this script
5.  exec sp_changedbowner 'sa' or 'DYNSA' on Dynamics and all company databases depending on which use you have decided on.
6.  Create users in GP
7.  Run GRANT70.sql on Dynamics and all Company databases
8.  If you have a credit card processing integration, or any other integrations those will still point to production.
You will have to modify those integration setups manually, or develop scripts to do that for you.
*/

IF @@SERVERNAME = '<Production server name, text, ...Production server name>'
BEGIN
PRINT '*** This code will NOT execute in Production***'
RETURN
END

DELETE
FROM Dynamics.dbo.sy00800
GO

DELETE
FROM dynamics.dbo.sy00801
GO

delete
dynamics.dbo.ASIEXP50 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.ASIEXP81 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.ASIEXP83 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.ASIEXP86 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.ASIEXP98 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.DT00100 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.EXT80100 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.EXT80200 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.EXT80400 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.LKACTVTY where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SLB11900 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SLB80000 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.STN41100 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.STN41300 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.STN41350 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY01301 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY01400 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY01402 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY01403 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY01404 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY01450 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY01600 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY01900 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY01990 where ScbOwnerID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY02100 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY02600 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY02700 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY04000 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY04400 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY05000 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07105 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07110 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07121 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07125 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07130 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07200 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07210 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07221 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07222 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07225 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY07226 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY08000 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY08100 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY08120 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY08130 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY08140 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY10500 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY10550 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY60100 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.SY70700 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.UPR10300 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.UPR10304 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.WDC41101 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.WDC41500 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

delete
dynamics.dbo.WDC51100 where USERID not in ('sa','DYNSA','SYSTEM','GLOBALUSER','dbo','')
GO

UPDATE dynamics.dbo.sy01500
SET cmpnynam = '<New Company Name, text, DEV>'
WHERE cmpnynam = '<Original Company Name, text, Production Company Name>'
GO

DELETE
FROM tbg.dbo.sy00400
WHERE userid<>'SA'
GO

DELETE
FROM tbg.dbo.sy01401
WHERE userid<>'SA'
GO

UPDATE TBG.dbo.sy01500
SET cmpnynam = '<New Company Name, text, DEV>'
WHERE cmpnynam = '<Original Company Name, text, Production Company Name>'
GO

Once you have run the data refresh script you, or the person responsible for managing GP users can login through the application and create the TEST users required, as well as point any integration points to the proper TEST machines and run the Dynamics Grant script to ensure that the dyngrp role has the access it needs.   This will be especially important for any credit card processing, shipping, or ETL third party packages you use.

Happy testing!

Categories: DynGP, SQL Tags: , ,