Creating A Test Environment

Creating a test environment requires that you do more than duplicate databases by backing up one company and restoring to a different database and company. Creating test companies within a live, production environment is highly discouraged.

AIT stores database specific information in it’s tables and “sees” all companies in your Dynamics GP system, so copying a company will contain data that is not consistent with it’s new database name, plus a test company still has the pointers to live companies, so you can still post from the test company to a live company and vice versa.  GP Agent requires system configuration setup changes, so your changes could impact users in a live company.

Because of this, creating a database with data copied from another database could copy records with invalid information and could allow cross-company transfers from a live to test company or vice versa. Also, creating an environment where database information is not truly identical is not a true test environment, because ANY set of differences can make a test fail that would otherwise pass in the live environment.

Updating AIT tables in the DYNAMICS database is not sufficient protection, because all company databases also contain columns that point to data in other company databases. Also, not all columns go by the standard column name, and in some tables, a column can refer to either a company id or some other piece of information, so simply updating the company id is not enough. Creating a test environment with just some of the companies is a possibility, but you will likely still have some errors when AIT looks for a database that you did not move to your test environment.

To create a test environment, you must create a separate instance of SQL Server and duplicate ALL databases that exist in the live environment or use a completely different server to host your test SQL Server.  If you decide that you want to create multiple companies by copying one database into another, make sure to do so before you have installed AIT or GP Agent, and run the “cleanup companies” script Microsoft provides for GP.

Here are the steps to creating a test environment:

1.Create a new instance of SQL Server with a new DSN.
2. Log into the new DSN with Dynamics GP Utilities. This will create the appropriate DYNAMICS database, logins and stored procedures required for Dynamics GP.
3. Drop the DYNAMICS database in the new instance.
4. Detach the DYNAMICS and all company databases from the production instance. If you do not want to use Enterprise Manager or SQL Management Studio and are comfortable with SQL Query, here is a sample script to detach multiple database quickly:

exec sp_detach_db ‘DYNAMICS’, ‘true’
exec sp_detach_db ‘TWO’, ‘true’

Copy the database files (.dbf and .log) from the Data folder of the production instance to the new instance.

5. Attach the copied database files within the new instance. If you do not want to use Enterprise Manager or SQL Management Studio and are comfortable with SQL Query, here is a sample script to detach multiple database quickly:

exec sp_attach_db @dbname = N’DYNAMICS’,
@filename1 = N’D:\MSSQL\Data\GPSDYNAMICSDat.mdf’,
@filename2 = N’D:\MSSQL\Data\GPSDYNAMICSLog.ldf’
exec sp_attach_db @dbname = N’TWO’,
@filename1 = N’D:\MSSQL\Data\GPSTWODat.mdf’,
@filename2 = N’D:\MSSQL\Data\GPSTWOLog.ldf’

6. Create user logins in the new instance. We recommend that you do not use the ‘sa’ login for user testing in the test environment. Privileges associated with the ‘sa’ login do not provide a true parallel test environment. Because of this, you will need to manually create SQL logins in the new instance.
7. Synchronize the logins to the database Users. Logins are different from database users when you copy databases, even though the login name might be the same. You need to synchronize the database users to the new logins in order to run Dynamics GP. Use the sp_change_users_login stored procedure to connect the new logins to the copied users.

If you are using SQL 2008, you must use ALTER USER:

use DYNAMICS
ALTER USER lessonuser1 WITH LOGIN = lessonuser1

Here’s a sample script if you are using SQL 2005 or lower:

use DYNAMICS
sp_changedbowner ‘DYNSA’
sp_change_users_login ‘Auto_Fix’, ‘lessonuser1’
sp_change_users_login ‘Auto_Fix’, ‘lessonuser2’
use TWO sp_changedbowner ‘DYNSA’
sp_change_users_login ‘Auto_Fix’, ‘lessonuser1’
sp_change_users_login ‘Auto_Fix’, ‘lessonuser2′

1. Finally, make sure that you use copies of the same dictionaries (including any forms and reports dictionaries) when running your tests.
After a separate instance and databases, you may want to automate the backup/restore processes for this test environment so that you can return to a clean slate, insuring that your data always starts without any corrupted data that might be created during your testing process.  Using SQL Management Studio can be cumbersome for restoring if you have many databases, so we are including some sample scripts that can help make the backup/restore process easier.

If you use SQL Standard, you will have to backup/restore with the standard backup/restore commands for each database:

BACKUP DATABASE [DYNAMICS] TO DISK = N’C:\SQL\BACKUP11\DYNAMICS.bak’
WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 20,
NAME = N'[DYNAMICS]-Full Database Backup’
RESTORE DATABASE [DYNAMICS] FROM DISK = N’C:\SQL\BACKUP11\DYNAMICS.BAK’
WITH FILE = 1, NOUNLOAD , STATS = 20, RECOVERY , REPLACE ,
MOVE N’GPSDYNAMICSDat.mdf’ TO N’C:\SQL\GPDATA11\GPSDYNAMICSDat.mdf’,
MOVE N’GPSDYNAMICSLog.ldf’ TO N’C:\SQL\GPDATA11\GPSDYNAMICSLog.ldf’

If you use SQL Enterprise, you can use snapshot databases to more quickly and easily restore databases to their original state.  Note: you will not be able to use a standard restore process while a snapshot exists.  If you need to refresh your data from a backup of your production data, you will need to drop all snapshots.

1. First create the snapshot databases:

CREATE DATABASE DynamicsSnapshot11 ON ( NAME = ‘GPSDYNAMICSDat.mdf’,
FILENAME = ‘C:\SQL\UPDATE\GP11\BACKUP\GPSDYNAMICSDat11.ss’ )AS SNAPSHOT OF DYNAMICS

2. Then restore them easily:

RESTORE DATABASE DYNAMICS FROM DATABASE_SNAPSHOT = ‘DynamicsSnapshot11’

3. To refresh the snapshots, drop the snapshot and recreate it using the command in #1:

DROP DATABASE DynamicsSnapshot11