Admin | ErpCoder

Dynamics 365 FO: Export Entity Store to Azure Data Lake

11. July 2019 3 Comments

Since version 10 Dynamics 365 for Finance and Operations supports the entity store export to Azure data lake. The main benefits are reduced costs because Azure Cloud storage is cheap and easy access for Business Intelligence tools like PowerBI.

If you are running a local development VM, the data connection tab in system parameters ist deactived by default. However, this can be actived using the SysFlighting table.

The configuration is pretty well documented by Microsoft. I’ve performed all the necessary steps and recorded a video:

Filed under Admin, Dynamics 365 Finance and Operations Tagged with Azure, Data Lake, Dynamics 365 FO

Connect Azure Data Lake Storage with PowerBI dataflow

3. June 2019 Leave a comment

PowerBI dataflow performs ETL (Extract Transform Load) workloads in the cloud. PowerBI Pro and Premium Users get dataflow storage without additional charges. However, this storage is managed by PowerBI and you cannot access it directly. Therefor BYOSA (Bring Your Own Storage Account) is support to connect you own Azure storage account with PowerBI dataflow. I’ve made a video, following the documentation, how to connect an Azure storage account with PowerBI. Please find my video youtube:

Configure Azure Data Lake storage with PowerBI dataflow

Filed under Admin, PowerBI

Configure PowerBI on Dynamics 365 FO developer VM

16. July 2018 1 Comment

I’ve created a video tutorial how to configure PowerBI on a stand alone Dynamics 365 Finance and Operations developer VM

Filed under Admin, Dynamics 365 Finance and Operations, PowerBI, Uncategorized

Setup multiple developer VMs for Version Control with Team Services in Dynamics 365 for Finance and Operations

10. April 2018 1 Comment

Here is a walkthrough how to connect two Dynamics 365 Finance and Operations developer VMs with VSTS.

Configure Azure AD and setup Visual Studio Team Services

Before you start, you have to add your developers to Azure Active Directory. If you have Azure AD Connect make sure the accounts have been synced to the Cloud. In my case I added two additional users to my Azure AD.

Next logon to your Azure Portal using your Organization Account. Create a new service and search for “Team Services”. You should find Visual Studio Team Services (preview).

Create a new instance of VSTS. The basic version of VSTS for 5 users is free. Make sure to use Team Foundation Server as Version Control system. You may choose any Methodology you like, but II ‘d recommend to go for CMMI compatible one.

After the deployment succeeded, logon to your Visual Studio Team Services account, using the URL https://<ACCOUNTNAME>.visualstudio.com . There you see a new project. Open the project and add your developer users by clicking the (+) icon on the upper left “Members” pane. If everything is configured correctly, you should be able to add your AD users. In my example developer one and developer two.

Configure Developer VMs

If you are using the VHD Images from Microsoft, the first thing you should do is to rename the computer. If you don’t rename the VMs you will get errors when mapping the source control on multiple machines. In my case I rename the VMs to “devbox1” and “devbox2”. No domain is needed. Details here.

Configure first Developer Box

After the VM reboots, open Visual Studio 2015 in Admin mode. Depending on your licensing you may need to provide additional credentials to use your subscription. Don’t get confused if this may be your Microsoft ID (aka. Live ID) while you need your Organization Account to access VSTS. Zwinkerndes Smiley From the menu bar select > Team > Manage Connections. Provide the URL for your VSTS account.

After you have connected to VSTS select the project to work on. Next, from the Team Explorer open the Source Control explorer. Map the root folder to a folder on your developer VM.

Afterwards use the source control explorer to create two new folders. One for Visual Studio Projects and one for metadata. This is where the D365 source code artifacts will be stored. Check in you pending changes. This will sync the folders to VSTS.

Now, in the Source Control Explorer open the dropdown Workspace and edit your workspace. Map the metadata folder to C:\AOSService\PackagesLocalDirectory.

From the menu bar > Dynamics 365 > Model Management > Create a new model. Give it a name and complete the wizard. This will ask you to create a new Dynamics X++ project.

In the solution explorer, right click on the project and add to source control.

Add a new element to the project, for example add a new String Extended Datatype called DMOCustomerName. In the solution explorer, right click the project and select build. After a few seconds you should see the console output “Build completed”. Check in all your pending changes.

