Release: Database Experimentation Assistant (DEA) v2.6

[Jim Toland]

First published on MSDN on Aug 06, 2018

Authored by rajsell@microsoft.com

Overview

The Database Experimentation Assistant (DEA) is a new A/B testing solution for SQL Server upgrades. The utility will assist in evaluating a targeted version of SQL for a given workload. Customers who are upgrading from previous SQL Server versions (SQL Server 2005 and above) to any newer version of the SQL Server will be able to use the analysis metrics provided, such as queries that have compatibility errors, degraded queries, query plans, and other workload comparison data, to help them build confidence and better ensure a successful upgrade experience.

What's new in v2.6?

The v2.6 release of DEA has the following improvements:

- Capture and replay of production database(s) workloads through automated set up.

- Support for server side traces and XEvents.

- Perform statistical analysis on traces and/or XEvents collected using both old and new instances.

- Visualize data through analysis report via rich user experience.

- Use SQL Authentication to both capture and replay.

- An Inbuilt replay tool in addition to already supported SQL Server Distributed Replay for simple workloads.

- Removes the dependencies of R and R-Interop.

- Capture and replay workloads to Azure SQL Database , Azure SQL Managed Instance , and SQL Server on Linux.

- Reporting enhancements:

- New error categorization chart to easily find upgrade / migration blockers.

- New error pie chart grouped by error id to easily identify the root cause of the errors.

- Bug fixes and other performance improvements.

Tutorials

The following postings provide step-by-step guidance for leveraging DEA to perform a workload comparison:

- Overview of Database Experimentation Assistant

- Get started with Database Experimentation Assistant

- Capture a trace in Database Experimentation Assistant

- Configure replay in Database Experimentation Assistant

- Replay a trace in Database Experimentation Assistant

- Create analysis reports with Database Experimentation Assistant

- View analysis reports with Database Experimentation Assistant

- Run Database Experimentation Assistant at a command prompt

Installation

You can download and install DEA from the Microsoft Download Center. Run ‘DatabaseExperimentationAssistant.exe’ to install the Database Experimentation Assistant.

Supported sources and target versions

- Source: SQL Server 2005 and above
- Target: SQL Server 2005 and above
- Analysis: SQL Server 2008 and above

How to contact the DEA team?

Customers can submit feedback by using a simple interface within the DEA tool or by sending an email to Ask Azure Database Migrations <AskAzureDatabaseMigrations@service.microsoft.com>.

[jongurgul]

.

[ferchu2209]

Hi! I'm planning to implement DEA and I wonder if I will be able to use my Amazon Relational Database Service SQL Server Databases as target where I can run the load against. I saw that on this release of DEA you added SQL authentication feature, but not sure if Amazon Relational Database Service could be used as target server.
Thanks!!

[edd_tintin]

Hi,

I hope someone can help. I have installed DEA 2.6 and the reporter.exe is missing. I use RML Utilities a lot so know how useful this tool could be. Any suggestions? 

[Jim Toland]

The tutorials and links in this post have been refreshed.

[DanielAdeniji]

Family :-

Unable to use the GUI.

My laptop resolution is configured for 1366 by 768.

When I try to import Extended Events \ Trace files the bottom portion of the screen is not visible and I am unable to access any buttons.

I tried to resize, but the application is intentionally preventing window resize.

Is there any reason why this application is intentionally "eating" up this event.

Please help!

Daniel Adeniji

[Jim Toland]

Hi Daniel.

In your Display Settings, please make sure that Scale and layout is set no higher than 100% to ensure full visibility of the DEA interface.

[DanielAdeniji]

 Jim :-

Thanks for the pointer.

I went back and looked at my MS Windows 10.  

Unfortunately, it does not have the "Scale and Layout" settings easily accessible.

What actually worked for me is to change my desktop view from 2 monitors, the laptop and projected screen to a single display, the projected screen alone.

Interestingly enough, while troubleshooting I installed the application on one of my servers that I was accessing via Remote Desktop.  It did not help at the time.

And, now that works, as well.

Before Change :-

Change  ( Chose to Show Only On 2.. )

Post Change Visual

Daniel

[Jim Toland]

Thanks for the heads up, Daniel!

[DanielAdeniji]

Covered here.

