Voice of the DBA

Category: Blog

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

  • Quick PoSh SQL Provision Databases for New Developers

    As part of some presentation work, I wanted to demonstrate some onboarding of new developers. To that end, as part of a demo, I wanted to build a script that would take a few parameters and generate some SQL Provision databases for development. The idea is that an admin can have a script here to set up a developer with a database for a project with SQL Provision. I imagine this would be part of a script that also cloned a repo, set up a project, etc.

    The Process

    My thought here was that I needed a script that uses some pattern to build databases. The one I decided on was that the basic image name would be used with the developer name to prevent collisions. This would work if an admin used login names or even nicknames, as long as there is some uniqueness on the instances. Of course, if you’re provisioning to a local instance, this doesn’t matter.

    In any case, the images I make are often date driven, with a format like:

    SimpleTalk_Base_20181001

    Using formats like this, I can easily strip off the beginning part of the name (SimpleTalk) and then append my _developer to the end. For me, this means I’d get a database named: SimpleTalk_Steve

    This manifests itself as a script:

    2018-10-03 20_09_05-NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

    That produces a new database.

    2018-10-03 20_08_57-SQLQuery6.sql - Plato_SQL2016.DataMaskerDemo (PLATO_Steve (61)) - Microsoft SQL

    I also see this in the SQL Provision dashboard as a new item.

    2018-10-03 20_10_14-Microsoft Edge

    The Script

    I started this with some simple PowerShell. The first part of this script is a few parameters and a variable. Note that I’m splitting the instance name here.

    2018-10-03 20_11_48-● NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

    From here, I connect to the SQL Provision server and then get the instance and image objects.

    2018-10-03 20_12_01-● NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

    Lastly, I create the clone, making a new name from the image, and if the word “base” is included (I do this often), I strip it out.

    2018-10-03 20_12_06-● NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

    Quick and easy, but this allows me to demo how to onboard a new developer.

    There are lots of enhancements, and I need to add some error checking if there isn’t an instance, and if the connection fails, but for now, PoSh lets me quickly start getting some useful scripts that I can use for demonstrating some functionality. This certainly would work in an environment where I knew the Clone server was there and I had control over imaging. If not, I’d be writing more PowerShell.

    SQL Provision is pretty amazing and lets you really leverage technology to provide developers with copies of databases in seconds. Download an evaluation and see what you think.

  • The Developer’s Guide to Azure–A Good Overview

    Someone sent me a link a few weeks back  about a free book from Microsoft. It’s the Developer’s Guide to Azure, and you can download it in Kindle or other formats. I actually got it for the Kindle on my phone and went through it in my spare time during the last couple weeks.

    dev-guide-cover

    The book is a typical overview e-book, similar to what I’ve found in other areas from Microsoft. It’s very high level, with some examples and code, but very limited in scope and coverage.

    What I did like about the book is that it really describes all of the different services that Azure offers at this time, with some examples of how you might think about them. There were a few services I hadn’t heard of, so this gave me a tiny bit of insight into what they are and why they might be useful for me.

    If you haven’t spent a lot of time looking at Azure and keeping up with the advancements, you might pick up this book as a good general guide that will help you think about which areas you need to focus more attention, read about, or even build a PoC.

    I was especially interested in the security section, as I haven’t done much myself with Azure Active Directory and I wasn’t aware of all the services that exist.

    The book ends with a nice walkthrough of setting up some different services and configuring them. I haven’t completed these, but I might go through some and even work these into presentations, as I think Azure has a lot to offer that I could use in an environment I can access from anywhere.

  • More than 25 Years for me

    I was surprised to see a 25 year celebration for SQL Server at Ignite recently. It was also a 25 year celebration for Bob Ward. If you haven’t met Bob or seen him speak, make an effort to do so. He does an amazing job and can likely answer most any question you pose about SQL Server. I didn’t know Bob had been at Microsoft for 25 years, but that’s an impressive milestone and I’ll congratulate him when I see him in person later this month at the SQL in the City Chicago summit (register now:code Steve).

    The surprise for me was that I installed SQL Server in 1991, which by my calculations, was 27 years ago. It was in the fall of 1991 that a corporate development department came down to our remote site and had us install a SQL Server in preparation for a new application that we’d install on Dec 31, 1991.

    However, if you read the celebration post from Amit Banerjee, you will see that he notes in 1993 SQL Server released on Windows NT. That’s true, and I remember getting a wide box of NT 3.1 Advanced Server manuals that I dutifully read since I hadn’t been thrilled with the performance and stability of SQL Server to this point.

    You see, in 1991, we installed SQL Server on OS/2 1.3, which was horribly unstable and unable to handle the load of our application. I’m not sure if the SQL Server port from Sybase was the issue or OS/2 wasn’t stable, or the hardware wasn’t sufficient. Suffice it to say that I was thrilled when we migrated in 1992 to OS/2 20 and later 2.1, which were more stable. Then I could stop working 100 hour weeks.

    Despite a poor first impression from me, I grew to really enjoy SQL Server and switched from networking and infrastructure to database work. The rest, as they say, is history.