This repo contains the scripts to create and populate the DBA Database to automatically gather information about your estate
- Install the DBA Database on your server
- Follow the Add Server to Auto Scripts Doc to add servers
- Copy the PowerShell scripts to a location that can be accessed by the server. You will need to alter each of them to add the Servername and log file location
- Create a credential and a proxy using Create Credential and proxy for Agent jobs.sql for an account with permissions on all of the servers that you need to monitor
- Create the agent jobs using the scripts provided - you will need to alter the Script location to the location you placed the powershell scripts
- The auto-install script requires you to download and add those scripts where the license requires this (Brent Ozar, Adam Mechanic, Ola Hallengren, Jared Zagelbaum)
- The script location needs to be updated in the [DBADatabase].[dbo].[ScriptList] table
- You can add extra scripts for your own environment using - LOAD - Script Data Load.sql and adding a new code block to the Auto Update PS job steps.ps1
- You can set which servers get which scripts using the - LOAD - Update the Needs Update Flag for a server.sql or - LOAD - Update the Needs Update for a script.sql scripts although more granular targetting is recommended. The auto script job will then install them.
- All agent jobs should show success when run but you MUST check (or scrape automatically) the errors in the log files I do this via an agent job running some Powershell and a SSRS report (I Will add this soon)
- There are a number of scripts for displaying information
- - INFO - All info for a Server.sql shows all of the information in the DBA Database for a single server
- - INFO - Query For Needs update =1.sql Shows the servers adn scripts you have set to be updated next time the Auto Script install job runs
- - INFO - Various scripts to get information.sql has other generic queries
- - INFO - Estate Detailed Information.sql Gives more detailed information about an estate, number of servers, databases, sizes, versions, editions, locations, no full backups etc
- The PowerBi Reports will need Power Bi Desktop a free download from (https://powerbi.microsoft.com/en-us/desktop/) You will need to alter each of the queries to use the server you have the DBA Database on
-
Populating My DBA Database for Power Bi with PowerShell – Server Info
-
Populating My DBA Database for Power Bi with PowerShell – SQL Info
-
Populating My DBA Database for Power Bi with PowerShell – Databases
-
PSConfEU - (https://www.youtube.com/watch?v=9BKlrOjMWXk&index=5&list=PLIg9rQe6gY0pHCeB9WpCkyI8_27sC94ZT)
-
PSConfEU - (https://www.youtube.com/watch?v=KIsPXCeIlJw)
-
SQL Relay - (https://www.youtube.com/watch?v=9BKlrOjMWXk&index=5&list=PLIg9rQe6gY0pHCeB9WpCkyI8_27sC94ZT)