Environment Management

2 Comments Posted on AX2012, Dynamics AX, Environment Management, SQL Scripts

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)

Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME'
---Reporting Services---
Declare @REPORTINSTANCE varchar(50) = 'AX'
Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX'
Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX'
Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]'
Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]'
Declare @REPORTFOLDER varchar(20) = 'DynamicsAX'
Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS';

---SSAS Services---
Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\AX'
Declare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration

---BC Proxy Details ---
Declare @BCPSID varchar(50) = '[BCPROXY_SID]'
Declare @BCPDOMAIN varchar(50) = '[yourdomain]'
Declare @BCPALIAS varchar(50) = '[bcproxyalias]'
---Service Accounts ---
Declare @WFEXECUTIONACCOUNT varchar(20) = 'wfexc'
Declare @PROJSYNCACCOUNT varchar(20) = 'syncex'
---Help Server URL---
Declare @helpserver varchar(200) = 'http://[YOURSERVER]/DynamicsAX6HelpServer/HelpService.svc'

---Outgoing Email Settings---
Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server
Declare @SMTP_PORT int = 25
---DMF Folder Settings ---
Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\ '

---Email Template Settings---
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX Workflow QA - TESTING'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'workflowqa@mydomain.com'
---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')

--List of users separated by | to keep enabled, while disabling all others 
Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|'
--List of users separated by | to disable, while keeping all the rest enabled all others  
--Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|'

--*****BEGIN UPDATES*******---

---Update AOS Config---
delete from SYSSERVERCONFIG where RecId not in (select min(recId) from SYSSERVERCONFIG)
update SYSSERVERCONFIG set serverid=@AOS, ENABLEBATCH=1 
	where serverid != @AOS -- Optional if you want to see the "affected row count" after execution.

---Update Batch Servers---
delete from BATCHSERVERGROUP where RecId not in (select min(recId) from BATCHSERVERGROUP group by GROUPID)
update BATCHSERVERGROUP set SERVERID=@AOS 
	where serverid != @AOS -- Optional to see "affected row count"
update batchjob set batchjob.status=4 where batchjob.CAPTION = '[BATCHJOBNAME]'
update batch set batch.STATUS=4 from batch inner join BATCHJOB on BATCHJOBID=BATCHJOB.RECID AND batchjob.CAPTION = '[BATCHJOBNAME]'

---Update Reporting Services---
delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS)
update SRSSERVERS set 
    SERVERID=@REPORTSERVERID, 
    SERVERURL=@REPORTSERVERURL, 
    AXAPTAREPORTFOLDER=@REPORTFOLDER,
    REPORTMANAGERURL=@REPORTMANAGERURL, 
    SERVERINSTANCE=@REPORTINSTANCE, 
    AOSID=@AOS,
    CONFIGURATIONID=@REPORTCONFIGURATIONID, 
    DESCRIPTION=@REPORTDESCRIPTION
where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution.

---Update SSAS Services---
delete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer)
update BIAnalysisServer set 
    SERVERNAME=@SSASSERVERNAME, 
    DESCRIPTION=@SSASDESCRIPTION,
	ISDEFAULT = 1
WHERE SERVERNAME <> @SSASSERVERNAME -- Optional where clause if you want to see the "affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Parameters---
Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT
---Update DMF Settings---
update DMFParameters set SHAREDFOLDERPATH = @DMFFolder 
where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)

---Disable all users except for a specific set---
update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0
---Disable specific users---
---update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0

--Clean up server sessions
delete from SYSSERVERSESSIONS
--Clean up client sessions.
delete from SYSCLIENTSESSIONS

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

Back to List

4 Comments Posted on Dynamics AX, Environment Management, Jobs, SQL Scripts

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.

--List of users separated by | to keep enabled, while disabling all others 
Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|'

update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0


--List of users separated by | to disable, while keeping all the rest enabled all others  
Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|'

update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0

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

Leave a comment Posted on AX2012, Dynamics AX, Environment Management, Jobs, SQL Scripts

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.

---Email Template Settings---Declare @WFAPPROVER_ADD varchar(50) = 'workflowqa@norplats.co.za'
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX - DEVELOPMENT'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'dev@mydomain.com'

---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')

---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)

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:

update sysuserinfo set sysuserinfo.EMAIL = 'youraddress@mydomain.com'

 

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

Leave a comment Posted on AX2012, Dynamics AX, Environment Management, SQL Scripts

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.

---Outgoing Email Settings---
Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server
Declare @SMTP_PORT int = 25

---DMF Folder Settings ---
Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\'

---Update Email Parameters---
Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT
---Update DMF Settings---
update DMFParameters set SHAREDFOLDERPATH = @DMFFolder 
where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows

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

Leave a comment Posted on AX2012, Dynamics AX, Environment Management, Security, SQL Scripts

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.