Next, from the Team Explorer open the Source Control Explorer. You should see the model structure in the tree view. Right click on the metadata folder and select “Add items to folder”. Navigate to your model folder and there to the Descriptor folder. Add the Model Descriptor XML file. Unfortunately you have to do this manually, otherwise the second developer can sync the Folders and Extended Datatypes etc. but will not see the model in the AOT.

You can also inspect your code in VSTS

Configure second Developer Box

Make sure that the second VM is properly renamed. Open Visual Studio in Admin mode and connect to VSTS. Logon with the account of the second developer. Select the Dynamics 365 project and again in the Source Control Explorer map the metadata folder to C:\AOSService\ PackagesLocalDirectory. Checkout the latest version of the metadata folder.

This will create the model folder in the packages directory.

In Visual Studio open the Dynamics 365 Application Explorer. If the AOT is in classic mode, right click and switch to model view. Scroll down and you we will see the synchronized model and the its software artifacts.

Filed under Admin, Development, Dynamics 365 Finance and Operations, Methodology

Setup und Configure Office 365 Project Web App

27. December 2017 Leave a comment

Here you can find my video on how to setup Project Web App, link with Project Desktop client and how to setup a SharePoint subwebsite with a synchronized task list

Filed under Admin, Office Tagged with Office 365; Project Web App

Inplace Upgrade of Windows Server 2012 HyperV to 2016

20. August 2017 Leave a comment

There is no upgrade path for Windows Server 2012 (non-R2) HyperV role to Windows Server 2016 HyperV. The easiest way would be to setup a new Windows Server 2016 and activate HyperV. However, there may be reasons to perform an inplace upgrade. This is an experience report doing so

Prepare for the Upgrade

First make sure there are no virtual machine related files left on C: drive. Move the configuration files and especially the virtual hard disk files to another drive. I recommend to make list where the VHDX files are place. This can easily be done via PowerShell

Get-VM –VMName * | Select-Object VMid | Get-VHD | select Path | ft

Next uninstall the HyperV role using the Server Manager. Simply select remove roles and features and select the HyperV role. This step requires a reboot and your server will come up a simple plain windows 2012 server.

After HyperV was uninstalled make sure to remove NIC teaming in case you are using this feature. I recommend to deactivate all but one network adapter. This can also be done using the Server Manager.

Perform Inplace Upgrade

Insert a disk or mount the Windows Server 2016 image. Start the upgrade process. When asked choose to keep all your data and apps. This will preserve your data and applications e.g. the RAID Manager software. The setup wizard warns you that an inplace upgrade is not the preferred way to setup server 2016. Accept and proceed. The upgrade will take a while and requires some reboots.

Setup HyperV on Server 2016

After the upgrade process has finished, its time to setup HyperV again. First configure the NIC teaming again. Activating the HyperV role in Server 2016 is almost the same as in Server 2012. This can be done by using the Server Manager installing new roles and features. Activating HyperV will require a reboot. After the reboot configure a virtual switch so your VMs can access the network again.

Import the Virtual Machines again

By default HyperV on Server 2012 has no clue about the VMs in former Server 2012. The virtual machines have to be imported manually. This can be done using the HyperV console. First, provide the folder where the virtual machine configuration is placed. Afterwards choose to directly register the VM.

Next provide the folder where the virtual hard disk files are placed. Don’t get confused because you don’t see the actual VHDX files in the selection dialog. The import wizard will check if the virtual hard disk named in the configuration file can be found in this folder. If the wizard can’t find the virtual hard disk file, take a look a the list of vhdx file paths generated by the PowerShell script.

When all the VMs are imported the upgrade is almost finished. The the network connection of the virtual machines and make sure they can access the network via the newly created virtual switch. Removing the virtual network adapter and adding the virtual network adapter again can help Zwinkerndes Smiley Perform some tests, maybe reboot the server again and install the latest updates.

Filed under Admin Tagged with Windows Server 2012; Upgrade

Performance Optimization by using Included Columns and Field Selects

25. April 2017 1 Comment

