azure.sql-database

SQL Server Database Resource

The azure.sql-database resource is a child resource of the SQL Server resource, and the SQL Server parent id is available as the c7n:parent-id property.

example:

Finds all SQL Servers Database in the subscription.

policies:
    - name: find-all-sql-databases
      resource: azure.sql-database

Filters

• advisor-recommendation

• cost

• data-encryption

• data-masking-policy

• diagnostic-settings

• event

• list-item

• long-term-backup-retention-policy

• metric

• offhour

• onhour

• parent

• policy-compliant

• reduce

• resource-lock

• short-term-backup-retention-policy

• transparent-data-encryption

• value

advisor-recommendation

Filter resources by Azure Advisor Recommendations

Select all categories with ‘all’

example:

policies:
  - name: disks-with-cost-recommendations
    resource: azure.disk
    filters:
      - type: advisor-recommendation
        category: Cost
        key: '[].properties.recommendationTypeId'
        op: contains
        value: '48eda464-1485-4dcf-a674-d0905df5054a'

properties:
  category:
    type: string
  default:
    type: object
  key:
    type: string
  op:
    enum:
    - eq
    - equal
    - ne
    - not-equal
    - gt
    - greater-than
    - ge
    - gte
    - le
    - lte
    - lt
    - less-than
    - glob
    - regex
    - regex-case
    - in
    - ni
    - not-in
    - contains
    - difference
    - intersect
    - mod
  type:
    enum:
    - advisor-recommendation
  value:
    oneOf:
    - type: array
    - type: string
    - type: boolean
    - type: number
    - type: 'null'
  value_from:
    additionalProperties: 'False'
    properties:
      expr:
        oneOf:
        - type: integer
        - type: string
      format:
        enum:
        - csv
        - json
        - txt
        - csv2dict
      headers:
        patternProperties:
          ? ''
          : type: string
        type: object
      query:
        type: string
      url:
        type: string
    required:
    - url
    type: object
  value_path:
    type: string
  value_regex:
    type: string
  value_type:
    enum:
    - age
    - integer
    - expiration
    - normalize
    - size
    - cidr
    - cidr_size
    - swap
    - resource_count
    - expr
    - unique_size
    - date
    - version
    - float
required:
- category
- type

data-encryption

Filter by the current Transparent Data Encryption configuration for this database.

example:

Find SQL databases with TDE disabled

policies:
  - name: sql-database-no-tde
    resource: azure.sql-database
    filters:
      - type: transparent-data-encryption
        enabled: false

properties:
  enabled:
    type: boolean
  type:
    enum:
    - data-encryption
required:
- type

data-masking-policy

Filter by the current data masking policy configuration for this database.

This filter will exclude the master database because data masking can not be configured on it.

example:

Find SQL databases with data masking disabled

policies:
  - name: sql-database-masking
    resource: azure.sql-database
    filters:
      - type: data-masking-policy
        enabled: false

properties:
  enabled:
    type: boolean
  type:
    enum:
    - data-masking-policy
required:
- type
- enabled
- type

long-term-backup-retention-policy

Filter SQL Databases on the length of their long term backup retention policies.

There are 3 backup types for a sql database: weekly, monthly, and yearly. And, each of these backups has a retention period that can specified in units of days, weeks, months, or years.

example:

Find all SQL Databases with weekly backup retentions longer than 1 month.

policies:
  - name: long-term-backup-retention-policy
    resource: azure.sqldatabase
    filters:
      - type: long-term-backup-retention-policy
        backup-type: weekly
        op: gt
        retention-period: 1
        retention-period-units: months

properties:
  backup-type:
    enum:
    - weekly
    - monthly
    - yearly
  op:
    enum:
    - eq
    - equal
    - ne
    - not-equal
    - gt
    - greater-than
    - ge
    - gte
    - le
    - lte
    - lt
    - less-than
  retention-period:
    type: number
  retention-period-units:
    enum:
    - day
    - days
    - week
    - weeks
    - month
    - months
    - year
    - years
  type:
    enum:
    - long-term-backup-retention-policy
    - long-term-backup-retention