Application Window, Does Not Fit on Screen

Link

[JulianDixon]

I've come across the absence of reporter.exe in the DEA 2.6 release too, as noted above by @edd_tintin.

My error message is:

06/26/19 15:18:28.951 [0X000004AC] The file [C:\Program Files (x86)\Microsoft Corporation\Reporter\Bin\Release\Reporter.exe] does not exist with operating system error 0x00000003 (The system cannot find the path specified)
06/26/19 15:18:28.951 [0X000004AC] Attempt to launch Reporter failed. Check the error log and your RML installation.

I thought Id be able to work around by downloading and installing RML but there doesn't seem to be a recent version...

Any thoughts?

[DanielAdeniji]

Julian Dixon :-

Please access SQL Server 2016 Feature Pack from here

You want to download the following packages :-

1) SQL System CLR TYpes

    ENU\x86\SQLSysClrTypes.msi

2) Report Viewer
    ENU\x86\ReportViewer.msi

Once downloaded, please install both msi packages.

And, you should be good.

 

[Jim Toland]

@JulianDixon, see Daniel's comment above.

[JulianDixon]

Thanks @DanielAdeniji,

I've installed the packages as you suggest and the error messages I previously posted have cleared from the ReadTraceLog.

However, I think the lack of reporter.exe I was seeing in the ReadTrace.log might have been a red herring.

I was previously (and am still) getting an error onscreen in DEA:

"An error occurred while creating Analysis Database. DacPac publishing failed."

This occurs repeatably at the stage:

36% complete, Creating report database

Despite this error message I can see the A & B databases have been created.

I also see in the ReadTrace.Log:

"* ReadTrace encountered one or more WARNINGS. A warning condition typically *
* continues processing with reduced functionality, but the ReadTrace output *
* may be adversely affected. Review the log file for details. *
*******************************************************************************
06/27/19 09:57:39.489 [0X000003D4] ***** ReadTrace exit code: 0"

The only warnings in the ReadTrace.Log are:

"06/27/19 09:57:35.596 [0X000003D4] WARNING: The following trace events were not captured: [Audit:Login, Audit:Logout]. Review the help file to ensure that you have collected the appropriate set of events and columns for your intended analysis."

and

"06/27/19 09:57:36.290 [0X000003D4] WARNING: One or more warning conditions exist that may affect the quality of the analysis data. See BFunctionTest636972226440609604.ReadTrace.tblWarnings table and the ReadTrace log for complete details."

The BFunctionTest636972226440609604.ReadTrace.tblWarnings table contains a single row with the warning message value of "One of more warning conditions exist that may affect the quality of the analysis data. Review tblWarnings and the ReadTrace log for details."

Any further assistance happily received!

[JulianDixon]

As DEA seemed to be struggling to deploy the DacPac to my SQL Server (SQL Server 2017) I installed an instance of SQL Server 2016 on the same server, pointed DEA at that and to my delight I have no more errors and can see the reports.

Is there any reason you can think of to explain why DEA Analysis seems to work in my environment with SQL Server 2016 but not SQL Server 2017?

[DanielAdeniji]

Julian Dixon :-

Glad you made such great progresses with so much fight in you.

I am not quite sure why SQL Server 2017 will not work, yet 2016 works.

Please do the following :-  

1) There are a few major steps in DEA, which specific one are you failing on 

2) Can you please review the SQL Server Error Log on the SQL Server 2017 machine; especially along the timeline when the errors was encountered

3) The other test is to re-run the process against SQL Server 2017 while engaging extended events or SQL Server Profiler and been sure to include error capture

You have made a lot of progress and done an exemplary job truly observing the steps and results and documenting them.

Again, thanks for greatly expanding the knowledge base.  

Daniel Adeniji

[rfitzgerald]

The documentation available currently does not provide any explanation on how to use XEvents when replaying a trace. What should I use for "Full path to source file" when I am configuring a replay and all I have is a big list of *.xel files?

[dbacmw]

I am getting the following error:

An unexpected error has occurred. Please restart the application and try again. If the problem persists, please contact the product team.

I have restarted the application and tried again.  

The server type is Azure SQL Database.

Is it possible to capture a trace on Azure SQL Database?

[Jim Toland]

