Migration zu SQL Server 2025 –
Risiken, Probleme und
sicheres Vorgehen

-- 1. Alle unverschlüsselten aktiven Verbindungen anzeigen 
SELECT 
s.session_id, 
s.login_name, 
s.program_name, 
s.host_name, 
c.encrypt_option AS Verschlüsselung, 
c.auth_scheme, 
c.protocol_version 
FROM sys.dm_exec_sessions s 
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id 
WHERE s.is_user_process = 1 
AND c.encrypt_option = 'FALSE' 
ORDER BY s.program_name, s.login_name; 
 
-- 2. Alle Linked Server und deren Provider anzeigen 
SELECT 
ls.[name] AS LinkedServer, 
ls.product AS Produkt, 
ls.provider AS Provider, 
ls.data_source AS Datenquelle, 
lso.is_rpc_out_enabled, 
lso.is_data_access_enabled 
FROM sys.servers ls 
INNER JOIN sys.linked_logins ll ON ls.server_id = ll.server_id 
LEFT JOIN sys.linked_logins lso ON ls.server_id = lso.server_id 
WHERE ls.is_linked = 1 
ORDER BY ls.[name]; 
 
-- 3. Replikations-Publisher und Remote-Distributor-Status 
SELECT 
srv.[name] AS Publisher, 
dist.distribution_db AS DistributionDB, 
dist.working_directory 
FROM distribution.dbo.MSdistributiondbs dist 
CROSS JOIN sys.servers srv 
WHERE srv.is_distributor = 1;

-- Verwendung deprecated Features im SQL Server Error Log / Trace 
SELECT 
instance_name AS Feature, 
cntr_value AS NutzungsZaehler 
FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%Deprecated%' 
AND cntr_value > 0 
ORDER BY cntr_value DESC; 
 
-- TEXT/NTEXT/IMAGE Spalten in allen Tabellen finden 
SELECT 
SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS Tabelle, 
c.[name] AS Spalte, 
tp.[name] AS Datentyp 
FROM sys.columns c 
INNER JOIN sys.tables t ON c.object_id = t.object_id 
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id 
WHERE tp.[name] IN ('text', 'ntext', 'image') 
ORDER BY Tabelle, Spalte; 
 
-- Extended Stored Procedures identifizieren (in SQL 2025 geblockt) 
SELECT 
[name] AS XP_Name, 
dll_name AS DLL, 
type_desc 
FROM sys.extended_procedures 
ORDER BY [name];

-- Nach dem Upgrade: aktuellen Level aller Datenbanken prüfen 
SELECT 
name, 
compatibility_level, 
CASE compatibility_level 
WHEN 170 THEN 'SQL Server 2025' 
WHEN 160 THEN 'SQL Server 2022' 
WHEN 150 THEN 'SQL Server 2019' 
WHEN 140 THEN 'SQL Server 2017' 
WHEN 130 THEN 'SQL Server 2016 (Kompatibilitätsmodus)' 
ELSE 'Älter / Unbekannt' 
END AS EffektivVersion 
FROM sys.databases 
WHERE database_id > 4 
ORDER BY name; 
 
-- Query Store aktivieren BEVOR der Level angehoben wird (Baseline erfassen) 
ALTER DATABASE [MeineDatenbank] 
SET QUERY_STORE = ON 
( 
OPERATION_MODE = READ_WRITE, 
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 
MAX_STORAGE_SIZE_MB = 1024, 
QUERY_CAPTURE_MODE = ALL -- Alles erfassen für Baseline 
); 
 
-- Level anheben – nach ausreichend Testzeit mit dem alten Level 
ALTER DATABASE [MeineDatenbank] 
SET COMPATIBILITY_LEVEL = 170; 
 
-- Bei Plan-Regression: Level zurücksetzen (von 2016 kommend: auf 130) 
ALTER DATABASE [MeineDatenbank] 
SET COMPATIBILITY_LEVEL = 130; -- Rollback-Option

-- Logins auf Quellserver skripten (sp_help_revlogin oder DMA) 
USE master; 
GO 
 
