SQL Server Database Migration
Migrate your Nodinite SQL Server databases with confidence using this comprehensive guide. Whether you're moving to a new disk volume or migrating to an entirely new SQL Server instance, we'll guide you through every step of the process.
- ✅ Step-by-step planning checklists
- ✅ Scenario-specific migration paths (local disk or remote instance)
- ✅ Secure configuration updates through the Nodinite Portal and the CLI
- ✅ Encrypted linked servers after a move or in-place upgrade to a newer SQL Server
- ✅ Complete validation and testing procedures
- ✅ Post-migration optimization and monitoring
Note
This guide covers Nodinite v7. Nodinite v6 is no longer supported; to move a v6 installation to new hardware, follow Migrating v6 to v7 on New Hardware.
Choose Your Migration Path
then new connection string and SqlServer parameter"] style C fill:#93d16d style D fill:#E47528
Diagram: pick the Local Disk Move (green) when the SQL Server instance stays the same, and the Remote SQL Instance Move (orange) for any other instance, including SQL Server version upgrades and consolidations.
Migration Phases Overview
Every migration follows these core phases, regardless of your scenario:
Diagram: the six guides in order: Planning, Local Disk Move or Remote SQL Instance Move, Configuration Changes, Validation and DACPAC Registration, and Post-Migration Steps, covering the Configuration Database, the Log Databases, and the Agent Database.
Phase 1: Planning and Preparation
Verify prerequisites, inform stakeholders, and prepare the target environment before you touch anything: 1. Planning.
Phase 2: Migration Execution
Stop the Nodinite Core Services and move the data. The steps depend on your scenario:
- 2. Local Disk Move — detach and reattach on the same SQL Server instance
- 3. Remote SQL Instance Move — backup and restore (or detach and attach) on a new SQL Server instance
Phase 3: Configuration Updates
Point Nodinite to the new database location: the connection string in the Nodinite Portal (Step 3: Infrastructure), the SqlServer System Parameter, and the Log Database entries: 4. Configuration Changes.
Phase 4: Validation and DACPAC Registration
Verify that the Core Services start and that the databases are operational and registered: 5. Validation and DACPAC Registration.
Phase 5: Post-Migration Optimization
Back up, monitor, and tune the performance of the new environment, then decommission the old one: 6. Post-Migration Steps.
Quick Decision Tree
| Scenario | Start Here | Key Phases |
|---|---|---|
| Moving to different disks on same SQL instance | Local Disk Move | Stop services → Detach → Move files → Reattach → Restart |
| Moving to different SQL Server instance | Planning → Remote Move | Plan → Prepare instance → Backup → Restore → Configure → Validate |
| Upgrading to newer SQL Server version | Planning → Remote Move | Verify compatibility → Prepare → Backup → Restore → Recreate linked servers → Test |
| In-place upgrade of SQL Server | Recreate Linked Servers | Stop services → Upgrade SQL Server → Drop and recreate linked servers → Verify → Restart |
| Consolidating multiple instances | Planning → Remote Move | Plan carefully → Prepare → Backup each → Restore to new instance |
Key Concepts
Local Disk Move
Use the Local Disk Move when moving to different disk volumes on the same SQL Server instance.
Advantages of a Local Disk Move
- ✅ Fast and simple
- ✅ No connection string change needed (server name unchanged)
- ✅ Lower risk
- ✅ Minimal downtime
What Happens in a Local Disk Move
- Stop the Nodinite Core Services
- Detach databases from current disks
- Move database files to new disks
- Reattach databases
- Restart the Core Services
Remote SQL Instance Move
Use the Remote SQL Instance Move when moving to any different SQL Server instance (new server, upgrade, consolidation).
Advantages of a Remote SQL Instance Move
- ✅ Complete infrastructure refresh
- ✅ Version upgrades supported
- ✅ No downtime on old server
- ✅ Easy rollback (old server still available)
What Happens in a Remote SQL Instance Move
- Plan and prepare new SQL instance
- Stop the Nodinite Core Services
- Backup databases from old instance
- Restore to new instance and recreate the linked servers
- Update the Nodinite configuration (connection string, SqlServer System Parameter, Log Database entries)
- Validate and test
- Decommission old instance
Connection Strings and Secrets
Nodinite v7 protects connection strings with certificate-based encryption stored in the Windows Certificate Store (see Secret Management). You always enter the new connection string unencrypted, in the Nodinite Portal or as a CLI argument, and Nodinite protects it automatically. Configuration Changes shows every place to update.
Recreate Linked Servers After a SQL Server Upgrade
Nodinite always reads the Log Databases through Linked Servers, even when they sit on the same SQL Server instance as the Configuration Database. A linked server definition stores the OLE DB provider and its connection options, and these changed in newer SQL Server versions:
- SQL Server 2022 and later no longer ship the SQL Server Native Client (
SQLNCLI,SQLNCLI11) provider that older linked servers use. - The replacement, the Microsoft OLE DB Driver for SQL Server (
MSOLEDBSQL), encrypts the connection by default and validates the SQL Server certificate.
A linked server that comes along from the older SQL Server, kept by an in-place upgrade or scripted in SSMS and run on the new instance, therefore keeps its old options and can fail. When it does, the Logging Service can't query or create Log Databases, and searches in the Web Client return no Log Events.
SQLNCLI, no encryption"] --> Upgrade{"fa:fa-arrow-up-right-dots Newer SQL Server
moved or upgraded in place"} Upgrade -->|"kept or scripted as is"| Fail["fa:fa-plug-circle-xmark Linked server queries fail
no Log Databases"] Upgrade -->|"dropped and recreated"| New["fa:fa-lock MSOLEDBSQL
Encrypt=Yes"] New --> OK["fa:fa-database Nodinite reaches the Log Databases"] style Old fill:#FFD525 style Fail fill:#c33d34 style New fill:#2678BD style OK fill:#93d16d
Diagram: an old linked server (yellow) carried over to a newer SQL Server fails (red); dropping and recreating it with the new provider and encryption options (blue) lets the Configuration Database reach the Log Databases again (green).
When to Recreate the Linked Servers
| Scenario | Recreate the linked servers? |
|---|---|
| In-place upgrade to a newer SQL Server version | ✅ Yes, all of them |
| Remote SQL Instance Move to a newer SQL Server version | ✅ Yes, create them with the new options; never run a script of the old ones unchanged |
| Remote SQL Instance Move to the same SQL Server version | ✅ Yes, create them on the new instance (see Planning) |
| Local Disk Move on the same instance | ❌ No, the linked servers stay as they are |
Recreate every linked server Nodinite uses: the one for localhost (or the server name), the ones for other instances that host Log Databases, and the ones for BizTalk Server and other monitored databases. With Always On Availability Groups or a failover cluster, recreate them on every node. The full list is in Required Linked Servers for Nodinite.
Old vs New Linked Server Options
| Setting | Old linked server | New linked server |
|---|---|---|
| Provider | SQLNCLI or SQLNCLI11 (SQL Server Native Client) |
MSOLEDBSQL (Microsoft OLE DB Driver for SQL Server) |
| Encryption | Optional (off) | Mandatory: Encrypt=Yes |
| Trust Server Certificate | Not used | TrustServerCertificate=Yes, unless SQL Server has a certificate the Nodinite servers trust |
| Security context | Be made using the login's current security context | Unchanged |
| RPC and RPC Out | true |
Unchanged, see Linked Server RPC and RPC Out |
Tip
TrustServerCertificate=Yesencrypts the connection but accepts the self-signed certificate SQL Server creates at startup. If your SQL Server has a certificate from a trusted certificate authority, clear Trust Server Certificate and, when the certificate name differs from the linked server name, enter it in Host Name in Certificate. SQL Server Connection Strings describes the same options for the connection string.
Step 1: List the Existing Linked Servers
Run this query in SSMS against the master database on the new or upgraded instance, and note each linked server's name:
SELECT name, provider, data_source, provider_string
FROM sys.servers
WHERE is_linked = 1;
Keep the names unchanged. Nodinite reads the linked server name from the LinkedServerName property of the SqlServer System Parameter, and the Server column of every Log Database entry must match a linked server name.
Step 2: Drop the Old Linked Server
Stop the Logging Service and the Monitoring Service first, unless you're in the middle of a migration where the Core Services are already stopped. Then drop the linked server, here localhost:
EXEC master.dbo.sp_dropserver @server = N'localhost', @droplogins = 'droplogins';
GO
Step 3: Create the Linked Server With Encryption
In SSMS, right-click Server Objects › Linked Servers › New Linked Server:
- On the General page, enter the linked server name, select SQL Server as the server type, set Encryption to Mandatory, and check Trust Server Certificate (see the tip above).
- On the Security page, select Be made using the login's current security context.
- On the Server Options page, set RPC and RPC Out to
True.

