Tuesday 28.03.2017
I spent the morning watching more of the SQL Server transact SQL (basic data retrieval) videos. Again, I needed to do this intermittently as it becomes hard to focus on videos for such long periods of time.
To allow myself a break from the videos, I did some further research into workflows and flowcharts. Along the way, I found a question which popped up in a forum, it was asked as follows. What is the difference between a workflow and a flowchart? I feel this following statement clearly defines the differences:
“A workflow” is a definition of a process for how an item of work should be done.
A flowchart is a diagram for describing a process. So a workflow can be described by a flowchart.
Perhaps it is the case that workflows are commonly described by flowcharts, that some people may use the two terms interchangably.
I also found this useful resource entitled ‘How do I Evaluate Workflow?’. Although it discusses the process in regards to medical workflows, the theory behind it is applicable in any environment.
After watching some more videos I had now completed the following modules and had learnt about the following topics:
- Module 2: Setting up a transact SQL Learning environment
- Installing SQL Server 2012 Express with tools
- Installing SQL Server Data tools
- Downloading and attaching the sample database
- Executing a basic data retrieval query
- Module 3: Writing s basic SELECT Statement
- Conceptual overview and demonstration for the key components of a SELECT statement
- Module 4: Querying Multiple Tables
- Building on module 3 and applying the concepts to queries that access multiple data sources
- Covers:
- INNER, OUTER, CROSS joins
- Self joins
- CROSS APPLY
- UNION
- INTERSECT/EXCEPT
- Common Table Expressions
- Module 5: Using Functions
- This module covers a variety of functions which can be used within data retrieval queries to meet application and reporting result set requirements
- Covers:
- Aggregate functions
- Mathematical functions
- Ranking functions
- Conversion functions
- Date and Time functions
- Logical functions
- NULL handling
- String functions
Module 5 raised some questions when I got to the video discussing conversion functions. I needed more clarification on the differences between PARSE, CAST and CONVERT. When is each used and what does one do, that the others don’t? There was a plethora of information out there, so it took some trawling through to make some sense of it.
At 2.30 I had a meeting with Adam to go over my prioritized list of alerts and to discuss where to go from here.
During the meeting Adam helped refine my list of Alerts as well as the questions I had drafted to ask the DBA team. He also sent an email to a few of the team (Kathryn, Tim and Rosie) who are lined up to help me with the first two of my alerts over the next couple of days.
Wednesday 29.03.2017
This morning started off with meeting with Tim to go over how he handles the first two alerts on my list. He walked me and Nadine (who was also interested in how Tim handled the alerts) through all of the open tickets relating to said alerts. During this process I also had the change to ask Tim the questions I had lined up, which he was more than happy to answer.
After working with Tim, I decided to take the information he had provided, and the related SharePoint pages and create some preliminary flow charts. I feel these will be helpful to follow along with when I work with both Rosie and Kathryn as I will be able to clearly see where their processes differ. I can also ask if they think the diagrams are intuitive and easily followed.
Kathryn let me know that she wouldn’t be able to catch up until later in the afternoon as she had had a super busy morning and now needed to catch up on the alerts board. So in the meantime I got a bit ahead of myself and created some more flowcharts using the SharePoint pages. By that stage I had begun flowcharts for the following 6 alerts:
- JobFailed: SSL_Backup_All_Databases
- JobFailed: SSL_Backup_All_Logs
- CustCode ServerName: Appears to be down – last message received x minutes ago
- ALERT: Log Full
- ALERT: Database Full
- Alert: Low Disk Space
I also had time to watch the last few videos I didn’t get time to watch yesterday afternoon. This was the final section in module five which covers the following analytic functions:
- LAG
- Compare values in current row with values in previous row
- LEAD
- Compares values in current row with values in following row
- FIRST_VALUE
- Return the first value in a result set
- Result set may or may not be logically partitioned
- LAST_VALUE
-
- Returns the last value in a result set
- Result set may or may not be logically partitioned
-
-
- CUME_DIST
- PERCENT_RANK
- PERCENTILE_CONT
- PERCENTILE_DISC
Last value brought up some questions surrounding windows frames. Windows frames were mentioned in the video, but not covered in detail. For the example unbounded preceding and unbounded following were used.
Thursday 30.03.2017
Today I carried on with the flowcharts I had made a start on yesterday. I’m now at a stage where I have initial flow charts for the following 5 alerts, based on a combination of the SharePoint pages and information gained from shadowing:
- JobFailed: SSL_Backup_All_Databases
- JobFailed: SSL_Backup_All_Logs
- CustCode ServerName: Appears to be down – last message received x minutes ago
- CustCode ServerName: Is now appearing as Alive in the SecurePortal, after previously being marked as Down!
- Alert: Low Disk Space
For the first two mentioned above, I have also managed to complete the after hours alert resolution process flowchart, based on information gained from shadowing Tim. I also have multiple variations of most of the above alerts, as I’m not 100% convinced on the best way of showing certain aspects of the process. So this is something that will be refined as I continue with the shadowing and interviewing process – figuring out exactly how the DBA’s resolve the alert rather than trying to follow the documentation.
Slade also let me borrow some of his time this morning, which was spent shadowing and interviewing him. Having my initial flowcharts for this process proved very useful, as it was great being able to ask him if he did follow that process, or where his process differed from what I already had documented. From there I could scribble down changes to be amended later.
Working with Slade really helped me to refine my flowcharts for JobFailed: SSL_Backup_All_Databases and JobFailed: SSL_Backup_All_Logs. During this time, I changed the part of my diagram relating to the decision “Is there an issue blocking further backup attempts?”. It now includes a path which can be followed if it is not possible for the DBA to resolve the problem without the clients cooperation – which was not possible with the original design of my diagram.
Original
Updated
However, later on in the afternoon, I realized the revised section of the diagram still didn’t work the way I needed it to. What would happen if you contacted the client regarding next step, but the next step wasn’t “Is a manual backup to be taken?”. But in fact, the client needed to provide a solution to the issue blocking a further backup being taken, for example, they needed to provide more disk space. My flowchart just didn’t allow for this. So along came the next version as seen below.
Restructured
For the remainder of the afternoon I just played around with the layout of the diagrams, trying to make them as intuitive and readable as possible. I did this by making all positives (Yes answers) go in the same direction where possible, then the same for negatives (No answers). There are also no intersecting arrows in any of the diagrams, which makes them neat and tidy and easy to read. I’m sure they will all need shifting around again as I continue, but for now they are all clear and uncluttered.
I now have the diagrams for the first two alerts ( JobFailed: SSL_Backup_All_Databases and JobFailed: SSL_Backup_All_Logs) at a point where I would love Adams feedback in order to see if I am on the right track. That will be my first task when I arrive back next Tuesday. I am also yet to catch up with Kathryn, as she was too busy again today to take me through her alert handling process. If all goes well, she should be able to set aside some time early next week. On that note, I haven’t heard back from Rosie yet either, as she hasn’t been in the office the past couple of days. So hopefully early next week works for her as well.
It is now the end of my fourth week here at SQL Services and I have completed 110.5 hours. So I’m over one third of the way there. Which sounds crazy! Time just flys on by when you’re having fun.
bryonywiechern 