Migration to SQL Server 2025: Risks, Issues, and Safe Approach

Pre-Migration Phase: Assessment

Step 1: Inventory Current Environment

-- Query current SQL Server version
SELECT @@VERSION;

-- Check compatibility level
SELECT name, compatibility_level FROM sys.databases;

-- List all logins, jobs, linked servers
SELECT name, type FROM sys.server_principals WHERE type IN ('S', 'U', 'G');
SELECT name FROM msdb.dbo.sysjobs;
SELECT name, srvname FROM master.sys.linked_servers;

-- Check for unsupported features (deprecated in SQL 2025)
-- Review: Database Mail, SQL Agent, SSIS, deprecated T-SQL syntax

Step 2: Run Microsoft Data Migration Assistant (DMA)

-- DMA is the official assessment tool
-- Download from: https://www.microsoft.com/download/details.aspx?id=53595

-- DMA will scan for:
// • Breaking changes (features removed in SQL 2025)
// • Compatibility issues (queries that may fail)
// • New feature compatibility
// • Performance recommendations

// Output: Detailed report of blockers and warnings

Step 3: Compatibility Level Check

-- SQL Server 2025 supports compatibility levels 150+ (SQL 2019+)
-- If your database is on SQL 2016 or older (compatibility 130 or lower),
-- it may have unsupported code

-- Query: Find databases needing compatibility update
SELECT name, compatibility_level
FROM sys.databases
WHERE compatibility_level < 150;

-- Plan: Upgrade compatibility level AFTER migration (not before)
// Reason: Changing compatibility before migration adds risk
// Better: Migrate on current level, test, then upgrade

Risk Assessment Matrix

Migration Approaches

Approach 1: In-Place Upgrade (Fastest, Riskiest)

-- Stop all applications
-- Stop SQL Server
-- Run SQL Server 2025 installer (choose "upgrade existing instance")
-- Installer upgrades master, msdb, model databases
-- Wait for automatic recovery
-- Restore system databases
-- Start services

Pros:
✓ Fastest (1-2 hours for medium databases)
✓ No extra hardware needed
✓ Same instance name, ports, logins

Cons:
✗ No rollback if something goes wrong (unless you have pre-upgrade backup)
✗ High risk of data loss if power fails mid-upgrade
✗ Can't keep old server running for comparison
✗ All applications offline during upgrade

Approach 2: Side-by-Side Migration (Safer, Requires Extra Hardware)

-- Set up new server with SQL Server 2025
-- Restore databases from old server onto new server
-- Run DBCC CHECKDB on restored databases
// Test thoroughly on new server
-- When ready, cut over (redirect apps to new server)
-- Keep old server offline for 48 hours (fallback option)

Pros:
✓ Old server remains intact (rollback option)
✓ Can test SQL 2025 extensively before cutover
✓ Parallel run possible (verify compatibility)
✓ Rollback is simple (redirect apps back)

Cons:
✗ Requires extra hardware/licensing
✗ Longer migration window (database copy + tests)
✗ Complexity in data sync during parallel run
✗ Licensing costs (temporary second SQL Server instance)

Approach 3: Log Shipping + Failover (Minimal Downtime)

-- Set up log shipping from old (2019) to new (2025) server
-- Let log shipping run for days/weeks, keeping new server in standby
-- Once confident, restore final log backup, bring new server online
-- Redirect applications to new server

Pros:
✓ Minimal downtime (seconds, not hours)
✓ Old server stays in production until last second
✓ Parallel testing possible
✓ Easy rollback if needed

Cons:
✗ Complex to set up and manage
✗ Risk of log backup queue backup if too many changes
✗ Requires log shipping to be working reliably
✗ Adds administrative overhead

Pre-Migration Checklist

Migration Readiness Checklist:

ASSESSMENT
☐ Run DMA on all databases
☐ Identify breaking changes and blockers
☐ Review deprecated features in use
☐ Audit custom CLR assemblies (need recompilation)
☐ Check collations (must be UTF-8 compatible for SQL 2025 features)

COMPATIBILITY
☐ Test application code against SQL 2025
☐ Update ODBC/OLEDB drivers on client machines
☐ Verify Windows domain trusts (for Windows Authentication)
☐ Test linked servers (may need reconfiguration)
☐ Verify third-party tool compatibility (SSMS, Redgate, etc.)

INFRASTRUCTURE
☐ Allocate disk space (SQL 2025 needs ~50% more space initially)
☐ Pre-allocate tempdb (critical for performance)
☐ Verify backup/restore capacity
☐ Test recovery procedures on staging
☐ Ensure rollback hardware/licenses available

BACKUPS
☐ Full backup of all databases
☐ Full backup of system databases (master, msdb, model)
☐ Full backup of registry (system config)
☐ Offsite backup copy (for disaster recovery)
☐ Test restore of backups to verify integrity

DOCUMENTATION
☐ Document current state (version, patches, config)
☐ Document all custom settings (startup params, etc.)
☐ Document application connection strings
☐ Document rollback procedure (step-by-step)
☐ Create maintenance plan post-migration

Migration Day Execution

The Safe Procedure

-- Time: Assume 4-6 hours total (include testing)

T-0:00 (Pre-migration)
  ☐ Notify all users (downtime starts)
  ☐ Backup all databases (FULL backup)
  ☐ Disable automated jobs (backups, replication, etc.)
  ☐ Stop applications (verify no connections)
  ☐ Run DBCC CHECKDB on all databases (document baseline)

T+0:00 (Migration starts)
  ☐ Stop SQL Server service
  ☐ Copy backup files to isolated location (safety measure)
  ☐ Run SQL Server 2025 installer
  ☐ Choose upgrade existing instance
  ☐ Installer performs upgrade (~1-2 hours depending on size)

