Articles in this section

Adaptiva OneSite Database Migration to New Server

Avatar
Camille Hansen
  • Updated
Follow

If planning to install the Adaptiva and Microsoft Configuration Manager (ConfigMgr) databases on separate servers, you must take some preliminary steps before completing this procedure. See Configure the Kerberos Authentication.

Prerequisites

Before moving the Adaptiva database to a new SQL Server, verify the following items:

Move the Adaptiva OneSite Database to a New Server

When migrating the Adaptiva database, and leaving the Adaptiva Server on the same hardware, complete the following steps:

  1. Stop, and then disable the Adaptiva Server service.

  2. Backup the Adaptiva SQL Server database using your preferred SQL backup method. The following example commands use the SQL Server command-line utility sqlcmd and TSQL BACKUP command:

    set DATESTAMP=%DATE:~-4%.%DATE:~7,2%.%DATE:~4,2%

    set DATABASENAME=adaptiva

    set BACKUPFILENAME=%CD%\%DATABASENAME%-%DATESTAMP%.bak

    set SERVERNAME=<your server name here>

    sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%'"

  3. Restore the Adaptiva database onto the new SQL Server using the following steps:

    1. Run AdaptivaServerSetup.exe on the existing Adaptiva Server, and then select Advanced Upgrade on the Adaptiva Server Installation dialog.

      KB-OS-Plat-Install-DbMigrate-UpgradeServer.png

    2. Set the SQL Database option to the value of three on the HKEY_LOCAL_MACHINE\SOFTWARE\Adaptiva\server\setup.adaptiva_db_option line in the registry, and then select Next to complete remaining steps in the Adaptiva Server installation wizard.

      KB-OS-Plat-Install-DbMigrate-Dboptions.png

      Note

      If a warning message appears during the Adaptiva Server installation describing a separate SQL server and linked servers, you can ignore the warning if the SQL Server shares the same SQL Server Management Studio (SCCM).

  4. Verify the Adaptiva Server upgraded successfully using the following steps:

    1. Verify the post install checks pass.

      KB-OS-Plat-Install-DbMigrate-Success.png

    2. Verify the following database details appear in the registry:

      • Check the registry for new server name.

        KB-OS-Plat-Install-DbMigrate-ServerName.png

      • If the SCCM database location changed, verify the location change in the registry. When the Adaptiva service restarts, the server will use the new database location.

        KB-OS-Plat-Install-DbMigrate-DbLocation.png

Configure the Kerberos Authentication

This information is also included in the [Appendix B] of the [Adaptiva OneSite Platform Site Planning Guide].

Create the SPNs

  1. Log onto the SQL Database server with a Domain Admin account (required).

  2. Create Service Principal Names (SPNs) in the Kerberos Configuration Manager using the following steps:

    1. Open the File Explorer, go to C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server, and then double-click on the KerberosConfigMgr.exe file to launch the Kerberos Configuration Manager.

    2. Select Connect from the menu.

    3. Select the SPN tab, scroll over to the Required SPN and Status columns, and then select Fix All to modify the service accounts.

      Note

      If unable to modify the service accounts, select Generate All, enter a file name, and then send the script to an administrator with the appropriate permissions.

    4. Select Refresh to confirm the expected SPNs, and then scroll to the right to confirm that the status is Good for all rows.

    5. When using the SQL Database server with SQL Always On Availability Groups, repeat the preceding steps on the second database server. If completing these steps with SQL Always On Availability Groups, use the AG Listener.

      Note

      The Kerberos Configuration Manager utility may not function when the SQL Server is in a different domain than the SQL Service account or the Local Administrators group has too many groups.

  3. Confirm the expected SPNs using the following steps:

    1. In Active Directory Users and Computers, locate the service account you want, select Properties, and then select the Attribute Editor tab.

    2. Confirm that the SPNs appear for one or more servers with port numbers.

Delegate Kerberos Authentication

When the SQL Server is on a different server than the Adaptiva Server, setup Kerberos trust delegation using the steps below.

Note

You must use a Domain Admin account when completing the steps below.

  1. In Active Directory Users and Computers, locate the SQL Service account you want, and then select Properties.

  2. Select the Delegation tab on the Properties dialog, select Trust this user for delegation to specified services only, and then select Use Kerberos only.

    Note

    When the service principal names (SPNs) have not been setup as expected, the Delegation tab does not appear.

  3. Select Add…, and then select Users or Computers….

  4. Enter the Service account name, select Check Names, and then select OK.

  5. Select the MSSQLSvc entries in the Available services list, select OK, and then select OK to close the Properties dialog.

  6. Log on to the SQL Server, and then use the SQL Server Configuration Manager to restart the SQL Server service.

