Microsoft SQL Server

The MSSQL integration allows Plakar Control Plane to back up and restore the Program Files folder used by Microsoft SQL Server on Windows. It is built on top of Plakar’s VSS integration. Plakar connects to the Windows host over SSH, requests a VSS snapshot of the volume containing the SQL Server installation directory, then copies the files from the snapshot over SFTP. Restoring requires the SQL Server services to be stopped first, since the destination files are locked while SQL Server is running.

Curretly no managed inventory has the capability of discoving Microsoft SQL Server resources. You need to set up a self-managed inventory before adding an MSSQL resource.

When using a self-managed inventory, you must register your resources manually or import them from a CSV file.

To add a Windows machine running SQL Server as a resource, use Database as the class. For the endpoint, use the IP address or hostname of the target machine. See resources documentation for more information on how to setup resources on a self-managed inventory.

flowchart TD
  subgraph Windows["Windows Server"]
    VSS["VSS Snapshot"]
    SQLDir["Microsoft SQL Server
Program Files"]
  end

  subgraph Plakar["Plakar Control Plane"]
    Source["MSSQL
Source app"]
    Backup["Backup process
Encrypt & deduplicate"]
  end

  Store["Kloset Store"]

  Source -->|"SSH: requests VSS snapshot"| VSS
  VSS --> SQLDir
  SQLDir -->|"SFTP: file transfer"| Source
  Source --> Backup
  Backup --> Store

flowchart TD
  Store["Kloset Store"]

  subgraph Plakar["Plakar Control Plane"]
    Destination["MSSQL
Destination app"]
    Restore["Restore process"]
  end

  subgraph Windows["Windows Server"]
    SQLDir["Microsoft SQL Server
Program Files"]
  end

  Store --> Restore
  Destination --> Restore

  Restore -->|"SSH: executes restore operations"| Destination
  Destination -->|"SFTP: transfers restored files"| SQLDir

The following settings are available when configuring both source and destination apps.

• Port: The SSH server port. Defaults to 22 if left empty.

• Root: The absolute path to the Windows drive and directory which should be backed up. Defaults to /C:/Program Files/Microsoft SQL Server.

• Ssh Private Key: To authenticate with the remote machine, PCP uses SSH key-based authentication. Generate a dedicated SSH keypair for PCP and add the public key to the remote machine’s authorized keys before configuring this field (see Setting up SSH access on the Windows host below), then copy the private key in full, including the header and footer lines, into this field:

  -----BEGIN OPENSSH PRIVATE KEY-----
  ...
  -----END OPENSSH PRIVATE KEY-----

• Username: Required. The name of an administrative user account to perform the backup or restore with, e.g. Administrator.

The mssql integration relies on OpenSSH Server being installed, running, and reachable on the Windows host before it can be used, with a dedicated SSH key authorized for the administrative account Plakar connects as.

See Setting Up OpenSSH Server on Windows for step-by-step instructions on installing OpenSSH Server, opening port 22, and authorizing an SSH key.

Restoring the Program Files folder requires SQL Server to be fully shut down, since its services and helper processes hold open file handles for the duration that they run. Before running a restore, stop the following services on the Windows host:

• SQL Server (MSSQLSERVER, or the named instance’s service): the main database engine.
• SQL Server Agent (SQLSERVERAGENT): scheduled job runner.
• SQL Server Browser (SQLBrowser): named instance discovery.
• SQL Server CEIP (SQLTELEMETRY): usage/telemetry reporting. Can be missed since it does not appear in SQL Server Configuration Manager’s default service list, but it holds files open and will block deletion or restore if left running.
• SQL Server VSS Writer (SQLWriter): used for VSS-based backups. Likewise omitted from Configuration Manager’s list; must be stopped separately.
• Any additional installed feature services that were configured, such as SQL Server Integration Services, SQL Full-text Filter Daemon Launcher, or SQL Server Analysis Services.

Stop-Service MSSQLSERVER, SQLSERVERAGENT, SQLBrowser, SQLTELEMETRY, SQLWriter -Force -ErrorAction SilentlyContinue

Confirm every SQL-related service is stopped before proceeding. This can be done either from PowerShell:

Get-Service | Where-Object {$_.Name -like "*VSS*" -or $_.DisplayName -like "*SQL*"}

or visually, using the Services app and confirm nothing SQL-related still shows Running.