December 1, 2016 at 11:35 pm
Comments posted to this topic are about the item First Class Jobs
December 2, 2016 at 1:13 am
Like the idea. If implemented, I would also like to see some sort of management system that gives a DBA simple way of getting an overall view of all jobs and their schedules in a SQL Server instance. If a job's schedules remain part of the job's definition (and are managed in the job's home database), a challenge may be enabling schedule creators to see what other schedules already exist in the same SQL Server instance so they can pro-actively avoid setting up new ones that conflict with existing ones outside their control.
Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.
December 2, 2016 at 2:31 am
I'm going to say this would be a major plus for us. Jobs are one of the toughest things to move when they have been embedded in a server for ages. I know they are scriptable but this often doesn't seem to help, and there are often quite a few for any given database setup.
December 2, 2016 at 3:26 am
A drawback of having the Jobs in the database would be, that - when you restore the database as _test, _dev or whatever, those jobs would be started and this could be problematic:
- when it are long running / resource intensive jobs (that will start at the same time as the job in the source database, since the schedule would be restored too)
- when they are exporting stuff / sending mails (-> outdated, invalid data; may overwrite exports from the productive system)...
- when you restore the database on another server, some directories / drives that are used in jobs may not exists
PS: regarding the overview, when which job will run: you can get a simple table with next runtime etc. in the SQL Server Agent (job activity monitor)
God is real, unless declared integer.
December 2, 2016 at 4:37 am
PS: regarding the overview, when which job will run: you can get a simple table with next runtime etc. in the SQL Server Agent (job activity monitor)
Sure, but what I'd really like to see is (for example) a whole week's worth or even a month's worth, not just the last and next instance for each job. In terms of planning, it's also useful to see the history of the previous week/month including job durations. At the moment, I resort to scripts for the odd occasion when I'm looking for this type of info, and such scripts are not guaranteed to work from one version of SQL Server to the next.
Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.
December 2, 2016 at 5:38 am
colin.frame (12/2/2016)
PS: regarding the overview, when which job will run: you can get a simple table with next runtime etc. in the SQL Server Agent (job activity monitor)
Sure, but what I'd really like to see is (for example) a whole week's worth or even a month's worth, not just the last and next instance for each job. In terms of planning, it's also useful to see the history of the previous week/month including job durations. At the moment, I resort to scripts for the odd occasion when I'm looking for this type of info, and such scripts are not guaranteed to work from one version of SQL Server to the next.
Oh, man. This. So many times this.
I keep mentioning it to Redgate, but they don't get round to it... Or they think it's just this one crazy guy asking... 😉
Thomas Rushton
blog: https://thelonedba.wordpress.com
December 2, 2016 at 5:55 am
I've long thought at least two sections of SQL needed to have a better place within SQL architecture: SQL jobs and LinkedServers. Every other component we track in version and source control is an object, and easily managed with a subset of RedGate or other tools. Change control and Contingency sync is simple, until we introduce these two component.
As others already mentioned, the main concern for Jobs would be the schedule component. I know our implementation required a manual manipulation of code to disable the schedule component & avoid exactly that issue.
LinkedServer scripts are even more problematic. You either take a straight script out of the LS with "#######" passwords and record those somewhere else for manual reintroduction, or you manually update the scripts after script out and secure those security mapping password scripts in an alternate fashion. I know there's an encrypted fashion of migrating a linkedserver without having to know the passwords, but is that really something anyone can upload to source control, and then check out and script into another environment for validation (LLE, Contingency, or a new Prod server)?
These two main critical components of our SQL environments have existed as 2nd class citizens for too long! There may be other components we use less regularly that are treated the same way. Perhaps if there were a simple, more programmatic method to treat them as "objects" in their current states, it wouldn't be such a hassle. Do I believe Microsoft will change that overnight or in any existing implementation? Of course not... Too much potential breakage, too little payoff for them from the existing user base. I'd expect to see them implement it for their Azure environments before any standalone SQL server implementation. As a matter of fact, are Azure SQL jobs and LinkedServers handled differently than in non-Azure SQL Server? :hehe:
December 2, 2016 at 6:12 am
I like the idea in principle, but suspect that if this were done by people who think in database terms rather than in overall system terms we would end up with something much less flexible than what I had when I had to have jobs coordinated across multiple servers and having various non-sql steps, different logging in different steps, complex interstep control-flow logic, and ability to transfer jobs from one server to another at the drop of a hat (which we did by copying and editing data in MSDB tables), ability to shuffle schedules, and many more things. We used jobs in SQL Server 2000 to manage the servers, including error management (protection, detection, containment, and recovery) with very little human intervention indeed.
Tom
December 2, 2016 at 7:28 am
Speaking of which, I've found that practically all database comparison / synchronization tools don't cover server level objects like jobs, logins, credentials, SSIS packages, or server options.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 2, 2016 at 7:32 am
I think I would push for the DDL commands to be part of the ANSI SQL Standard before setting them up in SQL Server.
I think there should be a new category of commands rather than lump them under DDL. Server Definition Language (SDL) or something similar. JCL is probably copyrighted.
December 2, 2016 at 8:15 am
One more thing to differentiate MS SQL from ANSI Standards. Is this a good thing? Or should their be a concurrent push to make this an ANSI Standard as well as a Microsoft feature? I view jobs as server related not necessarily database related so would jobs now need to be associated with a particular database? MSDB or some new JOB database. Or perhaps they might be part of a user database meaning some jobs are part of database A while others jobs are part of database B. I admit it may make some things easier (restoring to a new server where we want the jobs) But if we want to restore without the jobs it may complicate things. I am not saying I don't like the idea but I think there needs to be some thought behind the implementation and the implications and perhaps even ways to avoid some of the issues that would result. For example many times in a job we refer to User databases. If the database is restored with a new name does the job get updated or not? Hmm.....
Francis
December 2, 2016 at 8:49 am
t.franz (12/2/2016)
A drawback of having the Jobs in the database would be, that - when you restore the database as _test, _dev or whatever, those jobs would be started and this could be problematic:- when it are long running / resource intensive jobs (that will start at the same time as the job in the source database, since the schedule would be restored too)
- when they are exporting stuff / sending mails (-> outdated, invalid data; may overwrite exports from the productive system)...
- when you restore the database on another server, some directories / drives that are used in jobs may not exists
PS: regarding the overview, when which job will run: you can get a simple table with next runtime etc. in the SQL Server Agent (job activity monitor)
Certainly this isn't simple, but it could be handled. RESTORE DATABASE ... WITH JOBS_DISABLED could help here.
December 2, 2016 at 8:58 am
Or we could go completely the other way.
As it is, the job system is an awkward fit into the SQL management world. You can do a lot with it, but it's still in some ways shoved into the same box as SQL servers.
Maybe it should be a completely separate entity-- one instance with a job oriented interface, that controls all jobs in all the servers in a farm, department or organization, with top down tools for managing at all levels.
...
-- FORTRAN manual for Xerox Computers --
December 2, 2016 at 8:58 am
@ Eric: EXACTLY... It's the simple database objects that get the most attention because they're the simplest for a 3rd party tool to handle. They exist as actual OBJECTS to be scripted programatically, while near every other critical non-object (jobs, linkedservers, etc) require a larger manual effort to script out. At the very least, they require a manual effort to identify and create an automated process, and that has its own inherent issues.
December 2, 2016 at 9:02 am
jonathan.d.myers (12/2/2016)
@ Eric: EXACTLY... It's the simple database objects that get the most attention because they're the simplest for a 3rd party tool to handle. They exist as actual OBJECTS to be scripted programatically, while near every other critical non-object (jobs, linkedservers, etc) require a larger manual effort to script out. At the very least, they require a manual effort to identify and create an automated process, and that has its own inherent issues.
I won't complain about SSIS packages, although it is certainly possible to script out, compare, and deploy the contents of packages deployed in SSISDB catalog.
However, SMO or PowerShell can be used to script out jobs, linked servers, and server settings. Anything that be scripted out to T-SQL from the SSMS interface should be covered by a SQL comparison / sync tool.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply