dtcSoftware - powerShellDba

📖 Was ist das? ⚙️ Konfiguration 📊 Abfragen ⚡ OPENQUERY 📁 Remote‑SP 🛡️ Sicherheit

📌 Was ist ein Linked Server?

KONZEPT

Ein Linked Server erlaubt Ihrem SQL Server, Befehle auf einer anderen Datenbankinstanz auszuführen – ob SQL Server, Oracle, MySQL oder eine andere OLE DB/ODBC-Quelle. Sie können Tabellen serverübergreifend joinen, remote Prozeduren aufrufen und Daten an einer Stelle zentralisieren.

-- Merke: Ein Linked Server ist ein Brückenobjekt in der tempdb, das einen zweiten Datenknoten bekannt macht.
-- Vier-Universen-Notation für verteilte Abfragen
SELECT *
FROM [LinkedServerName].[Datenbank].[Schema].[Tabelle];

💡 Vier‑Universen‑Konzept: `[LinkedServer]` = Verbindung, `[Database]` = Datenbank, `[Schema]` = Besitzer, `[Table]` = Tabelle. So sprechen Sie jede remote verfügbare Tabelle an, als wäre sie lokal.

⚙️ 1. Linked Server erstellen (SSMS & T‑SQL)

EINSTIEGER

Sie können die Verbindung entweder grafisch über SSMS oder per Skript mit `sp_addlinkedserver` herstellen. Danach legen Sie die Anmeldezuordnung mit `sp_addlinkedsrvlogin` fest.

-- *** T‑SQL Variante (bevorzugt für Automatisierung) ***
EXEC master.dbo.sp_addlinkedserver 
  @server = N'REMOTE_SRV', -- Alias, der im Query verwendet wird
  @srvproduct = N'', -- bei MS SQL Server leer lassen
  @provider = N'SQLNCLI', -- OLE DB Provider
  @datasrc = N'192.168.1.100'; -- IP oder Hostname des Zielservers

-- Anmeldezuordnung (remote Login + Passwort)
EXEC master.dbo.sp_addlinkedsrvlogin 
  @rmtsrvname = N'REMOTE_SRV',
  @useself = N'false',
  @locallogin = NULL,
  @rmtuser = N'remote_user',
  @rmtpassword = N'strong_pw';

-- Sicherheitsoptionen: RPC Out aktivieren (um Stored Proc. aufzurufen)
EXEC master.dbo.sp_serveroption 
  @server = N'REMOTE_SRV',
  @optname = N'rpc out',
  @optvalue = N'true';

🔧 Hinweis: Nutzen Sie nach Möglichkeit Windows‑Authentifizierung mit `@useself = 'true'`; dann wird das Konto des ausführenden SQL‑Server‑Dienstes weitergereicht. Vermeiden Sie das Verwenden von `sa` oder anderen systemweiten Accounts.

📊 2. Abfragen, Joins & DML über Linked Server

PRAXIS

Sobald der Link eingerichtet ist, verwenden Sie ganz normale SELECT, INSERT, UPDATE oder DELETE – einfach mit dem vierteiligen Namen.

-- *** SELECT vom Remote‑Server ***
SELECT TOP 10 * FROM REMOTE_SRV.SalesDB.dbo.Customers;

-- *** JOIN zwischen lokaler und remote Tabelle ***
SELECT 
  o.OrderID,
  o.CustomerID,
  c.CustomerName
FROM LocalDB.dbo.Orders o
INNER JOIN REMOTE_SRV.SalesDB.dbo.Customers c
  ON o.CustomerID = c.CustomerID;

-- *** Daten auf remote Server einfügen / ändern ***
INSERT INTO REMOTE_SRV.SalesDB.dbo.Customers (CustomerName, City)
VALUES (N'Musterfirma GmbH', N'Berlin');

UPDATE REMOTE_SRV.SalesDB.dbo.Customers
SET City = N'Hamburg'
WHERE CustomerID = 100;

🚀 Performance‑Warnung: Ein direkter Join mit vierteiligem Namen kann die gesamte Remote‑Tabelle abziehen und erst dann lokal filtern. Für große Datenmengen ist `OPENQUERY` oft besser, weil der Filter direkt auf dem Remote‑System ausgeführt wird.

⚡ 3. OPENQUERY – Pass‑Through für maximale Performance

PROFI

`OPENQUERY` sendet eine SQL‑Anweisung unverändert an den Linked Server, wird dort vollständig ausgeführt, und nur das Ergebnis wandert zurück. Das reduziert Netzlast und nutzt Indexe des entfernten Systems optimal.

-- *** Grundlegende OPENQUERY‑Syntax ***
SELECT * 
FROM OPENQUERY(REMOTE_SRV, 
  'SELECT CustomerID, CustomerName, City 
   FROM SalesDB.dbo.Customers 
   WHERE City = ''Berlin''');

-- *** INSERT über OPENQUERY (gesamte Zieltabelle im FROM) ***
INSERT INTO OPENQUERY(REMOTE_SRV, 
  'SELECT CustomerID, CustomerName, City 
   FROM SalesDB.dbo.Customers WHERE 1=0')
VALUES (201, N'PowerShell AG', N'Frankfurt');

-- *** Daten in lokale Tabelle aus OPENQUERY holen ***
INSERT INTO #TempBerlinerKunden
SELECT * FROM OPENQUERY(REMOTE_SRV, 
  'SELECT CustomerID, CustomerName 
   FROM SalesDB.dbo.Customers 
   WHERE City = ''München''');

