Upgrading repository

Things to keep in mind when upgrading

For Dataedo to work, all components must be in the same major version. So the Desktop in version 25.2 will work with Portal 25.2.2 and Repository in 25.2 but will not connect to a repository in version 25.1.

We recommend creating a backup of the repository database before upgrading in case of any issues - these most commonly occur if a network connection is lost during the upgrade.

You can also create a copy of the folder Dataedo Portal is running from. If Dataedo Portal is running from a virtual machine, it may be easier to create a snapshot of it before the upgrade process.

Upgrading Dataedo Portal

To upgrade Dataedo Portal, connect to the server running Dataedo Portal and run the installer.

When asked for a location/instance, choose the same option you've selected during installation. The default instance is C:\Dataedo Web.

If unsure where your Dataedo Portal is running from, open the IIS Manager, navigate to and select the Dataedo site, then click "Explore" in the top right corner to open the folder in File Explorer.

If you've correctly chosen the instance, the installer will show an option to update Dataedo Portal.

If you don't see this option, restart the installer and choose the path correctly. Do not choose the "Install" option! Selecting this option will create another instance of Dataedo, which may cause issues later.

After installation, Dataedo Portal will warn about the repository being in an older version. The "Upgrading repository" step below will resolve this.

Upgrading the Desktop application

To upgrade Dataedo Desktop, install the new version on each user's PC.

Upgrading Repository

To upgrade your repository, you'll need a user with db_owner role in the database and the default schema set to 'dbo'. We recommend you backup the database before continuing.

Upgrading the repository from the Desktop

The easiest way to upgrade your repository is through the Dataedo Desktop app. After connecting from Dataedo Desktop, you will be shown a warning cautioning about possible issues with Dataedo caused by upgrading - you can safely ignore these if you've already installed Dataedo Portal in the current version.

When asked, choose the "Detach Web" option twice.

After the upgrade process finishes, both Dataedo Portal and Desktop should be able to connect to the repository.

Grant Admin role script (for Dataedo 23.2 and newer)

If you're upgrading from a version older than 23.2, you may need to grant the Admin role to your user. To do this, connect to the repository database and run the following script:

-- Script for creating a new group named Admins, granting it user management privilege
-- and adding YOUR_LOGIN_HERE to the group.
-- Before running, change YOUR_LOGIN_HERE to your login below.

DECLARE @custom_login NVARCHAR(1024);
SET @custom_login = 'YOUR_LOGIN_HERE';

-- Create group 'Admins' if it does not exist
INSERT INTO [dbo].[user_groups] (
    [name], [default], creation_date, last_modification_date
)
SELECT 'Admins', 0, GETDATE(), GETDATE()
WHERE NOT EXISTS (
    SELECT 1 FROM [dbo].[user_groups] WHERE [name] = 'Admins'
);

-- Grant repository management roles to the 'Admins' group
IF NOT EXISTS (SELECT 1 FROM [permissions] WHERE user_type = 'GROUP' AND user_group_id = (SELECT user_group_id FROM [dbo].[user_groups] WHERE [name] = 'Admins') AND object_type = 'REPOSITORY')
    INSERT INTO [dbo].[permissions] (
        user_type, user_group_id, object_type, role_id, creation_date, last_modification_date
    )
    VALUES
        ('GROUP', (SELECT user_group_id FROM [dbo].[user_groups] WHERE [name] = 'Admins'), 'REPOSITORY', 5, GETDATE(), GETDATE());

-- Add the specified user to the 'Admins' group
IF NOT EXISTS (
    SELECT 1 FROM [dbo].[users_user_groups] 
    WHERE user_id = (SELECT license_id FROM [dbo].[licenses] WHERE [login] = @custom_login)
      AND user_group_id = (SELECT user_group_id FROM [dbo].[user_groups] WHERE [name] = 'Admins')
)
    INSERT INTO [dbo].[users_user_groups] (
        user_id, user_group_id, creation_date, last_modification_date
    )
    VALUES (
               (SELECT license_id FROM [dbo].[licenses] WHERE [login] = @custom_login),
               (SELECT user_group_id FROM [dbo].[user_groups] WHERE [name] = 'Admins'),
               GETDATE(), GETDATE()
           );

-- Refresh cache tables
TRUNCATE TABLE [dbo].[cache_area_ancestors];

INSERT INTO cache_area_ancestors (area_id, ancestor_id, domain_id)
SELECT area_id, parent_area_id, domain_id FROM areas WHERE parent_area_id IS NOT NULL;

