AX DB Restore Scripts – Full Script

Just over a month ago I embarked on a series of posts on SQL scripts that I use when restoring Dynamics AX databases from one environment to another. We have covered topics including server setups, general configurations as well as data cleans to get your new environment up and smoothly running as quickly as possible. We are now at the end of the series and I trust it has been helpful to you in structuring your scripts.

I’d just like to remind you that these posts and scripts are not meant to be used as is but rather to serve as a guide to adapt for your specific scenario and to prompt your own thinking as to what you will need.

That said you can download my templated script here or view it below (download)

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

Back to List

AX DB Restore Scripts #8 – Disable users

A few weeks ago I embarked on a series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another. We have covered a variety of topics including various configuration and setting changes to Dynamics AX. This post will be the last in the set of “data cleanup scripts” designed to help scrub your data for use in a development or testing environment.

Disabling users.

Quite often companies or partners will require access to development environments to be restricted to a very select group of users that are aware of what to test and are also careful enough to work methodically in the correct environments (i.e. not trying to to real work in the wrong environment or test work in production). To ensure all of the above one could follow a few paths.

  1. Only provide a shortcut/configuration to users who require it.
  2. Color code your environments so that they immediately recognizable.
  3. Add a startup message to your AX environment to warn users what environment they are logged into.

All of these solutions do not physically stop users from accessing the alternate environment.  So if you need an additional security mechanism, simply disable the users either manually in AX after doing a restore or disable then along with the SQL scripts we have been building up over the past few weeks.

To disable users in AX navigate to “System Administration -> Common -> Users -> Users. Double click on the user in question, click “edit”, uncheck the “enabled” button.DisableUser

The accomplish the same in a SQL script you can do the following. I have parameterised the SQL for easier reuse or adjustment.

Note 1: Parameterising a list can be tricky in TSQL, so the above trick is what i found easiest.
Note 2: You will need to decide whether the list of users provided is inclusive (i.e. provide the list of all the users you WANT to disable) or exclusive (i.e. disable all users except for a small subset). I have provided both options, but would recommend the exclusive approach as it is more likely to give you safely give you what you expect.

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 #7 – Setting Email Templates and User Email Addresses

Back to List

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 #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

SQL Database Restore History

From time to time it is useful to determine the history of restores that a database has gone through. For example if you maintain a local copy of a client database / environment and would like to determine the exact date it was last refreshed. The following script is useful for getting this restore history from SQL:

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Sample Output

Screen Shot 2015-01-20 at 12.03.02 PM