Two years ago, I first dealt with Desired State Configuration (DSC) to create a demo for SQL Saturday #605 that constantly monitors and eventually corrects the configuration of the SQL server. Now I have another very nice 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 the 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 buildt 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) is 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 just 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 is explicitly created for the Windows Remote Management, in order to allow all connections within the private domain network, the public network is 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 actually receive the newly created policies automatically. But to make absolutely sure that this is the case, you can manually update the group policies once on the server itself.
Now, there should not 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. But 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 certain 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.
Björn works as a database administrator and Head of Competence for MS SQL and mySQL in Hamburg (Germany). He regularly participates in the PASS regional group meetings, events of the PASS such as SQLSaturday and SQLGrillen and he organizes the Azure Meetup group in Hamburg. He is interested in topics like SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.