required:
- backup-type
- retention-period
- retention-period-units
- type

short-term-backup-retention-policy

Filter SQL Databases on the length of their short term backup retention policies.

If the database has no backup retention policies, the database is treated as if it has a backup retention of zero days.

example:

Find all SQL Databases with a short term retention policy shorter than 2 weeks.

policies:
  - name: short-term-backup-retention-policy
    resource: azure.sqldatabase
    filters:
      - type: short-term-backup-retention-policy
        op: lt
        retention-period-days: 14

properties:
  op:
    enum:
    - eq
    - equal
    - ne
    - not-equal
    - gt
    - greater-than
    - ge
    - gte
    - le
    - lte
    - lt
    - less-than
  retention-period-days:
    type: number
  type:
    enum:
    - short-term-backup-retention-policy
    - short-term-backup-retention
required:
- retention-period-days
- type

transparent-data-encryption

Filter by the current Transparent Data Encryption configuration for this database.

example:

Find SQL databases with TDE disabled

policies:
  - name: sql-database-no-tde
    resource: azure.sql-database
    filters:
      - type: transparent-data-encryption
        enabled: false

properties:
  enabled:
    type: boolean
  type:
    enum:
    - transparent-data-encryption
required:
- type
- enabled
- type

Actions

• delete

• lock

• logic-app

• notify

• resize

• update-long-term-backup-retention-policy

• update-short-term-backup-retention-policy

• webhook

resize

Action to scale database. Required arguments: capacity in DTUs and tier (Basic, Standard or Premium). Max data size (in bytes) is optional.

example:

This policy will resize database to Premium tier with 500 DTU and set max data size to 750 GB

policies:
  - name: resize-db
    resource: azure.sqldatabase
    filters:
      - type: value
        key: name
        value: cctestdb
    actions:
      - type: resize
        tier: Premium
        capacity: 500
        max_size_bytes: 805306368000

properties:
  capacity:
    type: number
  max_size_bytes:
    type: number
  tier:
    enum:
    - Basic
    - Standard
    - Premium
  type:
    enum:
    - resize
required:
- capacity
- tier
- type

update-long-term-backup-retention-policy

Update the long term backup retention policy for a SQL Database.

There are 3 backup types for a sql database: weekly, monthly, and yearly. And, each of these backups has a retention period that can specified in units of days, weeks, months, or years.

example:

Enforce a 1 month maximum retention for weekly backups on all SQL Databases

policies:
  - name: update-long-term-backup-retention-policy
    resource: azure.sqldatabase
    filters:
      - type: long-term-backup-retention-policy
        backup-type: weekly
        op: gt
        retention-period: 1
        retention-period-units: months
    actions:
      - type: update-long-term-backup-retention-policy
        backup-type: weekly
        retention-period: 1
        retention-period-units: months

properties:
  backup-type:
    enum:
    - weekly
    - monthly
    - yearly
  retention-period:
    type: number
  retention-period-units:
    enum:
    - day
    - days
    - week
    - weeks
    - month
    - months
    - year
    - years
  type:
    enum:
    - update-long-term-backup-retention-policy
    - update-long-term-backup-retention
required:
- type

update-short-term-backup-retention-policy

Update the short term backup retention policy for a SQL Database.

example:

Update any SQL Database short term retentions to at least 7 days.

policies:
  - name: update-short-term-backup-retention-policy
    resource: azure.sqldatabase
    filters:
      - type: short-term-backup-retention-policy
        op: lt
        retention-period-days: 7
    actions:
      - type: update-short-term-backup-retention-policy
        retention-period-days: 7

properties:
  retention-period-days:
    type: number
  type:
    enum:
    - update-short-term-backup-retention-policy
    - update-short-term-backup-retention
required:
- type