AS a SQL Server DBA, your core functions are to automate every task. If you have to do something once, You know you will end up doing it again, either on the same server again later, or against all servers.
The first step: Treat your environment as if it was a set base operation. That’s right, every server should be treated as if it were a row in a table. Distinct, but they all fit a known pattern. Treating your SQL servers that way allows you to fix things across your entire environment, all at once, instead of RBAR style (Row-By-Agonizing-Row).
If you do not automate your tasks, you relinquish control, and turn yourself into a firefighter DBA, instead of a proactive DBA. You run from problem to problem fighting one issue at a time. Looking at things in a bigger picture, and resolving them all at the same time requires you to bullet proof your scripts for multiple executions, and add CASE statements for little things like variations in SQL Server Versions, but it is well worth the effort.
For On Prem servers, (or at least servers I can access with AD domain credentials )I have created a data driven SSIS package that I combine with Central Management Servers.
I am huge on Central Management Servers; it should be the go to repository for your list of servers, whether SQL or other not. That fact that it can be instantly shared with all the other members of your team is a huge benefit.
For this specific purpose I’ve organized a small portion of my CMS(Central Management Servers) into multiple sets. that contain each of my layers of servers:
- PRD – Production,
- DEV -Development
- QA -Quality Assurance
- STG – Stage
- UAT – User Acceptance Testing
or subsets of that list, depending on my environment so it makes sense to the business users.
with that list in place,