Voice of the DBA

Tag: software development

  • Project Euler

    I like having problems to practice solving in computer science. It can be a fun hobby, and it exercises your mind. I have enjoyed the Advent of Code, though some of those are complex and hard to focus on in my busy life.

    My middle son asked about Project Euler as he finished his first year of college and suggested we try some together. He’s been busy, though I have called him down as I’ve solved one or two. He’s asked I send him my code, which I’m doing in Python as practice. He’s using C++, which is something he’s studying, but he knows some Python.

    These are all math based problems, and most are short in the archives. I’ve been trying to take 5-10 minutes and solve 1 a day. I’ll ping my kid when I’m working, and we might pair program a bit to get a solution.

    Not terribly challenging, but it is a different type of thinking from most data work, and it can be fun. If you want to take a break from other work, maybe experiment with a new language, give Project Euler a try.

  • A Python SQL Server App

    I got a link recently from Microsoft on building apps easily for SQL Server. At the top of the page, they ask you to pick a language and OS. Since I’ve done a little Python and I used Windows, I chose that option.

    The page looks to walk you through a simple app. I decided to try this out and see if it works, following the instructions. Since I already had SQL 2016 installed (and newly upgraded to SP1), I just connected and verified it was there.

    2016-11-22 11_50_06-powershell

    I didn’t want to go back to Python 2.7, and I’d rebuilt this machine for testing, so I used Chocolatey to quickly get Python 3.5. I’ll have to adjust code to match this version.

    2016-11-22 11_52_38-cmd - choco install python (Admin)

    One python was installed, I needed to get virtualenv and then create a folder. I used my regular git folder since it’s really, really simple to get a project with version control running. Unfortunately, when I went to get pyodbc, I had an error.

    2016-11-22 12_18_53-cmd (Admin)

    This is going to use C++ tools to build the pyodbc files, so I need to get those installed. I can do that from Visual Studio.

    Once I run those and install them, I can now get pyodbc. There is a build error, but I seem to have the module. My next step is to create my db. I also create a login that is db_owner for this.

    2016-11-22 12_41_22-powershell

    Now it’s time for the code. That’s easy enough, so I copy the code into VSCode and change the instance and user information.

    2016-11-22 12_42_11-crud.py - Visual Studio Code

    I also had to replace the print statements with parenthesis around the quoted items, which is required in Python 3.x. Once that’s done, I start the program and it appears to work.

    2016-11-22 12_39_25-cmd (Admin)

    If I check from SSMS, sure enough the program has worked.

    2016-11-22 12_39_54-SQLQuery1.sql - localhost_SQL2016.SampleDB (PLATO_Steve (62))_ - Microsoft SQL S

    The second part of the tutorial uses the Django Web Framework to setup a simple site. Once again, I get a pyodbc error and there’s nothing that works. Fortunately I found the issue is with Python 3.5 and not having the proper binaries in the default PyPI directory. I resolved it with this link.

    2016-11-22 13_26_45-crud.py - Visual Studio Code

    However, there were other errors, which I suspect are related to Python 2.7 v Pyhton 3.5. Rather than solve those, I went on to the columnstore demo. In this, you create a table with 5mm rows and then run a query against it from Python. I did that, then created the columnstore index, then ran it again. The results are below.

    2016-11-22 13_46_23-columnstore.py - Visual Studio Code

    Note: I had to create the variable, tsql, and used this line of code:

    tsql = “SELECT SUM(Price) FROM Table_with_5M_rows”

    Python Works

    Despite some issues, which are probably my fault, this is a good introduction to how you might use Python with SQL Server. Since we now have SQL Server on Linux coming, and there are lots of Python developers, this might be a good place for some of you to learn a bit about connectivity with Python if you ever need troubleshoot that kind of setup.

  • Did My Dev Changes Get to Production?

    One of the things that people don’t think about often is whether the changes made in development actually get to production. Usually this is because most changes go, and unless it’s a big change that causes a problem for a large number of customers (or a large customer), we often just deal with mistakes as we can.

    That’s not ideal, and certainly not what I’d like to see as a developer. I’d like to get changes to customers quickly enough that I get feedback. In this post I want to look at how DLM Dashboard can help here.

    Tracking Changes

    There’s a disconnect in traditional software development. Developers write code to meet some spec, and often drop that into some storage spot, hopefully a Version Control System (VCS) and eventually that code gets deployed to production. Whether a web site, client local app, mobile, database, etc., that code tends to move in batches.

    Developers typically haven’t been concerned about the overall packaging of all the code, mostly because code gets pulled from multiple people, so it’s hard to track this. We depend on project manager or some deployment person to ensure all changes get deployed.

    For the database, because people often manually build, or at least edit, scripts, I find there are more changes for a particular line of code to get left out. That can be bad, and maybe really bad if you deploy the database in advance, such as a day or week before other code.

    How do we track changes?

    How can we track changes? In a VCS things are easy to see. However, we really need to see a bundle of changes. Having a version of the database, not just the latest version, becomes important. Let’s look at an example.

    I’ve made some changes in development, and my CI process pushed them to my integration environment. This shows my current database pipeline like the image below:

    2016-07-30 17_03_38-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    In this case, I’ll get a close-up of the details, but I’ve moved my Integration environment from a database v of 4.2.70 to 4.2.71. This is from a new stored procedure, as you can see below.

    2016-07-30 17_04_05-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    I can see the details of the procedure, as I’ve shown here.

    2016-07-30 17_05_30-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    I typically might not look at each CI build, though perhaps as a DBA I’d go through a series of changes across some period of time. That’s a post for another day. In this case, I usually would approve all the changes occurring in Integration environments. Eventually I’ll end up with quite a few changes. I’ll make a few more, with a few more commits and up the version a few numbers after marking this version as reviewed.

    I’ve made four separate commits, and now see this in my dashboard.

    2016-07-30 17_13_23-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    As you can see, the CI process has updated the integration database to 4.2.74 from 4.2.70. However, as I dig in, I can see details. The “Review” button only gets me the changes from the most recent commit.

    2016-07-30 17_14_22-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    However, I can click “See Schema History” at the top and then click the particular version to see all the changes. Here are the first two.

    2016-07-30 17_16_13-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    I don’t care about these changes by themselves. However, I do want to be sure that the appropriate changes have made it through to other environments, especially production. Let’s deploy some changes to the Test environment. Easy for me in Octopus Deploy. Let’s deploy 4.3.74.

    2016-07-30 17_17_31-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    Once Octopus Deploy does it’s thing, I should see the same changes in the Testing environment. However, when I look at the SimpleTalk_Test database, how can I tell the changes are correct? It’s not easy.

    2016-07-30 17_32_20-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    There are a lot of objects in here, and comparing them piece by piece is a pain. Certainly I could use something like SQL Compare, but do you really want to have to double check your deployments by running another tool? Will you remember to do this?  What if development has moved on, as shown below?

    2016-07-30 17_34_52-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    To run a comparison, I’d now need to track back versions in my VCS.

    However, if you look at the image above, you’ll see that I know what the state of every object in my test environment is. It’s at version 4.2.74. That’s the versions I had in Integration above, and the version I deployed to Test.

    Verification

    If a developer isn’t sure why something doesn’t work in production, or test, or anywhere else, they can look at the changes that were actually deployed, and reference back to the state they worked with in development. In fact, they could even create a new database, and trace back the exact version of all changes in the VCS or CI server, and actually try to reproduce the issues.

    Above all, a developer can tell quickly that if version 73 of the database is deployed, but the code expects version 74, there’s one thing that might need to be checked first: the changes made to the database in v74.

    Hopefully you’ll see there is value in using DLM Dashboard as a developer to ensure the work you complete gets deployed correctly to later environments. If you don’t have an automated pipeline like I do, and DBAs or developers create manual scripts, it’s even more likely DLM Dashboard can help ensure all the correct code is deployed.

    DLM Dashboard is free, and each instance works for up to 50 databases, so download it to day and give it a try.

    Download DLM Dashboard

  • MeasureUp 2016

    I’m heading down to Austin next weekend for MeasureUp 2016. This is a Microsoft stack conference that looks to help people build custom business software.  There are over 20 sessions, most of them devoted to .NET topics. I think I’m more excited to be in some nice company and have the chance to sit in on some interesting talks.

    I’ve got the one database talk, Bringing DevOps to the Database. I’ll be looking at some of the ways in which we can change database development to be more agile, to fit in more with the way that most application developers build code.

    This builds on work I’ve done in the past, as well as a lot of work that Redgate has done across the last 4-5 years.

    If you’re in the Austin area, and you develop software, maybe you want to come down on Saturday for the event. It’s low cost, and the chance to meet with other software developers (and get out of the heat).

    Register today for MeasureUp 2016 and come shake my hand.