• State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 2 replies
  • Subscribers 17 subscribers
  • Views 3655 views
  • Users 0 members are here
Options
Related

Migrating Older Historian Runtime Database to Historian 2017, 2020 or 2023

MinHo Kim

I’ve recently been doing more migrations of older Historian Runtime databases to Historian 2017, 2020, or 2023 using the Tech Note.

There is a part that is confusing me:

""
USE Runtime
ALTER USER wwAdmin WITH LOGIN = wwAdmin
ALTER LOGIN wwAdmin WITH DEFAULT_DATABASE = Runtime
ALTER USER wwUser WITH LOGIN = wwUser
ALTER LOGIN wwUser WITH DEFAULT_DATABASE = Runtime
ALTER USER wwPower WITH LOGIN = wwPower
ALTER LOGIN wwPower WITH DEFAULT_DATABASE = Runtime
ALTER USER aaAdmin WITH LOGIN = aaAdmin
ALTER LOGIN aaAdmin WITH DEFAULT_DATABASE = Runtime
ALTER USER aaUser WITH LOGIN = aaUser
ALTER LOGIN aaUser WITH DEFAULT_DATABASE = Runtime
ALTER USER aaPower WITH LOGIN = aaPower
ALTER LOGIN aaPower WITH DEFAULT_DATABASE = Runtime


ALTER AUTHORIZATION ON DATABASE::Runtime TO aadbo;

The line ALTER AUTHORIZATION ON DATABASE::Runtime TO aadbo; was added in the recent manual.

I’m not sure exactly what “aadbo” refers to here—

am I supposed to enter the username currently in use? Please explain.

  • +1

    Hi, 
    What's going on here is that your migration script ensures that 'built in' SQL users have access to the Runtime database.

    Historian has in the past been mainly managed by SQL Server users and groups, and the above query ensures that your upgraded database keeps this functionality.

    SQL Server Login has in recent versions, of both Historian and Microsoft SQL Server been deprecated, but is still available for backward combability. So if you by any reason still want this to be used, at least the migration supports this.

    This is true for the Historian specific users wwAdmin, wwUser and wwPower (used in InSQL and Historian > 10).

    These users were replaced by aaAdmin, aaUser and aaPower in more recent versions.

    But when you restore a database from a backup, witch is common when you migrate to a new version, the user is there, and it can even exist on SQL Server, but the connection between the user and the database has to be insured, thus the initial part of the script.

    This command maps an existing database user (aaUser) to a SQL Server login also named aaUser.

    ALTER AUTHORIZATION ON DATABASE::Runtime TO aadbo;

    aadbo (or dbo), is yet another user, (short for Data Base Owner) 

    - ALTER AUTHORIZATION: This command modifies who owns a securable (in this case, a database).
    - DATABASE::Runtime: Specifies the database named Runtime as the target.
    - TO aadbo: Assigns ownership of the Runtime database to the principal (user or role) named aadbo.

    'dbo' in SQL Server: 
    - It’s a default schema in SQL Server.
    - It represents the owner of the database, typically a user with full control over all objects within that database.
    - When you create a table or stored procedure without specifying a schema, it often gets assigned to the dbo schema.

    A thing to note when moving up in versions of SQL Server, the SQL Server database users will be disabled by default and you have to enable them manually to get these users to work (if needed). it is of course advised to try and move to AD Users for access control, but if that is not your choice then they are there to be used.

    You then need to enable SQL Server as Authentication Mode in your SQL Server.

    So the script should be executed as described without any modifications, if you have manually created any additional users in your older version of the Historian then has to be taken care of manually.
    Preferably by running the security part of the configurator, after the migration.