Title: Microsoft Visual Studio, Dynamics AX ETL SSIS Package Deployment, SQL Security Credential, SQL Proxies, and SQL Job implementation:

Author: Toraj Khavari

Date: December 22, 2015

Objective: Microsoft Visual Studio (VS) is an integrated solution to create SQL Server Integrated Services (SSIS) packages. In this case, the SSIS Package Extract Transfer and Load (ETL) Microsoft Dynamics AX tables to an Enterprise Data Warehouse (EDW).  In this article, we outline the following items.

Ø  VS created SSIS package deployment

Ø  Creating SQL Security Credential

Ø  Establishing SQL Proxies

Ø  Implementing a SQL Job with execution schedule

The SQL Security Credential and Proxies are needed to meet the integrated mode requirements. The SQL “Login” enables the administrators to setup SQL users. However, the SQL “Login” setup has no use when the SSIS package execution requires multiple databases integration. Henceforth, Microsoft SQL has Security Credential and Proxies technologies to empower jobs using domain / network “Login” across different SQL databases and multiple servers.

This article is based on the assumption that the VS SSIS package/s is created, tested, and ready for deployment, I recommend you refer to the following blogs for creating SSIS packages.

URL: http://torajkhavari.blogspot.com/

Tittles:

1-      Microsoft Dynamics AX to Enterprise Data Warehouse (EDW) SQL Extract Transform and Load (ETL).

2-      Microsoft Dynamics AX to Enterprise Data Warehouse (EDW) SQL Extract Transform and Load (ETL) for Multiple Tables.

<Side> During my VS SSIS deployment and SQL job setup, discovered a lot of information is on-line. However, there is not a comprehensive solution collection. My hope is, you will find the following helpful and save you some time.

Article Body: VS SSIS package deployment and SQL job setup using integration mode have multiple steps. Prerequisites are as follows.

A.      Have an existing and tested VS SSIS package. Rebuild the solution.

B.      Databases’ connections must have the ”Log on to the server” “Use Windows Authentication” selected. Also, known as integrated mode.

C.      Have a Network Login ID and Password, preferably service ID and Password with the SQL Access rights to all SQL databases involved in your solution.

D.     Delete an existing SQL Integrated Catalogs Projects, in my case “EDWSSIS_Extract”. Initializes your project in the SQL Integrated Catalogs Projects. Removes all the existing Projects’ Packages, connection string, and their components.

1.      VS created SSIS package deployment

1.1.  Identify and navigate to the VS SSIS Project’s Integrated Services Project Deployment File. It is in the VS project’s “bin” folder.  In my case the path is as follows.

C:\VSProjects\Solutions\Dev\SSIS\DWETL\EDWSSIS_Extract\EDWSSIS_Extract\bin\Development

1.2.  “Open” your project Integrated Services Project Deployment File. Integrated Services Deployment Wizard assist you to deploy the project.  Follow the wizard, in the “Select Destination” enter the “Server Name” and “Path”. The “Path” will be the SQL Integrated Catalogs Projects.

1.3.  Navigate to the SQL Integrated Catalogs Projects. Select the package, right mouse click “Validate” > Connection Managers tab. Validate the packages “ConnectionString”.

The connections must be “Integrated Security” and “UserName” equal to NULL. Connection String example is as follows.

Data Source=<YourServerName>;Initial Catalog=AX2012R2_PROD;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

If any of the connection string are not Integrated Security with user name equal NULL, repeat prerequisites A and B, and previous steps 1.

2.      Creating SQL Security Credential

2.1.  Prerequisites “C” outlined having a Network Login ID and Password. In my case, I prefer, service ID. Because they have a lot of different security roles than employee login. E.g., Service IDs in our company do not require frequent password changes.

2.2.  Start the Microsoft SQL Server Management Studio, connect to your database, navigate to SQL Server, “Security” > ”Credential. Create a new “Credential”. Type “Credential name”, “Network Service ID”, and “Password”.

3.      Establishing SQL Proxies

3.1.  Navigate to “SQL Server Agent” > “Proxies” > “SSIS Package Execution”. Select right mouse click, “New Proxy…” Enter “Proxy Name, “Credential name”, and select “SQL Server Integrated Services Package”. The “Credential name” must be the same as step number 2.2.

