SQL Agent Jobs
Server & Policy Management
Recovery & Backup
Azure Automation Service
The course is part of this learning path
Whether you’re running SQL Server on-premise, in a VM, on a managed instance, or using Azure SQL, there is maintenance to be performed and other tasks that need to be executed on a regular basis. In this course, we will look at the options available to you in these various environments for automating maintenance and regularly repeated tasks.
SQL Server has a long history of task automation through SQL Server agent, not only will we see how to use it but also how it’s been adapted to carry out maintenance tasks, and how similar functionality has made it into Azure services. From mundane single-database scheduled backups through to task and performance automation across multiple servers, this course looks at on-premise and in-cloud options.
If you have any feedback relating to this course, feel free to contact us at firstname.lastname@example.org.
- Maintain your databases and data through the use of maintenance plans and the SQL Server Maintenance Wizard
- Use SQL Agent Jobs to automate maintenance tasks
- Automate maintenance across multiple SQL servers
- Set up rules to enforce policies
- Back up your SQL Server instances using Azure Recovery Services Vault
- Learn about the Azure Automation Service and how it can be used
- Use Elastic Jobs to schedule tasks within the context of databases
- Manage database performance with Automated Tuning
- Manage database updates with Automated Patching
- Data engineers
- Database engineers
- IT professionals or anyone looking to learn about automating tasks in Azure SQL and SQL server
To get the most out of this course, you should have experience with SQL Server Management Studio, be familiar with reading and writing SQL, and have an understanding of basic database architecture and administration tasks, like indexes and backups.
Course Related SQL Scripts
You can create a Recovery Services vault by going to the search bar in the portal, type Recovery Services vault, and then click add new vault. Select or create a resource group, give the vault a name, select a region and create the vault. To create a backup policy, go into your Recovery Services vault and click the Add backup button at the top of the overview page, the plus sign with the word backup.
In backup goal, you want to select SQL Server in Azure VM from the what you want to backup dropdown. You can then click Start Discovery to find the virtual machines in your resource group. I've got two virtual machines, and I'm going to select both of them, then click discover DBS at the bottom of the page. This doesn't happen instantaneously, but if you go into view details, I can see the discovery process in action.
Now that the database has been found on the virtual machines, I'll go back to the previous page and click Configure backup. This will just take a few moments for my VMs and databases to appear in my resources for backup.
Okay, now we can see the two MS SQL Server instances on the two VMs, and if I click on the greater than sign to the left of the server, it will expand to show the databases on each virtual machine. I can select both of the VMs which will in turn select all the databases. Or I can just select individual databases that I would like to back up.
Alternatively, I can use the auto protect dropdown and set it to one. This will apply the backup policy to every database that is currently on the server and any databases that will be on the server. So any databases that will be created in the future. This feature is quite handy if you're in a dynamic development or test environment where databases are being created and deleted frequently, so you don't have to worry about manually adding them to the backup schedule.
Anyway, I'll turn auto protect off and just backup the databases I've created. So back to the policy where I can use a graphical interface not dissimilar to that in the maintenance plan schedule configuration. Although here I can set schedules for full backups, differential and log backups, as well as specify the retention period for each backup type. I can also specify the timezone for the context of the backup, I can say for my backup to be compressed.
Anyway, I will abandon my custom backup and enable the hourly log backup that was already present. Clicking on backup items under protect items, we can see I have four SQL and Azure VM items. Clicking on the VM items, we can drill down into the databases, and we can see that they're all waiting for an initial backup. I can go into backup policies under manage and modify the hourly log backup policy. I'll change the full backup to weekly, and add a daily differential backup. I'll also change the log backup frequency from once an hour to once every 15 minutes and only retain the log backups for seven days.
As I'm doing a full backup once a week, I'll change my differential retention period to 14 days. Okay, going back to my backup items, and we can see the backup status there is healthy. I would like to say that a week has passed and all the backups have been backed up according to the policy, but I cannot tell a lie and I have manually forced the backups with the Backup Now function available when you click on the ellipsis context menu.
From the same menu, you can do a restore to another location like an alternative server or override the current database. And as I said previously, you can do a point in time restore using day time picker controls based on your full differential and log backups. But as I only have one forced full backup for each database, my options very limited.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.