The biggest benefit of using Virtual Entities for new and existing Dynamics 365 / CDS projects is cost savings. The new licensing model provides a per user license quota of data storage (which depends on if you’re a D365, Per App or Per User licensed user). These entitlements don’t always meet the needs of customers who want to display large amounts of read-only information from external data sources.
Until recently, however, you could only create / use Virtual Entity providers that reached out to external APIs via web protocols. The recently enabled ability to reach out directly to Azure SQL from Sandboxed CDS Plugins makes it now possible to create a SQL Virtual Entity Data Provider without the need to create an intermediate service to serve that data using OData or other API protocols.
I recently started an open source project to build a standalone SQL Data Provider that is easily configurable and handles all of the Virtual Entity Data Provider plumbing for you. As part of this I’ve created a sample solution as well as the standalone provider that you can import into your CDS instance and start working with immediately.
Let’s take a look at how to use the sample solution, which will demonstrate the capabilities of the Direct SQL Virtual Entity Data Provider and give guidance to anyone who hasn’t worked with custom Virtual Entity Data Providers in the past.
In the next post I’ll dig into the specifics of how you can configure this for your organization or particular use case and extend it beyond the simple sample we have here.
What You’ll Need
- Required: A Dynamics 365 or Power Apps Subscription
- Required: An Azure Subscription with Rights to Create Resources
- Optional: SQL Server Management Studio (to test your connection)
Setting Up Your Sample SQL Azure Instance
- Navigate to portal.azure.com and signin
- Click Create a Resource + Databases
- Click SQL Database or Managed Instance
- Select your Subcription and Resource Group
- Select Create New if you don’t already have a SQL Server Instance
- Enter Server Name
- Enter Server Admin Login
- Enter and Confirm the Password for your Admin Account
- Select Okay
- Now Click on Networking
- Set Connectivity Method to Public Endpoint
- Select Add Current Client IP Address and Allow Azure Services to Access this server
- Click Additional Settings
- Next to Use existing data select Sample
- Select the remaining defaults to create your Azure SQL Server and Database
- Once the server and database are provisioned go to the resource and select Connection Strings from Menu
- Copy the ADO.NET Connection String to use in the next step
Installing and Configuring the Solution
- Download either the Unmanaged or Managed (recommended) SQL Virtual Entity Sample Solution. I plan to grow this over time and add more features, but it’s a good starting point and I welcome feedback and contributions. A big thanks to @rappen for allowing me to use some of his code to translate FetchXML to SQL that he has included as open source in FetchXML Builder with some slight modifications.
- There are 4 solutions available both the managed / unmanaged solution for the Sample App, which contains the preconfigured entities for the sample SQL database and the Provider only App that contains only the provider so you can roll your own Virtual Entities.
- Import the solution by going to make.powerapps.com and logging into your tenant.
- Select the environment you want to import the solution into.
- Go to Solutions and select Import and select the solution you downloaded.
- After the Solution has been imported you should see a new Model Driven App under Apps called “Azure SQL Product Catalog Sample”
- This app is based on the sample data that is installed with the Azure SQL database when you choose to install sample data. In the next article I’ll walk through how I created this app so you can create your own using the same Data Provider.
- The final step to configuring the app to display Products and Product Categories from Azure SQL is to configure the Provider using your specific SQL Connection String.
- Open the Azure SQL Product Catalog Sample App (NOTE: You’ll see an error when you launch the app because you haven’t configured the connection)
- Open Advanced Find
- Search for ‘SQL Providers’
- Open the record that is returned and paste the SQL Connection String from Azure SQL and Save the Record
- IMPORTANT: You will need to replace the {your_password} in the connection string with your password.
ALSO IMPORANT: I noticed that the connection string generated by Azure SQL occasionally doesn’t work without some tweaking. One of the extra key/values it adds is MultipleActiveResultSets=False; when present my connection failed. After removing this key/value it succeeded so you may need to tweak your connection string if you get an error with the generated connection string.Edit: This issue was because the field length for the connection string was initially too short. I’ve since updated the solution to set the connection string field to accept up to 1024 characters.
- Click on Products or Product Categories from the Left Menu to see the Products and Categories stored in Azure SQL.
- If you get an error at this point it could be related to your Azure SQL Firewall and as such you may need to allow specific IPs through.
That’s it. At this point you have a working Azure SQL Virtual Entity that allows you to display read-only data from SQL without using any of your precious CDS storage quota.
A couple of things to note.
- The provider handles converting the native FetchXml to SQL. However, it does have some current limitations that I plan to address in future versions.
- The provider doesn’t require that there be a guid column as a key in your data source. It will work with either a guid or an integer value as a key without any additional configuration.
- The provider allows you to use your virtual entity as a lookup on either a standard, custom or another virtual entity.
Feel free to provide feedback via comments and / or on github.