Create Linked Servers

  1. Expand Server Objects / Linked Servers in SQL Management Studio, and then verify the Microsoft Configuration Manager (ConfigMgr) SQL database server connection.

  2. Right-click on Linked Servers, and then enter the <AdaptivaSQLServerFQDN> into the field. Use the following best practices for using the correct name for this step:

    • If the instance of the SQL Server is the default instance, enter the name of the computer that hosts the instance of the SQL Server.

    • If the SQL Server is a named instance, enter the computer name and the name of the instance (such as Accounting\SQLExpress).

    • If the Adaptiva and ConfigMgr databases are located on different servers, implement the preceding best practices using the following steps:

      1. Configure the ConfigMgr SQL database to point to the new Adaptiva database SQL instance.

      2. Configure the new Adaptiva database to point to the new ConfigMgr SQL instance.

  3. Select SQL Server.

  4. Select Be made using the login’s current security context on the Security page.

    Important

    To successfully delegate this SQL Service account, disable the Be made using the login’s current security context option.

  5. Set the Collation Compatible option to True using the following steps:

    OS-Plat-Plan-Linked-Servers-Create.png

    1. Right-click on Collation Compatible on the New Linked Server dialog, and then select True from the menu.

    2. Select OK.

    Note

    If using SQL Always On Availability Groups, repeat the preceding steps to connect the second database server to the Availability Group Listener. For more information on creating linked servers, see Create linked servers (SQL Server Database Engine).

Test the Linked Servers

  1. In the SQL Server Management Studio, navigate to Server Objects > Linked Servers > FQDN to view the Fully Qualified Domain Name (FQDN) of the other server.

  2. Confirm that the list of databases returned from the other server as expected.

  3. Expand each returned database to confirm that the tables and views appear in the list as expected.

SQL Database Configuration

During the Adaptiva Server installation, the SQL database options default to grayed out on the SQL Database Options dialog. If you must change these options, you may set the desired value on HKEY_LOCAL_MACHINE\SOFTWARE\Adaptiva\server\setup.adaptiva_db_option line in the registry.

For example, if moving the Adaptiva database from a SQL Express server to a SQL standard server, the option must be set Create The Database In An Existing SQL Server Instance (value 2) in the registry.

SQL Database Configuration Best Practices

Use the following best practices when configuring the SQL Database:

  • If using a named SQL instance on a new SQL server machine, you must change the instance name on the HKEY_LOCAL_MACHINE\SOFTWARE\Adaptiva\server\setup.adaptiva_db_sql_named_instance line in the registry.

  • If using the default instance, you must leave the HKEY_LOCAL_MACHINE\SOFTWARE\Adaptiva\server\setup.adaptiva_db_sql_named_instance line blank in the registry.

  • If not using the default SQL port 1433, you must set the port on the HKEY_LOCAL_MACHINE\SOFTWARE\Adaptiva\server\setup.adaptiva_db_port line in the registry.

  • If moving the database to a remote system not co-located with the Adaptiva Server service, the Adaptiva Server requires the sysadmin permissions to the new SQL server to change the database name in the registry.

SQL Database Configuration Options

When installing the Adaptiva Server, you have the following SQL Database options:

  • Download And Install Free Microsoft SQL Express And Auto-create Database: This option automatically downloads the SQL database after installing the Adaptiva Server.

  • Create The Database In An Existing SQL Server Instance: This option creates the SQL database on an existing SQL server instance.

  • Create The Database In The Same SQL Instance As ConfigMgr Site Database: This options creates the SQL database in the same SQL instance as the Microsoft Configuration Manager (ConfigMgr) Database.

Configuring the SQL Database Options

When moving the Adaptiva Database to a new SQL server, you must set the SQL Database configuration to the Create The Database In The Same SQL Instance As ConfigMgr Site Database option. The Adaptiva Server requires sysadmin permissions to the new SQL server to migrate the Adaptiva database to the new server.

Grant the Adaptiva Server Sysadmin Permissions to the SQL Server

  1. Run the following T-SQL script from SQL Management Studio to change the database machine name in the registry:

    CREATE LOGIN [DomainName\ComputerName$] FROM WINDOWS;

    GO

    EXEC master..sp_addsrvrolemember @loginame = N' DomainName\ComputerName$', @rolename = N'sysadmin'

    GO

  2. Run the AdaptivaServerSetup.exe to enable these permissions for the server.

Set the SQL Database Option

  1. Set SQL option to the value of three on the HKEY_LOCAL_MACHINE\SOFTWARE\Adaptiva\server\setup.adaptiva_db_option line in the registry.

    KB-OS-Plat-Install-DbMigrate-Value3.png

  2. Run the AdaptivaServerSetup.exe to set the Create The Database In The Same SQL Instance As ConfigMgr Site Database option in the server.

Related articles

Comments

0 comments

Please sign in to leave a comment.