- 4 minutes to read

Remote SQL Instance Move

If you are moving from one SQL Server instance to another, follow the comprehensive steps below for a successful migration.

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

Stop Services

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

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

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

Stop Logging Service

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

Stop Monitoring Service

Remove DACPAC Information from the Old Database Server

To make your old SQL environment as clean as possible, you will next remove the installed DACPAC version information for databases no longer in the instance.

You can either use the automated script below or the manual approach described below. If you go for the manual approach, the databases must still exist

Tip

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

The information about the currently installed DACPAC version is present in the Nodinite Administration | Tools | Nodinite Overview.
DACPAC versions
DACPAC Version information

Automated DACPAC Removal

Execute the following SQL command to remove DACPAC information for ALL databases that no longer exist on the instance. Run this script from SSMS against the master database:

-- Remove DACPAC information for databases no longer present on the instance
DELETE FROM msdb.dbo.sysdac_instances
WHERE instance_id NOT IN (
    SELECT instance_id
    FROM msdb.dbo.sysdac_instances dac
    WHERE EXISTS (
        SELECT 1 FROM sys.databases db
        WHERE db.name = dac.instance_name
    )
)

-- Verify deletion
SELECT COUNT(*) AS RemainingDACPACRecords FROM msdb.dbo.sysdac_instances

This script:

  • Identifies DACPAC records for databases that no longer exist on the SQL instance
  • Removes only the orphaned DACPAC information
  • Preserves DACPAC records for databases that still exist
  • Returns the count of remaining DACPAC records for verification

If you are still on Nodinite version 6, the Install and Update Tool should now display 0.0.0.0 for all moved Nodinite databases.
Deleted DacPac
Example of empty DACPAC version information.

Manual DACPAC Removal per Database

If you prefer the manual approach, you can remove each database's Data-tier application using SSMS. On the old SQL Server instance; 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 the Next button for the Summary
Summary

Click the Next button for the Finish
Finish

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

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

Important

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

Apply User Rights

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

Configuration Database

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

Logging Databases

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

Next Step

Database preparation complete? Continue with: