A Fine Slice Of SQL Server
After a lengthy period in Public Preview it seems, the boffins at Microsoft have finally pushed Elastic Jobs for SQL Azure DB to general availability. Hooray!
But what are Elastic Jobs? And why would I want to use them in SQL Azure DB?
That’s one of the things you’ll learn in this post.
Comments closedAaron Bertrand makes use of an ordering:
In my previous post, I showed how to borrow a snake draft concept from fantasy football, or a packing technique from the shipping industry, to distribute different portions of a workload to run in parallel. In the previous example, we determined a distribution order for databases based on size – though you can rank by literally any attribute (or combination of attributes). Once we’ve determined how to build out this order, we may want to store that data somewhere because, sometimes, the source of that data is not directly accessible.
Read on for tips on storing the results in a table, querying the results, and using them to drive SQL Agent jobs.
Comments closedSrini Acharya makes an announcement:
Elastic Jobs is a fully integrated Azure SQL database service that allows you to automate and manage administrative tasks across multiple SQL databases in a secure, scalable way. It can run one or more T-SQL job scripts in parallel using Azure portal, PowerShell, REST, or T-SQL APIs. Jobs can be run on a schedule or on-demand, targeting any tier of Azure SQL Database. Job target can include all databases in a server, in an elastic pool, across multiple servers and even databases across different subscriptions and geo regions on Azure. Servers and pools are dynamically enumerated at runtime, so jobs run against all databases that exist in the target group at the time of execution.
If you’ve held off on Azure SQL DB because of a lack of the SQL Agent, take a look at this option.
Comments closedAaron Bertrand hits on an annoyance of mine:
If you haven’t migrated your workloads to a managed database platform yet, you’re probably still relying on SQL Server Agent for various maintenance and other scheduled tasks. Most of the time, these processes just work. But when it’s time to troubleshoot, it can be cumbersome to get to the root of some problems. In this post, I’ll share some ideas to help you minimize the level of annoyance and tedium when you have to figure out what went wrong with the execution of a job.
Recently, I was investigating a job that ran a stored procedure against multiple databases in sequence, and ultimately failed. Here was the output of the job step as seen in Management Studio’s View Job History dialog:
Read on for a few tips to help with SQL Agent job logging.
Comments closedWarwick Rudd ponies up the cash:
With the above limitations, this product can provide all of the functionality for your application to operate as required. Because there is no SQL Agent service in this edition this makes it more difficult for you to implement scheduled database maintenance. We are not going to discuss ways to implement database maintenance on an Express edition of SQL Server in this post.
But at some point in time you may find yourself in the position that you need to perform an upgrade of editions. This may or may not include and upgrade of versions. An upgrade to the version can be achieved and can bring benefits of its own. Normally, I would not be writing about and recommending performing in-place upgrades. However, sometimes due to restrictions of vendor applications it is a requirement to perform an in-place upgrade.
Read on to see what you’d need to do, knowing that Express Edition does not come with a SQL Server Agent and does not perform the appropriate setup for one.
Comments closedTom Collins answers a question:
Question: I have a SQL Agent job with a Last modified date of : 1/11/23. What activity causes the last modified date to occur ? And also is there a way to find out the change?
Read on for the answer to these two questions. Also check out Michael Bourgon’s comment for additional context around schedules.
Comments closedI’ve been experimenting with PowerShell and SQL Server recently and noticed how missing the “Alter” step when altering SQL Server jobs with PowerShell may cause some confusion. Let’s walk through the process of disabling SQL Server jobs using PowerShell.
Read on for that process.
Comments closedAnthony Norwood replaces on-prem SQL Agent jobs:
Both SQL Server on Azure VM and Azure SQL Managed Instance provide you with SQL Server Agent and therefore the capability to run scheduled tasks against your databases, so when we’re talking about being able to run jobs we’re only considering Azure SQL Database as needing guidance – some of the suggestions in the following paragraphs can also apply to all these options of SQL Server, but perhaps not as necessary.
We’re going to provide you with four options for how you might be able to still run your favourite SQL Agent Jobs against an Azure SQL Database, each of which come with their own advantages and disadvantages – one not mentioned is Data Factory, sometimes referred to as SSIS in the cloud, and this is because we’re trying to focus on some options that may be more comfortable to people who have never built an SSIS package before.
Read on for the four options Anthony has for us.
Comments closedKenneth Fisher goes back in time:
The defaults for saving SQL Agent Job history are ok (at best), so you should probably check and update them if needed. Sadly, if you are using a Managed Instance this isn’t an option.
SQL Managed Instance currently doesn’t allow you to change any SQL Agent properties because they are stored in the underlying registry values.
That’s a real kick in the pants. Still, Kenneth shows us (via Jovan Popovic) a workaround to store the job history someplace else.
Comments closedSean Gallardy performs some troubleshooting:
I was asked if I knew any reason why SQL Agent would be using “a bunch” of memory and more cpu than normal. You and I, reader, now have the same information to go on. What do you do? Think about for a minute or two if you want before reading on. I’m not saying the way I did it was the way to do it or the only way, but I gave an action plan for data capture and once data was in hand, was solved in a few minutes. There are all sorts of things that can cause this in a program, but Agent typically doesn’t use a whole lot, unless it’s executing many concurrent T-SQL jobs (which, it really shouldn’t be, get enterprise level scheduling) and even then, the memory should deallocate.
My first inclination turned out to be right: I figured it had to do with a job running. The specifics, that was something I wasn’t sure about, but Sean takes us through the troubleshooting process.
Comments closed