DEA – Fixing SQLPackage bug in DEA with SQL Server 2022
Welcome to a new article in Aleson ITC’s blog. In today’s post we will talk about DEA (Database Experimentation Assistant), we will explain what it is used for and we will see a possible error that can occur when we are making a comparison between 2 SQL Server traces.
We decided to test the different tools in order to compare SQL Server workloads. This is quite common, both in pre-production environments and as a preliminary step in database migrations.
What is DEA?
DEA (Database Experimentation Assistant), is a Microsoft application that allows us to compare SQL Server workloads by means of traces. It will allow us to perform 3 operations:
Capture a trace
We will be able to capture a specific trace on a server, and we will be able to save it in a specific path, together with the execution metrics.
Replay a trace
We can execute a trace that we have previously captured in an environment, and in turn, generate a resulting trace, with the metrics of this execution.
Comparison of traces
We can compare two traces, using metrics, and we can check if we find any of the following problems:
- Query compatibility issues.
- Queries or degraded implementation plans.
- Overloading in some of the traces analysed.
After collecting the trace on the source server and executing the replay on the target server without any problem, we went to process the traces to get the comparison of the traces.
Error detection when processing
In trying to obtain the results of our analysis, we encountered the following problem. We encountered what seems to be a bug in the tool, which we hope to fix soon. But in the meantime, I’m sharing it with you in case it happens to more people, so that it may help you to analyse your workloads.
The error:
“An error occurred while creating Analysis Database. DacPac publishing failed.”
After reviewing the detailed error and the application logs, it appeared that the problem originated at the security level with the connection when executing the DacPac package.
Unable to connect to target server 'LAPTOP-XXXXXX'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server. A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate is not valid for the requested usage.). The certificate is not valid for the requested usage.
Next, once we have reviewed the documentation, we installed an older version of SQL Server 2022, as it has by default that the connection has to be encrypted. We installed an older SQL Server, 2016 for example. We run it again and get the same result.
After not getting the desired result in the previous step, we went back to baseline. We checked the error log again, and saw that what was failing was when executing a SQLPackage command.
We detected that in version 161.XXXX.X of SQLPackage (module that is installed with the SQL Server Data-tier Application Framework as a requirement for the operation of the DEA), the module for encrypted connections is implemented, forcing all resources that use this SQLPackage, have to declare the security of the connection. This is what was causing the problem. Since the command, from the DEA tool, generates the command to “Hardcoded”, without taking into account this possible encryption of the connection. For this reason we installed the version just before this update, which is the SQLPackage 19.2 (From version 19.2, it jumped to 161.X). In order to install this version, we had to download the SQL Server Data-tier Application Framework 16.0.62.96.0.
If we update and install the old version, we try to process the traces again.
To set up the analysis, we will first have to assign the server we are going to use to process the traces, along with a name for the analysis. Next, we will select where we have the traces if, locally or in a Blob, and then the path to the traces. This time successfully.
After checking that it worked correctly, we repeated the procedure, with the old version of SQLPackage, on a SQL Server 2022. Indeed, the problem persists because although the SQLPackage supports the statement, the SQL Server does not allow the connection to be established. This is, again, because the DEA does not specify the security context in the connection, which is required in SQL Server 2022.
As at the date of publication of this entry, the problem still persists in version 2.6 of the DEA.
I hope you found this post useful, and I’ll see you in the next one.
Don’t forget to contact us if you have any questions!
Data Engineer.
