Wednesday, December 1, 2010

Copying a Production Database into a Development/Test Environment

Disclaimer: In no way am I making claim that this is the best, correct, or accepted approach to take when bringing down a copy of Production into Development. The outlined method has worked for me and served my purposes well.. with that said, follow my steps at your own risk!


1. Stop the AOS on Development or Test
2. Backup the AX Database in Production, and Restore it into Development or Test
3. Restore all the Security permissions/mappings to the AX Database (Model them after Production)
4. Write a T-SQL script to replace Production Server & User names in the Database with that which are related to Development or Test (see below for tables/columns that need to be changed)
5. On Development or Test, delete all files in the application folder (C:\Program Files\Microsoft Dynamics AX\50\Application\Appl\[Your AX Instance]) where the file extension ends with the letter “i”
6. Start the AOS on Development or Test (this may take awhile)
7. Open the AOT, right-click the Data Dictionary, and select Synchronize


Tables in the AX Database that need to be Modified (see step #4 above)

I had written a utility that mined the database for all references to Production system names. The following is the script that I came up with to “convert” a Production Database to Development/Test. Note: not all changes may actually be necessary (such as in the log tables) but for consistency, I change them anyway.

Declare @AOS varchar(15)
Declare @SP varchar(15)
Declare @SQL varchar(15)
Declare @axbcp varchar(15)

Set @AOS = '[Your DEV AOS Server Name]'
Set @SP = '[Your DEV SharePoint/Enterprise Portal Server Name]'
Set @SQL = '[Your DEV SQL Server Name]'
Set @axbcp = '[Your DEV axbcp User Account Name]'

Declare @P_AOS varchar(15)
Declare @P_SP varchar(15)
Declare @P_SQL varchar(15)
Declare @P_axbcp varchar(15)

Set @P_AOS = '[Your PRODUCTION AOS Server Name]'
Set @P_SP = '[Your PRODUCTION SharePoint/Enterprise Portal Server Name]'
Set @P_SQL = '[Your PRODUCTION SQL Server Name]'
Set @P_axbcp = '[Your PRODUCTION axbcp User Account Name]'


----------------------------------------------------------------------------------
Update [SYSBCPROXYUSERACCOUNT] Set NETWORKALIAS = @axbcp WHERE NETWORKALIAS = @P_axbcp
----------------------------------------------------------------------------------


Update [BATCH] Set SERVERID = REPLACE(upper(SERVERID), upper(@P_AOS), upper(@AOS))
Where upper(SERVERID) like '%' + upper(@P_AOS) + '%'
-----------------------------------------------------------------------------------


Update [BATCHHISTORY] Set SERVERID = REPLACE(upper(SERVERID), upper(@P_AOS), upper(@AOS))
Where upper(SERVERID) like '%' + upper(@P_AOS) + '%'
-----------------------------------------------------------------------------------


Update [BATCHSERVERGROUP] Set SERVERID = REPLACE(upper(SERVERID), upper(@P_AOS), upper(@AOS))
Where upper(SERVERID) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update [EPWEBSITEPARAMETERS] Set [INTERNALURL] = REPLACE(upper([INTERNALURL]), upper(@P_SP), upper(@SP))
Where upper([INTERNALURL]) like '%' + upper(@P_SP) + '%'
Update [EPWEBSITEPARAMETERS] Set [EXTERNALURL] = REPLACE(upper([EXTERNALURL]), upper(@P_SP), upper(@SP))
Where upper([EXTERNALURL]) like '%' + upper(@P_SP) + '%'
------------------------------------------------------------------------------------


Update SRSSERVERS Set [SERVERURL] = REPLACE(upper([SERVERURL]), upper(@P_SQL), upper(@SQL))
Where upper([SERVERURL]) like '%' + upper(@P_SQL) + '%'
Update SRSSERVERS Set [AXAPTAREPORTFOLDER] = REPLACE(upper([AXAPTAREPORTFOLDER]), upper(@P_SQL), upper(@SQL))
Where upper([AXAPTAREPORTFOLDER]) like '%' + upper(@P_SQL) + '%'
Update SRSSERVERS Set [REPORTMANAGERURL] = REPLACE(upper([REPORTMANAGERURL]), upper(@P_SQL), upper(@SQL))
Where upper([REPORTMANAGERURL]) like '%' + upper(@P_SQL) + '%'
------------------------------------------------------------------------------------


Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_AOS), upper(@AOS))
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_AOS) + '%'
Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_SP), upper(@SP))
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_SP) + '%'
Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_SQL), upper(@SQL))
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_SQL) + '%'
------------------------------------------------------------------------------------


Update [SYSEMAILSMTPPASSWORD] Set [AOSID] = REPLACE(upper([AOSID]), upper(@P_AOS), upper(@AOS))
Where upper([AOSID]) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_AOS), upper(@AOS))
Where upper([DESIGNNAME]) like '%' + upper(@P_AOS) + '%'
Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_SQL), upper(@SQL))
Where upper([DESIGNNAME]) like '%' + upper(@P_SQL) + '%'
Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_SP), upper(@SP))
Where upper([DESIGNNAME]) like '%' + upper(@P_SP) + '%'
------------------------------------------------------------------------------------


Update SYSSERVERCONFIG Set [SERVERID] = REPLACE(upper([SERVERID]), upper(@P_AOS), upper(@AOS))
Where upper([SERVERID]) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update SYSSERVERSESSIONS Set [AOSID] = REPLACE(upper([AOSID]), upper(@P_AOS), upper(@AOS))
Where upper([AOSID]) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_AOS), upper(@AOS))
Where upper([COMPUTERNAME]) like '%' + upper(@P_AOS) + '%'
Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_SP), upper(@SP))
Where upper([COMPUTERNAME]) like '%' + upper(@P_SP) + '%'
Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_SQL), upper(@SQL))
Where upper([COMPUTERNAME]) like '%' + upper(@P_SQL) + '%'
------------------------------------------------------------------------------------