INSERT INTO cache_area_ancestors (area_id, ancestor_id, domain_id)
SELECT a1.area_id, a2.ancestor_id, a1.domain_id
FROM cache_area_ancestors a1
         INNER JOIN cache_area_ancestors a2 ON a1.ancestor_id = a2.area_id;

TRUNCATE TABLE cache_permissions_repository;
TRUNCATE TABLE cache_permissions_repository_full;
TRUNCATE TABLE cache_permissions_databases;
TRUNCATE TABLE cache_permissions_databases_full;
TRUNCATE TABLE cache_permissions_areas;
TRUNCATE TABLE cache_permissions_areas_full;
TRUNCATE TABLE cache_permissions_domains;
TRUNCATE TABLE cache_permissions_domains_full;

-- Populate cache_permissions_repository
INSERT INTO cache_permissions_repository (user_id)
SELECT user_id
FROM (
         -- Permissions for users
         SELECT p.user_id
         FROM dbo.permissions AS p
                  INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
                  INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
         WHERE object_type = 'REPOSITORY'
           AND action_code = 'DOCUMENTATION_VIEW'
           AND p.user_id IS NOT NULL

         UNION ALL

         -- Permissions for user groups
         SELECT uug.user_id
         FROM dbo.permissions AS p
                  INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
                  INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
                  INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
         WHERE object_type = 'REPOSITORY'
           AND action_code = 'DOCUMENTATION_VIEW'
     ) AS t
GROUP BY user_id;

-- Populate cache_permissions_repository_full
INSERT INTO cache_permissions_repository_full (
    user_id, WEB_ACCESS, DOCUMENTATION_VIEW, DOCUMENTATION_EDIT, COMMUNITY_VIEW, COMMUNITY_EDIT,
    SOURCE_CONNECTION_VIEW, PROFILING_VIEW_DISTRIBUTION, PROFILING_VIEW_DATA, SCRIPTS_VIEW,
    SCHEMA_CHANGES_VIEW, AI_DESCRIPTION_GENERATE, ADMIN, HISTORY_VIEW, BADGE, MANAGE_EXTERNAL_QUERIES, MANAGE_CONNECTIONS
)
SELECT user_id,
       MAX(WEB_ACCESS) AS WEB_ACCESS,
       MAX(DOCUMENTATION_VIEW) AS DOCUMENTATION_VIEW,
       MAX(DOCUMENTATION_EDIT) AS DOCUMENTATION_EDIT,
       MAX(COMMUNITY_VIEW) AS COMMUNITY_VIEW,
       MAX(COMMUNITY_EDIT) AS COMMUNITY_EDIT,
       MAX(SOURCE_CONNECTION_VIEW) AS SOURCE_CONNECTION_VIEW,
       MAX(PROFILING_VIEW_DISTRIBUTION) AS PROFILING_VIEW_DISTRIBUTION,
       MAX(PROFILING_VIEW_DATA) AS PROFILING_VIEW_DATA,
       MAX(SCRIPTS_VIEW) AS SCRIPTS_VIEW,
       MAX(SCHEMA_CHANGES_VIEW) AS SCHEMA_CHANGES_VIEW,
       MAX(AI_DESCRIPTION_GENERATE) AS AI_DESCRIPTION_GENERATE,
       MAX(ADMIN) AS ADMIN,
       MAX(HISTORY_VIEW) AS HISTORY_VIEW,
       MAX(BADGE) AS BADGE,
       MAX(MANAGE_EXTERNAL_QUERIES) AS MANAGE_EXTERNAL_QUERIES,
       MAX(MANAGE_CONNECTIONS) AS MANAGE_CONNECTIONS
