SqlException occurred while fetching Databases for instance

JordanBentley

Trying to see if this product will do what we want, added a couple of systems with no issue until I try our CRM system.

Target is a SQL 2012 instance (11.0.7512.11) so should be compatible. Directly after adding you see a small red 'X' and hovering shows the tooltip message "SqlException occurred while fetching Databases for instance [instance] with user ".

No other logging that I can see to diagnose, but running a trace on the box during a refresh does show a couple of commands being executed. One of which (below) references a column in a DMV that doesn't appear to exist [hdrs.is_primary_replica], hence erroring.

Documentation here https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql states this field is only applicable to Server 2014 and above.

How can I add this server?!

SELECT d.name
FROM sys.databases d
    LEFT JOIN sys.dm_hadr_database_replica_states hdrs
        ON d.database_id = hdrs.database_id
           AND d.replica_id = hdrs.replica_id
    LEFT JOIN sys.availability_replicas ar
        ON d.replica_id = ar.replica_id
           AND hdrs.replica_id = ar.replica_id
           AND ar.replica_server_name = @@SERVERNAME
WHERE d.state = 0
      AND d.database_id > 4
	  AND d.source_database_id IS NULL -- excl. snapshots

      AND
      (
          hdrs.is_primary_replica IS NULL
          OR hdrs.is_primary_replica = 1
          OR
          (
              hdrs.is_primary_replica = 0
              AND ar.secondary_role_allow_connections = 2
          )
      )
ORDER BY d.name ASC;

JordanBentley

For those coming to this in the future, assuming no fix has been implemented in the software I found that I could add the database manually via SQL script and it picked everything up it needed. Once you see the database, just click the 'Scan for updated schema' link at it should begin to populate.

Sample script below:

INSERT INTO Redgate_SqlDataCatalog.dbo.Databases
(
    [Name],
    [InstanceId],
    [DetectedAtUtc],
    [Archived],
    [Id],
    [LastScannedUtc],
    [ScanResultHash]
)
VALUES
(
	N'<YOUR DATABASE NAME>', 
	<THE INSTANCE ID OF THE SERVER>, 
	N'<SOME DATE AND TIME>', 
	0, 
	<CONSTRUCTED ID FOR THE DATABASE>, 
	NULL, 
	NULL
);

INSTANCE ID OF THE SERVER
Can be obtained by looking it up in the 'dbo.Instances' table.
Or you can get it from the URL when you click into the server page, it will be a 19 digit number.

CONSTRUCTED ID FOR THE DATABASE
Not sure if this is a requirement but the ID of the database seems to be made up of the first 9 digits from the Instance ID then a random 10 digit number. This is the format I used and it worked.