Since version 2012, Dynamics AX supports included columns in indices although SQL Server supports it for quite a long time. Here are some examples how and why it is good practice to use included columns in an index. I’m using Dynamics AX 2012 R3 Cu12 on Windows Server 2016 and SQL Server 2016 with Contoso Demo data for this example

Cluster Index.

The cluster index can be defined using multiple fields and is used to defined the order of records stored in the table. Even more important is the fact, that if a table has a clustered index all the data is stored in the table, i.e. the cluster index IS the table!

Take a look at the space allocated by the indices. About 219 MB are used to store actual data and 167 MB are used to store index information

The following SQL Statement reveals the size in detail

SELECT
ind.name,
SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM
sys.indexes ind
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
INNER JOIN
sys.dm_db_partition_stats AS s ON s.[object_id] = ind.[object_id]
AND s.[index_id] = ind.[index_id]
WHERE
t.name = ‘INVENTTRANS’
GROUP BY ind.name
order by IndexSizeKB desc

The table data is stored in the TransOriginIdx

name	IndexSizeKB
I_177TRANSORIGINIDX	226992 ~ 221 MB
I_177OPENITEMIDX	63720
I_177STATUSITEMIDX	34312
I_177ITEMIDX	24872
I_177RECID	23416
I_177DIMIDIDX	22192

Index Usage with Field Select

Here is an example of a select statement with field select on the InventTrans table

while select ItemId,DatePhysical
from inventTrans
where
InventTrans.ItemId == ‘0001’ &&
inventTrans.DatePhysical >= str2Date(‘1.1.2011’,123)

{ .. }

The trace parser reveals the actual SQL Statement sent to the database

What happens is what you would expect, SQL uses the ItemIdx for this query

Only 5 logical reads where necessary

Select Non-Index fields

When the query selects fields which are not part of the index, SQL server has to perform a lookup in the Cluster Index for each record identified by the ItemIdx to get all the other fields. For example the Voucher and Qty are not part of the ItemIdx.

213 logical reads were necessary to fetch the data

This can get even worse, when performing the lookup becomes to expensive. This can happen when the query returns a larger number of records. For example, when querying for another ItemId. In this example SQL server does not use the ItemIdx anymore, but performs a search in the clustered index instead. The ItemIdx became completely useless for this query.

SQL server required 1345 logical reads to fetch the data!

Included Columns

Since version 2012 Dynamics AX supports the definition of Included Columns for indices. These columns are not used to sort the index. These are just fields which are stored within the index to avoid costly lookups in the clustered index. In Dynamics AX you just add columns to the index and set the property IncludedColumn to Yes.

You can find the included columns in SQL server when viewing the properties of the index

When the statement from above is executed again, SQL server can use the included columns from the index and does not perform costly lookups in the clustered index.

Only 6 logical reads are required to fetch the data. This is a huge optimization compared to the 1345 reads without included columns.

Filed under Admin, Development, Dynamics AX, Performance Tagged with Index, SQL Server

SQL Server 2016 SP1 and Dynamics AX 2012 R3

7. February 2017 7 Comments

Here are some ideas on SQL Server 2016 SP1 and Dynamics AX 2012 R3

Enterprise Features in Standard Edition since Service Pack 1

There was a major change in Service Pack 1 for SQL Server 2016. While most cool features were Enterprise-Edition-Only for a very long time, many features like Column Store Index and Compression are now available for Standard Edition too. Have a detailed look at this Blog. SQL 2016 also introduces new features like the Query Store and Power BI Integration with Reporting Services

Reporting Services

SQL Server 2016 Reporting Services require Dynamics AX R3 CU12 and an additional KB3184496 hotfix. Otherwise the installation will fail. The typical AX user won’t see the difference between SSRS 2016 and older versions. However, there are some features that might be interesting for us AX folks too, namely Power BI Integration.

Right now (January 2017) Power BI Integration is not so useful. You can place your Power BI files at the SSRS, which is actually only a better alternative to place the .PBIX file on a file share. However, it is said SSRS will be able not only to store but also to render Power BI files On Premises. This might be interesting for customers who are not willing to use Power BI in the cloud.