4.      Implementing a SQL Job with execution schedule

4.1.  Select “SQL Server Agent” > “Jobs” > right mouse click “New Job…”. In the “General” tab enter “Name” and “Owner”. The “Owner” should be the Network Service ID Login from prerequisite “C”.

4.2.  In the “Steps” tab, select “New…”. In the “General” tab, type “Step name”. Select “SQL Server Integration Services Package” for “Type”. Select in my case “SSIS_Proxy_AXDWUser” for the “Run as”. The “Run as” must match the step 3.1. Select “SSIS Catalog” for “Package source”. Type “Server” name. Select “Log on to the server” “Use Windows Authentication”. For the “Package” navigate to the desired package.

4.3.  In the “Schedules” tab select the appropriate setup to meet the business requirement.

To test, select the created job, right mouse click “Start Job at Step…”.

Enable the new job. You are done.

The scheduled job uses the created proxy. The proxy uses a credential with correct network login and password. To me; proxy and credential are delegate / trusted agent for security.

Hints:

-          The Network service ID must have a SQL Logins with correct SQL authority.

-          The job “View History” and “Integrated Services Catalogs” “Packages” reports have great tools for debugging, performance and traceability.

-          The “Proxies” “SSIS Package Execution” “References” tab has all the “Jobs step” listed which utilizes this specific proxy. SQL will do the references for you.

Microsoft VS, Dynamics AX ETL SSIS Package Deployment, SQL Security Credential, SQL Proxies, and SQL Job implementation are powerful tools and technologies. Enjoy using them to meet the business needs.

I wish you and yours a Merry Christmas, Happy New Year, good health and prosperous 2016.

References:

-          MSSQL - How to configure SQL Proxy and Credentials - https://www.youtube.com/watch?v=Z--t2MUF5E4

-          MSSQL – How to schedule SSIS package in SQL Agent - https://www.youtube.com/watch?v=JbC_ronWGXo

-          Configure a User to Create and Manage SQL Server Agent Jobs - https://msdn.microsoft.com/en-us/library/ms187901.aspx  

Title: Stress and Load Test Web Application and Dynamic AX:

Author: Toraj Khavari

Date: July 15, 2015

Objective: Outline practical solutions to stress and load test of a Web Application and Dynamic AX:

Article Body: A common thread among good system architects and engineers is finding answers to the following questions.

• How does the system behave under load and stress?
• What is the systems' virtual and physical capacity as the number of users increases?
•  When there are concurrent activities, e.g., Batch Jobs, Reports, Fail over recovery, etc., what are their effects on the End Users?
•  Are there enough analysis and facts available to share with the System Stakeholders about the system behavior?
• There are so many system items to monitor in production, what should be the monitor focus? Where are the weak points?

Although, the above questions and similar ones paralyze us with concerns, very often, stress and load tests are not performed. There are a variety of reasons, e.g., test technologies are not available, limited time between stable product and release time, budget limitation. Or, if stress and load tests are performed; they are compromised. Thereafter, real production environments will become the stress and load test laboratory. Leading to performance degradation, system patches, loss of or unhappy customers, and system modification on the fly, Also Known As (AKA), “Fire Fighting”.

In this article, practical solutions to stress and load test of a Web Application and Dynamic AX are outlined. The A-dec OrderNet and CORE System, which include Dynamic AX, are used as case studies.

Case Description:

A-dec Inc. is an international dental equipment and dental office furniture manufacturer head quartered in Newberg Oregon, USA. A-dec is in the process of releasing a new version of OrderNet and change Enterprise Resource Planning (ERP) BaaN to Microsoft Dynamic AX 2012 R2.

OrderNet is a web solution for customers to place, to configure, and to submit orders, on-line. A-dec is among very few companies providing a web solution for ordering dental equipment on-line. The system supports both standard and configured quotes and sales orders.