Update SYSWORKFLOWPARAMETERS Set [SITEURL] = REPLACE(upper([SITEURL]), upper(@P_SP), upper(@SP))
Where upper([SITEURL]) like '%' + upper(@P_SP) + '%'
------------------------------------------------------------------------------------



Re-Syncing Enterprise Portal

Unfortunately, by restoring a Production Database into Development/Test, it appears to break connectivity with Enterprise Portal. As of now, the only way I have figured out how to resolve this issue is by doing the following:

Verify Your SharePoint Administrative Access

1. START > Administrative Tools > SharePoint Central Administration
2. Go to Application Management > SharePoint Site Management > Site Collection Administrations
3. Click the Site Collection drop down > Change Site Collection
4. Click the "/sites/DynamicsAx" URL and click OK
5. Make sure your name is listed as either the Primary or Secondary site collection administrator. NOTE: if you are not the Primary or Secondary site collection administrator, you will get an "Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)" error when attempting to install Enterprise Portal

Reinstall Enterprise Portal

1. Load the Setup.exe (run as Administrator) from your Dynamics AX 2009 installation path
2. Select “Add or modify components”
3. Select “Role Centers and Enterprise Portal” and click Next
4. Verify that the “Domain\user name” is correct for your environment (if not make the change to the NETWORKALIAS column in the SYSBCPROXYUSERACCOUNT table in the AX database, and then restart the setup) and enter in the password for the account
5. Select the existing Dynamics AX EP website
6. Check the “Configure for Windows SharePoint Services” checkbox
7. Check the “Create Web site” checkbox
8. Verify the Site URL
9. Click Next
10. Click “Yes” to allow the setup to delete and recreate
11. Click Install

Run a command prompt (run as Administrator) and enter the following command lines:

1. cd c:\Program Files\Microsoft Dynamics AX\50\Setup
2. AxUpdatePortal –updateWebSites –verbose > “C:\EPUpdate.log”
3. iisreset

Enterprise Portal should once again be functioning. At this point you will need to go into your EP site, and add your user permissions again.

9 comments:

  1. Im starting to follow your blog :). Nice work.

    ReplyDelete
  2. Hello Corey,

    Great post, nice blog!

    Maybe one addition to this post: The GUID used for the Ax cache file may be an issue. Check out

    http://dynamics-ax-live.blogspot.com/2011/02/small-detail-to-keep-in-mind-when.html

    ReplyDelete
  3. Not sure why you have to run a almost complete reinstallation of EP. It shouldn't really be necessary. EP is dependent on having a proper business connector configuration on that server (Start->Admininistrative Tools->AX Configuration). Then again the AOS answering the requests has to have a proper configuration (EP settings and System account settings). If the DEV EP and the PROD EP is different, then just run the AXUpdatePortal (which has the same dependencies as EP). Then it will take all the parts in the AOT and publish it out to the EP.

    Am I missing something here?

    ReplyDelete
  4. Willy, Thanks for the tip, I will take a look at this and keep it in mind the next time I do an environment refresh!

    Skaue, Yeah, one would think that a reinstallation of EP is a bit ridiculous. However, I have yet to find another resolution within our environment (3 machines [1] AOS v 5.0.1500.2116 [2] SQL Server 2008 R2 [3] SharePoint 2007 … all Windows Server 2008 R2 64-bit) to get EP running again. I verify the settings that you mentioned including running the AXUpdatePortal, and everything looks as it should. Unfortunately within our environment, nothing does the trick outside of a reinstall of EP. So the install is doing something else behind the scenes that I have yet to figure out.

    ReplyDelete
  5. So the installer asks for BC proxy login. It needs this to make sure the application pool has the BC proxy as Identity account. Besides setting up the application pool it also contacts the AOS and asks for any web elements that should be updated/added into the SharePoint 12/14 hive.
    Would you mind sharing what errors or trouble you encounter if you simply run the AxUpdatePortal utility? :-)

    ReplyDelete
  6. Hey Corey,
    one other table which may need to be refreshed is AifChannel (specifically the TransportAddress column). This will need to be done if you are pulling in / pushing out data via the AIF, e.g. to change the directories used to pick up files sent from interfaced systems. I'm currently doing an analysis of the tables we need to change for our environment, so will feed back if I get any useful info. Thanks for providing this post - it's made a great starting point.

    ReplyDelete
  7. also, a thought on the EP issue. . .
    Perhaps you should take a copy of all AX related databases (including sharepoint) before doing the reinstall. Once done, use Visual Studio's Data Comparison tool (under data, data compare) to see the differences. I'm guessing there will be a lot of created/modified date columns which will be different, so will need excluding, but hopefully there won't be much other noise, so you can see if there's a setting hidden there somewhere? Sadly we're not making use of the EP at present, so I can't play with this yet. . .

    ReplyDelete
  8. As a fresher, it is very useful to me

    thanks
    teja

    ReplyDelete
  9. I am regular visitor of this blog .I am working as blog reviewer in a private press and I saw many useful posts here. Sure, I will give best ratings for this blog .Keep posting best posts like this to get top reviews and ratings from blog reviewers and people .And I am thankful for this valuable post.
    Marine Colleges in Chennai | Mechanical Colleges in Chennai | ECE Colleges in Chennai

    ReplyDelete