Voice of the DBA

Author: way0utwest

Practical Web Scraping–More Reading

As part of my learning goals for 2018, I wanted to work through various books. This is part of my work with Python.

It’s the end of the month, and I’m behind. I know it’s a short month, but it’s not that short. I had time to read the last couple week and I did some reading, but I didn’t get much practice.

First the positive, then the excuses.

Reading

I did manage to read through the basic tutorial in Part I for how to use he BeautifulSoup module to read through an http page. This provided more ideas for finding sections of code and extracting out sections.

I started Part II, which talks about more in depth http, with forms and posting. I’m still inside that one, and as of this point, I’m only 30% through the book.

The plan for this weekend is to stop and practice a bit from BeautifulSoup and see what I can accomplish by reading a few web pages.

Life Gets in the Way

This month I had two long events on weekends that ate into my reading/working time, as well as a work project. I’m in the middle of volleyball season, and February brought me two 3 day tournaments to coach or watch. One was coaching a 13 year old team, and that occupied a lot of time. The evenings were spent with other coaches and I didn’t bring a laptop because I knew time would be limited. Add to that the need to get a few things done before and after the trip and I couldn’t practice web scraping.

The second trip was for my daughter, and apart from watching her, my wife was in charge of team activities and I had to help. I had my laptop, but again, no time. I also chose to enjoy some time with my wife in the evening, which was worth it.

Work was busy. We’re trying to migrate SQLServerCentral to a new platform and I’ve had to test things, as well as try and get other work done. As a result, I’ve been busy during the week and couldn’t even make time to incorporate any web scraping into my job. I also had to get other work done, so it’s been too busy to do much learning.

March 1, 2019
Schema Security

How many of you grant access to schemas in your databases? I’m curious because I slowly am starting to see more and more people using schemas in their designs and not defaulting every object to “dbo”. That was the default action for many developers for years, and it’s still a habit of mine. Without SQL Prompt to add the schema to my queries, I’d still be producing code that followed that practice.

Schemas exist to allow some separation of different types of objects. In early versions of SQL Server, we didn’t have schemas, but considered the third of four part naming to be the owner. In other RDBMSes, the use of a schema was to allow grouping and separation of different objects. This was also, and still is, a security boundary that helps ensure that authorized users can be easily limited to data access for certain objects.

This is also a nice organizational boundary for related objects that have the same schema, or even name. I have seen auditing or etl schemas that maintain the same named table that exists in a dbo schema, but is separated by placing this in a separate schema. I have even seen some archiving schemas that move data to a related table that exists in a different schema, making it easy for new administrators and developers to find (or remove) older data.

This week, let us know if you manage objects and security with schemas or if you stick to granting access at a database/object level the two more common ways of working with databases. If you have never used schemas and stuck with the defaults, perhaps you want to learn a bit more about how schemas work and the ways in which they may make your work easier.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

March 1, 2019
Pulling the Plug

I’ve worked on some large scale software projects. For the purposes of this discussion, I’ll define a large scale project as one that takes more than a two man-years to develop. I’ve never spent two years on a project, but I certainly have been involved with some that took more than two people, all more than a year to complete to some level. Even while working in a DevOps style environment where we released software every week, I would count a project that followed this process across a year for multiple developers as a large scale project.

When working on a large project, it’s hard to sometimes keep perspective on whether to keep going or stop and change directions. We often try to continue to improve and fix a project, even when it is not going well. There’s a term for this: Concorde fallacy. I’ve seen this similar situation occur pre-Concorde in other industries, who often have made an investment and continue to do so because of the weight of that previous investment on them. If I’ve spent $20 or $20mm on a project and I am evaluating whether to spent an equivalent amount moving forward, I can’t continue to worry about the money I’ve already spent.

That money is gone whether I stop now or continue on. What I ought to do is look forward and decide if future spending is worth the investment. Certainly my reputation, and often some pain for switching or decommissioning existing work is to be considered, but that’s part of the value and too often we become afraid of abandoning something we ought to get rid of for a newer, better something else.

This is discussed a little in the case of legacy technology in this piece about pulling the plug. If you start to avoid maintenance, especially in this day and age of additional regulation, you need to upgrade. If your staff or users are starting to pick other software because you can’t maintain your application, you ought to abandon it and move on. Does some application take too many resources to keep going? Look for something else. In fact, some portion of your staff ought to be making evaluations on alternatives. Not as a full time job, but with some framework to help them perform a cost benefit analysis.

This doesn’t mean pick the shiny new thing or move to the cloud or make everything self-service, but it should be an evaluation of how well you can get software to your users. One of the best lessons of The Phoenix Project, to me, is that continuing to bet on a bad software project isn’t as good as building a plan to get replace it with something better, often something that is built with a more agile, DevOps process. This may not be the cheapest or quickest way to move, but if can often be the best way to change.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

February 28, 2019
Changing the Database Collation–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was testing some changes recently and needed to verify how things worked in a different collation. Here’s the scenario: I had this situation and ran a query.

As you can see, the query rates these two Unicode strings as equal, which makes some sense as this is a Case Insensitive, Accent Sensitive, default US English database.

I then went to the database properties to change this. When I did so, I picked a case sensitive collation.

I clicked OK, and a few minutes, later I get this:

Hmmm, why is this locked on my workstation? I have a query window open. Changing a collation is potentially a disruptive operation and requires an exclusive lock on the database. This means no other users can be inside the database with a connection.

The solution? Change the context of my query window. I can change this to master (or any other database) and make the change again.

While collation isn’t something you often change, if you do find a database that needs this alteration, you will need to drain off any connections before you can make this change.

SQLNewblogger

I ran across this while doing testing and realized this was a nice, short piece of information that I should remember. Not because I do this often, but because as I automate changes and use pipelines, I’d need a way to remove all connections before this would deploy.

An example of a nice short piece of information that I can document about my knowledge, and that might get asked about in an interview.

February 27, 2019