How to move the Nodinite databases

This user guide will teach how to move the SQL Server Nodinite Configuration Database and the Logging Databases.

Local move

If you intend to move the Nodinite databases to other disk volumes on the same SQL Server instance; Then use the quick guide below:

1. Stop Nodinite applications and services; see section 2.1.
2. Detach databases to be moved (with drop existing connections options set). Make sure to read the Microsoft Database Detach and Attach (SQL Server) user guide first.
3. Move the database files locally to the new destination.
4. Attach the databases.
5. Review the default paths for Data and Log files; review section 3.9.
6. Restart services; review section 6.

Above is an example of a local move from one set of disks to another.

Otherwise, moving from one SQL Server instance to another; Then, you will need to go the long road and follow the steps below.

1. Planning

Depending on the type of move operation, there may be many steps to perform and potentially many competencies required. So make sure to have a plan!

1.1 Inform

Ensure all stakeholders are aware of the pending move operation since Nodinite is unavailable for the business/IT-ops/AM-Team during downtime.

1.2 Check-list

Before disrupting any services what so ever you must first make sure the new environment has the proper configurations regarding

1.3 Prepare the new SQL Server instance(s)

Regardless of the type of new SQL Server instance to use, you must conform to the prerequisites.

The new SQL environment may be either of:

• Stand-alone
• Fail-over cluster
• SQL Server Always On (AOAG)

1.3.1 Linked Servers

You must properly configure the set of Linked Servers, review the linked guide before proceeding.

According to the table below; You need to add the same set of Linked Servers for all that apply. If the new SQL environment is the same type as the old one; Copy/script the old Linked Servers using SSMS.

All Linked Server configurations must have the Security option 'Be made using the login's current security context' and the two Server options RPC enabled

1.3.2 MSDTC

Review the MSDTC user guide.

Prepare the Nodinite Application Servers

If the new SQL Server instance is running with a higher version, you must update the SQLPackage (DACPAC) on the Nodinite Application Server to match the new version. In addition, you must change the SqlPackageExecutable system parameter with information about the new path, which includes the new version number.

Before continuing with the following steps, make sure to perform the steps with consideration to the prerequisites.

2. Moving the databases

Before you proceed with the database move operation, stop all the Nodinite services. Nodinite is not available for end-users during the move operation.

2.1 Stop services

First, stop the following services:

2.1.1 Stop the IIS - Application Pool(s)

Stop all the application pools except for the app pool for the Update Client. There can be multiple Application Pools depending on your actual configuration. Review the IIS Authentication settings user guide for additional information.

2.1.2 Stop the Logging Service

Using either the Windows Services management console or the Nodinite Install and Update Tool. The stop operation may take some time (0-120 seconds is common).

2.1.3 Stop the Monitoring Service

Using either the Windows Services management console or the Nodinite Install and Update Tool. This may take some time (0-60 seconds is usual).

2.2 Remove DACPAC information from the old database Server

Before removing the databases, to make your old SQL environment as clean as possible, you will next remove the installed DACPAC version information.

The information about the currently installed DACPAC version is present in the Nodinite Install and Update Tool.

DACPAC Version information

For each Nodinite database; Remove the Data-tier application using SSMS. Right-click on each Nodinite database (those part of the move):

Menu option for the selected database

Then the 'Delete Data-tier Application' Wizard will start:

Click the Next button for the Summary

Click the Next button for the Finish

2.2.1 Repeat step for all Nodinite databases

Repeat this step for ALL Nodinite log- and configuration databases.

The Install and Update Tool should now display 0.0.0.0 for all Nodinite databases.

If you are decommissioning the old SQL Server environment then you can safely ignore this step.

2.3 Detach databases

Please make a note of who is the DBOwner on each database to be moved prior to detaching them. This information may come in handy in later steps.

If your new SQL instance is compatible, it is faster to detach and later re-attach the databases.

1. Detach the databases included in the move operation with the 'drop existing connections option' checked.
2. Copy or move the data files to the new destination.
3. Attach the data files to the new SQL Server instance.
4. Optionally rename the databases (for example, when migrating/updating from Integration Manager to Nodinite).

If your new environment cannot attach the old files, you must perform a backup/restore operation.

2.4 Apply user rights

Next, you need to fix/adjust the privileges set on the re-attached or restored databases.

2.4.1 Configuration database

Please make sure to set the Nodinite install account as the database owner (DBO) on the Nodinite Configuration Database.