📦 Wichtige Einschränkungen: • OPENQUERY akzeptiert keine Parameter (keine Variablen). Für dynamische Werte muss der gesamte String dynamisch gebaut werden. • Innerhalb des OPENQUERY‑Strings müssen Einfachanführungszeichen durch zwei Anführungszeichen escaped werden (`WHERE City = ''Berlin''`). • OPENQUERY kann keine erweiterten Stored Procedures (xp_*) ausführen.

📁 4. Remote Stored Procedures ausführen (drei Methoden)

ERWEITERT

Es gibt drei bewährte Wege, eine gespeicherte Prozedur auf einem Linked Server zu starten: den Vier‑Teile‑Namen, `OPENQUERY` sowie die mächtigste Variante `EXECUTE ... AT`.

-- Methode 1: Vier‑Teile‑Name (einfach, aber erfordert RPC Out)
EXECUTE REMOTE_SRV.SalesDB.dbo.sp_GetTopCustomers @TopCount = 10;

-- Methode 2: OPENQUERY (für SELECT‑ähnliche SPs ohne Ergebnisparameter)
SELECT * 
FROM OPENQUERY(REMOTE_SRV, 
  'EXEC SalesDB.dbo.sp_GetTopCustomers @TopCount = 5');

-- Methode 3: EXECUTE AT (dynamische, komplexe Batches, auch mit OUTPUT)
DECLARE @RemoteScript NVARCHAR(MAX) = 
  N'DECLARE @RC INT; 
   EXEC @RC = SalesDB.dbo.sp_GetCustomerCount @City = ''Berlin''; 
   SELECT @RC AS ResultCode;';

EXECUTE (@RemoteScript) AT REMOTE_SRV;

⚙️ Best Practice: • Für einfache Prozeduren ohne Tücken reicht der Vier‑Teile‑Name, wenn `RPC Out` auf `TRUE` gesetzt ist. • `EXECUTE ... AT` ist die robuster Wahl bei komplexer, dynamischer Logik oder wenn Sie OUTPUT‑Parameter benötigen. • Prüfen Sie vor dem Aufruf die Verfügbarkeit des Linked Servers mit `sp_testlinkedserver`, um härtere Fehler zu vermeiden.

🛡️ 5. Sicherheit, Berechtigungen & bewährte Handhabung

BEST PRACTICES

Ein Linked Server besitzt eigene Sicherheitskontexte und kann zum Einfallstor werden, wenn er falsch konfiguriert ist. Beachten Sie folgende Grundsätze.

-- minimale Berechtigungen: ein dedizierter Remote‑Login mit Read/Write-Einschränkung
CREATE LOGIN linked_user WITH PASSWORD = 'strong!PW';
CREATE USER linked_user FOR LOGIN linked_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON SalesDB.dbo.Customers TO linked_user;

-- Immer nach Möglichkeit Windows‑Authentifizierung mit @useself='true' nutzen
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'REMOTE_SRV', @useself = 'true';

-- Sicherheitsoptionen: Nur notwendige Server‑Optionen aktivieren
EXEC sp_serveroption @server = 'REMOTE_SRV', @optname = 'lazy schema validation', @optvalue = 'true';

🔐 Zentrale Regeln:
• Verwenden Sie eigene, schwachprivilegierte Logins auf dem Zielserver – niemals `sa` oder ein Domain‑Admin‑Konto.
• Aktivieren Sie `RPC Out` nur, wenn Sie tatsächlich gespeicherte Prozeduren aufrufen müssen.
• Schalten Sie die MSDTC‑Unterstützung nur in verteilten Transaktionen an, sonst bleiben Sie im lokalen Transaktionsmodus.
• Testen Sie die Verbindung mit `sp_testlinkedserver`, bevor Sie darauf zugreifen.

🔧 6. Fehlerbehandlung & Werkzeuge

TROUBLESHOOTING

Netzwerk‑ oder Berechtigungsfehler sind keine Seltenheit. Die folgenden Abfragen helfen bei der Diagnose.

-- Verfügbarkeit des Linked Servers testen
sp_testlinkedserver 'REMOTE_SRV';

-- Liste aller vorhandenen Linked Server + Eigenschaften
SELECT s.name, s.product, s.provider, s.data_source, s.is_linked
FROM sys.servers s
WHERE s.is_linked = 1;

-- Fehler beim Join / OpenQuery: häufig `SET FMTONLY OFF` vergessen
SET FMTONLY OFF;
SELECT * FROM OPENQUERY(REMOTE_SRV, 'SET NOCOUNT ON; EXEC dbo.usp_ComplexReport');

💡 Hinweis: Hat Ihr Query keinen Spaltenkopf oder der Provider meldet "The object has no columns", fügen Sie `SET FMTONLY OFF;` vor den auszuführenden Text ein. Dies teilt dem SQL Server mit, dass es die Ergebnismenge nicht vorab analysieren muss.