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.

capture1_dea.jpg

 

capture2_dea.jpg

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

capture4_dea.jpg

capture5_dea.jpg

 

  • 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.

 

capture6_dea.jpg

 

capture7_dea.jpg

capture81_dea.jpg

 

  • At this point we were able to open the file in SQL Server Profiler and run the replay process.

capture11_dea.jpg

  • And the process finished correctly:

capture121_dea.jpg

 

Using the XEvents format, I didn't see this issue with the permissions.

 

Enjoy!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.