2.4.2 Logging Databases

For each Logging Database, make sure to make the account used for the Logging Service the database owner** (DBO).

3. Change configuration

Information about the whereabouts of the Configuration Database and Logging Databases now needs to be updated to the Nodinite services and Web applications listed in the table below.

To avoid performing the following steps, please re-use the SQL Server cluster and/or listener names. If you are using stand-alone SQL Server instances, and not re-using the old Server name, the new name must be specified accordingly.

The actual path depends on where Nodinite is installed (user option during installation). The Nodinite application default paths are documented below.

3.1 Change connection string for the Logging Service

Locate the .config file for the Logging Service

C:\Program Files\Nodinite\%ENVIRONMENT%\Nodinite Core Services\Logging Service\Nodinite.Service.LoggingServiceHost.exe.config

...
  <connectionStrings>
    <add name="NodiniteConfigDatabase" connectionString="Data Source=localhost;Initial Catalog=NodiniteConfig_Dev;Integrated Security=SSPI" providerName="" />
  </connectionStrings>
...

3.2 Change connection string for the Monitoring Service

Locate the .config file for the Monitoring Service

C:\Program Files\Nodinite\%ENVIRONMENT%\Nodinite Core Services\Monitoring Service\Nodinite.Service.MonitoringServiceHost.exe.config

...
  <connectionStrings>
    <add name="NodiniteConfigDatabase" connectionString="Data Source=localhost;Initial Catalog=NodiniteConfig_Dev;Integrated Security=SSPI" providerName="" />
  </connectionStrings>
...

3.3 Change connection string for the Web API

Locate the web.config file for the Web API, and change the connection string to match the new target environment

C:\Program Files\Nodinite\%ENVIRONMENT%\Nodinite Core Services\WebAPI\web.config

...
  <connectionStrings>
    <add name="NodiniteConfigDatabase" connectionString="Data Source=localhost;Initial Catalog=NodiniteConfig_Dev;Integrated Security=SSPI" providerName="" />
  </connectionStrings>
...

3.4 Change connection string for the Log API

Locate the web.config file for the Log API, and change the connection string to match the new target environment

C:\Program Files\Nodinite\%ENVIRONMENT%\Nodinite Core Services\LogAPI\web.config

...
  <connectionStrings>
    <add name="NodiniteConfigDatabase" connectionString="Data Source=localhost;Initial Catalog=NodiniteConfig_Dev;Integrated Security=SSPI" providerName="" />
  </connectionStrings>
...

3.5 Change connection string for the Install and Update tool

Locate the settings.json file for the Install and Update Tool

C:\Program Files\Nodinite\Nodinite Update\UpdateClient\App_Data\settings.json

Change the following Database-related object variables Server and Name accordingly.