The A-dec ERP upgrade is more than a Microsoft Dynamic AX implementation. The solution collection, referred to as the CORE Replacement Project, CORE for short, consists of a collection of products; Microsoft Dynamics AX, and Independent Software Vendor (ISV) systems meeting the business needs. The CORE system includes a collection of interfaces, e.g., Electronic Data Interchange (EDI), Manufacturing Execution Systems (MES), Shipping Systems, Warehouse Carousel, etc. The following picture describe the scope, pictorially.

The CORE system includes integration with the A-dec data warehouse, and existing other business systems, while using infrastructure technologies, inside the firewall. The solution collection is modeled to support “A5” methodologies. It seeks to enable anyone, from anywhere, at any time, from any device to access any application.

Stress and load testing CORE solution was prioritized based on the business impacts. Although the stress and load testing were focused on OrderNet and Dynamics AX, we exercised the lower priorities interfaces and batch processes.

Load and Stress Testing Definitions:

Let us explore what is load and stress testing and define them.

“Load testing is a software testing technique used to examine the behavior of a system when subject to both normal and extreme expected load conditions.”

“Stress testing is a form of testing which deliberately stresses the application and observes its stability.”

The best analogy I can think of is cardiovascular stress testing. Under observation, doctors stress the patients and monitor the patient’s vital signs.

When the solution collection is stressed, it does not really mean to break the system. Yes, we want to know where the threshold is; however, don’t destroy the server to find the threshold. Back to our analogy, when the Doctor puts the patient under stress test, the Doctor does not push the patient to a point of catastrophic event. Watch the system under stress, and monitor the servers key stress indicators. E.g., CPU usage > 80%, memory loss over time.  The key system stress indicators will outline the weak points and trouble areas.

A-dec OrderNet Load and Stress Testing:

A-dec OrderNet enables end users to create quote, sales orders, and keep track of the order. The solution supports both standard components and configures products.

Outside the A-dec firewall, internet users interface with OrderNet using EmpowerID (Federation Services) and A-dec Portal.

The business requirements for the A-dec stress and load testing as follows.

·         100 Concurred Users

o   40 Users Create Sales Orders

o   40 Users Modify Sales Orders

o   20 Users Print Sales Order

We selected Microsoft Visual Studio Online (VSO) to instantiate / create 100 concurrent virtual users. The virtual users will use ID and Password to login to A-dec New OrderNet. The A-dec OrderNet has its own data base using SQL cluster.

We utilized on-site local computers and created the test scripts. Copied the scripts to a “Load Test Rig” in Azure – A cloud server. The VSO instantiated 100 concurrent users, ran the scripts, repeatedly, after login to A-dec OrderMet.

Let us review the sequence.

1- Created the test script in Visual Studio

2- Copied the scripts to the cloud load test rig

3- The load test web services created a few workers to instantiate 100 concurrent users

4- The agents ran the scripts

5- The VSO captures the ran time transactional data

6- The internal A-dec servers logged very specific systems information for more analysis.

7- Ran the test scripts, capture end users and systems experiences / performance.

Dynamic AX Stress and Load Testing:

The CORE solution collection contains a few Microsoft ISV solutions, including Dynamics AX. The Dynamics AX is the heart of A-dec CORE solution. Although Dynamics AX has web interface, we use and stress the application interface. A-dec internal employees access the Dynamics AX using Remote Desktop Services (RDS) sessions. We have a few dedicated RDS Servers to support Dynamics AX application users.

The A-dec Dynamics AX stress and load testing utilized AXceptance technology. AXceptance is a great product. A-dec's partner to implement stress and load testing is Armanino. The team successfully simulated 100s of concurrent virtual users.

Observations and Conclusion:

There are significant lessons learned through the stress and load testing. The following are a few highlights.

Although infrastructure architecture is out of scope of this article, let us touch on a few highlights.

•  Virtual verses physical servers – A-dec CORE systems consist of virtual and physical servers. The SQL Custer, AX AOS, and AX batch are physical servers (appliances).
• Load balancing – KEMP technology is powerful, proved very versatile, and very adequate.

ROI – A-dec’s continued commitment to quality and customer services are paramount to the company’s success. Even one dissatisfied customer has a large financial impact. In this case, the stress and load testing cost justifications received a significant Sr. management support.

