none
Database experimentation Assistant issues (DEA) RRS feed

  • Question

  • Hi, i am trying to set-up the DEA tool for the first time.

    This is on my laptop so all easy and vanilla set-up in preparation for proper server set-up at work. I am on the first page trying to connect to the server. I have connected to the server from SSMS and i am also the admin (as you would expect). I have tried both with Windows Authentication and also as SA just in case. DEA is complaining that "The current user doesn't have the required permissions to perform the operation. Please ensure you have sysadmin priviledges".

    Do you know what's wrong. I have also played around with turning on and off the 2 options about the "Trust server certificate" and "Encrypt connection". Didn't seem to help.  Any ideas what i am doing wrong?

    I am sysadmin and i can start trace from Profiler. also added the username with explicit permission alter trace on the server but to no avail.

    update - i get an error that says "Server name not returned from GetRoleName.sql"

    Thanks.

    Error message below:

    DEA Information: 0 : DiagnosticEvent, 11/05/2019 12:35:16, Data: {"Message":"Entered GetSqlServerConnectionMetaData","Timestamp":"2019-11-05T12:35:16.5762928Z"}
    DEA Information: 0 : DiagnosticEvent, 11/05/2019 12:35:16, Data: {"Message":"Attempting to get the SQL server metadata.","Operation":"CaptureModel","Properties":{"ServerRoleName":null,"ProductVersion":null,"TraceId":"0","SelectedDuration":"5","TraceType":"Minimum","CaptureStartTime":"01/01/0001 00:00:00","CaptureStopTime":"01/01/0001 00:00:00","CaptureDuration":"00:05:00","CaptureStatus":"NotStarted","ErrorCode":"0","PreprocessTraceStatus":"NotStarted","ReplayStatus":"NotStarted","Id":"e723c98a-cf19-4ddf-9a67-dd0f044eeb10"},"Timestamp":"2019-11-05T12:35:16.5762928Z"}
    DEA Information: 0 : DiagnosticEvent, 11/05/2019 12:35:16, Data: {"Message":"Server name not returned from GetRoleName.sql script.","Timestamp":"2019-11-05T12:35:16.5782859Z"}
    DEA Information: 0 : SystemInformationEvent, 11/05/2019 12:35:16, Data: {"Properties":{"TotalPhysicalMemoryKB":"8311928","AvailablePhysicalMemoryKB":"4174692","TotalPageFileKB":"16623856","AvailablePageFileKB":"10858820","SystemCacheKB":"3416000","CPUCount":"4","HyperthreadRatio":"4","StackSizeKB":"2044","MaxWorkersCount":"512","SchedulerCount":"4","SchedulerTotalCount":"11","SqlServerStartTimeMsTicks":"429958","CurrentTime":"05/11/2019 12:35:16","Id":"e723c98a-cf19-4ddf-9a67-dd0f044eeb10"},"Timestamp":"2019-11-05T12:35:16.5932464Z","Id":"e723c98a-cf19-4ddf-9a67-dd0f044eeb10"}
    DEA Information: 0 : TraceEvent, 11/05/2019 12:35:16, Data: {"Message":"Successfully retrieved SQL server metadata.","Operation":"CaptureModel","Properties":{"ServerRoleName":null,"ProductVersion":"14.0.1000.169","TraceId":"0","SelectedDuration":"5","TraceType":"Minimum","CaptureStartTime":"01/01/0001 00:00:00","CaptureStopTime":"01/01/0001 00:00:00","CaptureDuration":"00:05:00","CaptureStatus":"NotStarted","ErrorCode":"0","PreprocessTraceStatus":"NotStarted","ReplayStatus":"NotStarted","Id":"e723c98a-cf19-4ddf-9a67-dd0f044eeb10"},"Timestamp":"2019-11-05T12:35:16.5942431Z"}
    DEA Information: 0 : MetricEvent, 11/05/2019 12:35:16, Data: {"IsSuccess":false,"Name":"Duration","StartTime":"2019-11-05T12:35:16.5762928Z","Value":17.7928,"Operation":"GetSqlServerConnectionMetaData","Timestamp":"2019-11-05T12:35:16.5942431Z"}
    DEA Information: 0 : DiagnosticEvent, 11/05/2019 12:35:16, Data: {"Message":"Exited GetSqlServerConnectionMetaData","Timestamp":"2019-11-05T12:35:16.5942431Z"}
    DEA Information: 0 : DiagnosticEvent, 11/05/2019 12:35:16, Data: {"Message":"Entered VerifyUserServerRoleName","Timestamp":"2019-11-05T12:35:16.59524Z"}
    DEA Information: 0 : MetricEvent, 11/05/2019 12:35:16, Data: {"IsSuccess":true,"Name":"Duration","StartTime":"2019-11-05T12:35:16.59524Z","Value":0.8276,"Operation":"VerifyUserServerRoleName","Timestamp":"2019-11-05T12:35:16.5962379Z"}
    DEA Error: -2146233088 : ExceptionEvent, 11/05/2019 12:35:16, Data: {"Code":-2146233088,"Message":"Exception has occurred while starting the trace on server.","Operation":"CaptureModel","Properties":{"ServerRoleName":null,"ProductVersion":"14.0.1000.169","TraceId":"0","SelectedDuration":"5","TraceType":"Minimum","CaptureStartTime":"01/01/0001 00:00:00","CaptureStopTime":"01/01/0001 00:00:00","CaptureDuration":"00:05:00","CaptureStatus":"NotStarted","ErrorCode":"0","PreprocessTraceStatus":"NotStarted","ReplayStatus":"NotStarted","Id":"e723c98a-cf19-4ddf-9a67-dd0f044eeb10"},"Timestamp":"2019-11-05T12:35:16.6012414Z"}, 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.CaptureTrace.<RunAsync>d__12.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at Microsoft.DEA.BusinessLogic.Bootstrappers.CaptureTrace.<RunAsync>d__12.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.<CreateCaptureBootstrapperAsync>d__24.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.NewCaptureViewModel.<StartTraceAsync>d__104.MoveNext()






    • Edited by panlondon Tuesday, November 5, 2019 2:24 PM
    Tuesday, November 5, 2019 11:34 AM