T+1:30 (Post-migration)
  ☐ Verify SQL Server started successfully
  ☐ Run DBCC CHECKDB on all databases (verify no corruption)
  ☐ Check error log for warnings/errors
  ☐ Verify all logins exist and have correct permissions
  ☐ Run test queries on critical tables
  ☐ Check job status (should all be disabled, that's OK)

T+2:30 (Application testing)
  ☐ Enable a test application connection
  ☐ Run smoke tests (basic queries, inserts, updates)
  ☐ Monitor performance (CPU, I/O, tempdb)
  ☐ If issues found, STOP and analyze
  ☐ If OK, enable additional applications incrementally

T+4:00 (Full operation)
  ☐ All applications online
  ☐ Monitor for errors/performance issues
  ☐ Re-enable automated jobs (one at a time)
  ☐ Re-enable replication/log shipping
  ☐ Keep old server offline for 48 hours (fallback)

Common Post-Migration Issues

Issue 1: "Object not found" or "Invalid column name"

-- Cause: Deprecated stored procedure or function no longer exists
-- SQL Server 2025 removed several legacy procedures

-- Solution:
-- 1. Run DMA to identify missing objects
-- 2. Update application code to use modern equivalents
// 3. Or: Create wrapper procedures mimicking old behavior

// Example:
// Old: sp_rename (deprecated)
// New: sp_rename still exists but behavior may differ
// Test: Run legacy code in SQL 2025 (find syntax errors)

Issue 2: "Login failed for user"

-- Cause: Windows logins based on old domain/server name
-- When you move to new hardware, SIDs may not match

-- Solution:
// 1. Check that domain trusts are configured
// 2. Manually recreate SQL logins (not Windows logins)
// 3. Orphaned users: Fix with sp_change_users_login

EXEC sp_change_users_login 'Report';  -- Find orphaned users
EXEC sp_change_users_login 'Auto_Fix', 'orphaned_username';

Issue 3: Slow Queries After Migration

-- Cause: Query optimizer changed in SQL 2025
// Execution plans different from SQL 2019
// Statistics might be stale

-- Solution:
// 1. Update all statistics: UPDATE STATISTICS database_name
// 2. Recompile procedures: sp_recompile
// 3. Clear plan cache: DBCC FREEPROCCACHE
// 4. Run workload again (rebuild plan cache)

DBCC FREEPROCCACHE;
UPDATE STATISTICS;

-- Allow queries to run normally (plans regenerated, this time correct)

Issue 4: Tempdb Pressure

-- Cause: SQL 2025 may use more tempdb than prior version
// Especially if running new query optimizer features

-- Solution:
// 1. Pre-allocate tempdb: Size to ~30% of largest user database
// 2. Use multiple tempdb files (one per logical CPU core, up to 8)
// 3. Monitor tempdb usage:

SELECT
  SUM(unallocated_extent_page_count) * 8 / 1024.0 AS free_space_mb
FROM sys.dm_db_file_space_usage;

Rollback Plan

If Migration Fails (Within 48 Hours)

-- Option 1: Restore from pre-migration backup (fastest)
RESTORE DATABASE database_name
FROM DISK = 'backup_path.bak'
WITH REPLACE, RECOVERY;

-- Then redirect apps to old server (or parallel environment)
-- This takes ~30 min - 2 hours depending on database size

-- Option 2: Downgrade SQL Server (if possible)
-- Restore backup on old SQL Server 2019
// This is slower but ensures no data loss

-- Option 3: Keep old server running parallel
// If you chose side-by-side migration
// Old server is still in production
// Just redirect apps back to old server (seconds)

Post-Migration Tasks

After Migration (Day 1):
☐ Full backup of all databases (on SQL 2025)
☐ Update backup/maintenance jobs (point to SQL 2025)
☐ Re-enable replication/log shipping
☐ Verify backups work (test restore)
☐ Monitor error log (watch for warnings)

After Migration (Week 1):
☐ Update compatibility level (if desired)
  ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 160;
☐ Run UPDATE STATISTICS with FULLSCAN (on critical tables)
☐ Rebuild fragmented indexes (if needed)
☐ Verify query performance matches baseline
☐ Update documentation (version, patches, config)

After Migration (Month 1):
☐ Review and address any performance regressions
☐ Validate security configuration
☐ Test disaster recovery (full restore procedure)
☐ Decommission old SQL Server (after success period)
☐ Finalize license changes

Key Success Factors

• ☐ Plan Early: Start DMA and planning 3-6 months before migration
• ☐ Test Thoroughly: Staging environment must match production exactly
• ☐ Dry Run: Execute full migration procedure twice (practice + real)
• ☐ Communication: Notify all stakeholders; set clear expectations
• ☐ Rollback Ready: Have tested procedure to revert if needed
• ☐ Backups: Multiple backups on multiple storage locations
• ☐ Patience: Don't rush; take time to validate at each step
• ☐ Documentation: Write down every decision and action
• ☐ Team Readiness: Entire team trained; no single points of failure
• ☐ Monitoring: Watch for issues immediately after migration

The Bottom Line

SQL Server 2025 migration is not a 2-hour job. It's a multi-month project involving assessment, planning, testing, and meticulous execution. Rush it, and you'll face data loss, hours of downtime, and career-threatening consequences.

Use DMA to identify blockers early. Test extensively in staging. Practice your rollback procedure. Keep the old server ready as a fallback. And most importantly: expect something to go wrong, and have a plan for it.

Done right, you'll have a modern, performant, secure SQL Server 2025 instance. Done wrong, you'll be explaining to your CEO why the database is offline for 8 hours.