Skip to content

Latest commit

 

History

History
133 lines (104 loc) · 4.29 KB

SQLServer.md

File metadata and controls

133 lines (104 loc) · 4.29 KB
Raw

SQL Server

WAL-G facilitates SQLServer backups by emulating Azure Blob storage, allowing You to backup database to URL with standard BACKUP/RESTORE commands.

Backup to URL peculiarities

SQLServer requires URL to look like https://backup.local/folder/...

Note:

  • domain name required, no plain IP address supported
  • default https port (443) required, no custom port supported
  • valid HTTPS ceritficate required
  • SQLServer credential for URL https://backup.local/folder/ should exist

So you need to:

  • create fake DNS record '127.0.0.5 backup.local' in C:\Windows\System32\Drivers\etc\hosts
  • create and import self-signed certificate for backup.local domain
  • create SQLServer login, able to backup/restore any desired database
  • create SQLServer credential for URL https://backup.local/folder/
CREATE CREDENTIAL [https://backup.local/folder]
WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = 'does_not_matter'

Configuration

Your wal-g.yaml config for SQLServer than may look like

WALG_FILE_PREFIX: "C:/backup"
SQLSERVER_BLOB_CERT_FILE: "C:/Path/To/cert.pem"
SQLSERVER_BLOB_KEY_FILE:  "C:/Path/To/key.pem"
SQLSERVER_BLOB_LOCK_FILE: "C:/ProgramData/wal-g.lock"
SQLSERVER_BLOB_HOSTNAME:  "backup.local"
SQLSERVER_CONNECTION_STRING: "sqlserver://backupuser:backuppass1!@localhost:1433/instance"

WALG_UPLOAD_CONCURRENCY:   8  # how many block upload requests handle concurrently
WALG_DOWNLOAD_CONCURRENCY: 8  # how many block read requests handle concurrently

Of course, you may use any wal-g storage instead of FILE

You also need some configuration in SQLServer for wal-g to connect it.

CREATE LOGIN [backupuser] WITH PASSWORD = 'backuppass1!';
ALTER SERVER ROLE [sysadmin] ADD MEMBER [backupuser];
CREATE CREDENTIAL [https://backup.local/basebackups_005]
WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = 'does_not_matter';
CREATE CREDENTIAL [https://backup.local/wal_005]
WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = 'does_not_matter';

S3 Configuration

SQLServer backups/restores database by 4MB blocks (MAXTRANSFERSIZE). As we upload every block as a separate file to S3, it makes sense to set S3 part size to the smalles possible value (5MB) to prevent overuse of memory:

WALG_S3_MAX_PART_SIZE: 5242880 # 5MB

Usage

WAL-G SQLServer extension currently supports these commands:

proxy

wal-g proxy

Starts Azure Blob emulator on host SQLSERVER_BLOB_HOSTNAME, port 443. Proxy runs in foreground, until Ctrl+C pressed. While proxy is running, you are able to any BACKUP/RESTORE TO/FROM URL commands in SQLServer. Proxy intended for debug/manual backups only, it rely on user to maintain proper backups folder structure. For simple backup/restore process please consider using backup-push and backup-restore commands.

backup-push

wal-g backup-push
wal-g backup-push -d db1 -d db2
wal-g backup-push -d ALL

Backups serveral databases to the backup. You can specify which databases to backup via -d flag. You can backup all (including system) databases using -d ALL flag. By default it will backup all non-system databases.

backup-restore

wal-g backup-restore backup_name
wal-g backup-restore LATEST
wal-g backup-restore backup_name -d db1
wal-g backup-restore backup_name -d db1 -n
wal-g backup-restore backup_name -d db1_copy -f db1

Restores several databases from backup. You can specify particular backup_name or use LATEST alias for the last backup. You can specify which databases to restore via -d flag. You can restore all (including system) databases using -d ALL flag. You can restore database with new name (create copy of database) using flag -f (--from) By default it will restore all non-system databases found in backup.

backup-list

wal-g backup-list

delete

wal-g delete retain 3
wal-g delete before backup_name
wal-g delete everything

Proxy as Service

By default any wal-g command, like backup-push, runs proxy in background for the duration of the command. Alternatively wal-g commands may use proxy started as system service. In order to do this you need to start wal-g proxy as system service (or daemon on linux) and add following config options:

SQLSERVER_REUSE_PROXY: True