Desired State Configuration – Simplify your SQL Server Administration with DSC

Two years ago, I first dealt with Desired State Configuration (DSC) to create a demo for SQL Saturday #605 that continually monitors and eventually corrects the configuration of the SQL server. Now I have another lovely customer project, where I have the choice between complete implementation in Powershell (via dbatools) or just a little DSC in connection with dbatools.

Tasks in this scenario

At a push on a button, two new SQL Servers should be installed and configured to run multiple databases in Basic Availability Groups. So I’ve started with expanding my Azure test environment and here is how I started my way how built that SQL Server environment in a fully automated way.

I will show you the following topics in the next few blog posts:

• Preparation of the server
• Installation of the SQL Server
• Configuration of the SQL Server
• Backup / Restore Automation
• Creation of the BAGs from the backups
• Activation and completion of the new server

Create preconditions for DSC

I’ll start here from with following considerations:

The new servers were installed and fully configured within the operating system, joined into a domain and had current Windows Management Framework installed, ideally Windows Remote Management (WinRM) already installed and activated… if not just install it before continuing.

Initially, I also wanted to join the server into the domain with Powershell, but I had a little bit trouble with that at the beginning (then done it manually) and want to show you a screenshot of my initial problem. 😉

Ok, so on… so I get rolled out and configured WinRM automatically on all target servers and opened the associated firewall ports… Google helped me a bit with the brainstorming, but my thoughts were already in the right direction = > Domain Group Policy (aka Group Policies).

To do this, create a group policy in the Group Policy Management of your domain so that you can build a proper and traceable structure.

1.) Set up service

Add a new service and configure it

Computer Configuration > Preferences > Control Panel Settings > Services

2.) Allow access to Windows Remote Shell

At least allow the service to access, should that not be enough, allow even the remote shell access.

Computer Configuration > Policies > Administrative Templates > Windows Components > Windows Remote Management (WinRM) > WinRM Services

Now continuing with the holes in the firewall

3.) Open firewall for winRM

Last step in the configuration so that all servers in the domain are configured identically using this group policy to be able to install a SQL Server over DSC (Desired State Configuration) later on.

Computer Configurations > Policies > Windows Settings > Security Settings > Windows Firewall and Advanced Security > Windows Firewall and Advanced Security 

Now create a new inbound rule, so that access from outside (here from the DomainController to the SQL server) are possible.

This rule explicitly created for the Windows Remote Management, to allow all connections within the private domain network, the public network not considered as a precaution.

Now you can roll out this new group policy on all servers and should then be able to configure and administer all servers using DSC.

4.) Update Group Policy

Now bring the new server into the domain and thus the server should receive the newly created policies automatically. However, to make sure that this is the case, you can manually update the group policies once on the server itself.

Now, there should not be anything in its way to configure the server with DSC, and you can start to create configurations on the central administration server, roll them out and see what’s happening. However, more about that in the next blog post. 😉
Quelle: How to Enable WinRM via Group Policy

Group Policy update due to errors in script execution

After I had finished the first post and researched and tested for further contributions as well as the customer project, I, unfortunately, had to conclude that in the later course of the script I am dependent on using WMI to determine specific values such as RAM and CPU. This is only possible if I can establish a connection from the central management server to the destination server. Unfortunately, there was a rule in the firewall of the server that allows inbound and outbound communication with the RPC server/service. So I had to make another adjustment to the group policy.