As I mentioned in my previous posts....this was my first actual SQL job. I had been responsible for a SQL Server at my previous job. I had also been tasked with creating a database for a specific department. However, those SQL tasks were in addition to my other responsibilities.
My only responsibility at my first SQL job was....SQL.
This was the job that showed me what a full time DBA actually does. It was a stressful yet exciting time. In a very short amount of time I was exposed to enough SQL equivalent to a year's worth of experience.
As I mentioned in Part 2 of this post, my notebook was invaluable to me. In that notebook I had my tasks, the due dates, the progress I had made, and what I had actually learned. One of my tasks, in fact, my most important task was documentation.
The previous SQL DBA had left with all the documentation he had amassed in the four years he had worked there. So I had to take specific steps. My first step was to find out what servers were the actual SQL Servers I would be working with. My next step was to gather all the relevant information about the servers. I needed their IP addresses, their physical specifications such as RAM and hard drive space.
I also needed to know the version of SQL Server we were running, the size of the database, and the backups.....where were they being placed? On what schedule?
Also...what SSIS packages did what? In my case, what steps had the previous DBA taken when troubleshooting one particular error that kept coming up?
My manager wanted me to use OneNote for documentation purposes. I was a bit opposed to this idea at first because I had very little OneNote experience. I was also used to the old school way of creating a Word Doc or an Excel Spreadsheet for documentation. However, OneNote was great. In fact, I still use it to document the work I do in MySQL when I have side projects.
I had several different notebooks within OneNote. One notebook was about the servers. It had tabs such as Memory, SQL Version, Login Information, Notes, etc. Another notebook was called SSIS Packages. It had screenshots of the packages. It had the SQL code written out and explained. Indeed, it had the actual package steps clearly explained.
I also had a Data Integrity Notebook. This notebook contained all the queries I had come up with to confirm that, at least on the CRM database side of things, the records matched what the program was putting out. As a starting point for my queries I used the SQL Server Profiler. I ran some reports via the CRM's web interface, and captured those queries in the Profiler.
I studied how those queries were put together. I did this by finding the tables in the query, and looking up additional details that were in the Data Dictionary provided by the CRM software's vendor. I also looked at the ERD quite a bit.
I made a copy of the current live database, and put it on another SQL Server. I did this so that my queries wouldn't slow the server down during production hours. The way the database was structured, and the kinds of views my manager wanted, caused timeouts quite often during querying.
Let me give you an example. The CRM had all the companies my company was working with. One of the views I was required to create had all relevant company information: address, phone number, contact person, and financial information. This financial information was actually brought in from the Financial Database via an SSIS package which performed ETL.
Okay, easy enough, right? You know you're going to have to query the company table, right? Wrong. There was no company table. Everything within the database was seen as a contact. Either a company contact or a non-company contact. However, both of these were in one table called, contacts.
There was also a difference between the way the program was meant to be used, and the way the company was using it. There were some built in reports on the web side of the program that if you ran them it would take literally 12 hours to come back with the results. If you performed the same query via SSMS it would time out the database.
I extended the time out period, but this had no effect. I ended up using a trick from my Java programming days. I broke up each view into separate views. So I treated each view as a separate object, or building block, of what I actually wanted. Then I joined all the views together using LEFT and INNER JOINS.
This kept the system from timing out, and gave me the results I needed with very little wait time. I also made it a point to document this particular method. In case sometime later another DBA was trying to figure out why I had done something the way I had done it. After all, this was only a 3 week contractor position.
Was this the correct way of doing things? I didn't know. I was inexperienced and I wasn't getting help with the SQL part of my job. Since I had made a copy of the database I was able to test out my queries without slowing down production.
My numbers came back accurate. That meant my views could be trusted. In fact, I had found that they way the report person was running one of the reports was incorrect. By changing some criteria her report numbers matched exactly what my view had given back.
In IT you often have to think outside of the box. Creating multiple views and JOINing them together was one of those outside of the box moments. I confirmed that each separate view gave me the correct result set in and of itself. I then confirmed that as a whole the new view, which remember was merely the collection of confirmed views JOINed together, gave me the correct result set.
I was fortunate in that I had a Help Desk, and Windows and Linux system administration background. So I was able to use that knowledge to assist me with my tasks.
No comments:
Post a Comment