Voice of the DBA

Tag: software development

  • SQLServerCentral Design–QotD

    This is part of a series of posts that look at the changes to the SQLServerCentral design, bringing us to v3 of the site. You can see the other posts with the SQLServerCentral tag.

    We’re a few weeks in and the site is stabilizing a bit, but a lot of work still to go. This week, I wanted to write about the changes to our Question of the Day section, which are likely more backend than front end, but still an important part of the site.

    When we started the question of the day, it was a whim from Brian Knight, one that annoyed me because I ended up being responsible for most questions. Over time we allowed users to submit questions and enhanced the way the testing worked, but it took a bit of a step backward in v2 of the site. We had more restrictive forms for loading the questions, not supporting images in the explanation or even any HTML at first. We enhanced this over time, but the marking of multiple choice questions, scoring, awarding back points, and more were somewhat broken for a long time.

    I got quite familiar with running direct “fix-em-up” queries against the database to correct issues.

    When we talked with the developers about the new system, I wanted a more flexible way to get questions written, scheduled, scored, and even fix when we corrected issues. There are a number of plugins for WordPress that allow quizzes, but none that really fit our paradigm.

    A custom plugin was written which allows a more flexible editing system for the question and explanation, similar to what most users are familiar with for writing posts in WordPress. This is more visually appealing, and easier to author a question.

    On the front end, things are very similar, though we have reformatted a few things in the last couple weeks to show more information:

    • We let you know if you were right or wrong
    • We give you the correct answer, or if you were wrong, we tell you what you picked
    • We give you the percentage and count of people answering each choice.

    Many of these changes were simple, and some added back functionality that was lost when we launched the new site, but the changes were quicker and easier than in the past.

    That’s a good sign because in the process of building this out, there are a few things missing. We still need to award back points for poor questions. Right now I’d be reduced to making a database query, but I need a “award everyone points regardless of answer” button built.

    We also want to start collecting items into a test that might quiz a user in some particular area. In other words, rather than a question a day, we want to put questions more into a group like a certification test. Since the items are all linked in a more normalized fashion, and this would in some sense mirror what we’ve done with the Stairway series, I’m hopeful we can add this.

  • SQLServerCentral Design Decisions–Forum Threads

    It’s been a long couple of weeks. Almost two weeks ago we cut over from our older, v2 version of the site to the new (and current) v3. Lots of bugs, lots of disruption, and lots of unexpected items.

    As a bit of trying to help me understand as well as a catharsis, I wanted to jot a few thoughts on things that I’ve had to make decisions on or work within our framework. This post looks at one small item, the display of forum posts.

    The New Look

    Here’s the top of a thread in the current site.

    2019-04-08 11_38_35-Window

    A post and two responses, and I think it’s easy to see who’s posted, their avatar, the text and content, I can follow a user or send a message, I can reply or quote a post. My signature is small and a different font from the text.

    Here’s another couple posts, which I think are harder to read:

    2019-04-08 11_45_19-Window

    These are older signatures, which don’t format as well, and some of the quoting is a little broken and causing issues.

    I don’t think the new format is necessarily more visually appealing to me, but it is less elements and a cleaner look in a more modern style. For everyone that liked the old format, we have people that didn’t.

    And vice versa.

    In this new format, we took the default bbPress items and tried to aim for less extraneous information. We styled this to have less color and contrast that is distracting while presenting the information that users need to see. We limited the options, of which we had too many and confused people in the older forums.

    This also gives us a good codebase on which we can make future changes. While many of those aren’t visible here, the pace and breadth of changes we’ve made in the last two weeks to tweak things is way above anything we went through the last time we touched the forums. I’m amazed at how well we’ve adapted to new areas and requirements as we go along.

    That being said, I know there are lots more changes to make and look for more to be coming in the next few months. We’ll change the styles slightly and perhaps redo some of the layout to make things easier.

    One thing this project has taught me is that what I think is a simple decision for software isn’t so simple for my users. We have thousands, really hundreds of thousands of users and some days it feels like none of them are pleased. I couldn’t imagine how hard it is when your software or site has 10x or 100x that size.

  • 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.

  • Practical Refactoring

    Today I hosted a webinar with Gene Kim (@RealGeneKim) and we had a fantastic discussion. I was slightly star struck since I’ve been reading his work and quoting him for years in talks about Database DevOps. It feels like I got to work with someone really famous, and I’m hoping I didn’t appear too nervous on the webinar.

    In any case, we had a great discussion, and I think you can still register to watch the recording. If not, we should have this on our Redgate YouTube page soon. We discussed the State of DevOps report, and specifically how the findings relate to databases. It was a good discussion, but when we talked testing, we both had some links to ways that we could build better software.

    In my case, I referenced this talk, Practical Refactoring, which I think is great. A bit is the technical approach, but mostly I find the philosophy and freedom that comes with having tests in place to be invaluable.

    This is based on a real project that these consultants worked on. The code is mocked, so don’t get caught up in the actual methods and structure, but think about how you could apply the ideas to your own work. How can you make the code better in a few minutes.