Managing Microsoft SQL Server Resources from the Command Line (LKCLI)

Microsoft SQL Server resources protect a SQL Server instance by managing the SQL Server service during failover and failback operations. When the primary server fails, LifeKeeper brings the SQL Server service in-service on a standby server.

Resource Type: database/sqlapp
Aliases: sqlapp

Preconditions

• Microsoft SQL Server must be installed on the primary server.
• The SQL Server instance must exist and be reachable with the provided credentials.
• For a named instance, the instance must be configured and accessible by the specified instance name.
• If --ip-resource is specified, the referenced LifeKeeper IP resource must already exist on the server.
• If --relocation-volume is specified, the referenced shared/replicated volume must already exist on the server and be protected by a LifeKeeper resource.
• The Microsoft SQL Server user provided for monitoring (via the --sql-username flag) must have the “sysadmin” fixed server role (SA privileges).

Subcommands

Microsoft SQL Server resources are managed by commands of the form:

lkcli resource database/sqlapp <subcommand> [flags]

create

lkcli resource database/sqlapp create \
  --instance=<string> --sql-username=<string> --sql-password=<string> [flags]

Note one --protected-service-list: At creation time, supply service display names as shown in the Windows Services MMC snap-in (e.g., “SQL Server Agent (INSTANCE1)“). When reading the property back via get-property or updating via set-property, use short service names instead.

extend

lkcli resource database/sqlapp extend \
  --primary-sys=<string> --primary-tag=<string> [flags]

get-property / set-property

lkcli resource database/sqlapp get-property --tag=<string> --property=<string>
lkcli resource database/sqlapp set-property --tag=<string> --property=<string> --value=<string>

Additional set-property flag:

Properties

Example Usage

# Create a SQL Server resource for the default instance
lkcli resource database/sqlapp create \
  --host=server1 \
  --instance=MSSQLSERVER \
  --sql-username=sa \
  --sql-password=MyPassword! \
  --tag=sqlapp.MSSQLSERVER

# Create with additional protected services
lkcli resource sqlapp create \
  --host=server1 \
  --instance=INSTANCE1 \
  --sql-username=sa \
  --sql-password=MyPassword! \
  --protected-service-list="SQL Browser,SQL Server Agent (INSTANCE1)" \
  --tag=sqlapp.INSTANCE1

# Extend the resource to server2
lkcli resource database/sqlapp extend \
  --primary-sys=server1 \
  --primary-tag=sqlapp.MSSQLSERVER \
  --standby-sys=server2

# Get the current switchback setting
lkcli resource database/sqlapp get-property \
  --host=server1 \
  --tag=sqlapp.MSSQLSERVER \
  --property=switchback

# Change switchback type
lkcli resource sqlapp set-property \
  --host=server1 \
  --tag=sqlapp.MSSQLSERVER \
  --property=switchback \
  --value=AUTOMATIC