A quick video for the Accidental DBAs in the crowd:
Thanks for watching!
2018 Highlights and 2019 Goals
Happy New Year!
They say focus on the positive and make your goals public, so that’s exactly what I’m going to do here!
Dallas DBAs had a great 2018, by every measurable category. No formal goals were laid out, other than “don’t close the doors” and “help people”.
The stats:
- 7 new clients added – mix of short-term and long-term
- 1 customer carried from 2017 into the year and 3 extensions signed for 2019
- 21K views/115K minutes watched on our YouTube channel
- 26,415 page views on DallasDBAs.com
- 1 new employee added (part-time Junior DBA)
- 2 new contractors working on skills not in house (Power BI and SQL Dev)
- 3 other sub-contractor agreements signed
- 1 SQL Saturday sponsored (Dallas, May) with a booth (many others at the “personal” level)
- 2 Pre-conference sessions and 2 regular session presentations given at SQL Saturdays (Cincy and Dallas)
- 3 scripts updated or added to the Microsoft Technet Gallery
- 2 Databases rescued from corruption issues (Thanks Red-Gate!)
- 6 free HealthChecks
- At least $12 in net profit
The goals for 2019:
- 6 new long-term part time DBA support clients
- Add one Senior SQL Server DBA to staff
- Add one apprentice to this DBA
- Add one new Oracle DBA to the team
- Sponsor 2 SQL Saturdays with a booth (Dallas and Cincy)
- Deliver “DBA Fundamentals” training for free once per quarter (Cincy, Dallas(?), Q3/Q4 TBA)
- 30K views/180K minutes on YouTube channel
- 6 new videos – Accidental DBA target audience
- 40K Hits on DallasDBAs.com
- At least $13 net profit
- 12 Free HealthChecks, bare minimum once a month
The things we put in place this year have worked well, so I may be fudging the number too conservatively for 2019. Check back next year…we may actually hit $14 net profit!
Thanks for reading!
Integration Services Catalog package errors
Short post today…
My client makes extensive use of SSIS and deploys the packages to the Integration Services Catalog (ISC), and runs them via hundreds of jobs.
When one of the jobs fail, I have to go get the details.
Job History doesn’t have it.
So my process was:
- Get the package path out of the job step
- Expand the ISC
- Expand SSISDB
- Expand the Folder (get this from the job step)
- Expand the Projects Node
- Expand the correct Project (get this from the job step)
- Expand Packages
- Right-click the relevant package (get this from the job step)
- Go to Reports, Standard Reports, All Executions
- Filter for Failed Executions
- Cross your fingers that you can get useful info from the result.
Now, repeat for each job step that failed.
The SSIDB database has all of this in the Tables (schema – Internal) and or the Catalog Views.
It took a while, but I created this to run around those tables and views to get ALL of the errors for the last day:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
/* Created by Kevin Hill, Dallas DBAs LLC, 12/28/2018 Inspired by work from Jules Behrens Index tested by Peter Schott This queries multiple SSISDB tables to return a clear path from Top to bottom related to errors in an Integration Services Catalog based SSIS package. It has not yet been tied back to job execution, nor is it set to email info out. Use this as a backup to your normal job failure checks to tie it all together instead of spending a full cup of coffee clicking and drilling into the cumbersome All Executions report. Free to use and modify, please leave this header as a courtesy. */ Use SSISDB go Select Distinct fold.[name] as Folder_name ,proj.[name] as Project_Name ,pack.[name] as Package_Name ,ops.[message_time] ,mess.[message_source_name] ,ops.[message] --,mess.[execution_path] -- this is pretty long path if you are pasting into an email or Excel From [internal].[projects] proj Join [internal].[packages] pack on proj.project_id = pack.project_id Join [internal].[folders] fold on fold.folder_id = proj.folder_id Join [internal].[executions] execs on execs.folder_name = fold.[name] and execs.project_name = proj.[name] and execs.package_name = pack.[name] Join [internal].[operation_messages] ops on execs.execution_id = ops.operation_id join [internal].[event_messages] mess on ops.[operation_id] = mess.[operation_id] and mess.event_message_id = ops.operation_message_id and mess.package_name = pack.[name] Where 1=1 and ops.message_type = 120 -- errors only --and mess.message_type in (120,130) -- errors and warnings and ops.message_time > getdate() - 1 -- adjust as necessary /* If you have a very large SSISDB due to activity or long retention, please consider this index: CREATE NONCLUSTERED INDEX [NC_OpsID_MessageType_MessgeTime] ON [internal].[operation_messages] ( [operation_id] ASC, [message_time] ASC, [message_type] ASC ) INCLUDE ([message]) */ |
If this is useful for you, please rate it on the TechNet Gallery where I posted it.
If you have a very active server, this can take awhile as the indexes on the underlying tables are for the cascading delete cleanup process. I’m working on a set of good indexes that will likely be unsupported by Microsoft, but useful.
Adjust as you see fit. Please leave the header if you use it.
Thanks for reading!
—————————————————————-
DBA Fundamentals pre-cons 2019
While this post is for everyone, it is specifically written for SQL Saturday organizers…
I am filling out my 2019 schedule of target dates/locations of SQL Saturdays I want to attend/present (US only…I don’t even have a passport)
I am offering to do my DBA Fundamentals pre-con and one or two Saturday sessions (Security/DR) with a twist:
You keep my part of the pre-con proceeds for your group. I’ll cover my own travel, meals, etc. if you provide the space. This should allow you to increase your funding for the rest of the event, buy some pizza for your local group, or even discount the pre-con price should you choose to do so.
If you only have 1 or 2 pre-con spots and cannot secure an additional room at your venue, please pick from the other submissions 🙂
Tentative schedule:
- January –
- March – Chicago (23rd, submitted) or Cincinnati (30th)
- April – Dallas *
- June –
- August –
- October –
- December –
* All of these are tentative, some dates not granted yet
So, dear organizer…if you think this offer will be a benefit to your SQL Saturday, please get in touch and let me know what your planned date is so I can be sure to submit. Kevin@DallasDBAs.com
Thanks for reading!
PASS Summit 2018 – My Schedule
If you are going to PASS Summit in Seattle and want someone to sit with, check out my schedule (primary and backup sessions) – very much subject to change:
Wednesday, November 7, 2018
============================
10:15 AM – 11:30 AM Room: 2AB
dbatools Powershell Library – The Complete Introduction (Yes, my very first session is Powershell, lol)
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=85712
10:15 AM – 11:30 AM Room: 612
Uncovering Duplicate, Redundant, & Missing Indexes
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=77036
1:30 PM – 2:45 PM Room: 606
Improving Availability in SQL Server and Azure SQL Database
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=84728
1:30 PM – 4:00 PM Room: 6E
SQL Server Migrations Done the Right Way
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=84219
4:45 PM – 6:00 PM Room: 6A
SSMS & T-SQL Tricks: Working Smarter, Not Harder
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=78153
Thursday, November 8, 2018
============================
10:45 AM – 12:00 PM Room: 6B
Containers, Pods, and Databases- The Future of Infrastructure
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=78022
1:30 PM – 4:00 PMRoom: 606
Query Tuning Internals for the Advanced SQL Developer
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=78050
1:30 PM – 2:45 PM Room: TCC Tahoma 2
Top 5 Power BI Custom Visuals
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=82110
1:30 PM – 2:45 PM Room: 6C
Top Tips for Deploying AGs and FCIs On Premises or In the Cloud
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=78041
3:15 PM – 4:30 PM Room: 618
An Introduction to Partitioning
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=77002
4:45 PM – 6:00 PM Room: 604
Data Science 101 for the SQL DBA
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=86521
4:45 PM – 6:00 PM Room: 6C
Validate Your SQL Server Estate with Ease Using dbachecks
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=78382
Friday, November 9, 2018
============================
8:00 AM – 9:15 AM Room: 6C
T-SQL Habits and Practices That Can Kill Performance
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=78111
9:30 AM – 12:00 PM Room: 6E
Migrating to SQL Server 2017
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=78285
11:00 AM – 12:15 PM Room: 3AB
BI Power Hour
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=86517
2:00 PM – 3:15 PM Room: 3AB
Lightning Talks: DBA
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=85592
2:00 PM – 3:15 PM Room: 618
What’s New With Adaptive Query Processing?
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=78238
3:30 PM – 4:45 PM Room: TCC Yakima 1
A DBAs Guide to the Proper Handling of Corruption
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=78274
3:30 PM – 4:45 PM Room: TCC Tahoma 4
SQL Server Performance Monitoring Made Easy
http://www.pass.org/summit/2018/Sessions/Details.aspx?sid=85138
PLEASE feel free to come introduce yourself like we are old friends. My brain is defective in remembering names and where we met, so you may have to remind me if in fact we have met before 🙂 Fair warning…I may ask you to take a selfie with me. I suck at selfies so I may actually get a shot of your shoes, or the ceiling 🙂
Thanks for reading!
Put a DBA in Your Pocket
Oh snap…one of your two DBAs just gave 2 weeks notice.
Now what?
You have 2 weeks before your other DBA starts feeling the pressure of doing 80 hours of work in a 40 hour week.
Did you see this coming? Do you have a favorite recruiting firm on your vendor list? Do you know a guy that knows a guy?
Assuming that the budget exists to pay for a replacement, you are very likely 4 weeks out from actually getting a replacement in the door:
- 1 week for the recruiter to source candidates
- 1 week to interview, if you are really on the ball and can “Agile” through the stack of resumes and the interview process
- 2 weeks for the person you make an offer to so they can give proper notice
- At best.
- If you rush the process.
In reality, recruiters these days are handling 30+ openings and dozens of applicants per opening. That’s a lot on their plate.
You have Stuff To Do™ as well, which limits your time to properly review resumes and get through the interview process. Hopefully your process isn’t 3 phone calls and 4 face to face on different days…
Great…so we’ve defined the problem. What is the solution?
Put a DBA in your pocket.
You likely already have a lawyer and accountant in there. Put a few critical technical people in there with them.
Set up a retainer agreement with an independent DBA to fill in when:
- Someone leaves
- Someone goes on a well-deserved vacation
- Someone goes on Maternity/Family leave
- A new project comes in and you cannot let the care and feeding go by the wayside
This doesn’t even have to be an actual independent contractor…many qualified DBAs, SysAdmins, storage people, etc. that are working full-time jobs but can check on your systems on weekends and evenings.
If your systems are mission critical, and you are running skinny in certain areas…protect yourself by having someone you already know in your pocket, ready to call when the unexpected happens.
For my fellow DBAs…would you like to take a vacation without your laptop? Or go to a training class? Send this link to your manager if you are stretched so tight you can’t breathe!
Dallas DBAs provides Remote DBA services at ‘X’ hours a week. Contact us if this resonated with you.
Thanks for reading!