DTA - Error while tuning using a Test Server

Question

I have 2 servers, both have SQL Server 2016 Developer edition installed. I want to tuning the production server using a test server, a method described here - Reducing the Production Server Tuning Load.

Basically it uses the Test Server to Tune the Production Server.

Please note than I am running the DTA in Test Server itself, not in production server. Trying to run DTA remotely from Test Server (But going to tune Production Server).

This is my command line options:

dta -s Test16 -e [msdb].[dbo].[DTA_tuninglog] -S 192.168.8.102 -U WMATSystem -P myPass123$ -D AdventureWorks2014 -d AdventureWorks2014 -if C:\Users\Deepan\Desktop\test.sql -ix C:\Users\Deepan\Desktop\input.xml -ox C:\Users\Deepan\Desktop\output.xml

Here is the input XML File

    <?xml version="1.0" encoding="utf-16" ?>
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/dta">
  <DTAInput>
    <Server>
      <Name>192.168.8.101</Name>
      <Database>
        <Name>AdventureWorks2014</Name>
      </Database>
    </Server>
    <Workload>
      <File>C:\Users\Deepan\Desktop\test.sql</File>
    </Workload>
    <TuningOptions>
      <TestServer>DESKTOP-COJD62N</TestServer>
      <FeatureSet>IDX</FeatureSet>
      <Partitioning>NONE</Partitioning>
      <KeepExisting>NONE</KeepExisting>
    </TuningOptions>
  </DTAInput>
</DTAXML>

I am getting the Following Error Message:

Cannot create shell database. Check tuning log table, if specified, for details. When I check the logs table it is empty.

Please note that I have the user in both Production and Test Server. And I have given necessary permissions.

MORE DETAILS:

I got the following suggestions from a similar question.

Make sure that you are not using an unsupported tuning option. The following link provides the unsupported tuning options: http://msdn.microsoft.com/en-us/library/ms345360.aspx
Ensure that the two SQL Server instances are the same SQL Server editions.
Ensure that the two machines belong to the same domain.
Use the Trusted Connection option in the DTA utility command.

But,

I haven't used any unsupported tuning options as you can see in the XML File provided.
Microsoft hasn't specified any constraints saying that the machines should be in the same domain or it is a must to use Trusted Connection.
There is no point of using a Test Server instance in the same server.

DTA - Error while tuning using a Test Server

0 Answers0