@dbacmw, the details on the DEA 2.6 download page (and above) indicate that this version can capture and replay on Azure SQL Database, Azure SQL Managed Instance and SQL Server on Linux.

If you continue to experience issues, please contact the Database Experimentation Assistant Feedback alias (deafeedback@microsoft.com). Thanks!

[John Sterrett]

I get the following bacpac error on the analysis when I compare two traces or XEvents when SQL 2017 i used for the repository. I have no problems if SQL 2016 is the repository used for analysis.

Here is what is braking incase it helps.

DEA Information: 0 : DiagnosticEvent, 10/17/2019 19:20:16, Data: {"Message":"Error occurred while running process. Command: \"C:\\Program Files\\Microsoft SQL Server\\130\\DAC\\bin\\SqlPackage.exe /Action:Publish /SourceFile:\"ExperimentationDatabase.dacpac\" /TargetDatabaseName:\"AnalysisTempTable637069368000347489\" /TargetServerName:\"DESKTOP-H2V08UL\" /Variables:\"DatabaseA\"=\"ATempTable637069368000347489\" /Variables:\"DatabaseB\"=\"BTempTable637069368000347489\" /Variables:\"ReportName\"=\"TempTable\" /Variables:\"DEAVersion\"=\"2.6.61459.2\" /Variables:\"SchemaVersion\"=\"1.3\" /p:BlockOnPossibleDataLoss=False\", Error:\"*** Could not deploy package.\r\nUnable to connect to master or target server 'AnalysisTempTable637069368000347489'. You must have a user with the same password in master or target server 'AnalysisTempTable637069368000347489'.\r\n\r\n\"","Timestamp":"2019-10-17T19:20:16.2065652Z"

[Jim Toland]

@John Sterrett, the current logged in user running DEA requires sysadmin privileges to the analysis server. Based on the detail you provide, it as if the specific user doesn’t have sysadmin privileges in their server. Being a member of a group with sysadmin privileges is not sufficient - the user specifically needs sysadmin privileges on the server.

[panoslondon1]

@Jim Toland you say that DEA can capture and replay on Azure SQL Database, Azure SQL Managed Instance and SQL Server on Linux. Are you saying that it cannot work on normal Windows instance?! Also does the DEA follow the mechanisms of Distributed Replay Controller provided my MS which basically better simulates parallel data loads? Thank you.

[Jim Toland]

@panoslondon1 , support for capturing and replaying workloads to Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Linux are improvements offered in DEA 2.6. DEA already supports SQL Server on Windows. Also, DEA does follow DReplay Controller mechanisms.

[panoslondon1]

@Jim Toland i have raised a question in the msdn forums see link below prpbably no reason to recreate here. See link below. Thank you for any help. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/21395a77-1e45-42d3-a47f-755e4b2b0df3/databa...

[Jim Toland]

@panoslondon1 , have you reviewed the detail here and ensured that your environment meets all requirements?

[panoslondon1]

@Jim Toland, I believe so, i am the sysadmin and SQL Server running just on a pc, i am using SQL 2017 BTW. Can you confrim for the Windows account what setting i should have, I am the sysadmin, and can run profiler ok. I managed to "resolve" the issue by using a Sql server account and managed to get the trc file finally. Now Step 2 (replay), again i am using the same SA like account. This plays the trace but doesn't produce any replay results. It kept going for 24 mins when my capture was for only 5min. Then i stopped it to see if anything happened. I did get some long xel files but not no other trc files. Step 3 (reporting) i decided to use the same trc file for both sections to see if i will get anything. I am using the same SA like username. You mention analysis database, we don't need an SSAS database or something? Unfortunately i cannot supply you any screenshots to explain better. Do i have basically use Windows authentication for all 3 steps and should give up on the SA like account which seemed to have fix some of the issues? Also your documentation doesn't appear to be for latest version 2.6. Also same for the video tutorial on channel9. Any extra help appreciated. Thank you again.

[Jim Toland]

@panoslondon1 - Thanks for your feedback about the need to refresh the content. We are removing the outdated video and are currently working on a major content update. Re the issues you are experiencing, please email me directly (jtoland@microsoft.com) and I will work with the Database Experimentation Assistant team for guidance.  Thanks!

[panoslondon1]

Hi @Jim Toland After uninstalling SQL 2017 and started using SQL 2016 my reporting side is working now. Similar to what @JulianDixon mentioned previously. You should be able to reproduce if you try it on a 2017 instance. The reporter.exe error has disappared as well even though i don't see a reporter.exe in the DEA dir. I am hopefull i can get the replay working as well. Thanks.

[Jim Toland]

Thanks for the update, @panoslondon1. Please be sure to keep the related thread with the DEA Feedback alias up-to-date with your findings as well.

[panoslondon1]

Hi @Jim Toland, How much difference there is in terms of functionality/ performance between the inBuilt Tool and the Distributed replay controller. We would like to avoid setting the latter up if possible. The inBuild seems fine although to be fair i have set the maxDop = 1 in 1 session and the other = 0 and 99% of queries come up with the same time, bit surprising. I am running relatively simple sql but it's still 50-60k rows per table so was hoping that 5% diff should show something up. Also any ideas when the documentation will be available for ver 2.6? Also, is is the XEL format better in terms of detail than trc (trace)? I will pass my comments to the team as well. Thank you.

[Jim Toland]

@panoslondon1, sending your questions directly to the Database Experimentation Assistant Feedback alias (deafeedback@microsoft.com) is the most efficient way to get an answer.  Thanks!

[CBercero_Abarca]

Hi @Jim Toland,

Is the source code available?

Thank you!

[Jim Toland]

@CBercero_Abarca, no, we do not provide external access to the source code. Thanks for your interest!

[Yanek2412]

Hi. 

I have a very serious question.

Does DEA make changes into source  DB while "inbuit" replaying?

 Situation: 

We made trace capture on prod db, then made "inbuit" replay on prod db and then developers found duplicates rows that was "captured".

[Jim Toland]

@Yanek2412, DEA will make modifications to the target db against which you are replaying the captured trace if the source capture traces from production database have insert / update/ delete statements.

[Yanek2412]

But how can it be? If sql user has only public and data reader rights for prod db.

[Jim Toland]

@Yanek2412 , did you set up your DReplay environment per the detail in this article?

https://docs.microsoft.com/sql/dea/database-experimentation-assistant-configure-replay?view=sql-serv...

If so, unless you migrate the permissions on your production database to the simulated production database in the DReplay environment, the permissions shouldn't be a factor.  If you have follow-up questions, please feel free to contact the Database Experimentation Assistant Feedback (deafeedback@microsoft.com) alias.  Thanks!

[sneharakesh2019]

Hi Team, I have installed the DEA tool for testing the upgraded environment. About the environment: I have installed SQL Server 2017 Developer edition and restored the production database from SQL server 2014 on it. On the same server I configured the Distributed Controller, distributed client and also installed the DEA tool. I can run the DEA tool successfully to capture the trace on Production server (SQL server 2014) and replay them on A (SQL server 2014 Dev server) and B (SQL server 2017 Dev server). As per the process, before I start the capture I take the transactional log backup from the production database and then restore it on both A and B dev servers and then start the replay. So far everything looks fine, except the pass% which does not increase more than 32% on both A and B Dev servers, due to which I think the analysis seems to be blank with no graphs or recommendations. Issue : I couldn’t get enough information on how to get pass rate above 90%. I tried doing everything but couldn't try to match the hardware requirements of production and development environment as mentioned in microsoft page https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-get-started?view=sql-ser.... Can I get some help to achieve the pass rate above 90%.

[Jim Toland]

@sneharakesh2019 , please reach out to the the Database Experimentation Assistant Feedback (deafeedback@microsoft.com) alias directly with your query. Thanks!

[hahmeddigmap]

Can this version be used for testing with SQL Server 2019?

I am running into the same issue as JulianDixon, with the failure at 36%, the error is as below:

02/06/20 12:23:47.058 [0X000011AC] The file [C:\Program Files (x86)\Microsoft Corporation\Reporter\Bin\Release\Reporter.exe] does not exist with operating system error 0x00000003 (The system cannot find the path specified)
02/06/20 12:23:47.058 [0X000011AC] Attempt to launch Reporter failed. Check the error log and your RML installation.
02/06/20 12:23:47.058 [0X000011AC] *******************************************************************************
* ReadTrace encountered one or more ERRORS. An error condition typically *
* stops processing early and the ReadTrace output may be unusable. *
* Review the log file for details. *
*******************************************************************************
02/06/20 12:23:47.058 [0X000011AC] ***** ReadTrace exit code: -23

I can't find a SQL Server 2019 Feature Pack.

[Jim Toland]

@hahmeddigmap, it should work with SQL Server 2019. The only issue would be DReplay, probably.

The error below isn’t actually a real error – it’s just RML Utils saying it can’t launch the reporter, which isn’t needed for us anyway.  If you can share your  DEA logs (%temp%/DEA) with the Database Experimentation Assistant Feedback (deafeedback@microsoft.com) alias, the detail about any actual error should be more explicit.  It's possible that the issue relates to the step that publishes the dacpac/uses the SqlPackage.exe. We can be sure though without the logs.

Try downloading the latest SSDT and repeating the process. 

https://docs.microsoft.com/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15

[hahmeddigmap]

Thanks @Jim Toland! I was able to run the assessment on a different server without any issues or additional setup. Other server had SQL Server 2012 with SSMS 11 running, no additional configuration was needed.

[Kusza6]

Hello,

I am getting following error on DEA startup:

Unhandled Exception: System.InvalidOperationException: GetExportedValue cannot be called before prerequisite import 'Microsoft.DEA.Logging.TraceLogger..ctor (Parameter="configManager", ContractName="Microsoft.DEA.ConfigurationManager.IConfigManager")' has been set.

Is someone able to let me know what I am missing?

[Jim Toland]

Kusza6, please reach out to the Database Experimentation Assistant Feedback (deafeedback@microsoft.com) alias with your query.  Thanks in advance!

[anoop50]

is there a way I can change the events being captured by DEA, as on my production server a trace for 20 mins generated 30GB of trc files.

[Jim Toland]

@anoop50, sending your questions directly to the Database Experimentation Assistant Feedback alias (deafeedback@microsoft.com) is the most efficient way to get an answer.  Thanks! 

[anoop50]

Is it possible to run a trace captured using profiler for DB name X on server A, against a DB named Y on Server B using DEA ? If yes, please let me know the steps.

[EnertiaSteve]

I am having the same issue with reporter.exe. Whenever I try and email email address removed for privacy reasons, it bounces back as an invalid email address. Is this product supported still or has it been decommissioned? How do I get this product to work?

[Jim Toland]

@EnertiaSteve, please use this alternate address: Ask Azure Database Migrations <AskAzureDatabaseMigrations@service.microsoft.com>

I have updated the blog post to reference this contact address as well.

[sylvain325]

hi

First time i'm using your tool, when i try to run an analysis report, i have an error saying Failed to update the report Schema to latest version, please try again.   Schema update is required, continue to update schema, i press update schema, and still have the same errors.

Account is sysadm

Running on SQLserver 2022 

Stack

EA Error: -2146233088 : ExceptionEvent, 03/30/2023 20:46:54, Data: {"Code":-2146233088,"Message":"Failed to generate new analysis report.","Operation":"AnalysisModel","Properties":{"ProductVersion":"16.0.1000.6","Version":"2.6.61459.2","SchemaVersion":"1.3","CreatedDate":"3/30/2023 4:46:54 PM","IsSchemaUpgradeRequired":"False","ProgressPercentage":"0","ErrorCode":"1902","Id":"f444797d-092a-4327-9461-196e1207c704"},"Timestamp":"2023-03-30T20:46:54.8687029Z"}, Exception:
[Exception Info]:Microsoft.DEA.Common.Exceptions.DeaException, Message:Exception of type 'Microsoft.DEA.Common.Exceptions.DeaException' was thrown.
StackTrace: at Microsoft.DEA.BusinessLogic.Actions.VerifyUserServerRoleName.Run()
at Microsoft.DEA.BusinessLogic.ActionDecorator.<>c__DisplayClass2_0.<Run>b__0()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DEA.BusinessLogic.ActionDecorator.<Run>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.DEA.BusinessLogic.ActionDecorator.<Run>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DEA.BusinessLogic.Bootstrappers.NewAnalysisXEvents.<RunAsync>d__21.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.DEA.BusinessLogic.Bootstrappers.NewAnalysisXEvents.<RunAsync>d__21.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DEA.BusinessLogic.BootstrapperFactory.<StartWorkflowAsync>d__37.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DEA.BusinessLogic.BootstrapperFactory.<CreateNewAnalysisBootstrapperAsync>d__28.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DEA.UI.ViewModels.MainRegion.NewReportViewModel.<StartNewAnalysisAsync>d__113.MoveNext()

[jyseong]

while i run the analysis report, i hit the below error.

DEA Information: 0 : DiagnosticEvent, 07/27/2023 10:06:46, Data: {"Message":"SqlPackage.exe found from the Data-Tier Application Framework Installation path [C:\\Program Files\\Microsoft SQL Server\\130\\DAC\\bin\\SqlPackage.exe].","Timestamp":"2023-07-27T10:06:46.5630633Z"}
DEA Information: 0 : DiagnosticEvent, 07/27/2023 10:06:57, Data: {"Message":"Process C:\\Program Files\\Microsoft SQL Server\\130\\DAC\\bin\\SqlPackage.exe /Action:Publish /SourceFile:\"ExperimentationDatabase.dacpac\" /TargetDatabaseName:\"Analysisreport1638260492064800950\" /TargetServerName:\"SQL2019\" /Variables:\"DatabaseA\"=\"Areport1638260492064800950\" /Variables:\"DatabaseB\"=\"Breport1638260492064800950\" /Variables:\"ReportName\"=\"report1\" /Variables:\"DEAVersion\"=\"2.6.61459.2\" /Variables:\"SchemaVersion\"=\"1.3\" /p:BlockOnPossibleDataLoss=False completed with exit code: 1","Timestamp":"2023-07-27T10:06:57.8601219Z"}
DEA Information: 0 : DiagnosticEvent, 07/27/2023 10:06:57, Data: {"Message":"Process output: ","Timestamp":"2023-07-27T10:06:57.8601219Z"}
DEA Information: 0 : DiagnosticEvent, 07/27/2023 10:06:57, Data: {"Message":"Error occurred while running process. Command: \"C:\\Program Files\\Microsoft SQL Server\\130\\DAC\\bin\\SqlPackage.exe /Action:Publish /SourceFile:\"ExperimentationDatabase.dacpac\" /TargetDatabaseName:\"Analysisreport1638260492064800950\" /TargetServerName:\"SQL2019\" /Variables:\"DatabaseA\"=\"Areport1638260492064800950\" /Variables:\"DatabaseB\"=\"Breport1638260492064800950\" /Variables:\"ReportName\"=\"report1\" /Variables:\"DEAVersion\"=\"2.6.61459.2\" /Variables:\"SchemaVersion\"=\"1.3\" /p:BlockOnPossibleDataLoss=False\", Error:\"*** Could not deploy package.\r\nUnable to connect to master or target server 'Analysisreport1638260492064800950'. You must have a user with the same password in master or target server 'Analysisreport1638260492064800950'.\r\n\r\n\"","Timestamp":"2023-07-27T10:06:57.8601219Z"}
DEA Information: 0 : DiagnosticEvent, 07/27/2023 10:06:57, Data: {"Message":"DacPac publishing failed.","Timestamp":"2023-07-27T10:06:57.8601219Z"}
DEA Information: 0 : MetricEvent, 07/27/2023 10:06:57, Data: {"IsSuccess":true,"Name":"Duration","StartTime":"2023-07-27T10:06:46.5600638Z","Value":11300.780700000001,"Operation":"PublishDacPac","Timestamp":"2023-07-27T10:06:57.8611482Z"}
DEA Error: -2146233088 : ExceptionEvent, 07/27/2023 10:06:57, Data: {"Code":-2146233088,"Message":"Failed to generate new analysis report.","Operation":"AnalysisModel","Properties":{"ProductVersion":"15.0.4316.3","Version":"2.6.61459.2","SchemaVersion":"1.3","CreatedDate":"7/27/2023 10:06:46 AM","IsSchemaUpgradeRequired":"False","ProgressPercentage":"6","ErrorCode":"1004","Id":"23bba51e-7c76-4bb6-8b5f-5d3f34ef9d7a"},"Timestamp":"2023-07-27T10:06:57.8671206Z"}, Exception:
[Exception Info]:Microsoft.DEA.Common.Exceptions.DeaException, Message:Exception of type 'Microsoft.DEA.Common.Exceptions.DeaException' was thrown.
StackTrace: at Microsoft.DEA.BusinessLogic.Actions.PublishDacPac.Run()
at Microsoft.DEA.BusinessLogic.ActionDecorator.<>c__DisplayClass2_0.<Run>b__0()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DEA.BusinessLogic.ActionDecorator.<Run>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.DEA.BusinessLogic.ActionDecorator.<Run>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
at Microsoft.DEA.BusinessLogic.Bootstrappers.NewAnalysisXEvents.<RunAsync>d__21.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.DEA.BusinessLogic.Bootstrappers.NewAnalysisXEvents.<RunAsync>d__21.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DEA.BusinessLogic.BootstrapperFactory.<StartWorkflowAsync>d__37.MoveNext()

yes. user is one of sysadmin

query

select r.name as Role, m.name as Principal
from master.sys.server_role_members rm inner join master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = 'R'
inner join master.sys.server_principals m on m.principal_id = rm.member_principal_id
where m.name = 'SQL2019\labuser'

result

Role Principal
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
sysadmin SQL2019\labuser

(1 row affected)

here is the sql error log

LogDate ProcessInfo Text
----------------------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2023-07-27 09:51:48.040 Server Microsoft SQL Server 2019 (RTM-CU21) (KB5025808) - 15.0.4316.3 (X64)
Jun 1 2023 16:32:31
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hyper
2023-07-27 09:51:48.040 Server UTC adjustment: 0:00
2023-07-27 09:51:48.040 Server (c) Microsoft Corporation.
2023-07-27 09:51:48.040 Server All rights reserved.
2023-07-27 09:51:48.040 Server Server process ID is 4244.
2023-07-27 09:51:48.040 Server System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
2023-07-27 09:51:48.040 Server Authentication mode is MIXED.

.

.

.

.

.

.

2023-07-27 09:52:29.120 Logon Error: 18456, Severity: 14, State: 38.
2023-07-27 09:52:29.120 Logon Login failed for user 'SQL2019\labuser'. Reason: Failed to open the explicitly specified database 'AnalysisReport1638260483477650960'. [CLIENT: <local machine>]
2023-07-27 09:52:39.120 Logon Error: 18456, Severity: 14, State: 38.
2023-07-27 09:52:39.120 Logon Login failed for user 'SQL2019\labuser'. Reason: Failed to open the explicitly specified database 'AnalysisReport1638260483477650960'. [CLIENT: <local machine>]
2023-07-27 10:04:34.430 Logon Error: 18456, Severity: 14, State: 38.
2023-07-27 10:04:34.430 Logon Login failed for user 'SQL2019\labuser'. Reason: Failed to open the explicitly specified database 'Analysisreport2638260490716048968'. [CLIENT: <local machine>]
2023-07-27 10:04:44.440 Logon Error: 18456, Severity: 14, State: 38.
2023-07-27 10:04:44.440 Logon Login failed for user 'SQL2019\labuser'. Reason: Failed to open the explicitly specified database 'Analysisreport2638260490716048968'. [CLIENT: <local machine>]
2023-07-27 10:06:47.800 Logon Error: 18456, Severity: 14, State: 38.
2023-07-27 10:06:47.800 Logon Login failed for user 'SQL2019\labuser'. Reason: Failed to open the explicitly specified database 'Analysisreport1638260492064800950'. [CLIENT: <local machine>]
2023-07-27 10:06:57.800 Logon Error: 18456, Severity: 14, State: 38.
2023-07-27 10:06:57.800 Logon Login failed for user 'SQL2019\labuser'. Reason: Failed to open the explicitly specified database 'Analysisreport1638260492064800950'. [CLIENT: <local machine>]
2023-07-27 10:09:24.220 Logon Error: 18456, Severity: 14, State: 38.
2023-07-27 10:09:24.220 Logon Login failed for user 'SQL2019\labuser'. Reason: Failed to open the explicitly specified database 'Analysisreport2638260493630376443'. [CLIENT: <local machine>]
2023-07-27 10:09:34.230 Logon Error: 18456, Severity: 14, State: 38.