Right now in SSRS 2016 SP1 you can pin SSRS reports to your Power BI (Online) dashboard. This means, you can integrate your SSRS reports in Power BI. This might not sound very useful for Dynamics AX users. Why should I pin an invoice to a Power BI dashboard? But if a customer is already using SSRS for reporting, this might be a good option to start with Power BI and reuse the existing reports. Some Dynamics AX reports with OLAP data source can also be pinned to the Dashboard.

There is a Power BI Button in the SSRS report portal

This will pin your report to one of your Power BI (Online) dashboards

Query Store

This is a very useful feature. All of us are familiar with performance problems reported by some users. The problem is to identify and reproduce the query which performed badly and find the reason. Query Store can be used to store information about such problem-queries, like the SQL statement executed, the used execution plan, etc. In SQL Server Management Studio you can view reports based on execution time, logical and physical write/reads, memory usage, etc.Query Store therefore is a very useful feature in SQL 2016 to identify performance issues.

Column Store Index

Column Store Indices were introduced in SQL Server 2012 too speed up aggregation queries (e.g. sum). However, CSI hat a lot of limitations and was an Enterprise Edition features till 2016 (non SP). In SQL 2016 SP1 we can now use CSI in combination with Dynamics AX at our customers who have licensed Standard Edition of SQL Server.

In contrast to traditional Row Store Indices where records stored in 8 KB pages (e.g. CustInvoiceJour records), CSI store column values (e.g. LineAmountMST) together in 8 KB pages. Therefore aggregation functions can perform faster because less pages have to be read.

Here is an example:

select CustGroup, year(InvoiceDate) as YR, sum(LineAmountMST) as Amount
from CustInvoiceJour
group by CustGroup, year(InvoiceDate)

When executing this query against a Dynamics AX Contoso Demo database, 2158 logical reads were required.

Next, create a non-clustered Column Store Index on the fields CustGroup, InvoiceDate and InvoiceAmountMST which are used in the query

The same query now utilizes the Column Store Index to fetch and aggregate the data. The IO statistics show that less reads were required to get the result. The query performs faster than with the traditional Row-Store index.

Be aware that Dynamics AX removes the Column Store Index from the database when you synchronize the data dictionary. This might not be such an issues in a production environment. When you deploy a new application version from Test to Live, make sure to recreate all lost CSI.

Stretch Database

With stretch database you can migrate cold data (aka. existing but hardly not used) from your on premises expensive high performance storage to the cloud. This means you can split the data in large table and move old records in SQL azure. The application doesn’t recognize this split. Only if you query cold data, it will take longer to fetch the result. This sounds good. however there are some very crucial show stoppers.

You can’t UPDATE or DELETE rows that have been migrated, or rows that are eligible for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables.
You can’t INSERT rows into a Stretch-enabled table on a linked server.

So right now, this feature is not useful for Dynamics AX on premises installation

Filed under Admin, Dynamics AX, PowerBI Tagged with SQL Server 2016

SQL Backup Restore fails due to insufficient free space

26. May 2016 Leave a comment

A customer recently tried to restore a Dynamics AX database backup from the Live system to the Testing environment. The SQL Server data disk had 17 GB space left, and the size of database backup (.bak) file was about 11 GB.

However, SQL Server refused to restore the database because of insufficient free space.

The reason was the file layout in the original Database. Typically, the database files and log files pre-allocate space to avoid costly file operations when the content of the database grows. In this case, the database file had 20 GB space and the log although the content of the database was only about 11 GB.

When a backup is created, SQL only backups the content of the files and adds additional information about the file layout. When the database is restored, the database files will be allocated like in the source database. Therefore 21 GB were needed but not available on disk.

The solution was to increase the storage on the Test system, restore the database and afterwards shrink the database file.

Filed under Admin Tagged with Backup, Restore, SQL Server

Using Azure Automation to copy Live Transaction DB to Test

30. January 2016 Leave a comment

Updating the Test system with actual transactional data from the Live system is a common task. This can be accomplished using scripts. However, in a hybrid IT environment you might want to organize, manage and monitor all your on-premises and cloud based scripts at a central place. Azure Automation is the platform to do this.

Prerequisites

Dynamics AX Live and Test installation on Windows Server 2012 R2
Azure Account (all services can be used for free in basic mode)

Azure Automation

In Azure Portal create a new instance of Azure Automation. When the instance was created, use the key to display the keys and URL. You’ll need this to connect your on-premises server with Azure Automation.

