DOCSEARCHbyAlgolia
Submit Docs

SqlServerDsc Documentation

repository·main·Indexed 18 days ago

https://github.com/dsccommunity/sqlserverdsc

A PowerShell Desired State Configuration (DSC) module for the automated deployment and configuration of Microsoft SQL Server instances. It includes resources for managing Always On Availability Groups (SqlAG, SqlAGDatabase, SqlAGListener, SqlAGReplica), SQL Server Agent (SqlAgentFailsafe, SqlAgentOperator), database mail (SqlDatabaseMail), configuration options (SqlConfiguration), and other instance settings like default file locations and object permissions.

Tokens
29K
Snippets
48
Records
131
Agent score
64%

What's inside SqlServerDsc

  1. Overview of SqlServerDsc resources

    main

    The SqlServerDsc module provides Desired State Configuration (DSC) resources and commands specifically designed for the automated deployment and configuration of:

    • Microsoft SQL Server
    • SQL Server Reporting Services (SSRS)
    • Power BI Report Server

    You can use these resources within your DSC configurations to ensure these SQL-related services are installed and configured according to your defined state.

  2. Use the SqlDatabaseUser DSC resource to manage database users

    main

    The SqlDatabaseUser resource manages the creation and mapping of SQL Server database users.

    Key Capabilities:

    • Create database users with or without an associated login.
    • Map a database user to a certificate or an asymmetric key.
    • Re-map an existing SQL login to a database user.
    • Handle non-updatable databases: If the targeted database is not updatable, the resource returns true (indicating it is in the desired state).

    Limitations:

  3. Use the SqlAgentOperator DSC resource

    main

    The SqlAgentOperator resource allows you to manage SQL Server Agent Operators via Desired State Configuration (DSC). You can use it to:

    • Add new SQL Agent Operators.
    • Remove existing SQL Agent Operators.
    • Update the email address of an existing operator.

    Requirements

    To use this resource, the target machine must meet the following criteria:

    • Operating System: Windows Server 2012 or later.
    • Database Engine: SQL Server Database Engine 2012 or later.

  4. Manage SQL Server Configuration Options with SqlConfiguration

    main

    The SqlConfiguration DSC resource is used to manage SQL Server Configuration Options on a specific SQL Server instance.

    To identify which configuration option names are available on your specific instance, you can use the Connect-SqlDscDatabaseEngine and Get-SqlDscConfigurationOption commands from the SqlDsc module.

    $serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'SQL2022'
$serverObject | Get-SqlDscConfigurationOption | ft

  6. Use the SqlMemory DSC resource to manage SQL Server memory

    main

    The SqlMemory DSC resource manages the minimum and maximum server memory configuration options for a SQL Server instance. It sets these values in MB for the buffer pool.

    Default Settings:

    • Minimum server memory: 0 MB
    • Maximum server memory: 2147483647 MB
    IMPORTANT

    Do not configure these same settings using the SqlConfiguration resource. Using both SqlMemory and SqlConfiguration for these specific options will cause conflicts. SqlMemory is preferred if you require dynamic memory allocation logic.

  10. Use the SqlAGDatabase DSC resource to manage Availability Group databases

    main

    The SqlAGDatabase resource allows you to add or remove databases from a specific SQL Server Availability Group (AG).

    Automatic Seeding Behavior:

    • If a replica is configured with Automatic seeding set to Automatic, the resource will not use a restore operation for that replica.
    • If all replicas in the availability group are configured for automatic seeding, no backup is performed, unless the database has never been backed up before.