- 13 minutes to read

Moving Nodinite databases

In this user guide, you will learn the steps required for successfully moving the Nodinite Logging Databases and the Configuration Database.

Local move

If you are moving the databases to other disk volumes on the very same SQL Server instance; Then use the quick guide below:

  1. Stop 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 databases
  5. Review default paths for Data and Log files; review section 3.7
  6. Restart services; review section 6
graph LR subgraph "After" roSQL2[fal:fa-database SQL Server instance] roD2[fal:fa-hdd M:\ DATA] roE2[fal:fa-hdd N:\ LOG] roSQL2 --> roD2 roSQL2 --> roE2 end subgraph "Before" roSQL[fal:fa-database SQL Server instance] roD[fal:fa-hdd D:\ DATA] roE[fal:fa-hdd E:\ LOG] roSQL --> roD roSQL --> roE end

Example of a local move from one set of disks to another

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

graph LR subgraph "Moving
" roSQL[fal:fa-server Source instance] --> roSQL2[fal:fa-server Destination instance] end

1. Planning

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

Make sure to plan this operation carefully. Verify that all prerequisites are satisfied on new SQL Instances before moving any of the Nodinite databases

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

Control Step Description
Version Same or a newer version of SQL Server? Can you detach and attach the database files, or do you need to perform a backup and restore operation? The latter takes more time. The SqlPackage.exe may need to be updated; Make sure to match accordingly
SQL Server instance name New name on new SQL Server instance? (named or default instance) If you are not already using Alias then this is the time to apply and use aliases. Multiple config files need to be changed
Firewall What firewall policies exist in the old environment? Make sure to open ports for the new environment
Linked Server(s) Make sure the new environment has the corresponding linked servers setup as in the old environment Without these Nodinite can't access the other databases
MSDTC Nodinite relies on this service Make sure the new environment has the same configuration as the old one meeting the Nodinite prerequisites
SPNs registered Kerberos requirement in a distributed environment This step depends on the type of the new SQL environment and if you are using Logging from BizTalk Server and/or uses SQL Server Always-On
Trusted for delegation To get Kerberos to function in a distributed environment Applies to distributed environments
Access rights Nodinite Accounts exists with the same set of rights Copy from the old environment

Preparations on the new SQL Server instance(s)

The new SQL environment may be stand-alone, clustered, or even running SQL Server Always On. Either way, the Nodinite prerequisites must be met.

Linked servers

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

For all that apply according to the table below; You need to add the same set of linked servers. If the type of the new SQL Environment is the same as the old one; Copy/script the old linked servers using SSMS.

Product SQL Environment Step Comment
Nodinite Default instance Make sure there is a linked server for 'localhost\instancename' Repeat for other external SQL Server instances with Nodinite databases
Nodinite Named instance Make sure there is a linked server for 'localhost\instancename' Repeat for other external SQL Server instances with Nodinite databases
BizTalk (BizTalkMgmtDB) Default instance Make sure there is a linked server for 'BizTalkSQL[ClusterMGMTInstance]Name'
BizTalk (BizTalkMgmtDB) Named instance Make sure there is a linked server for 'BizTalkSQL[ClusterMGMTInstance]Name\instancename'
BizTalk (BizTalkDTADb) Default instance Make sure there is a linked server for 'BizTalkSQL[ClusterMGMTInstance]Name' If this database is in another instance than the BizTalkMgmtDb
BizTalk (BizTalkDTADb) Named instance Make sure there is a linked server for 'BizTalkSQL[ClusterMGMTInstance]Name\instancename' If this database is in another instance than the BizTalkMgmtDb

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

MSDTC

Review the MSDTC user guide.

Preparation on Nodinite Application Servers

If the new SQL Server instance is on a higher version you must update the SQLPackage (DACPAC) to match the new version. This also means you must change the SqlPackageExecutable system parameter with information about the new path.

Before continuing with later steps, make sure to verify all steps are performed according to the various prerequisites


2. Moving databases

All services must first be stopped and Nodinite will not be available for end-users during the move.

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.
App Pools

2.1.2 Stop the Logging Service

Using either the Windows Services management console or the Nodinite Update Client. This may take some time (0-120 seconds is usual).

Stop Logging Service

2.1.3 Stop the Monitoring Service

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

Stop Monitoring Service

2.2 Remove DACPAC information from the old database server

Before removing the databases, making 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
Installed DACPACs
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):
Delete DataTier
Menu option for the selected database

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

Click on the Next button for the Summary
Summary

Click on the Next button for the Finish
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.

Deleted DacPac

If the old SQL Server environment is being decommissioned this step can be ignored

2.3 Detach databases

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 of a compatible version it will be faster to simply detach databases to be moved.

  1. Detach databases to be moved with drop existing connections option checked
  2. Copy or move the data files to the new destination
  3. Attach the data files on the new SQL Server instance
  4. Optionally rename the databases (for example when migrating/updating from Nodinite to Nodinite)

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

2.4 Apply user rights

Since all necessary accounts according to the planning and prerequisites guide in the table first in this document are already in place, you need to fix/adjust the privileges set on the newly attached or restored databases.

2.4.1 Configuration database

Make sure to set the Nodinite install account as the database owner (DBO).

2.4.2 Logging Databases

For each Logging Database, make sure to make the account used for the Logging Service as 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.

Avoid these steps by the use of alias DNS names for the different SQL Server instances.

Core Service Configuration file Restart required Comment
3.1 Logging Service Nodinite.Service.LoggingServiceHost.exe.config Yes Change the connectionString content
3.2 Monitoring Service Nodinite.Service.MonitoringServiceHost.exe.config Yes Change the connectionString content
3.3 Web API web.config Automatic when saving web.config Change the connectionString content
3.4. Log API web.config Automatic when saving web.config Change the connectionString content
3.5. Install and Update Tool settings.json Restart App Pool Change the connectionStrings (multiple)
3.6. Log Databases LogTables table - Change the Server name

The actual path depends on where Nodinite is installed (user option during installation). The 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

From within the moved environment configuration, change the Database object variables Server and Name according to the names used for the new SQL Database environment

...
        "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 parameter 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 imlogdatabases ([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 imlogdatabases l
ORDER BY [Database]
Print @@Rowcount
Print @sql

Creates a script to update the Server column in the LogTables table in Configuration Database

3.7 Review 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. DataLogDisks

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

Use the Install and Update Tool and navigate to the newly moved environment (Prod/Test/Qa/...). The Core Services should now before updating the registration be listed as online and all databases should display 0.0.0.0 version.

For each database to update, there is a Manual Update button, and when you click on the button, a script presents. Run this script to update the version information.

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.

QuickLinkButtons

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

LogDatabaseOperational.png

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 and validate that it is operational by testing that new Log Events gets listed and that these are processed in the Admin Log View

7. Post steps

  • Make sure to update existing documentation and inform stakeholders about the outcome of your move operation.
  • Make sure backups are taken on databases in the new SQL environment
  • Add SQL Monitoring of new SQL Instance(s) using the Nodinite Database Monitoring Agent
  • Decommission the SQL server instances that is no longer being used
    • 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 these are not used by any other services.
    • If you copied (not moved) the data-files they should now be removed from the old SQL Server environment