Voice of the DBA

Tag: T-SQL Tuesday

  • Automation for Databases–T-SQL Tuesday #110

    tsqltuesdayThis is the day for T-SQL Tuesday #110, a topic picked by Garry Bargsley. Automation has been a cornerstone of most of my career. As much as I work hard, and don’t mind putting time and effort into accomplishing tedious work, I also know that automation makes my life much, much easier.

    And me a far more productive employee.

    Automation is king, so here’s one place I’ve used this in the past.

    Complying with Standards

    One of the big items auditors want to have is proof that this thing happened or didn’t happen. That an employee actually performed some action, with some proof. At one point, we were being audited and I was asked if we checked that all backups were running for all databases.

    Of course, most backups worked, some failed,  and we were aware. More importantly, I could prove it. In our Exchange Public folders, we had a folder for database actions. In here, for each day, was a report from each database server. If there was a question of compliance for any of our sensitive systems, we could give them a link. More importantly, anytime we ran a remediation, we posted a note and the scripts run for the system. That way any backups that failed and were manually run, or other changes, were logged.

    This was a combination of a T-SQL querying system DMVs and DBMail to send out reports to our public folder (and DBAs). Today, if I needed something similar, I might move to PoSh, but likely lots of this is still easy to accomplish in T-SQL. However, I’d really rather not build any more monitoring systems. Too many things to manage, and my time is better spent.

    Now I’d use SQL Monitor and if anything, build my own reports on top of that infrastructure. That way the data gathering, archiving, etc. isn’t something I need, and I can extract out the text reports for auditors, which they like. Plus, with automation, they know that the system is running as intended.

  • T-SQL Tuesday #109–Influence

    tsqltuesdayThis month’s T-SQL Tuesday host is Jason Brimhall. He asks everyone to write about influence, which is something that I should know about and think I have, but am somewhat uncomfortable writing about.

    That being said, here’s my entry this month.

    Influencing Others

    One of the motivations behind creating SQLServerCentral was to educate and help others. Our goal (Andy, Brian, myself) was to try and find ways that we’d had success, solved problems, or handled challenges and share that with others by writing articles, answering questions, and creating questions for our Question of the Day quiz.

    Across the years as I’ve done that, and I’ve written many editorials, there have been plenty of occasions to meet members of the SQLServerCentral community. Many of them have thanked me or talked about how the site and community made a difference in their careers, which is something I’m quite proud of. Helping others is a form or volunteerism and service, and while this is a vocation for me, it’s also a passion.

    One of the things I think that I’m most proud of us influencing others to also give back and share. I can only do so much, but if I can also convince others to share their knowledge, there is a level of helping and sharing that grows exponentially to everyone in our #SQLFamily. To that end, I have two stories.

    One is about blogging, which I tend to do regularly. I had a friend, someone I met years ago that wanted to start blogging and asked me for some hints and idea. I shared a few things that help me, but also challenged this individual to set a goal for writing. They did, and years later, they have quite a blog. Many of you have heard of this person, but I won’t mention the name here. That’s not important. What is important is they have helped many others and continue to do so today.

    The other story is a person I met at a user group meeting. I was giving a presentation and this individual had some intelligent questions to ask. Over a few meetings, as I talked with this person, I realized they were someone with a lot of talent on the data platform and encouraged them to think about speaking. I did this regularly, and while it took over a year, this person now has spoken at many events, including the PASS Summit.

    It can be scary and intimidating to share knowledge with others publicly, but it is also immensely rewarding and a way of helping others walk the path you have already covered. Perhaps I’ll influence one of you reading today to share some of what you’ve learned with those hungry to learn.

  • T-SQL Tuesday #108

    tsqltuesdayIt’s that time of month, and this is a good topic as it relates to career learning. I’m a big fan of improving your career, so I like this topic. The invitation is from Mala, one of the people I look forward to seeing each year at various events.

    Non SQL Server Tech

    At heart, I’m something of a data person, though I dabble in various other technologies at times. This year, I made it a point to work on learning two new technologies, one of which was outside of SQL Server. Python was what I chose and I ended up spending some time on various Python courses for about 5 months. Then life and work got in the way.

    I still want to spend a bit more time on Python, but I also recognize that I need a new challenge, so I’m going to pick something else for 2019. For me, this will be CosmosDB.

    I think CosmosDB is a neat technology and has some really good things inside of it, but I really don’t know enough about it. I’ve had minor exposure to NoSQL structures, but not really enough to know how well I’d use them for a project.

    The Plan

    For 2019, or at least for the first quarter(ish), I want to port a database from SQL Server to CosmosDB and play with the differences. I have a few sample ones, but I’ve been compiling a database of some SQL Saturday data and want to use that as a test. I’ll work on moving the data to the different CosmosDB structures, likely a document structure and a graph structure, and gain some experience as to how these work.

    I hope to build a simple REST website that accesses these databases, which should also let me compare the differences for data access and note where one structure might work better than the other.

    I’ll set a reminder for the end of each month in 2019 (Jan-Apr) to evaluate where I am.

  • Lots of Scaffolding–T-SQL Tuesday #107 The Death March

    tsqltuesdayIt’s an interesting topic for this month’s T-SQL Tuesday, and while I think this isn’t necessarily a SQL Server project, because usually SQL Server isn’t the problem with building complex software,  my death march involves SQL Server.

    This month’s invitation is from Jeff Mlakar and I really am looking forward to reading some of the entries.

    Adopting OOP

    If you’ve ever worked on a greenfield OOP project, you’ve probably felt like there is a lot of work investment up front that builds a base for your application. For a long time bit, it feels like very little is getting done as you build classes and subclasses, and experiment with the structures that make sense. Your users and managers may complain wonder about what work is actually being completed in this stage.

    Eventually this tips over and all of a sudden you have something to show for your efforts and your objects start becoming useful and visible to others.

    This is my story.

    Leaving FoxPro

    Early in my career, I learned to program using FoxPro. This served me well and let me build a number of useful applications for various clients and employers. Eventually I was hired to manage a tiny small IT department that had to maintain and existing FoxPro inventory and order entry system and the internal infrastructure, as well as manage a rewrite of this application to a more powerful, useful system.

    Prior to my being brought on, the company had hired a consultant to handle the rewrite, while I managed another employee who maintained desktops, servers, printers, and more. The consultant had decided to move to Visual FoxPro backed by SQL Server. Since I had experience with FoxPro, SQL Server, and infrastructure, I ended up managing and participating in both sides of this environment.

    Visual FoxPro

    I had more experience with SQL Server and the consultant had more experience with Visual FoxPro, so I handled setting up the database and discussing some of the data modeling choices with him. He made all the OOP and VFP decisions, with the estimation that this would be a 8-10 month project. After all, we had an existing system, most of the business rules were already encoded, and we weren’t looking to add much functionality.

    Over the first 2-3 months, regular meetings showed me code being written, with some basic scaffolding of the objects we’d used and the various properties and methods that were required. We agreed on a object to relational mapping, and things seemed to be going well.

    Disaster Strikes

    The first issue that set us back was a disk crash early in month 3. The consultant had been backing up work to a zip file on his machine, but not the network. You can cry along with me guess what happened. His machine crashed, and we were slightly stuck. Fortunately, we discovered a copy of code that was weeks old, and managed to restart development.

    At this point, we had no VCS in place and my boss wasn’t interested in buying VSS. I decided on a simple setup. We worked off file shares for all code, with coordination on which files we’d edit (we were across the hall from each other). We also scripted a simple method to zip up all code and copy it to a different folder for each day of the week. Rudimentary, but it gave us some control in the mid 90s.

    Complaints Curiosity Rises

    About 6 months in, my boss started to ask questions. Where were the prototypes? Where is something to test. We put him off, showing him some basic objects, and explaining how we were front loaded with setting up new classes to better handle our business rules. We were coming from a procedural system, so much of the code had to be rewritten to work in an OOP environment.

    I was told that we needed to move, so I started joining in with coding. Up to this point, I’d just done some reviews, and now I needed to brush off some of my OOP skills from college as well as learn more about VFP, which was different from FoxPro.

    Over the next few months, I started pushing us to make decisions and move, rather than debate whether we needed to set parent methods that would be shared by multiple subclasses. I argued with our consultant, insisting we move on, even if we had to store some code in both subclasses. We needed to get work done.

    A Large System

    This was a fairly complex inventory and order entry system, unique to our business, and somewhat large for two people. At 8 months, we still didn’t have a working prototype, though we had certain items built and could show to users.

    We also had change orders coming in, with requests to alter some functionality that wasn’t as useful or relevant as it had been in the past. My ability to help rose and fell as other issues came up, so development was sporadic.

    A New Server

    Finally at about 12 months, we were close enough for testing that we decided to order a production database server. It was a nice, large Compaq. I think a PII, with 2GB of RAM and 5 or 6 internal SCSI drives. It came in multiple boxes, and we spent a day or two assembling pieces and installing the OS and SQL Server.

    We built the database, ran a test migration that worked flawlessly failed miserably, but we managed to tweek things across a few days and get most data moved to create what would be our QA environment. We were feeling good as we walked from the server room to our desks. We walked back to our desktops and started the application, and checked on some data. Things worked, so time for QA.

    Caching is amazing horrible

    We let some management know that we were close and one of the department heads volunteered one of his order entry people to do some testing. This young lady came over and sat at our desk, trying to process a few orders that she’d entered the week before. She entered a customer and clicked search.

    And watched. It took maybe 15s to load the information, which was waaaaaaayyyyyy slower than our Fox for DOX application. Still, she persisted. She entered a partial product and searched for inventory, and

    the results appeared to her delight after about 1 minute and a half. She was less than impressed.

    We were slightly horrified. How could this server run slower than our workstations, which were mostly 64MB of RAM. Why wasn’t our 2GB server flying compared to the DOS app?

    It took a little time, some research, and lots of questions to realize that the amount of data we had was coming from disk, with SQL Server’s optimizer deciding how to search, how to compile a plan, and how to read from disk. This was a problem, with spinning disks not performing as optimally as we’d like. Things were better once we’d warmed the cache, but users were not thrilled with early tests.

    Auto-Start Procedures

    We continued working, across a few more months to finish the application to the point where users could switch. At this point, we were about 16 months in and it felt like we’d never get there, but we got far enough that we could migrate data and switch, changing a few procedures for functionality that would take longer.

    My boss and the owner, who were business people, were less than thrilled with the length of this project, but we had improved a lot of features along the way, and they didn’t fire us because of the delays. We cut over, and while slow in the first hour, the system improved over the day and once users got used to some new ergonomics, they were pleased.

    One of the things that saved us was the use of some auto-start procedures that ran when the database engine started up. We ran a few very common queries to pre-load the cache. We couldn’t load everything, but we were smart enough to load the queries that were often used by the people who complained talked to use the most. This reduced the friction of the cutover.

    We ended up buying more RAM, doubling us to 4GB on the server after a few weeks to try and cache even more data. After a month, we’d rapidly added more functionality, somewhat working in a DevOps process, although we lacked some automation, with rapid responses to feedback from users.

    We finally let our consultant go after 20 months, more than double the original estimate for the project. Up until a couple months before that, I wasn’t sure we’d ever finish, but we actually did. At least up to the point where myself and another IT programmer could maintain and enhance the application.

    Nobody died got fired, but it felt like those were distinct possibilities for some time.