-- Alle SQL Logins mit Hashes exportieren (für Passwort-Migration) 
SELECT 
'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ''' 
+ [name] + ''') CREATE LOGIN [' + [name] 
+ '] WITH PASSWORD = ' 
+ CONVERT(VARCHAR(512), [password_hash], 1) 
+ ' HASHED, SID = ' 
+ CONVERT(VARCHAR(512), [sid], 1) 
+ ', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;' AS CreateScript 
FROM sys.sql_logins 
WHERE [name] NOT LIKE '##%' 
AND [name] != 'SA' 
ORDER BY [name]; 
 
-- SQL Agent Jobs exportieren (für Import auf Zielserver) 
SELECT OBJECT_DEFINITION(OBJECT_ID('msdb.dbo.sp_get_composite_job_info')); 
-- Alternativ: SSMS → SQL Agent → Alle Jobs skripten

-- Plan-Regressionen im Query Store identifizieren 
-- Abfragen deren Laufzeit nach dem Level-Wechsel gestiegen ist 
WITH Baseline AS ( 
SELECT 
q.query_id, 
AVG(rs.avg_duration) AS avg_dur_baseline 
FROM sys.query_store_query q 
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id 
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id 
WHERE rs.last_execution_time BETWEEN 
DATEADD(DAY, -14, GETUTCDATE()) 
AND DATEADD(DAY, -1, GETUTCDATE()) -- Vor Level-Wechsel 
GROUP BY q.query_id 
), 
Aktuell AS ( 
SELECT 
q.query_id, 
AVG(rs.avg_duration) AS avg_dur_aktuell 
FROM sys.query_store_query q 
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id 
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id 
WHERE rs.last_execution_time > DATEADD(DAY, -1, GETUTCDATE()) -- Nach Level-Wechsel 
GROUP BY q.query_id 
) 
SELECT TOP 20 
qt.query_sql_text, 
b.avg_dur_baseline / 1000.0 AS baseline_ms, 
a.avg_dur_aktuell / 1000.0 AS aktuell_ms, 
(a.avg_dur_aktuell - b.avg_dur_baseline) / b.avg_dur_baseline * 100 
AS Verschlechterung_Pct 
FROM Baseline b 
INNER JOIN Aktuell a ON b.query_id = a.query_id 
INNER JOIN sys.query_store_query q ON b.query_id = q.query_id 
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id 
WHERE a.avg_dur_aktuell > b.avg_dur_baseline * 1.25 -- > 25% langsamer 
ORDER BY Verschlechterung_Pct DESC;

-- SSMS 22 Migration Component Assessment (GUI-Schritte): 
-- 1. Quellinstanz verbinden 
-- 2. Im Objekt-Explorer: Rechtsklick auf Instanz → Migrate SQL Server 
-- 3. "New Assessment" wählen 
-- 4. Zielversion: SQL Server 2025 auswählen 
-- 5. Assessment ausführen → Report enthält: 
-- - Breaking Changes (mit Priorität) 
-- - Deprecated Features 
-- - Linked Server-Warnungen (OLE DB 19) 
-- - Empfohlene Maßnahmen mit T-SQL-Snippets 
 
-- Alternativ: Verbindungsstring-Scan mit PowerShell (Schnellcheck) 
-- PowerShell: Alle Verbindungsstrings in .config-Dateien suchen 
-- Get-ChildItem -Path "C:\Apps" -Recurse -Filter "*.config" | 
-- Select-String -Pattern "Encrypt=False|Trusted_Connection" | 
-- Select-Object Path, LineNumber, Line 
 
-- Full-Text Indizes vorbereiten: Rebuild-Skript generieren 
SELECT 
'ALTER FULLTEXT CATALOG [' + fc.[name] + '] REBUILD;' AS RebuildScript, 
fc.[name] AS KatalogName, 
fi.change_tracking_state_desc 
FROM sys.fulltext_catalogs fc 
INNER JOIN sys.fulltext_indexes fi ON fc.fulltext_catalog_id = fi.fulltext_catalog_id 
ORDER BY fc.[name];

-- AG-Zustand und Versionsmix während Rolling Upgrade prüfen 
SELECT 
ag.[name] AS AG_Name, 
ar.replica_server_name AS Replika, 
ars.role_desc AS Rolle, 
ars.synchronization_health_desc AS SyncHealth, 
SERVERPROPERTY('ProductVersion') AS InstanzVersion, 
ars.connected_state_desc AS Verbindung 
FROM 
sys.availability_groups ag 
JOIN sys.availability_replicas ar 
ON ag.group_id = ar.group_id 
JOIN sys.dm_hadr_availability_replica_states ars 
ON ar.replica_id = ars.replica_id 
ORDER BY ag.[name], ars.role_desc; 
 
-- Geplanten manuellen Failover auf SQL 2025-Sekundäre ausführen 
-- Auf der Ziel-Sekundäre ausführen (nicht auf dem Primary): 
ALTER AVAILABILITY GROUP [AG_Produktion] FAILOVER; 
 
-- Nach Failover: Automatischen Failover-Modus wieder aktivieren 
ALTER AVAILABILITY GROUP [AG_Produktion] 
MODIFY REPLICA ON N'SQL2025-PRIMARY' 
WITH (FAILOVER_MODE = AUTOMATIC);

-- Vollbackup aller Datenbanken unmittelbar vor dem Upgrade 
DECLARE @BackupPath NVARCHAR(260) = N'\\backup\PreUpgrade_20260419\'; 
 
SELECT 
'BACKUP DATABASE [' + name + '] TO DISK = ''' 
+ @BackupPath + name 
+ '_PreUpgrade_20260419.bak'' WITH COMPRESSION, CHECKSUM, STATS = 5;' 
FROM sys.databases 
WHERE database_id > 4 -- Benutzerdatenbanken 
AND state_desc = 'ONLINE'; 
 
-- Nach dem Backup: Restore-Fähigkeit prüfen (ohne tatsächlichen Restore) 
RESTORE VERIFYONLY 
FROM DISK = N'\\backup\PreUpgrade_20260419\MeineDatenbank_PreUpgrade_20260419.bak' 
WITH CHECKSUM; 
 
-- msdb (Jobs, Maintenance Plans) und master (Logins, Linked Server) sichern 
BACKUP DATABASE msdb TO DISK = N'\\backup\PreUpgrade_20260419\msdb_PreUpgrade.bak' WITH CHECKSUM; 
BACKUP DATABASE master TO DISK = N'\\backup\PreUpgrade_20260419\master_PreUpgrade.bak' WITH CHECKSUM;