AX DB Restore Scripts #7 – Setting Email Templates and User Email Addresses

In my series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another, we have so far covered various configuration and setting changes to Dynamics AX. Today I will move on to some suggested data changes that you may wish to consider making when doing database restores. These changes won’t affect the functioning of AX, but may prevent some awkward or confusing situations like clients or user’s receiving emails etc..

These data cleanup scripts are really meant as guidelines to help you in creating your own scripts, rather than a comprehensive set of “must-do” scripts.

The two changes for today are as follows

  1. Setting your email templates to clearly indicate that they originate from a testing system. It may be perfectly legitimate for users to receive emails from your testing or development environments, e.g. for testing workflows, however it is very helpful for the users to clearly see that they are viewing test data rather than production data. So setting the senders name and address to indicate “Development” will assist.
    Email templates are found under: Organisation administration -> Setup -> Email templates
    Email_Templates
    2. Setting user email addresses. We obviously don’t want to send emails to users who have no interest in our development systems so one may, depending on your needs, want to do one of the following.

    1. Clear all user’s email addresses, except for a specific subset.
    2. Set some or all user’s email addresses to a single address (e.g. You want emails to still be generated to see that they are working, but you would like to see the contents of them yourself and not expose the user to the test data)

These settings can be found under System Administration -> Users -> Options (ribbon button) ->  Email
Email_Address

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

Note, the above example clears all user email addresses except for a specific set, you could easily adjust this to set them all to a specific address as follows:

 

I hope this assists you and will be useful in your management of your AX environments. Keep an eye out for new posts in this series!

View Previous – AX DB Restore Scripts #6 – SMTP Server and DMF Folder

View Next – AX DB Restore Scripts #8 – Disable users

Back to List

AX DB Restore Scripts #6 – SMTP Server and DMF Folder

Recently I started creating a list and a series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another, typically from production to QA or development.

We have so far discussed a number of configurations that must be changed for the new system to operate effectively and starting yesterday we moved onto exploring more optional configurations.

Today we will cover two of these items namely re-configuring the outgoing SMTP server in AX and configuring the settings for the DMF working folder.

Note: In my experience the SMTP server will most likely not change between a production and development server, but you may need to reconfigure it if moving to a new domain etc. Similarly it will work to use the same DMF shared folder between PROD and DEV, however I would recommend changing this to keep the data 100% separate.

These two configurations are located in the Dynamics AX 2012 client under the following paths.
1. SMTP Server, accessed via System Administration -> Setup -> System -> E-mail parameters.EmailServer

2. Data import, export framework shared working directory. Accessed via “Data import export framework -> Setup -> Data import export framework parameters”
DMF

To update these 2 settings above we need to do the following:

1. Update the SysEmailParameters table to update the SMTP server name (and additional login parameters)
2. Update the DMFParameters table to reflect the new shared folder path.

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

I hope this assists you and will be useful in your management of your AX environments.

View next – AX DB Restore Scripts #7 – Setting Email Templates and User Email Addresses

View Previous – AX DB Restore Scripts #5 – Configure Service Accounts and Help Server

Back to List

AX DB Restore Scripts #5 – Configure Service Accounts and Help Server

Last week I embarked on a series of posts on DB restore scripts for Dynamics AX by creating a list of items in your AX database that need to be updated/reconfigured when restoring a database from a production environment to a development or QA one.

So far we have covered the majority of the most critical issues to fix when doing a DB restore, so from today on most of the scripts are basically optional. They are optional for one of the following reasons: 1. It may be common to have the same configuration in both live and dev, yet in some circumstances they may differ e.g. Business connector may remain the same if you are restoring from live to dev, but not from live to a demo VPC.
2. The data is correct but for safety or security you may want to manipulate it for a development environment. E.G. Changing all email templates to clearly indicate the source is from a dev system.

Today we will cover two topics namely re-configuring the service accounts in AX and configuring the AX help server URL.