FROM (
         -- Permissions for users
         SELECT p.user_id,
                CASE WHEN action_code = 'WEB_ACCESS' THEN 1 ELSE 0 END AS WEB_ACCESS,
                CASE WHEN action_code = 'DOCUMENTATION_VIEW' THEN 1 ELSE 0 END AS DOCUMENTATION_VIEW,
                CASE WHEN action_code = 'DOCUMENTATION_EDIT' THEN 1 ELSE 0 END AS DOCUMENTATION_EDIT,
                CASE WHEN action_code = 'COMMUNITY_VIEW' THEN 1 ELSE 0 END AS COMMUNITY_VIEW,
                CASE WHEN action_code = 'COMMUNITY_EDIT' THEN 1 ELSE 0 END AS COMMUNITY_EDIT,
                CASE WHEN action_code = 'SOURCE_CONNECTION_VIEW' THEN 1 ELSE 0 END AS SOURCE_CONNECTION_VIEW,
                CASE WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION' THEN 1 ELSE 0 END AS PROFILING_VIEW_DISTRIBUTION,
                CASE WHEN action_code = 'PROFILING_VIEW_DATA' THEN 1 ELSE 0 END AS PROFILING_VIEW_DATA,
                CASE WHEN action_code = 'SCRIPTS_VIEW' THEN 1 ELSE 0 END AS SCRIPTS_VIEW,
                CASE WHEN action_code = 'SCHEMA_CHANGES_VIEW' THEN 1 ELSE 0 END AS SCHEMA_CHANGES_VIEW,
                CASE WHEN action_code = 'AI_DESCRIPTION_GENERATE' THEN 1 ELSE 0 END AS AI_DESCRIPTION_GENERATE,
                CASE WHEN action_code = 'ADMIN' THEN 1 ELSE 0 END AS ADMIN,
                CASE WHEN action_code = 'HISTORY_VIEW' THEN 1 ELSE 0 END AS HISTORY_VIEW,
                CASE WHEN action_code = 'BADGE' THEN 1 ELSE 0 END AS BADGE,
                CASE WHEN action_code = 'MANAGE_EXTERNAL_QUERIES' THEN 1 ELSE 0 END AS MANAGE_EXTERNAL_QUERIES,
                CASE WHEN action_code = 'MANAGE_CONNECTIONS' THEN 1 ELSE 0 END AS MANAGE_CONNECTIONS
         FROM dbo.permissions AS p
                  INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
                  INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
         WHERE object_type = 'REPOSITORY'
           AND p.user_id IS NOT NULL

         UNION ALL

         -- Permissions for user groups
         SELECT uug.user_id,
                CASE WHEN action_code = 'WEB_ACCESS' THEN 1 ELSE 0 END AS WEB_ACCESS,
                CASE WHEN action_code = 'DOCUMENTATION_VIEW' THEN 1 ELSE 0 END AS DOCUMENTATION_VIEW,
                CASE WHEN action_code = 'DOCUMENTATION_EDIT' THEN 1 ELSE 0 END AS DOCUMENTATION_EDIT,
                CASE WHEN action_code = 'COMMUNITY_VIEW' THEN 1 ELSE 0 END AS COMMUNITY_VIEW,
                CASE WHEN action_code = 'COMMUNITY_EDIT' THEN 1 ELSE 0 END AS COMMUNITY_EDIT,
                CASE WHEN action_code = 'SOURCE_CONNECTION_VIEW' THEN 1 ELSE 0 END AS SOURCE_CONNECTION_VIEW,
                CASE WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION' THEN 1 ELSE 0 END AS PROFILING_VIEW_DISTRIBUTION,
                CASE WHEN action_code = 'PROFILING_VIEW_DATA' THEN 1 ELSE 0 END AS PROFILING_VIEW_DATA,
                CASE WHEN action_code = 'SCRIPTS_VIEW' THEN 1 ELSE 0 END AS SCRIPTS_VIEW,
                CASE WHEN action_code = 'SCHEMA_CHANGES_VIEW' THEN 1 ELSE 0 END AS SCHEMA_CHANGES_VIEW,
                CASE WHEN action_code = 'AI_DESCRIPTION_GENERATE' THEN 1 ELSE 0 END AS AI_DESCRIPTION_GENERATE,
                CASE WHEN action_code = 'ADMIN' THEN 1 ELSE 0 END AS ADMIN,
                CASE WHEN action_code = 'HISTORY_VIEW' THEN 1 ELSE 0 END AS HISTORY_VIEW,
                CASE WHEN action_code = 'BADGE' THEN 1 ELSE 0 END AS BADGE,
                CASE WHEN action_code = 'MANAGE_EXTERNAL_QUERIES' THEN 1 ELSE 0 END AS MANAGE_EXTERNAL_QUERIES,
                CASE WHEN action_code = 'MANAGE_CONNECTIONS' THEN 1 ELSE 0 END AS MANAGE_CONNECTIONS
         FROM dbo.permissions AS p
                  INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
                  INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
                  INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
         WHERE object_type = 'REPOSITORY'
     ) AS t
GROUP BY user_id;