Avez-vous déjà voulu utiliser T-SQL pour interroger Dynamics 365 Customer Engagement (CE) ou des données CDS à partir d'un environnement hébergé dans le cloud, sans avoir à répliquer vos données à l'aide du service d'exportation de données (DES) ou d'une autre solution de réplication? Bien sûr que oui! Avec la sortie de la vague 2020 à venir en 1, Microsoft déploie cette capacité à tous les nerds passionnés de données CRM. Il y a quelques étapes à franchir pour mettre les choses en place, et nous allons les passer en revue aujourd'hui.

Tout d'abord, un bref aperçu de la fin de jeu recherchée depuis longtemps: l'écriture et l'exécution d'une requête SQL sur un environnement CDS en direct! Il existe d'autres façons de se connecter à ce nouveau point de terminaison en plus d'une requête dans SQL Server Management Studio (comme via .NET C #), cependant, SSMS permet une validation facile de la configuration des fonctionnalités.

Conditions préalables et mises en garde

1. La version de votre environnement CDS DOIT être 9.1.0.17437 ou supérieur. Cela est nécessaire pour activer la fonctionnalité sur le backend. Au moment de la rédaction de ce document, cette fonctionnalité est toujours dans l'aperçu, alors tenez-en compte lorsque vous envisagez de l'utiliser dans vos environnements de production. Pour déterminer facilement la version de votre environnement CDS actuel, connectez-vous à celui-ci, cliquez sur le Paramètres engrenage dans le coin supérieur droit, puis cliquez sur À propos de. Si vous ne savez pas pourquoi vous utilisez une version spécifique, il est souvent utile de vérifier Microsoft Dynamics 365 CE libérer la documentation du train pour savoir quand prévoir la disponibilité de la version dans votre région.

2. Vous aurez besoin d’un moyen de modifier votre OrgDBOrgSettings indicateurs de fonctionnalité. Cela se fait généralement avec le Outil Microsoft OrgDBOrgSettings (sélectionnez CRM2016-Tools-KB4046795-ENU-amd64.exe pour le téléchargement), mais vous pouvez également le faire de diverses autres manières, comme l'excellent Solution gérée OrgDBOrgSettings de Sean McNellis qui a également été mis à jour pour prendre en charge ce nouveau drapeau de fonctionnalité, qui est finalement ce qui a été utilisé ici.

3. La documentation Microsoft indique que vous aurez besoin SQL Server Management Studio (SSMS) version 18.4 ou supérieur pour pouvoir se connecter à la base de données CDS, et vous devriez idéalement suivre cette recommandation. Cela dit, nous avons réussi à nous connecter avec succès à partir de la version 17.9.1 de SSMS.

4. Cela ne sert qu'à interroger les données d'une base de données CDS (en lecture seule). Vous ne pourrez pas l'utiliser pour faire des insertions ou des mises à jour.

5. Sécurité: Notez que vos autorisations de sécurité en lecture dans CRM sont transférées vers SQL - si vous n'avez pas d'accès en lecture pour l'entité Compte par exemple, vous ne pouvez pas l'interroger à partir du point de terminaison CDS SQL.

Configuration en 3 étapes

1. Vérifiez la version actuelle de votre environnement CDS comme indiqué dans les conditions préalables. Une fois que vous avez vérifié que votre environnement CDS est bien sur la version 9.1.0.17437 ou ultérieure, vous pouvez continuer. Si vous n'êtes pas encore sur cette version ou version ultérieure, revenez en arrière maintenant car tu ne vas pas être en mesure d'activer cette fonctionnalité.

  • Si vous ne disposez pas de cette version ou d'une version plus récente pour votre environnement CDS, vérifiez le Microsoft Dynamics 365 CE libérer la documentation du train pour savoir quand prévoir la disponibilité de la version dans votre région.

2. Ensuite, vous devrez activer le point d'extrémité Tabular Data Stream (TDS) pour CDS pour votre environnement. Cela peut être fait via l'OrgDBOrgSettings Tool de Microsoft, la solution gérée OrgDBOrgSettings de Sean McNellis, qui prennent actuellement en charge la modification de ce nouvel indicateur de fonctionnalité (vous pouvez trouver des liens vers ceux-ci dans la section des conditions préalables). L'indicateur de fonctionnalité que nous devons activer est appelé 'EnableTDSEndpoint '.

  • Utilisation de l'outil OrgDBOrgSettings: il y a un grand rédaction de Microsoft sur la façon de procéder, cependant, nous rencontrions systématiquement cette erreur lorsque nous essayions d'exécuter la dernière étape, comme l'ont fait d'autres membres de la communauté: 'Une erreur s'est produite dans OrgDBOrgSettings et les détails de l'erreur sont que le fournisseur de ressources GDS n'est pas en mesure d'obtenir des instances pour tenantId:…".

  • Pour contourner cette erreur, la solution gérée OrgDBOrgSettings a été utilisée à la place. Installez simplement la solution gérée et ouvrez la page de configuration de la solution. Vous verrez une liste complète des paramètres d'Org DB que vous pouvez modifier - recherchez celui nommé EnableTDSEndpoint.

  • Cliquez Ajoutez lien pour le EnableTDSEndpoint ligne, puis cliquez sur Éditeret définissez la valeur sur vrai et cliquez sur Actualité. Le résultat doit être une ligne qui ressemble à ceci:

3. Vous devriez maintenant pouvoir vous connecter à votre environnement à partir de SQL Server Management Studio (SSMS)!

  • Comme indiqué dans les prérequis, vous devrez avoir SSMS version 18.4 ou ultérieure pour se connecter à une base de données CDS.
  • Pour vous connecter, placez simplement l'URL de votre organisation dans le champ Nom du serveur et ajoutez ",5558’ jusqu'à la fin. Donc, si l'URL de votre organisation est dancat.crm.dynamics.com, le champ Nom du serveur de connexion doit être dancat.crm.dynamics.com, 5558.
  • Définissez votre liste déroulante d'authentification sur "Azure Active Directory - Mot de passe'. Actuellement, c'est la seule façon de vous connecter à une base de données CDS à partir de SSMS - vous ne doivent pas pouvoir utiliser l'authentification Windows ou l'authentification SQL. Saisissez votre nom d'utilisateur et votre mot de passe, puis cliquez sur Contact.

importante: Si vous obtenez l'erreur suivante illustrée ci-dessous: «Échec de la connexion: le point de terminaison du protocole TDS est désactivé pour cette organisation…", Vous devez revoir l'étape 2, car votre indicateur EnableTDSEndpoint n'est pas défini correctement.

Interrogation de la base de données CDS

Si vous avez correctement effectué votre configuration, lorsque vous vous connectez à l'organisation via SSMS (étape 3 ci-dessus), vous devriez voir la base de données d'organisation apparaître à gauche dans l'Explorateur d'objets. Pour ceux qui ne sont pas familiers, vous pouvez développer l'URL de l'organisation de niveau supérieur, puis développer la section Bases de données, dans laquelle vous pouvez voir votre base de données CDS. Si vous développez le dossier Tables à l'intérieur, vous pouvez voir vos tables d'entités CDS, comme si vous étiez connecté à une base de données CRM SQL locale!

Par Microsoft, les opérations suivantes sont prises en charge (mais il y en a d'autres qui fonctionnent en dehors de cette liste): SELECT, UNION, JOIN, FILTER, opérations par lots et opérations d'agrégation comme COUNT () et MIN () ou MAX (). La limitation que FetchXML a pour un maximum de 50,000 XNUMX lignes agrégées a disparu lorsque nous utilisons également T-SQL ici, ce qui est génial! Comme mentionné précédemment, il s'agit d'une base de données en lecture seule, donc toute opération qui aurait besoin d'autorisations d'écriture dans la base de données ne fonctionnera pas.

Voici une simple requête et le résultat attendu:

sélectionnez a.accountid, a.accountnumber, cas où a.accountnumber n'est pas nul puis 1 quand a.accountnumber est nul puis 0 fin comme test_case de dbo.account a (nolock)

performance

On pourrait s'attendre à ce que les requêtes T-SQL fonctionnent beaucoup mieux et plus rapidement que leurs homologues FetchXML lorsqu'il s'agit d'interroger à partir de CDS, car nous devrions contourner tout le processus d'une requête FetchXML traduite en requête T-SQL sur le backend.

Pour tester cela, nous chargé 100,000 XNUMX lignes dans la table des comptes de notre environnement CDS, avec seulement quelques champs de données remplis comme un simple test de performance. Pour tester les performances des requêtes FetchXML par rapport à un grand nombre d'enregistrements, nous avons utilisé la version 1.2019.12.1 du module complémentaire FetchXML Builder pour l'application XrmToolBox. Pour les performances des requêtes T-SQL, SSMS version 18.5.

Test 1: Attributs de la requête 3 pour tous les comptes

Résultats (moyenne de 5 séries de tests pour chaque requête):

FetchXML: en 16.97 secondes T-SQL: 5.17 seconde Amélioration moyenne: 11.8 seconde

Test 2: requête 10 attributs pour tous les comptes avec une jointure aux utilisateurs

Résultats (moyenne de 5 séries de tests pour chaque requête):

FetchXML: en 44.62 secondes T-SQL: 83.51 seconde Amélioration moyenne: -38.89 secondes

Que signifient ces résultats? Eh bien, tout d'abord, nous devons nous rappeler que ce test est exécuté sur une organisation d'essai dans la région de l'Inde en raison de la disponibilité actuelle des fonctionnalités. Cela signifie que l'organisation n'aura pas le même type d'allocation de ressources qu'un environnement de production, nous nous attendons donc à ce que les requêtes plus complexes s'exécutent plus rapidement qu'elles ne le font ici dans une situation réelle. Les autres tables d'entités fonctionneraient également différemment de l'entité de compte «plus lourde». Le point d'inflexion où le nombre de colonnes a commencé à ralentir la requête Account T-SQL que la requête FetchXML était à 6 colonnes. Faire une requête de tous les comptes et de toutes les colonnes (mauvaise requête, bon test de charge) atteindra le délai d'attente de 2 minutes à chaque fois dans cet environnement. Votre kilométrage peut absolument varier ici, vous devrez donc tester les performances de vos propres cas d'utilisation spécifiques dans votre environnement pour confirmer quel chemin peut être le mieux pour l'instant. Il est probable qu'avec la sortie GA de cette fonctionnalité et les développements continus, la plupart sinon toutes les requêtes sur le point de terminaison CDS SQL devraient être plus rapides que FetchXML.

Limites

Il s'agit d'une toute nouvelle fonctionnalité (dans Aperçu toujours au moment de la rédaction), il existe donc actuellement plusieurs limitations que vous devez prendre en compte avant d'utiliser cette solution. Voici notre liste des limitations actuellement connues ou découvertes avec cette fonctionnalité:

  • Toutes les tables de l'environnement CDS ne sont pas utilisables avec cette fonction. Les tables comme audit et plugintracelog ne sont pas disponibles pour les requêtes ici. Une agréable surprise pour quiconque a utilisé le service d'exportation de données pour répliquer une base de données CDS vers Azure SQL notera que l'entité dbo.activitypointer est présente dans cette nouvelle fonctionnalité de requête CDS DB, même si elle n'est pas prise en charge pour DES.
  • Tous les attributs des tables d'environnement CDS ne sont pas utilisables avec cette fonction. Selon la documentation Microsoft, les types de données de binaire, image, ntext, sql_variant, varbinary, virtuel, HierarchyId, propriété gérée, filet, xml, partyliste horodatage ne sont pas utilisables pour le moment dans une requête SQL CDS. Le plus notable de cette liste est peut-être ntext. Sans support pour ntext, vous ne verrez pas de données pour les champs Ligne de texte multiple dans vos requêtes. En fait, le système semble fonctionner un peu bizarrement à l'heure actuelle - si vous regardez l'entité native Account, vous pouvez voir qu'elle a un champ Description qui serait un type de données SQL ntext. Dans la liste développée des colonnes pour l'entité dbo.account dans SSMS, elle n'affiche pas le champ Description… qui a du sens, non? Sauf si vous faites une description SELECT FROM dbo.account, elle retourne la colonne, mais avec des données NULL indépendamment de ce qui se trouve réellement dans le champ.
  • Une syntaxe ne fonctionne tout simplement pas encore. Les requêtes sans biais se comportent étrangement dans la fonction de requête SQL CDS - si nous devions exécuter cette requête, cela fonctionne:
sélectionnez s.fullname, s.systemuserid, s.createdon, a.accountid de dbo.systemuser s (nolock) gauche rejoindre dbo.account a (nolock) sur s.systemuserid = a.ownerid

Cependant, si nous exécutons cette requête sans alias, elle échoue:

sélectionnez * dans dbo.systemuser (nolock) à gauche, rejoignez dbo.account (nolock) sur dbo.systemuser.systemuserid = dbo.account.ownerid

  • Les expressions de table communes (CTE) ne sont pas encore prises en charge. C'est un gros problème, mais nous nous attendons à ce qu'il soit ajouté à la fonctionnalité en raison de la valeur des CTE pour les données CRM.
  • Sécurité possible / autres implications pour l'utilisation de Retrieve et RetrieveMultiple. À partir de nos tests, si vous pouvez vous connecter à CRM, vous pouvez vous connecter à la base de données CDS SQL en lecture seule. Vos autorisations de sécurité sont transférées à CDS SQL pour la plupart, en ce sens que si vous ne pouvez pas lire les enregistrements d'entité dans l'interface CRM, vous ne pourrez pas les lire à partir du point de terminaison CDS SQL. Il y a cependant un léger écart, qui ne devrait pas affecter de nombreuses implémentations, mais il est important pour certains en ce qui concerne la sécurité des données - si vous avez une logique qui se déclenche sur Retrieve ou RetrieveMultiple, cette logique NE TIRERA PAS lorsque les mêmes données sont interrogées à partir du point de terminaison CDS SQL à partir de maintenant.
    • Par exemple, si un utilisateur a un accès en lecture à une entité, mais que cette entité a un plug-in synchrone exécuté sur RetrieveMultiple de cette entité pour masquer la charge utile de retour, un utilisateur peut contourner cela avec la connexion SQL CDS à partir de maintenant. Il existe des théories sur un nouveau type de message qu'un plug-in pourrait lier afin d'avoir une logique d'interception similaire sur une requête T-SQL pour une entité, mais qui n'a pas encore été documentée ou confirmée pour le moment.
    • Il existe peut-être un moyen de bloquer l'utilisation des points de terminaison CDS SQL pour des utilisateurs spécifiques via des rôles de sécurité, mais qui n'a pas encore été identifié ou documenté.
  • Le délai de 2 minutes est toujours une chose! Dans les conditions par défaut, le point de terminaison CDS SQL ne tolérera pas les requêtes de longue durée. Si une requête T-SQL s'exécute pendant plus de 120 secondes, que ce soit en raison de requêtes mal formées ou de limitations d'environnement, la requête échouera avec le message d'erreur: "Le canal de demande a expiré en attendant une réponse après 00:02:00. Augmentez la valeur du délai d'attente passé à l'appel à Request ou augmentez la valeur SendTimeout sur la liaison. Le temps alloué à cette opération peut avoir été une partie d'un délai plus long.”Tenez compte de cela lorsque vous utilisez le point de terminaison CDS SQL et, comme pour les autres requêtes, effectuez-les de la manière la plus conservatrice possible, en vous rappelant qu'une requête CRM qui s'exécute normalement pendant 1 minute peut prendre 2 minutes ou plus dans différentes conditions de charge.

  • Vous vous reconnecterez assez souvent dans SSMS. En fin de compte, cela peut être une bonne fonctionnalité de sécurité, mais SSMS vous déconnectera du point de terminaison CDS SQL s'il y a une inactivité pendant ce qui semble être de 10 minutes. Cela peut être configurable, mais cela n'est pas connu pour le moment.

Conclusion

Le point de terminaison CDS SQL est une fonctionnalité que beaucoup d'entre nous attendaient depuis la création de CRM dans le cloud à l'époque où nous appelions simplement le produit CRM Online. Désormais, avec Dynamics 365 CE / CDS, cette fonctionnalité est la bienvenue, et bien qu'elle présente actuellement certaines limitations, nous nous attendons à ce que bon nombre de ces limitations soient résolues à mesure que la fonctionnalité du produit arrive à maturité. Comme toujours, n'hésitez pas à nous contacter si vous avez des questions - nous sommes heureux de vous aider!

Avatar de Joe D365

Joe D365

Joe D365 est un super-héros Microsoft Dynamics 365 qui s'exécute sur l'adrénaline pure. En tant que visage de PowerObjects, la mission de Joe D365 est de révéler des moyens novateurs d’utiliser Dynamics 365 et d’apporter l’application à un plus grand nombre d’entreprises et d’organisations du monde entier.