Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1015551%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23113%3A%20Experimenting%20with%20Database%20Experimentation%20Assistant%20tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015551%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20we%20worked%20in%20an%20interesting%20service%20request%20when%20our%20customer%20is%20using%20DEA%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdea%2Fdatabase-experimentation-assistant-overview%3Fview%3Dsql-server-ver15%2520%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EDatabase%20Experimentation%20Assistant%3C%2FA%3E%20tool%20that%20basically%2C%26nbsp%3Bis%20an%20experimentation%20solution%20for%20SQL%20Server%20upgrades%20(OnPremise%20or%20Azure)%20that%26nbsp%3Bcan%20help%20you%20evaluate%20a%20targeted%20version%20of%20SQL%20Server%20for%20a%20specific%20workload.%20Customers%20who%20are%20upgrading%20from%20earlier%20SQL%20Server%20versions%20(starting%20with%202005)%20to%20a%20more%20recent%20version%20of%20SQL%20Server%20can%20use%20the%20analysis%20metrics%20that%20the%20tool%20provides.%20In%20this%20case%20we%20are%20migrating%20from%20OnPremise%20to%20Azure%20SQL%20Managed%20Instance.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20found%20an%20issue%20that%20our%20customer%20was%20not%20able%20to%20use%20the%20Trace%20file%20due%20to%20a%20security%20issue%20and%20we%20are%20going%20to%20explain%20how%20we%20fixed%20this%20issue.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEverytime%20that%20our%20customer%20tries%20to%20use%20they%20got%20the%20following%20error%20message%20%22%3CSTRONG%3EFailed%20to%20preprocess%2C%20usually%20this%20is%20a%20result%20of%20an%20invalid%20or%20empty%20trace%20%2F%20xevent%20file%2C%20please%20get%20the%20detailed%20information%20from%20log%20file.%3C%2FSTRONG%3E%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFollowing%20I%20would%20like%20to%20explain%20the%20issue%20found%20and%20how%20I%20fixed%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIn%20my%20OnPremise%20database%20I%20created%20a%20database%20called%20Amanda%2C%20creating%20a%20table%20and%20an%20script%20to%20simulate%20a%20workload.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ECREATE%20TABLE%20%5Bdbo%5D.%5BTable_1%5D(%0A%20%20%20%20%20%20%20%5BID%5D%20%5Bint%5D%20IDENTITY(1%2C1)%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%5BVALUE%5D%20%5Bnchar%5D(10)%20NULL%2C%0ACONSTRAINT%20%5BPK_Table_1%5D%20PRIMARY%20KEY%20CLUSTERED%20%0A(%0A%20%20%20%20%20%20%20%5BID%5D%20ASC%0A)WITH%20(PAD_INDEX%20%3D%20OFF%2C%20STATISTICS_NORECOMPUTE%20%3D%20OFF%2C%20IGNORE_DUP_KEY%20%3D%20OFF%2C%20ALLOW_ROW_LOCKS%20%3D%20ON%2C%20ALLOW_PAGE_LOCKS%20%3D%20ON)%20ON%20%5BPRIMARY%5D%0A)%20ON%20%5BPRIMARY%5D%0AGO%0A%0A%0Adeclare%20%40j%20as%20int%20%3D%200%0A%0Awhile(%40j%26lt%3B100000)%0Abegin%0Aset%20%40j%3D%40j%2B1%0Ainsert%20into%20Table_1%20(value)%20values(%40j)%0Aend%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EI%20created%20the%20captured%20file%20using%20trace%20format.%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20952px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157733i2B75A7C9B27E1956%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture1_dea.jpg%22%20title%3D%22capture1_dea.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157735iBA547D554FA70BE7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture2_dea.jpg%22%20title%3D%22capture2_dea.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EI%3CSTRONG%3Ef%20I%20try%20to%20open%20the%20file%20I%20got%20an%20access%20denied.%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20823px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157740i6C117B6C6D94632A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture4_dea.jpg%22%20title%3D%22capture4_dea.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20698px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157742iF9D6CEEF69DC9683%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture5_dea.jpg%22%20title%3D%22capture5_dea.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EI%20found%20that%20the%20trace%20was%20generated%20by%20MSSQLSERVER%20account%20and%20the%20owner%20and%20the%20permissions%26nbsp%3Bgiven%20was%26nbsp%3Bjust%20only%20for%20this%20account.%20For%20this%20reason%2C%20we%26nbsp%3Bfaced%20%E2%80%9CAccess%20Denied%E2%80%9D%20Error.%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EIn%20this%20situation%2C%20to%20be%20able%20to%20read%20the%20file%20using%20the%20DEA%20Tool%2C%20we%20need%20to%20take%20the%20ownership%20of%20the%20file%20and%20provide%20the%20full%20permissions%20for%20this.%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20494px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157743iF89D3F317FE9D688%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture6_dea.jpg%22%20title%3D%22capture6_dea.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20882px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157744i22AE9DB205D3C2A4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture7_dea.jpg%22%20title%3D%22capture7_dea.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20491px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157754i6BFFB65C3F4214D4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture81_dea.jpg%22%20title%3D%22capture81_dea.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EAt%20this%20point%20we%20were%20able%20to%20open%20the%20file%20in%20SQL%20Server%20Profiler%20and%20run%20the%20replay%20process.%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20958px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157757i2FD83A478670EDD4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture11_dea.jpg%22%20title%3D%22capture11_dea.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EAnd%20the%20process%20finished%20correctly%3A%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157761iB09CB0F7D00CBA84%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture121_dea.jpg%22%20title%3D%22capture121_dea.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUsing%20the%20XEvents%20format%2C%20I%20didn't%20see%20this%20issue%20with%20the%20permissions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1015551%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20we%20worked%20in%20an%20interesting%20service%20request%20when%20our%20customer%20is%20using%20DEA%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdea%2Fdatabase-experimentation-assistant-overview%3Fview%3Dsql-server-ver15%2520%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EDatabase%20Experimentation%20Assistant%3C%2FA%3E%20tool%20that%20basically%2C%26nbsp%3Bis%20an%20experimentation%20solution%20for%20SQL%20Server%20upgrades%20(OnPremise%20or%20Azure)%20that%26nbsp%3Bcan%20help%20you%20evaluate%20a%20targeted%20version%20of%20SQL%20Server%20for%20a%20specific%20workload.%20Customers%20who%20are%20upgrading%20from%20earlier%20SQL%20Server%20versions%20(starting%20with%202005)%20to%20a%20more%20recent%20version%20of%20SQL%20Server%20can%20use%20the%20analysis%20metrics%20that%20the%20tool%20provides.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20found%20an%20issue%20that%20our%20customer%20was%20not%20able%20to%20use%20the%20Trace%20file%20due%20to%20a%20security%20issue%20and%20we%20are%20going%20to%20explain%20how%20we%20fixed%20this%20issue.%3C%2FP%3E%3C%2FLINGO-TEASER%3E

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!