In the Dynamics AX 2012 client, the configurations that we are going to look at are:
1. System Service Accounts, accessed via System Administration -> Setup -> System -> System service Accounts

ServiceAccounts

2. Batch Jobs, accessed via System Administration -> Setup -> System -> Help system parameters
Helpserver

To update the settings above we need to do the following:

1. Update the SYSBCPROXYUSERACCOUNT table to reflect the business connector user’s SID, networkdomain and network alias. For this step you will need to find out the SID of the business connector either by setting it manually the first time and then running select SID from SYSBCPROXYUSERACCOUNT where NETWORKDOMAIN='[yourdomain]’ and NETWORKALIAS='[yourbcuseralias]’ or follow one of the suggestions over here http://blogs.msdn.com/b/gaurav/archive/2014/06/03/get-sid-of-the-object-registry-wmic-powershell.aspx
2. Update the SYSWORKFLOWPARAMETERS table to reflect the userId of the workflow execution account.
3. Update the SYNCPARAMETERS table to reflect the userId of the Microsoft Project server synchronization service account.
4. Update SYSGLOBALCONFIGURATION to reflect new help server URL if necessary.

Notes:
1. If you are wanting to use an Alias/Network domain combination for either of the two execution accounts, I would recommend setting them manually in AX as they create users and permissions automatically when you do this. 
2. The above script assumes that execution accounts you are setting exist in the new system and have the necessary permissions.

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

I hope this assists you and will be useful in your management of your AX environments.

View Next – AX DB Restore Scripts #6 – SMTP Server and DMF Folder

View Previous – AX DB Restore Scripts #4 – Reconfigure batch jobs

Back to List

AX DB Restore Scripts #3 – Configure AX servers and batch servers

Earlier this week I embarked on a series of posts on DB restore scripts for Dynamics AX by providing a list of data entities in your AX database that need to be updated when restoring a database from a production environment to a development or QA one.

Today I will be drilling into how to reset/configure your AX AOS and batch AOS configurations via DB scripts. These scripts are necessary especially for the SSRS script and for the batch server update script (available soon) as these rely on a correct AOS configuration.

Note: Under normal circumstances a new configuration for your AOS will automatically be created if it does not exist when the AOS starts up after a restore, however all your other configs like batch jobs, SSRS settings etc won’t be pointing to it, but rather the old settings that are not cleared. Thus I prefer to fix this in my script.

In the Dynamics AX 2012 client these configurations are located under System Administration -> Setup -> System -> Server Configuration

AOS_servers

Depending on the configuration of your source system (e.g. Production) you may have multiple AOSs setup here and typically your destination may only have a single AOS configuration therefore your script would need to do the following:

1. Remove all but one of the AOS configurations
2. Update the server details of the remaining configuration
3. Enable the remaining server as the “batch” server.

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

Note: These changes assume everything is setup correctly on the SSAS server itself.

2015-09-03_0751

View Next – AX DB Restore Scripts #4 – Reconfigure batch jobs

View Previous – AX DB Restore Scripts #2 – SSAS Servers

Back to List

AX DB Restore Scripts #2 – SSAS Servers

Recently I started a series of posts on DB restore scripts for Dynamics AX by providing a list of data entities in your AX database that may need to be updated when restoring from one environment to another, in particular a production DB to a development or QA environment.

Today I will be drilling into how to reset the SSAS (SQL Server analysis services) configurations via DB scripts.

In the Dynamics AX 2012 client these configurations are located under System Administration -> Setup -> Business Intelligence -> Analysis Services -> Analysis Servers.

Analysis

Depending on the configuration of your source system (e.g. Production) you may have multiple servers setup here and typically your destination would only have a single server (Development) therefore your script would need to do the following:

1. Remove all but one of the SSAS configurations
2. Update the server details of the remaining configuration

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

Note: These changes assume everything is setup correctly on the SSAS server itself.

2015-09-03_0751

View Next – AX DB Restore Scripts #3 – Configure AX servers and batch servers

