Tag: software development

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

  • Running Multiple Threads Quickly

    Recently I was looking to generate a quick workload against an instance. I wanted to add to the amount of work my system was doing by executing a number of queries simultaneously. There are a variety of ways to do this, but I just wanted a quick set of queries. I’ll show a couple ways to do this and then look at a few other tools in later posts.

    The Cumbersome Way

    I can open queries in two windows in SSMS. Note, each of these will execute 50 times.

    2016-05-20 14_17_34-SQLQuery1.sql - 192.168.1.71.SQLServerCentral (sa (56))_ - Microsoft SQL Server

    Now I have two windows, and I can click execute in one, then click to the other, and click execute again. That’s easy. When I do this, I’ll have two threads, each running a query 50 times.

    A Better Way

    A better way is to use a SQLCMD call with my query in it. In this case, I’ll create a notepad file and add multiple SQLCMD calls in it.

    2016-05-20 14_29_03-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    The key here is the “start” at the beginning of the line. This will spawn a new thread with the program being called in it. In this case, I’ll get 5 windows very quickly, each running my query. My query is in another file:

    2016-05-20 14_28_19-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    If each query is set to run multiple times, I’ll have a simple load generated. In my case, I’ll run the .CMD file from the command line, but I could double click it. When I do, I see this:

    2016-05-20 14_30_13-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    You can see the window where I started the queries in front. Three of the command windows are in the background, each of them running queries over and over. The output from the query, with all the dashes for spacing between the headers and data, are in each window.