In this blog, we highlight a lesson learned when we used a Dynamics 365 Customer Engagement online data source connector, called Instance Web API, for Power BI reports.
Have you ever wanted to use T-SQL to query Dynamics 365 Customer Engagement (CE) or CDS data from a cloud-hosted environment, without having to replicate your data using the Data Export Service (DES) or some other replication solution? Of course you have! With the upcoming 2020 release Wave 1, Microsoft is rolling out this capability to all of us eager CRM data nerds. There are some steps involved to set things up, and we will go over those today.
First, a brief look at the long sought-after endgame here: writing and executing a SQL query against a live CDS environment! There are other ways we could connect to this new endpoint besides a query in SQL Server Management Studio (like via .NET C#), however, SSMS allows for easy validation of the feature setup.
1. Your CDS environment version MUST be 22.214.171.12437 or higher. This is needed to enable the feature on the backend. At time of writing, this feature is still in Preview, so take that into account when considering whether to use this in your production environments. To easily determine the version of your current CDS environment, login to it, click the Settings gear in the upper-right corner, and click About. If you’re unsure why you’re on a specific version, it is often helpful to check the Microsoft Dynamics 365 CE release train documentation for information on when to expect version availability in your region.
2. You will need a way to modify your OrgDBOrgSettings feature flags. This is typically done with the Microsoft OrgDBOrgSettings Tool (select CRM2016-Tools-KB4046795-ENU-amd64.exe for the download), but can be done in various other ways as well, like the excellent OrgDBOrgSettings managed solution from Sean McNellis which has also been updated to support this new feature flag, which is ultimately what was used here.
3. The Microsoft documentation states you’ll need SQL Server Management Studio (SSMS) version 18.4 or greater to be able to connect to the CDS database, and you should ideally go with that recommendation. That said, we’ve been able to connect successfully from SSMS version 17.9.1.
4. This is only for querying data from a CDS database (read-only). You will not be able to use it to do inserts or updates.
5. Security: Note that your read security permissions in CRM carry over to SQL – if you do not have read-access for the Account entity for example, you cannot query it from the CDS SQL endpoint.
1. Verify the current version of your CDS environment as mentioned in the prerequisites. Once you have verified that your CDS environment is on version 126.96.36.19937 or later, you can proceed. If you’re not yet on that version or later, turn back now because you will not be able to enable this feature.
2. Next, you’ll need to enable the Tabular Data Stream (TDS) endpoint for CDS for your environment. This can be done via the OrgDBOrgSettings Tool from Microsoft, the OrgDBOrgSettings managed solution from Sean McNellis, both of which currently support editing this new feature flag (you can find links to these in the prerequisites section). The feature flag we need to enable is called ‘EnableTDSEndpoint’.
3. Now you should be able to connect to your environment from SQL Server Management Studio (SSMS)!
Note: If you get the following error shown below: “Login failed: TDS protocol endpoint is disabled for this organization…”, you need to revisit step 2, as your EnableTDSEndpoint flag is not set correctly.
If you’ve done your setup correctly, when you connect to the org via SSMS (step 3 above), you should see the org DB appear on the left in the Object Explorer. For those unfamiliar, you can expand the top-level org URL, then expand the Databases section, wherein you can see your CDS database. If you expand the Tables folder in there, you can see your CDS entity tables, just as if you were connected to an on-premises CRM SQL database!
Per Microsoft, the following operations are supported (but there are more that do function outside this list): SELECT, UNION, JOIN, FILTER, batch operations, and aggregate operations like COUNT() and MIN() or MAX(). The limitation that FetchXML has for a max of 50,000 aggregate rows is gone when we use T-SQL here as well, which is great! As mentioned before, this is a read-only database, so any operation that would need write permissions to the database will not work.
Here’s a simply query, and the expected result:
select a.accountid, a.accountnumber, case when a.accountnumber is not null then 1 when a.accountnumber is null then 0 end as test_case from dbo.account a (nolock)
One would expect that T-SQL queries should perform much better and faster than their FetchXML counterparts when it comes to querying from CDS, as we should be circumventing entire process of a FetchXML query being translated to a T-SQL query on the backend.
To test this, we loaded 100,000 rows into the Account table of our CDS environment, with only a few fields of data populated as a simple performance test. To test FetchXML query performance against a large set of records, we used version 1.2019.12.1 of the FetchXML Builder add-on for the XrmToolBox application. For the T-SQL query performance, SSMS version 18.5.
Test 1: Query 3 Attributes for all Accounts
Results (mean of 5 test runs for each query):
FetchXML: 16.97 seconds T-SQL: 5.17 seconds Average Improvement: 11.8 seconds
Test 2: Query 10 Attributes for all Accounts with a Join to Users
Results (mean of 5 test runs for each query):
FetchXML: 44.62 seconds T-SQL: 83.51 seconds Average Improvement: -38.89 seconds
What do these results mean? Well, first of all, we must remember that this test is being run on a trial org in the India region due to current feature availability. That means the org will not have the same kind of resource allocation that a production environment would, so we would expect more complex queries to perform faster than they are performing here in a real-world situation. Other entity tables would perform differently than the ‘heavier’ Account entity as well. The inflection point where the number of columns began to make the Account T-SQL query slower than the FetchXML query was at 6 columns. Doing a query of all Accounts and all Columns (bad query, good load test) will hit the 2-minute timeout every time in this environment. Your mileage may absolutely vary here, so you’ll want to performance test your own specific use-cases in your environment to confirm which path may be better for now. It is likely that with the GA release of this feature and continued developments, most if not all queries against the CDS SQL endpoint should be faster than FetchXML.
This is very new feature (in Preview still at time of writing), so there are currently several limitations you need to consider before using this solution. Here is our list of currently known or discovered limitations with this feature:
select s.fullname, s.systemuserid, s.createdon, a.accountid from dbo.systemuser s (nolock) left join dbo.account a (nolock) on s.systemuserid = a.ownerid
However, if we run this query without aliases, it fails:
select * from dbo.systemuser (nolock) left join dbo.account (nolock) on dbo.systemuser.systemuserid= dbo.account.ownerid
The CDS SQL endpoint is a feature many of us have been waiting for since the inception of CRM being in the cloud back in the days when we referred to the product simply as CRM Online. Now with Dynamics 365 CE/CDS, this feature is most welcome, and while it has certain limitations currently, we expect many of those limitations to be resolved as the product feature matures. As always, don’t hesitate to reach out to us if you have questions – we’re glad to help!