Technologies and implementation partners – Discovering correct technology and implementation partner for the stress and load testing OrderNet and AX were changing. In our case, Microsoft Visual Studio Online (VSO) team is the best choice for OrderNet stress and load testing. Armanino stress and load testing team, and AXceptance technology were most matched for A-dec needs.

Commitment – There are a lot of discussion around stress and load testing. However, very few committed to them prior to production. The production environments are aimed to serve customers and not be a laboratory for testing functionality, and stress and load testing. Just commit to stress and load testing, and do them. Technologies and partners are available to bring software releases to higher quality.

The stress and load testing has been very productive. Let me share with you some highlights.

•   Identified a few transactional enhancements.
•  Improvements to end users’ experience. E.g., UI Rendering, Network Chatting, and Volume of data across Network.
• Identified the weak design spots.
• Proved the infrastructure bandwidth.
• Above all, system performance in production is no longer a mystery. 

A-dec New OrderNet and AX testing are not aimed at fault finding or challenging confidence. The goal is and will be to find a balance between stress and load testing. Identify a confidence level so that we can meet the business need based on the business check list and requirements.

Load and Stress testing bring to light two things; weakness and strength. Powerful ingredients to a successful new product deployment.

Take ambiguity out of production deployment, create a test lab, stress and load systems before production release, and commit to quality. Keep the end users happy and satisfied.

Have fun deploying solid products to production.

Toraj

Dynamics AX - An Introduction to Query Services:

Title: Dynamics AX - An Introduction to Query Services:

Author: Toraj Khavari

Date: March. 25, 2015

Objective: In this wiki article we explore the use of Dynamics AX Query Services, and Microsoft Dynamics AX flexibility, extendibility, with powerful interfaces.

At times, we may have business requirements to query Dynamics AX data bases. Dynamics AX out-of-box supports significant number of queries. Run Dynamics AX. Anywhere in the UI, type <Cntl>D. The Dynamics AX AOT dialogue box opens. Open Queries node. All the available queries are displayed.

This article introduces the Query Service Web Service to consume any query. Consuming the Dynamics AX Query Service is a software language agnostic.  You can use any software language that consumes Web Service.  You can access Dynamic AX queries. I will use. C#.

Article Body:

1-      Create a class liberty.

2-      Add Service Reference, in my case, AOTQueryServiceRef, pointing to Dynamics AX Query Service, URL, an example as follows.

http://<ServerName:PortNumber>/DynamicsAx/Services/QueryService

For correct Query Service URL, and security access, contact the AX Admin team.

3-      In the AOT, identify the Query and table you want to use. In my case, I am using query "DirPartyContacts" table “DirPerson”. Table names are in the AOT, Query, Data Source Node. Identify the columns you are interested. Columns, AKA, fields are identified in AOT.

4-      Update the “using” statements. In my case, I use the following collection.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Adec.BTSUtility.QueryService.AOTQueryServiceRef;

using System.Runtime.Serialization;

using System.IO;

using System.Data;