---BC Proxy Details ---
Declare @BCPSID varchar(50) = '[BCPROXY_SID]'
Declare @BCPDOMAIN varchar(50) = '[yourdomain]'
Declare @BCPALIAS varchar(50) = '[bcproxyalias]'
---Service Accounts ---
Declare @WFEXECUTIONACCOUNT varchar(20) = 'wfexc'
Declare @PROJSYNCACCOUNT varchar(20) = 'syncex'
---Help Server URL---
Declare @helpserver varchar(200) = 'http://[YOURSERVER]/DynamicsAX6HelpServer/HelpService.svc'

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

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

Leave a comment Posted on AX2012, Dynamics AX, Environment Management, SQL Scripts, SSRS

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.

 

Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME'
Declare @REPORTINSTANCE varchar(50) = 'AX'
Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX'
Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX'
Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]'
Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]'
Declare @REPORTFOLDER varchar(20) = 'DynamicsAX'
Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS';

delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS)
update SRSSERVERS set 
    SERVERID=@REPORTSERVERID, 
    SERVERURL=@REPORTSERVERURL, 
    AXAPTAREPORTFOLDER=@REPORTFOLDER,
    REPORTMANAGERURL=@REPORTMANAGERURL, 
    SERVERINSTANCE=@REPORTINSTANCE, 
    AOSID=@AOS,
    CONFIGURATIONID=@REPORTCONFIGURATIONID, 
    DESCRIPTION=@REPORTDESCRIPTION
where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution.

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

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

Back to List

3 Comments Posted on AX2012, Dynamics AX, Environment Management, Quick Tips

Remotely Controlling AOS services

I’ve recently moved into an environment with multiple load balanced AOSs that need to be controlled during code deployment and maintenance. If one needs to stop and start these services include either to logging into each machine individually or using the Windows services control panel to connect to the indivual machines to shutdown each AOS service.

Both of these options unfortunately are rather tedious and time consuming especially if you have numerous services to control and need to do it quickly. A simple way to do this is to batch/script the process using command line instructions to control the remote services. You can use sc.exe application to do so. Here are some examples:

sc \\[SERVERNAME] stop [SERVICENAME] e.g. sc \\JH-Srv-01  stop AOS60$01
sc \\[SERVERNAME] start [SERVICENAME] e.g. sc \\JH-Srv-01  start AOS60$01

This will issue the commands but won’t necessarily wait around while the services are stopping or starting.
Adding multiple of these commands to a single .BAT file will make you environment management life much easier! You can also use it to control other services like SSRS etc…

Note 1: You will obviously need the necessary permissions on each of these servers to do these tasks.
Note 2: The service name is the name listed under the properties (right click on the service, click properties) of the service not the one in the list of services.

2015-06-03_1515

 

Reference: https://support.microsoft.com/en-us/kb/166819

1 Comment Posted on AX2012, Dynamics AX, Environment Management, X++

Environment based Dynamics AX Color coding

Before I start: As a disclaimer, I’m posting this to show a potential way to solve this issue or similar issues, and to spark thought not necessarily as a production recommended solution.

I have had the requirement in the past to ensure that our environments (DEV, UAT and PROD) are reliably color coded to reflect the true environment that is currently open. This should not be application dependent (code only existing in individual environements) or database dependent (settings residing in the database) as these may change, be restored or be overwritten.

The best solution out there on how to physically color forms is Palle Agermarks’ solution. I would recommend you look at his code to get started as this is not the primary purpose of this post.

This solution however relies on a database table with the required color settings. This means that if a database is restored the color settings in UAT may reflect the color settings in Prod. For some purposes this may be ok or indeed what is needed, but we want to be able to clearly and confidently differentiate between our environments without having to remember restore scripts etc.

To do this I took Palle Agermarks‘ color coding code and modified it to look (in code) at what the current database is and color code my environment accordingly. Unfortunately this does require a bit of hard-coding a set of database names, but the colors will change correctly regardless of database or application loaded.

The key to this is to use SysSQLSystemInfo::construct().getLoginServer(); to determine the current database server you are connecting to.

public void setFormStatusBarColor()
 {
 int a,r,g,b;
 Color color;
 str serverName = SysSQLSystemInfo::construct().getLoginServer();
 if (formRun)
 {
 switch (serverName)
 {
 case "MYDEVSERVER" : color = "33CC33"; //green
 break;
 case "MYPRODSERVER" : color = "FF9933"; //red
 break;
 case "MYUATSERVER" : color = "0066FF"; //blue
 break;
 }
if (color)
 {
 a = 254;
 [b,g,r] = WinAPI::RGBint2Con(hex2Int(color));
 formRun.setStatusBarBackgroundColor(a,r,g,b);
 }
 }
 }

 

Some variations on this, depending on your setup, would include looking for keywords in your server names instead of the whole name to determine what environement is being used. E.G. Does servername contain “DEV”. This may make it a bit more flexible and re-usable.