Operational Insights

Azure Automation can be instantly used to script your cloud-based datacenter. However, to manage the on-premises environment you have to connect your local systems with the cloud. This is done via Operational Insights.

In Azure Portal create a new instance of Operational Insights
Logon to Operational Insights Portal and start the “Get Started” checklist
Add the Automation Solutions
Connect the Dynamics AX server by downloading and installing the Agent for Windows

At your on-premises server open PowerShell in admin mode and go to the agents installation directory e.g.

cd "C:\Program Files\Microsoft Monitoring Agent\Agent\AzureAutomation\<version>\HybridRegistration"

Import the Hybrid Registration module and register the server in Azure Automation.

Import-Module HybridRegistration.psd1
Add-HybridRunbookWorker –Name <String> -EndPoint <Url> -Token <String>

In this example I’ve used the Name parameter value “Dynamics” at the Add-HybridRunbookWorker Cmdlet. This creates a runbook worker group called “Dynamics” with one assigned on-premises server.

On-Premises configuration

Create a folder where to backup the Dynamics AX database. In this example I’m using a directory on the local system drive C:\AxTemp (which of course is not best practice). By default the Ops. Insights Agent runs as Local System. Make sure to give the account “NT Authority\System” appropriate rights in your SQL Server installation to access the Live DB and Test DB.

Runbook

In Azure Portal, go to Azure Automation > Runbooks > Create a new runbook > Choose runbook type “Powershell”. Add the following code to your runbook.

echo "Stopping Services"
Stop-Service -Name AOS60`$01
Stop-Service -Name AOS60`$02

echo "Creating Backup from Live"
Invoke-Sqlcmd -Query "backup database [Live] to Disk = N’C:\AxTemp\Live.bak’ with copy_only" -QueryTimeout 0

echo "Restoring Backup to Test"
Invoke-SqlCmd -Query "restore database [Test] from DISK = N’C:\AxTemp\Live.bak’ with file = 1, move N’R3Demo1‘ TO N’C:\Data\Test.mdf’, MOVE N’R3Demo1_log‘ TO N’C:\Data\Test_log.ldf’" -QueryTimeout 0

echo "Cleanup Backup"
Remove-Item -Path "C:\AxTemp\Live.bak"

echo "Starting Services"
Start-Service -Name AOS60`$01
Start-Service -Name AOS60`$02

This will stop both AOS instances. Make sure the AOS service name fits your environment. The service name can be found in the services mmc. For example my Live AOS is named AOS60$01. Make sure to use the ` to escape the $ sign in the AOS name.

The script will then create a backup from database “Live” to C:\AxTemp\Live.bak. If your live DB has another name, change it to fit your name.

Next the script will restore the backup to database “Test”. In my case the logical name of the database file is called “R3Demo1” and it’s log is called “R3Demo1_log”. Make sure this fits your installation. You can find the names in SQL Server management studio, by checking the database file properties.

Finally the script removes the backup file and restarts both AOS

Runbook Execution

In Azure Automation Runbook editor, save the actual runbook code and click publish.

At the Azure Automation main page go to runbooks, select your newly created runbook and press the start button. On the next page select execution using a Hybrid Worker and select your worker group. In my example it’s called Dynamics (see PowerShell screenshot)

This will submit your runbook to the on-premises server and execute it. You can check the execution by monitoring the C:\AxTemp directory where the backup will be placed. Don’t worry about the warnings when the workbook finishes. Starting the AOS’ takes a while and results in the typical message “service is not responding”.

Azure Automation supports timing of runbooks, so you can create a batch in the cloud to copy your data on-premises
This example uses a single server installation. However, the script can easily be modified to run on different servers
Using the Invoke-SqlCmd Cmdlet you can do all the cleanup work like changing the reporting server instance etc.

Filed under Admin, Cloud Tagged with Dynamics AX 2012; Azure

← Older posts

Newer posts →

ErpCoder

Dynamics 365 FO: Export Entity Store to Azure Data Lake

Connect Azure Data Lake Storage with PowerBI dataflow

Configure PowerBI on Dynamics 365 FO developer VM

Setup multiple developer VMs for Version Control with Team Services in Dynamics 365 for Finance and Operations