Upgrading dbachecks to Pester5, Sampler and teamwork - Jess Pomfret, Rob Sewell - PSConfEU 2023

The complete session title:
The rollercoaster of upgrading dbachecks to Pester v5 and Sampler and teamwork

Summary
We, Jess Pomfret and Rob Sewell, dive into DBA checks and confirm that most of the audience is already using it. We introduce DBHX, an open source PowerShell module that validates the configuration of SQL instances and allows customization based on individual needs. We emphasize the importance of proper configuration and express our disappointment when people don't take it seriously.

Next, we explain the process of upgrading DBHX and the challenges we faced due to its wide usage by clients. We assure the audience that we approached the upgrade process carefully to avoid disrupting the operations of major companies using DBHX. We discuss how we handled the upgrade process by using different commands based on the Pesta version being used. Consistent results were important to us, so we made sure the tests functioned the same way in both versions. We also mention that we will be making performance improvements and highlight the DBA Tools command called "get dbaspconfigure" that retrieves SQL Server configuration settings. We explain how we optimized the gathering of configuration information to reduce the number of requests made to SQL instances and improve efficiency.

In the next part of the conversation, we discuss the use of Jakob's profiler module to analyze and improve code. By using the profiler, we were able to identify the slowest lines of code and address any issues. We also found regression bugs within Pesta, which we reported and resolved. The value of open source modules is emphasized as they help improve code. We then talk about the journey of developing the open-source modules and the opportunity to rewrite and structure the code in a better way while maintaining backward compatibility. Bugs in the v4 code were discovered during the rewriting process. Additionally, we highlight the standardization of tests in the module and the benefits of making it easier for users to skip or exclude specific tests. The challenges of remote collaboration due to geographical distances and different work schedules are also discussed.

Moving on, we mention a demo in VS Code where we showcase the use of DBA Tools version 2 and the upgraded SMO version, which includes improvements and promotes encryption for connecting to SQL instances.

In the next part of the conversation, we discuss the conversion of tests from version 4 to version 5. We focus on the recovery model test and make some changes to the test block. We explain the concept of recovery models in SQL Server and why it's important to have the correct recovery mode. We document the checks with additional details and provide links to documentation or best practices. We optimize the check by building an object using the getAllDatabaseInfo function, which is a performance improvement in the new version of DBHX.

Shifting gears, we set up credentials for our development container and connect to SQL using authentication. We run the recovery model test and analyze the results. The tests run in both v4 and v5, with the new code running slightly slower in v5. However, as we add more checks, the overall performance improves. We investigate duplicate checks and slow code, making changes and committing them to the repository.

Next, we discuss the setup in our VS Code environment, including different extensions and settings. We recall an issue with the trim whitespace setting causing problems for one team member and how we resolved it by including it in the dev container JSON file for consistency. We emphasize the need for time management and prioritization in our spare time development, while also being careful not to get distracted. Automating…