All replies

  • Hi panlondon,

    DEA connects to database servers by using Windows authentication. Be sure that a user running DEA can connect to database servers (source, target, and analysis) by using Windows authentication. User running DEA has sysadmin rights on the source database server. Did you use the Windows authentication and the user has the sysadmin permission?  And please follow the steps from blog to use DEA. Please refer to Configure DEA and How to use the SQL Server Database Experimentation Assistant (DEA) tool.

    Best regards,
    Cathy

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 6, 2019 8:40 AM
  • Hi Cathy, new Windows user created and used Windows authentication.  Now Step 1, 2 (replay) completed. However I don't see a trc file created from step 2. There are some very large xel files created but no trc.

    For step 3 just to test it again as I have no trc from step 2 I used the same trc file as produced from step 1. The processes runs and then stops at 36% with some errors see attached. Note that I am using the same trc file as I have no other trc fil available (from step 2). Thank you for any help.

    Some of the log file as it doesn't allow me to post the whole section:

    Cleaning up unique procedure hash table\r\n11/06/19 14:33:33.149 [0X000026F8] Indexing tables...\r\n11/06/19 14:33:33.268 [0X000026F8] Doing post-load data cleanup...\r\n11/06/19 14:33:33.542 [0X000026F8] Computing partial aggregates...\r\n11/06/19 14:33:33.713 [0X000026F8] Building analysis indexes ...\r\n11/06/19 14:33:33.760 [0X000026F8] Data load completed.\r\n11/06/19 14:33:33.762 [0X000026F8] Using execution path: C:\\Program Files (x86)\\Microsoft Corporation\\Database Experimentation Assistant\\Dependencies\\X64\\\r\n11/06/19 14:33:33.766 [0X000026F8] 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)\r\n11/06/19 14:33:33.767 [0X000026F8] Attempt to launch Reporter failed.  Check the error log and your RML installation.\r\n11/06/19 14:33:33.767 [0X000026F8] *******************************************************************************\r\n* ReadTrace encountered one or more ERRORS. An error condition typically      *\r\n* stops processing early and the ReadTrace output may be unusable.            *\r\n* Review the log file for details.                                            *\r\n*******************************************************************************\r\n11/06/19 14:33:33.767 [0X000026F8] ***** ReadTrace exit code: -23\r\n11/06/19 14:33:33.789 [0X000026F8] \r\n11/06/19 14:33:33.790 [0X000026F8] INFO: Cleaning up unique batch hash table\r\n11/06/19 14:33:33.791 [0X000026F8] INFO: Cleaning up unique statement hash table\r\n11/06/19 14:33:33.791 [0X000026F8] INFO: Cleaning up unique plan hash table\r\n11/06/19 14:33:33.791 [0X000026F8] INFO: Cleaning up unique procedure hash table\r\n11/06/19 14:33:33.791 [0X000026F8] INFO: Cleaning up connection info hash table\r\n\r\n","Timestamp":"2019-11-06T14:33:34.0052419Z"}
    DEA Information: 0 : DiagnosticEvent, 11/06/2019 14:33:34, Data: {"Message":"Import completed for C:\\SQLTracepanos\\Minimum_test1Copy2306.trc","Timestamp":"2019-11-06T14:33:34.0062385Z"}
    DEA Information: 0 : MetricEvent, 11/06/2019 14:33:34, Data: {"IsSuccess":false,"Name":"Duration","StartTime":"2019-11-06T14:33:30.7579212Z","Value":3249.0921000000003,"Operation":"ImportTraceFileToDatabase","Timestamp":"2019-11-06T14:33:34.0062385Z"}
    DEA Information: 0 : DiagnosticEvent, 11/06/2019 14:33:34, Data: {"Message":"Exited ImportTraceFileToDatabase","Timestamp":"2019-11-06T14:33:34.0072361Z"}
    DEA Information: 0 : DiagnosticEvent, 11/06/2019 14:33:34, Data: {"Message":"Entered ImportTraceFileToDatabase","Timestamp":"2019-11-06T14:33:34.0072361Z"}
    DEA Information: 0 : DiagnosticEvent, 11/06/2019 14:33:34, Data: {"Message":"ReadTrace.exe not found from the path [] specified in the configuration, continuing to search in other locations..","Timestamp":"2019-11-06T14:33:34.0072361Z"}
    DEA Information: 0 : DiagnosticEvent, 11/06/2019 14:33:34, Data: {"Message":"ReadTrace.exe found from the the path [C:\\Program Files (x86)\\Microsoft Corporation\\Database Experimentation Assistant\\Dependencies\\x64\\ReadTrace.exe].","Timestamp":"2019-11-06T14:33:34.0082324Z"}
    DEA Information: 0 : DiagnosticEvent, 11/06/2019 14:33:34, Data: {"Message":"ReadTrace file path: C:\\Program Files (x86)\\Microsoft Corporation\\Database Experimentation Assistant\\Dependencies\\x64\\ReadTrace.exe","Timestamp":"2019-11-06T14:33:34.0092303Z"}
    DEA Information: 0 : DiagnosticEvent, 11/06/2019 14:33:34, Data: {"Message":"Importing trace C:\\SQLTracepanos\\Minimum_test1Copy2306.trc into Btest4637086476106911016. Command: C:\\Program Files (x86)\\Microsoft Corporation\\Database Experimentation Assistant\\Dependencies\\x64\\ReadTrace.exe -I\"C:\\SQLTracepanos\\Minimum_test1Copy2306.trc\" -d\"Btest4637086476106911016\" -S\"DESKTOP-4JMV8S3\" -E -o\"C:\\Users\\panos\\AppData\\Local\\Temp\\DEA\" -f -T271 -T141 -T18","Timestamp":"2019-11-06T14:33:34.0102281Z"}
    DEA Information: 0 : DiagnosticEvent, 11/06/2019 14:33:36, Data: {"Message":"Process C:\\Program Files (x86)\\Microsoft Corporation\\Database Experimentation Assistant\\Dependencies\\x64\\ReadTrace.exe -I\"C:\\SQLTracepanos\\Minimum_test1Copy2306.trc\" -d\"Btest4637086476106911016\" -S\"DESKTOP-4JMV8S3\" -E -o\"C:\\Users\\panos\\AppData\\Local\\Temp\\DEA\" -f -T271 -T141 -T18  completed with exit code: -23","Timestamp":"2019-11-06T14:33:36.9872693Z"}
    DEA Information: 0 : DiagnosticEvent, 11/06/2019 14:33:36, Data: {"Message":"Process output: \r\n11/06/19 14:33:34.723 [0X00000428] I/O Completion manager started\r\n11/06/19 14:33:34.730 [0X00003418] Attempting DOD5015 removal of [C:\\Users\\panos\\AppData\\Local\\Temp\\DEA\\ReadTrace.log]\r\n11/06/19 14:33:34.741 [0X00003418] Readtrace a SQL Server trace processing utility.\r\nVersion 9.04.0098 built for x64.\r\nCopyright © 1997-2018 Microsoft. All Rights Reserved\r\n11/06/19 14:33:34.741 [0X00003418]             Computer: DESKTOP-4JMV8S3\r\n11/06/19 14:33:34.741 [0X00003418]          Base Module: C:\\Program Files (x86)\\Microsoft Corporation\\Database Experimentation Assistant\\Dependencies\\x64\\ReadTrace.exe\r\n11/06/19 14:33:34.741 [0X00003418]           Process Id: 10748\r\n11/06/19 14:33:34.741 [0X00003418]  Active proc mask(0): 0x0000000F\r\n11/06/19 14:33:34.741 [0X00003418]         Architecture: 9\r\n11/06/19 14:33:34.741 [0X00003418]            Page size: 4096\r\n11/06/19 14:33:34.741 [0X00003418]                 CPUs: 4\r\n11/06/19 14:33:34.741 [0X00003418]     Processor groups: 1\r\n11/06/19 14:33:34.741 [0X00003418]         Highest node: 0\r\n11/06/19 14:33:34.742 [0X00003418]   Proximity: 00  Node: 00\r\n11/06/19 14:33:34.742 [0X00003418] ---------------------------------------\r\n11/06/19 14:33:34.742 [0X00003418]                Group: 0\r\n11/06/19 14:33:34.742 [0X00003418] ---------------------------------------\r\n11/06/19 14:33:34.742 [0X00003418]         Processor(s): 0x00000003 Function units: Shared\r\n11/06/19 14:33:34.742 [0X00003418]         Package mask: 0x0000000F\r\n11/06/19 14:33:34.742 [0X00003418]         Processor(s): 0x0000000C Function units: Shared\r\n11/06/19 14:33:34.742 [0X00003418]         Processor(s): 0x0000000F assigned to Numa node: 0\r\n11/06/19 14:33:34.742 [0X00003418] Current time bias: 0 minutes 0.00 hours DST Standard \r\n11/06/19 14:33:34.742 [0X00003418] -IC:\\SQLTracepanos\\Minimum_test1Copy2306.trc\r\n11/06/19 14:33:34.743 [0X00003418] -dBtest4637086476106911016\r\n11/06/19 14:33:34.743 [0X00003418] -SDESKTOP-4JMV8S3\r\n11/06/19 14:33:34.743 [0X00003418] -E\r\n11/06/19 14:33:34.743 [0X00003418] -oC:\\Users\\panos\\AppData\\Local\\Temp\\DEA\r\n11/06/19 14:33:34.743 [0X00003418] -f\r\n11/06/19 14:33:34.743 [0X00003418] -T271\r\n11/06/19 14:33:34.743 [0X00003418] -T141\r\n11/06/19 14:33:34.743 [0X00003418] -T18\r\n11/06/19 14:33:34.749 [0X00003418] Using language id (LCID): 1024 [English_United Kingdom.1252] for character formatting with NLS: 0x0006020F and Defined: 0x0006020F\r\n11/06/19 14:33:34.749 [0X00003418] Attempting to cleanup existing RML files from previous execution\r\n11/06/19 14:33:34.751 [0X00003418] Using extended RowsetFastload synchronization\r\n11/06/19 14:33:34.751 [0X00003418] Establishing initial database connection\r\n11/06/19 14:33:34.751 [0X00003418] Server: DESKTOP-4JMV8S3\r\n11/06/19 14:33:34.751 [0X00003418] Database: Btest4637086476106911016\r\n11/06/19 14:33:34.751 [0X00003418] Authentication: Windows\r\n11/06/19 14:33:34.831 [0X00003418] Using SQLOLEDB version 11.0.7462.6\r\n11/06/19 14:33:34.835 [0X00003418] Connected to SQL Server Version, Major: 14, M


    • Edited by panlondon Wednesday, November 6, 2019 3:47 PM
    Wednesday, November 6, 2019 3:38 PM
  • Hi Cathy, Do you know if the Distributed Replay controller  needs to be used, the details are very limited in the  microsoft help website. And if i use inBult can i avoid using the Replay controller? Also it mentions User running DEA can connect to the analysis database. Is that a SSAS database? The user running DEA can connect to SSAS or the normal SQL database. Thanks.

    Thursday, November 7, 2019 9:36 AM
  • Hi,
    It may spends a lot of your time, but I still suggest you read these documents. You can get the information that you want from these documents.

    Thursday, November 7, 2019 9:56 AM
  • An update. Basically everything seems better once i installed SQL 2016 and used that instead instead of SQL 2017. I didn't change anything else on DEA side but now i can generatre the reporting side. Hoping that i can do the same with the replay section as well...

    • Edited by panlondon Friday, November 8, 2019 9:52 AM
    Thursday, November 7, 2019 4:46 PM
  •  So glad to hear that your issue resolved.
    Friday, November 15, 2019 9:16 AM