Last updated

Moving Nodinite databases

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

If you are simply moving the databases to other disk volumes on the very same SQL Server instance than the following quick guide can be used:

  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, see section 3.7
  6. Restart services, see section 6

1. Planning

Make sure 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 carefully plan this operation. 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 Note
Version Same or a newer version of SQL Server? Can you simply detach and attach database files or do you need a backup/restore? The latter can take a longer 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 needs to be changes
Firewall What firewall policies exist on 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. Changes may depend on cluster or no cluster
SPNs registered Required for Kerberos in a distributed environment This step depends on the type of new SQL environment and if you use Logging from BizTalk and/or SQL Server Always on
Trusted for delegation To get Kerberos to function in a distributed environment Applies to distributed environments
Nodinite Accounts exists with the same set of rights

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 prerequisites must be met.

Linked servers

You needed to add the same set of linked servers for all that apply according to the table below (if the type of new SQL Environment is the same as the old one simply copy/script the old ones)

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 normal).

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 normal).

Stop Monitoring Service

2.2 Remove DACPAC information from old server

Before removing the databases and to keep your old SQL environment as clean as possible you will now remove the installed DACPAC version information.

The current DACPAC version installed is most easily seen in the Nodinite Install and Update Tool
Installed DACPACs
DACPAC Version information

For each Nodinite database remote the Data tier application using SSMS and right click in order on each Nodinite database to be moved:
Delete DataTier
Menu option on 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 data files on new sql instance
  4. Rename databases if you like at this point (for example when migrating from Nodinite to Nodinite)

If your new environment cannot attach the old files you need to 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 now 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 DBOwner

2.4.2 Logging Databases

For each Logging Database make sure to make the account used for the Logging Service as DBOwner.

3. Change configuration

If you were not using alias names for the SQL Server or have changed from for example default SQL Instance to a named instance you must change the connection string information for the following Nodinite Core Services:

The actual path depends on where Nodinite got 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 .config file for the Install and Update Tool

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 .config file for the Log API

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 configuration file for the Install and Update Tool

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

For the moved environment configuration, change the Database object variables Server and Name according to the name in new SQL 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 in the Web Client

From the Web Client change the server name and database name according to new SQL environment. All Log databases should be listed as operational

LogDatabaseOperational.png

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, the Manual Update button should be listed and when you click on the button a script is presented.

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 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. Validation

Start all stopped Application Pools enabling all Web-based Nodinite applications. Do NOT start the Windows Services yet.

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 test for example 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

QuickLinkButtons

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

6. Restart services

Do not start these services until validation was 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 gets processed in the Admin Log View

6.3 Start the IIS - Application Pools

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

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 Database Monitoring Agent
  • Decommission the no longer used SQL servers
    • If other services continue to use the old SQL Server environment you can remove the accounts and linked server that is now 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