5-      Copy and paste the following code in your class.

        #region GetNamePhoneEmailbyPostalAddressRecId

        /// <summary>

        /// Gets the name phone and email by postal address record identifier.

        /// </summary>

        /// <param name="postalAddressRecIdstr">The postal address record id.</param>

        /// <returns></returns>

        public string GetNamePhoneEmailbyPostalAddressRecId(string postalAddressRecIdstr)

        {

            // Refrence http://ax2012aifintegration.blogspot.com/2012_05_01_archive.html and

            // Book Inside Microsoft Dynamics® AX 2012 by The Microsoft Dynamics AX Team

            // Return value

            string resultStr = null;

            try

            {

                // Instantiate Query Client, Query Meta data, and Query Data Source for Meta Data

                QueryServiceClient client = new QueryServiceClient();

                AOTQueryServiceRef.QueryMetadata query = new AOTQueryServiceRef.QueryMetadata();

                query.DataSources = new AOTQueryServiceRef.QueryDataSourceMetadata[1];

                // Identify the Query

                query.Name = "DirPartyContacts";

                // Instantiate Query Data Source for Meta data

                AOTQueryServiceRef.QueryDataSourceMetadata dirPartyContactsDS = new AOTQueryServiceRef.QueryDataSourceMetadata();

                // Identify the query namd and its table

                dirPartyContactsDS.Name = "DirPartyContacts";

                dirPartyContactsDS.Table = "DirPerson";

                dirPartyContactsDS.Enabled = true;

                // Using xpass as a bypass to get all data.

                if (postalAddressRecIdstr.Contains("xpass") != true)

                {

                    // If you are using Range, modify the for loop

                    AOTQueryServiceRef.QueryDataRangeMetadata range = new AOTQueryServiceRef.QueryDataRangeMetadata();

                    range.Enabled = true;

                    range.TableName = "DirPerson";

                    range.FieldName = "RecId";

                    range.Value = postalAddressRecIdstr; // Input string

                }

                query.DataSources[0] = dirPartyContactsDS;

                dirPartyContactsDS.DynamicFieldList = true;

                AOTQueryServiceRef.QueryDataFieldMetadata name, phone, email, recId;

                name = new AOTQueryServiceRef.QueryDataFieldMetadata();

                name.FieldName = "Name";

                name.SelectionField = AOTQueryServiceRef.SelectionField.Database;

                phone = new AOTQueryServiceRef.QueryDataFieldMetadata();

                phone.FieldName = "PrimaryContactPhone";

                phone.SelectionField = AOTQueryServiceRef.SelectionField.Database;

                email = new AOTQueryServiceRef.QueryDataFieldMetadata();

                email.FieldName = "PrimaryContactEmail";

                email.SelectionField = AOTQueryServiceRef.SelectionField.Database;

                recId = new AOTQueryServiceRef.QueryDataFieldMetadata();

                recId.FieldName = "RecId";

                recId.SelectionField = AOTQueryServiceRef.SelectionField.Database;

                // Get all the records

                Paging paging = null;

                DataSet dataset = client.ExecuteQuery(query, ref paging);

                if (dataset.Tables[0].Rows.Count == 0)

                {

                    resultStr = resultStr + string.Format("Querey Result is empty");

                    return resultStr;

                }

                if (postalAddressRecIdstr.Length < 1)

                {

                    resultStr = resultStr + string.Format("Invalid Empty PostalAddressRecId");

                    return resultStr;

                }

                // If you are using Range, modify the for loop

                for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)

                {

                    DataRow datarow = dataset.Tables[0].Rows[i];

                    if (postalAddressRecIdstr.Contains("xpass") == true)

                    {

                        resultStr = resultStr + string.Format("RecID: {0}; Name:{1} ; Phone:{2} ; Email: {3} ;\r\n",

                            datarow[0].ToString(), datarow[1].ToString(), datarow[2].ToString(), datarow[3].ToString());

                    }

                    else

                    {

                        if (datarow[0].ToString() == postalAddressRecIdstr)

                        {

                            resultStr = resultStr + string.Format("{0} {1} {2}",

                                datarow[1].ToString(), datarow[2].ToString(), datarow[3].ToString());

                        }

                    }

                }

                if (resultStr == null)

                {

                    resultStr = resultStr + string.Format("No Match for {0}", postalAddressRecIdstr);

                    return resultStr;

                }

            }

            catch (Exception e)

            {

                resultStr = resultStr + string.Format("Error Message:{0}\n", e.Message);

            }

            return resultStr;

        }

        #endregion

Hints:

-          Modify the code to meet your query name, tables, and fields.

-          If range is not working for you, take a look at AOT Range node in the query. Validate its setup before using it.

-          Data Sources has associated AOT table, Data Dictionary > Table.

-          You can see the table resolute by selecting the Table, right mouse click open.

-          Data Sources in AOT outlines the relational data sources; I like to call them the children relations. The children data sources are available for query, too.

-          The copy and paste removed the indentation. The code is in TFS Toraj.khavari $/Core/Adec.ConsumeQueryService/Adec.BTSUtility.QueryService.

Dynamics AX Query Services brings a whole new dimension to Dynamics AX interface. If AIF Exposed services does not meet your need take a look at Query Services. In addition to Query Services, we have Meta Data Query Services with more flexibility.

Have fun learning and coding. These are great time to take advantage of Service Oriented Architecture.

Cheers Toraj

Version 1.0.2425.01