Example of the encryption options on the General page of a new linked server for LOCALHOST.
Or run the script below; it creates the same linked server for localhost. Replace localhost in every statement with your linked server name, such as SERVERNAME\INSTANCENAME or an Always On listener name:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'localhost', @srvproduct = N'', @provider = N'MSOLEDBSQL', @datasrc = N'localhost', @provstr = N'TrustServerCertificate=Yes;Encrypt=Yes'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'localhost', @useself = N'True', @locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULL
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'collation compatible', @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'data access', @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'dist', @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'pub', @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'rpc', @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'rpc out', @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'sub', @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'connect timeout', @optvalue = N'0'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'collation name', @optvalue = NULL
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'lazy schema validation', @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'query timeout', @optvalue = N'0'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'use remote collation', @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'localhost', @optname = N'remote proc transaction promotion', @optvalue = N'true'
GO
@useself = N'True' is the login's current security context, and @provstr holds the encryption options. Repeat the script for every linked server you listed in Step 1.
Step 4: Verify the Linked Server
Query through the linked server, then start the Logging Service and the Monitoring Service again:
SELECT name FROM [localhost].[master].[sys].[databases];
EXEC sp_helpserver;
The query lists the databases on the linked instance, and sp_helpserver shows rpc and rpc out for the linked server. If the query fails with a certificate error, such as The certificate chain was issued by an authority that is not trusted, check Trust Server Certificate or install a trusted certificate on SQL Server. For other errors, see Troubleshooting on the Linked Servers page.
Databases Affected
This guide covers migration of:
- Configuration Database — Central settings and parameters
- Log Databases — Message logging storage (one or many)
- Monitoring Databases — Monitor data storage (if applicable)
Estimated Time & Downtime
| Migration Type | Planning Time | Service Downtime | Total Duration |
|---|---|---|---|
| Local Disk Move | 1-2 hours | 30-60 minutes | 2-3 hours |
| Remote SQL Instance Move (same server) | 2-4 hours | 1-2 hours | 4-6 hours |
| Remote SQL Instance Move (different server) | 4-8 hours | 2-4 hours | 6-12 hours |
| SQL Server Version Upgrade | 6-12 hours | 2-4 hours | 8-16 hours |
Times vary based on database size, network speed, and team experience.
Best Practices
- ✅ Always Plan First — Never rush into migration without a clear plan
- ✅ Test in Staging — Validate the process in a non-production environment
- ✅ Backup Everything — Full backup before any migration
- ✅ Notify Stakeholders — Inform users about planned downtime
- ✅ Have a Rollback Plan — Know how to revert if issues occur
- ✅ Monitor After Migration — Watch for issues in the first 24-48 hours
- ✅ Document Changes — Keep records of what was moved and when
Next Steps
- First time? Start with the Planning Guide
- Know your scenario? Jump to Local Move or Remote Move
- Already migrating? Go to Configuration Changes
- Finishing up? See Validation & DACPAC and Post-Migration
Related Topics
- SQL Server Troubleshooting
- Secret Management — Certificate-based encryption in Nodinite 7
- Configuration Database — Central configuration storage
- Log Databases — Message logging storage
- SQL Server Connection Strings
- SQL Server Linked Servers — Linked server setup, naming, and troubleshooting
- Post Installation Steps — Environment tuning and optimization after migration
- Updating Nodinite v7 — Stay current with latest version
- Upload and restore SQL Managed Instance .bak using Azure Blob Storage