Historical data after database migration on SQL Server

[ziljavi13]

We recently migrated our databases to new servers running an updated version of SQL Server. Based on the documentation, we understood that to retain historical data, we need to create a Connection ID within the DBA Dash service configuration. The "old" server operated under this Connection ID for a period, and after the migration, we updated the connection string to point to the new server.

However, we are unable to view historical data, such as table changes, stored procedures, job history, and other relevant details. This data is critical for us to track and monitor changes effectively. After the migration and updating of the connection string, only basic information, such as server configuration and disk usage, is displayed. The historical data remains inaccessible.

We have verified that there are no errors in the service logs indicating issues related to permissions, firewall settings, or other potential causes.

Could you provide guidance on how to properly configure the instances to ensure that historical data is accessible after the migration?

[DavidWiseman]

Hi,

I usually just have the old and new instances as separate SQL instances in DBA Dash when doing migrations. This allows for easy comparison of server configurations before the migration. Post migration, the old instance can be marked hidden or (soft) deleted. The historical data can still be accessed from the old instance if needed - it is less convenient though, so I understand why you would want to carry this over to the new server.

I think the issue might be related to how the Databases collection is processing the data. This matches based on database_id rather than name. Also, either the name or the create_date must match. This ensures that history is carried over when you rename a database. In the case of a migration, if the database_id doesn't match it will set the old Database as IsActive=0 and create a new row which has none of the history associated. This isn't ideal and I'll probably consider changing it at some point.

This query should help you identify the old databases:

SELECT D.DatabaseID, D.name,d.IsActive,D.database_id 
FROM dbo.Databases D 
JOIN dbo.Instances I ON D.InstanceID = I.InstanceID
WHERE I.ConnectionID = '<connection_id>'
ORDER BY D.name

Swapping the database_id and IsActive status for each DB should bring back your old history. You could always restore a copy of the repository database to test this. The GUI can switch connection to the restored repository.

Hope this helps,

David

[ziljavi13]

https://dbadash.com/docs/setup/options/#connectionid

We created the Connection ID and after the migration, we put the new server in the connection string as written in the link above. After that change, it did not show historical data or all data from the new server.
Maybe we misunderstood something or something needs to be done differently to have the data before and after the migration in one place.