View Previous – AX DB Restore Scripts #1 – SSRS Servers

Back to List

AX DB Restore Scripts #1 – SSRS Servers

Yesterday I started a series of posts on DB restore scripts for Dynamics AX by providing a list of data entities in your AX database that should be updated when restoring from one environment to another.

Today I will start drilling into the details of each of these items starting with SSRS (SQL Server reporting services) configurations.

In the Dynamics AX 2012 client these configurations are located under System Administration -> Setup -> Business Intelligence -> Reporting Services -> Report Servers.

SSRS1

Depending on the configuration of your source system (e.g. Production) you may have multiple servers setup here and typically your destination would only have a single server (Development) therefore your script would need to do the following

1. Remove all but one of the SSRS configurations
2. Update the remaining one to be default and update the relevant data fields.

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

 

Note: These changes assume everything is setup correctly on the SSRS side.

View next – AX DB Restore Scripts #2 – SSAS Servers

Back to List

AX Database Restore Scripts (List)


sqlserver (1)
A  common task in the administration of a Dynamics AX installation is restoring databases from a live application to a development environment or quality assurance (QA) environment. However when doing so one needs to be very careful to re-configure the database so that it behaves correctly in its new context. For example you would need to re-configure your SSRS servers to point to the correct DEV/QA SSRS instance. Many of these changes can be done via the front-end, but it is very useful to script these in SQL so that nothing is missed and alot of effort is saved.

microsoft-dynamics-ax-iconThe following post aims to list as many of the potential data items that need to be changed when doing a DB restore. I will in the following days be posting the relevant SQL alongside the front-end equivalent and ultimately a full DB restore script that you can use when doing such a restore. Some of these may need to be adapted for your specific configurations and you may need to add your own based on customization etc.

Infrastructure setups

  • SSRS Servers – Re-point or recreate your SSRS (SQL Server Reporting services) instances (View Details)
  • SSAS Servers – Re-point or recreate your SSAS (SQL Server Analysis services) instances (View Details)
  • Configure AX AOSs and batch AOSs – Live environments may typically have multiple AOS’ and batch servers. Cleaning up and reconfiguring these references will assist in getting new batch jobs and SSRS setups up and running. (View Details)
  • Batch Jobs/Batch Groups (View Details)
    • Reconfigure batch jobs and batch groups to use the new server configurations as soon as the AOS is started up (e.g. Workflow processing)
    • Disable certain critical batch jobs – This is especially important if there are batch jobs that should NOT run in a DEV/QA environment. E.G. Automatic placement of orders etc… These jobs should typically never be run outside of live so we want to disable them before the AOS even starts up.
  • Service accounts (Optional) Reset your AX service accounts (workflow execution and business connector proxy) if live and DEV/QA differ. (View Details)
  • Help server(Optional) Re-point your help server URL if necessary if live and DEV/QA differ. (View Details)
  • Reset outgoing email server (Optional) if live and DEV/QA differ. (View Details)
  • Reset Data Migration Framework’s shared folder (View Details)
  • Enterprise portal websites – Re-point your enterprise portal website urls to the DEV/QA instances.

Data safe guards (Optional)

  • Reset all email templates to have a sender name and address that clearly shows that they originate from a DEV/QA system. (View Details)
  • Reset Customer/Vendor/User email addresses – We may not want to inadvertently send out mails to clients, vendors or users while performing tests. These may include alert or workflow emails. (View Details)
  • Disable users – You may require that only certain users have access to DEV/QA databases for testing, scripting the disabling of the users in bulk will help prevent the wrong people from accessing the environment and save you having to do this manually. (View Details)
  • Clean up sensitive data if needed – E.G. Bank accounts, credit cards etc.
  • Set user status bar options – It is sometimes useful to set your users status bar options in DEV/QA so that you can easily identify critical bits of information relating to their sessions. (View Details)

Clean Ups

  • Clean up SYSServerSessions
  • Clean up SYSClientSessions