SQL DBA with A Beard

He's a SQL DBA and he has a beard

Menu

Skip to content
  • Home
  • About Me
  • dbatools in a Month of Lunches

Monthly Archives: June 2015

Installing and upgrading default scripts automation – part one – Introduction

Posted on June 8, 2015 by RobSewell

First I must say thank you to all of the wonderful people who have put time and effort into providing free tools and scripts to enable not only myself but all SQL DBAs to ease their work. For this series I especially thank

  • Brent Ozar – w|t
  • Ola Hallengren – w
  • Adam Mechanic – b|t
  • Jared Zagelbaum – b|t

The aim of this series is to share the methodology and the scripts that I have used to resolve this issue.

How can I automate the deployment and update of backup, integrity ,index maintenance and troubleshooting scripts as well as other default required scripts to all of the instances under my control and easily target any instances by OS version, SQL version, Environment, System or any other configuration of my choosing

This is Part 1 – Introduction I will link to the further posts here as I write them

So the scenario that lead to this series is a varied estate of SQL servers and instances where I wanted an automated method of deploying the scripts and being able to target different servers. It needed to be easy to maintain, easy to use and easy to alter. I wanted to be able to update all of the scripts easily when required. I also wanted to automate the install of new instances and ensure that I could ensure that all required scripts were installed and documented.

The method of doing this that I chose is just that – Its the way that I chose, whether it will work for you and your estate I don’t know but I hope you will find it of benefit. Of course you must test it first. Ensure that you understand what is happening, what it is doing and that that is what you want it to do. If you implement this methodology of installing scripts you will easily be able to start by targeting your Development Server and then gradually rolling it out to any other environments’ whilst always making sure that you test, monitor and validate prior to moving to the next.

I decided that I needed to have a DBA Database to start with. The role of the DBA Database is to be the single source of truth for the instances that are under my control, a source for the location of the scripts that I need to deploy and a place to hold the information that I gather from the servers. It is from this database that I will be able to target the instances as required and set the flags to update the scripts as and when I need to

agentjob

On that instance I also chose to put the SQL Agent Job that will deploy all of the scripts. This is an important point. The account that you use to run that job whether it is the Agent Service Account or a proxy account will need to have privileges on every instance that you target. It will need to be able to run every script that you wish to target your servers. The privileges it requires are defined by the scripts that you want to run. How that is set up is unique to your environment and your system. I will only say that all errors are logged to the log files and will enable you to resolve these issues. You should always use the principle of least privilege required to get the job done. Domain and Sys Admin are not really the best answer here 🙂

I also created 2 further Agent Jobs to gather Windows and SQL Information from the servers. These jobs target all the instances and servers in the DBA Database and gather information centrally about Windows and SQL configurations making it easy to provide that information to any other teams that require it. I am always looking for methods to reduce the workload on DBAs and enabling people (using the correct permissions) to gather the information that they require by self-service is very beneficial

Documentation and logging about the scripts are provided by the log files stored as text files to troubleshoot the script and also documented in the Change log table in a DBA database on each instance which I blogged about previously here

The last thing was the script which needed to be modular and easy to add to and amend.

Throughout this series of blog posts I will share and expand on the methods I used to do this. If you have any questions at any point please feel free to ask either by commenting on the post or by contacting me using the methods on my About Me page

Share this:

  • Email
  • Facebook
  • Twitter
  • LinkedIn
  • Print
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram
  • WhatsApp
  • Skype

Like this:

Like Loading...
Posted in Meta Data Script Install, PowerShell, SQL Server / Tagged Agent Jobs, Auto Script Install, automate, automation, documentation, Permissions, Powershell, script, SQL Server / 1 Comment

This blog has moved
blog.robsewell.com

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 5,861 other subscribers

dbatools in a Month Of Lunches

50% off the book until September 8!
Use code mlsewell at manning.com

Follow me on Twitter

My Tweets

Recent Posts

  • #tsql2sday #130 – Automate your stress away – Getting more SSIS Agent Job information
  • Creating Azure SQL Database AAD Contained Database Users with an SPN using PowerShell, Secrets Management, Azure Key Vault, and dbatools
  • Notifying a Teams Channel of a SQL Agent Job result
  • Sending a SQL Agent Job results overview to a Microsoft Teams Channel
  • Using Secret Management module to run SSMS, VS Code and Azure Data Studio as another user

Calendar

June 2015
M T W T F S S
1234567
891011121314
15161718192021
22232425262728
2930  
« May   Jul »

Recent Posts

  • #tsql2sday #130 – Automate your stress away – Getting more SSIS Agent Job information
  • Creating Azure SQL Database AAD Contained Database Users with an SPN using PowerShell, Secrets Management, Azure Key Vault, and dbatools
  • Notifying a Teams Channel of a SQL Agent Job result
  • Sending a SQL Agent Job results overview to a Microsoft Teams Channel
  • Using Secret Management module to run SSMS, VS Code and Azure Data Studio as another user

Menu

  • Twitter
  • LinkedIn

Top Posts & Pages

  • Automatically updating the version number in a PowerShell Module - How I do regex
  • dbatools with SQL on Docker and running SQL queries
  • Getting SQL Server File Sizes and Space Used with dbatools
  • Using Plaster To Create a New PowerShell Module
  • Altering a Job Step on Hundreds of SQL Servers with PowerShell
  • VS Code - Automatic Dynamic PowerShell Help
  • Using Pester with dbatools Test-DbaLastBackup
  • Adding a Folder of Scripts to GitHub
  • Using the new SQLServer Powershell module to get SQL Agent Job Information
  • Making Start-Demo work with multi-line commands without a backtick

Archives

Powered by WordPress.com.
 

Loading Comments...
 

    %d bloggers like this: