Retrieve data from on-premises SQL Server

Dynamics 365 can be integrated with PowerApps, Flow, and Power BI, called Power Platform, to further enhance its value. If you have a Dynamics 365 Customer Engagement Plan license, you should definitely take advantage of this application.

Currently, Dynamics 365 uses the Common Data Service to manage data, so data stored in Dynamics 365 can be seamlessly passed to PowerApps, Flow, and Power BI, which also use the Common Data Service.

Reference: What is the Common Data Service (Microsoft)

Data integration into Common Data Service Building an application typically involves data from multiple sources, which may be done at the application level. Consolidating this data into a common store can simplify the application building experience and may allow for a single set of logic to maintain and manipulate the data. Common Data Service integrates data from multiple sources into a single store that can be used in PowerApps, Flow and Power BI along with data already available from Dynamics 365 applications.

Reference: What is the Common Data Service (Microsoft)

I will show you the steps to use this Common Data Service (CDS) to retrieve data from on-premises SQL Server and store it in a new entity in Dynamics 365. For more information on what CDS is, please refer to the technical information above.

advance preparation

There are some preparations to be made before reflecting SQL Server data into Dynamics 365.

Build an on-premises SQL Server

I built an on-premises SQL Server using an Azure VM.

The procedure is as follows

• Log in to the Azure portal with a free account and build a single VM (Windows 10) environment.
• Install SQL Server 2017 on-premises with remote desktop access
• Install and connect SQL Server Management Studio (SSMS)
• Download the sample data and restore it from SSMS. *For the procedure, refer to “Using the Sample DB with SQL Server”.

I have done this.All can be verified for free. *I should have installed SQL Server on my own client PC, but due to various adult circumstances, I couldn’t do it. We responded by building a VM in Azure. On-premise? I’m putting aside once and for all the slight question of “what’s the point?

Store the table “SalesPerson” from the sample data “AdventureWorks2017” into a new entity in CDS.

Set up a free version of Dynamics 365 and PowerApps

If you do not have both environments ready, please start using the free version. Here’s how.

• Set up the free version of Dynamics 365 for Sales
• Set up the free version of PowerApps with the onmicrosoft.com account you created in step 1 above.

I prepared in the following order. I will skip the steps.

Installing the On-Premises Data Gateway

Now that we are ready, let’s get started. First, install the On-Premises Data Gatewayon your on-premises SQL Server. By installing this on-premises data gateway, you will be able to easily exchange data between on-premises and cloud.

The On-Premise Data Gateway can be downloaded from here.

Prerequisites for on-premises data gateways

There are some things to keep in mind when installing the On-Premise Data Gateway in an on-premise environment.

Minimum:

• .NET 4.5 Framework
• 64-bit version of Windows 7 or Windows Server 2008 R2 (or later)

Recommended:

• 8-core CPU
• 8 GB memory
• 64-bit version of Windows 2012 R2 (or later)

Related considerations: Gateways cannot be installed on domain controllers. Do not install the Gateway on a computer (such as a laptop) that may be powered off, in sleep mode, or not connected to the Internet. The Gateway will not be able to operate under these conditions. In addition, the Gateway’s performance may be degraded over a wireless network.

Reference: About On-Premise Data Gateways for Canvas Apps

Once downloaded, start the installation. Please refer to the technical information above for the installation procedure.

In this example, we configured the name of the data gateway as shown in the figure below.

After successfully completing the process, you should now see the following image, which shows that PowerApps is ready.

When I opened the “Gateways” section of PowerApps, I could see the data gateway I created.

Retrieve data from on-premises SQL Server

Now that we have completed the configuration of the on-premises data gateway on the on-premises SQL Server, we will retrieve the specific database on the PowerApps side and store it in an entity.

Connect to SQL Server

Go to the PowerApps screen and switch the Environment from the upper right corner.

Expand “Data” in the left panel and click “Data Integration”. Click “Create Data Integration Project”.

Select “SQL Server Database” as the data source.

Enter the SQL Server server name and the database name you want to retrieve in the connection settings, select the data gateway you just created as the on-premises data gateway, and enter the credentials as Windows Authentication. Then click “Next”.

Select the data column to retrieve.

Select a table for the database. In this case, select “SalesPerson” and click “Next”.

This will take you to the edit screen of the query. Under “Column Management”, click “Select Columns” to get only the columns you need.

After making your selection, click “Next.

Load into a new entity

Load the information from the table into a new entity. Check the “Load into new entity” checkbox in the loading settings, and set the entity name and display name.

Set the key field in Field Mapping. Here we have selected a unique ID field called BusinessEntityID.

When I tried to set the LastName in the Primary Name field, I got an error. It says that multi-line text cannot be set, so I changed the field type for LastName to “Text”.

I changed the Primary Name field to LastName and the other field types to “Text” and clicked “Next”.

*If you prefer multi-line text, you can leave it as is.

Finally, update the settings. In this case, we set “Update manually”. If you want to update automatically, set the update interval and start date.

Click “Create”. When the loading status becomes “Completed”, it is OK.

A new project has been created in Data Integration.

If you look at the entities, you will see that the newly created entities are listed.

I tried to display it on the Dynamics 365 sitemap.

This completes the import of data from on-premises SQL Server to Dynamics 365. The migration from on-premise to Dynamics 365 will be smooth by using CDS.

That’s it for this time.