...
        "Installations": [
        {
          "Environment": "Dev",
          "LastVersion": "5.0.0.75",
          "DatabaseInstallation": {
            "ServiceAddress": "http://localhost:8000/Nodinite/Service/Update/",
            "AccountName": ".\\NodiniteService",
            "Database": {
              "Server": "localhost",
              "Name": "NodiniteConfig_Dev"
            }
          },
...

3.6 Change Log Database information

This script creates another TSQL script. Use it to change the SQL Server Database instance name for selected Log Databases in the LogTables table in the Configuration Database (name like the @prefix variable).

Change the @prefix, @oldServerName and @newServerName parameters as appropriate and run from the Configuration Database database in SSMS.

DECLARE @sql NVARCHAR(max) =''
DECLARE @prefix NVARCHAR(255) =  'NodiniteLog_Dev_'
DECLARE @oldServerName NVARCHAR(255) =  'localhost'
DECLARE @newServerName NVARCHAR(255) =  'MYOTHERSQLDATABASE[\InstanceName]'
DECLARE @length INT
SET @length = LEN(@prefix) 

;WITH nodinitelogdatabases ([Database]) as
(SELECT [Database] From LogTables Where [Database] Like (@prefix + '%'))--'IM_Log_PROD_%')
Select @sql += 'UPDATE LogTables SET [Server] = ''' + @newServerName + ''''  + ' WHERE [Database] = ''' + l.[Database] + '''' + ' AND [Server] = ''' + @oldServerName + ''''   + CHAR(13)+CHAR(10) 
FROM nodinitelogdatabases l
ORDER BY [Database]
Print @@Rowcount
Print @sql

This script creates another script that you can use to update the Server column in the LogTables table in the Configuration Database.

3.7 Change the connection string for the Nodinite Pickup Service

Locate the Settings.json file for the Nodinite Pickup Service, and change the connection string(s) to match the new target environment

C:\Program Files\Nodinite\Logging Agent - Pickup Service\Settings.json

...
  <connectionStrings>
    <add name="ConfigurationDatabaseConnectionString" connectionString="Data Source=localhost;Initial Catalog=NodiniteConfig_Dev;Integrated Security=SSPI" providerName="" />
  </connectionStrings>
...

3.8 Moving Monitoring Agent Databases

If you are also moving the Nodinite Monitoring Databases, you may need to change the connection string in the Settings.json file and restart the monitoring agent.

3.9 Review the default paths for Data and Log files

On the new SQL Server instance(s), you might be using other default disk volumes compared to the old environment.

IF you have set values for the System Parameter - IMLogLocations it must now be updated to reflect the disk volumes used on the new environment.

4. Register DACPAC

In the new SQL Server instance, the Nodinite version in use needs to be correctly registered.

1. Use the Nodinite Install and Update Tool and navigate to the environment being moved (Prod/Test/Qa/...).
2. Verify the Nodinite Core Services are online.
3. Verify the Nodinite databases displays the 0.0.0.0 version.
4. For each Nodinite database, there is a Manual Update button.

1. Click the button to present a script.
2. Run this script from a CMD-prompt to update the version information according to bullets 4.1 and 4.2.

4.1 Configuration database

To register the DACPAC for the Configuration database follow the steps below.

• 4.1.1 Using remote desktop on the Nodinite Server with the Install and Update Tool, start a command prompt as the service account used as installation account for Nodinite
• 4.1.2 Paste the script for the configuration database and wait for it to finish (review the Manually update database user guide for additional information )

If this step fails, please retry the operation at least once

• 4.1.3 Validate registration by looking in the Install and Update Tool, the version number for Nodinite is now of the same version as before the move and the update button is no longer available

4.2 Logging database

To register the DACPAC for the Logging Database follow the steps below.

• 4.2.1 Using remote desktop on the Nodinite Server with the Logging Service, start a command prompt as the service account used for the Nodinite Logging Service
• 4.2.2 Paste the script for the logging database and wait for it to finish (review the Manually update database user guide for additional information

If this step fails, please retry the operation at least once

• 4.2.3 Validate registration by looking in the Install and Update Tool, the version number for Nodinite is now of the same version as before the move and the update button is no longer available
• 4.2.4 Repeat for each Logging Database

5. Start the IIS - Application Pools

Start all the stopped Nodinite application pools. Review the IIS Authentication settings user guide for additional information.

Do NOT start the Windows Services yet.

5.1 Basic tests

The 'ProductArtifactsPath' System Parameter provides information about where the Nodinite Core Services are located.

Click on each of these links and validate that they are all still working:

Using the Install and Update Tool there are buttons with quick links to the Web applications:

• Web Client - Perform a search in the Admin Log View and verify that there are Log Events in the search result.
• Web API - Using the Swagger feature, perform a test of the Get operations for Roles. All roles should be returned in the response JSON.
• Log API - If possible try to post a minimal JSON Log Event example and find it in the Admin Log View.

From the Web Client the Server name(s) and database name(s) according to the new SQL Server environment. All Log databases should be listed as operational.

6. Restart services

Do not start any of these services until the validation is successful in the previous step!

6.1 Start the Monitoring Service

Next, start the Monitoring Service and validate that it is operational by testing at least one Remote Action from some Monitor View.

6.2 Start the Logging Service

Next, start the Logging Service.

1. Validate that it is operational using the AdminLog View.

1. New Log Events shoud get listed.
2. New entries changes from Unprocessed to another evaluated state.

7. Post steps

• Make sure to update any existing documentation and inform key stakeholders about the outcome of your move operation.
• Make sure to backup the databases in the new SQL environment.
• Add SQL Monitoring of new SQL Instance(s) using the Nodinite Database Monitoring Agent.
• Decommission the old SQL Server instances (if they are not in use for other purposes).
  • If other services continue to use the old SQL Server environment, you can remove the accounts and Linked Server that is no longer needed. Make sure that any other services do not use these.
  • If you performed a copy (not move) operation, the data and log files might now be removed from the old SQL Server environment since they are no longer required.