Contents
What should I automate in SQL Server?
The following are common repetitive tasks that should be automated.
- Install SQL Server.
- SQL Server Configurations.
- Install Service pack, Hotfix / cumulative updates.
- Server Maintenance.
- Database Maintenance.
- Alerts and Notifications.
- MonitoringMonitoring Blocking more than 15 mins.
- High Availability.
How do I automate a SQL script in SQL Server?
Three easy steps:
- Use the Alias field to name your Task.
- Select Run SQL Script With Date Parameters in the Action Type field.
- Use the Target Database Connection field to select the Database Connection that you created.
- Add the SQL query that you want to automate to the SQL Script field.
How to automate patching of SQL Server 2012?
You can use Server Automation Tasks in an Automation Plan to automate patching of SQL Server 2012 or 2014 in a Microsoft® Windows® cluster, with minimal impact on cluster availability. The automated patching is done in phases, one phase per node, and at least one node remains operational at all times during the patching.
What should I do when SQL Server is patched?
During patching and/or after patching SQL server needs to be restarted along with a reboot of windows machine – depending on the files that are locked during patching. Also, dbatools has Update-DbaInstance.ps1 that can perform installation of SQL Server Service Packs and Cumulative Updates on local and remote servers.
Is there a way to automate SQL cu patching?
For reasons that are not mine to address, we are looking for alternatives to WSUS and SCCM to automate SQL CU patching ( particularly on SQL 2017+) There are some neat Powershell solutions like this one by Adam Bertram which I found via the related post Automate the patching of SQL Severs on Windows
How to patch SQL Server in Windows cluster?
Note that this cluster patching solution patches SQL Server and not the underlying Microsoft® Windows® operating system. The Windows cluster scripts must be installed on each node in the cluster. Use Server Automation Task ID 110 to download the scripts to each node in the cluster.