Lesson Learned #113: Experimenting with Database Experimentation Assistant tool

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

Today, we worked in an interesting service request when our customer is using DEA - Database Experimentation Assistant tool that basically, is an experimentation solution for SQL Server upgrades (OnPremise or Azure) that can help you evaluate a targeted version of SQL Server for a specific workload. Customers who are upgrading from earlier SQL Server versions (starting with 2005) to a more recent version of SQL Server can use the analysis metrics that the tool provides. In this case we are migrating from OnPremise to Azure SQL Managed Instance.

We found an issue that our customer was not able to use the Trace file due to a security issue and we are going to explain how we fixed this issue.

Everytime that our customer tries to use they got the following error message "Failed to preprocess, usually this is a result of an invalid or empty trace / xevent file, please get the detailed information from log file."

Following I would like to explain the issue found and how I fixed:

In my OnPremise database I created a database called Amanda, creating a table and an script to simulate a workload.

CREATE TABLE [dbo].[Table_1]( [ID] [int] IDENTITY(1,1) NOT NULL, [VALUE] [nchar](10) NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO declare @j as int = 0 while(@j<100000) begin set @j=@j+1 insert into Table_1 (value) values(@j) end

I created the captured file using trace format.

If I try to open the file I got an access denied.

I found that the trace was generated by MSSQLSERVER account and the owner and the permissions given was just only for this account. For this reason, we faced “Access Denied” Error.
In this situation, to be able to read the file using the DEA Tool, we need to take the ownership of the file and provide the full permissions for this.