#SQL101: Installing a Baseball Database

Earlier, I wrote about how I installed SQL Server 2016 on my machine.  In this article, I talk about creating and populating a baseball database.

I am a huge baseball fan.  I started following the New York Yankees when I was about 12.  (In an act that is likely anathema to religious fanatics, I married a Boston Red Sox fan; during baseball season, one of us — metaphorically speaking — ends up sleeping on the couch!)  I have been known to schedule vacations around Major League Baseball schedules.  I believe that Cooperstown is Mecca.

Why, you might ask, is this relevant to installing SQL Server?  Because I believe that one of the best ways to learn something is to have fun while you’re doing it.  So in this implementation, instead of installing NorthWind Traders or AdventureWorks (the standard practice databases used by SQL Server enthusiasts everywhere), I will instead install a copy of Sean Lahman’s baseball database.  (At the time of this article, the most recent version goes to and includes the 2015 baseball season.)

I should note that this isn’t to say NorthWind or AdventureWorks isn’t fun; rather, baseball is something about which I’m passionate, and is more likely to keep my attention.  At some point, I’ll likely install NorthWind or AdventureWorks as well, as many SQL references and guides refer to them.

I downloaded the most recent SQL version from Sean Lahman’s website, which left me with a .ZIP file.  Inside the file, I found a MSSQLMASTER.SQL file (you’ll find it in \mysql core\core\) that I extracted from the .ZIP file.  I opened the file with SSMS.

The first thing I noticed was that it created a database called “stats.”  I did not want to use this as my database name, so I first replaced all instances of “stats’ with “baseball”.  Once this was done, I ran the database build query.

Unfortunately, it would not be that easy.  The query file is very large.  When I ran the query, I received the following message.

Okay.  It has a lot of rows to insert (the data includes over a hundred years of statistics, after all).  Instead, I opted for just creating the database and tables.  However, even that proved to be problematic.

“Okay,” I said to myself, “what’s going on now?”

First, I had a permission issue to deal with (this doesn’t appear in the above log; I neglected to include that message).  I looked for the C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA folder.  When I tried to open it, I got a message saying that I had insufficient permissions to open it.  “What?” I said, “this is my own machine, and I’m the administrator!”  However, it also prompted me asking whether I wanted to open it.  I answered yes.  Once I did, I tried running the query again, and the permission error seemed to disappear.

However, that’s when I got the errors shown above.

That’s when I noticed that the filename references MSSQL11.  My database path includes MSSQL13.  Okay.  I changed the path in the filename from MSSQL11 to MSSQL13 and reran the query.  This time, it ran successfully.

I did notice one thing about the table infrastructure.  All columns were defined as NULL, which indicated to me that there were no unique indices or primary keys defined.  All tables would be heaps.  That’s something that I’d need to fix, but that was likely another project for another time.  For now, I was more concerned about building the database infrastructure and inserting the data.

Once my database and tables were built, I commented out the CREATE DATABASE and CREATE TABLE lines from the code, leaving only the INSERT commands.  Just for grins, I tried running the query again, and once again got the “insufficient memory” message.  Okay, no dice.  How was I going to get around this?  I suppose I could’ve selected and run the INSERT queries piecemeal, but that was going to be a long and painful process.

I ran a Google search, and came across my old friend, StackOverflow, where I found this entry.  So I tried the command line, substituting my database name and SQL filename (including the path).

sqlcmd -d baseball -i Downloads\mssqlmaster.sql

Here’s what I got:

sqlcmd: Error: Connection failure. SQL Native Client is not 
installed correctly. To correct this, run SQL Server Setup.

Interesting.  I went to look up SQL Server 2016 Native Client, and found this.  So SQL Server 2016 does not include Native Client.  So, I tried to download and install Native Client.

Of course, nothing ever goes as planned.  I got this message.

So, I went into my Settings to check my Native Client.  It told me that 2012 Native Client was already installed.

After Google-searching my error messages, I realized that I had SQL Server 2008 Express installed, and my SQLCMD was likely getting confused between versions.  That was likely the culprit.  So I went ahead and uninstalled SQL Server 2008, and tried my SQLCMD command line again.  This time, the SQLCMD did not give me any errors, and my command prompt started scrolling with the familiar “(1 rows affected)” message that appears when SQL Server inserts rows into tables.  The script finished without any problem.

Once the script finished, I ran a query to see if my data had made it into the tables.

So it appears that we are good to go!  Our baseball database is populated!

I now have a baseball database with which I can play to my heart’s content.  I’ll be using this to practice my SQL skills — and ‘blog about my adventures (or misadventures, as the case may be) as I do so.

Advertisement

#SQL101: Installing SQL Server 2016

In an earlier article, I discussed how to get started with SQL Server.  I mentioned downloading a copy of SQL Server Developer Edition and installing a sample database.

It had been quite some time since I performed a SQL Server installation (my current job only requires that I know T-SQL, not how to install or implement it; after all, I am a developer, not a DBA).  Additionally, I figured that having SQL Server installed on my own local machine gave me a platform on which I could practice my skills in a safe environment.  With that, I decided that I would shake off the rust and perform an installation on my own machine.  This article documents my efforts.

I’ll start by describing the platform on which I’m installing SQL Server.  I am installing this on my own laptop PC, not a server.  It might or might not be an ideal environment for running SQL Server, but it’s what I have.

• HP ProBook 4430s laptop
• Intel Core i5-2450M processor @ 2.50 GHz
• Windows 10 Pro 64 bit
• 8GB RAM
• 325GB available disk space

I decided that I wanted to keep up with the latest version (which, at the time of this article, is SQL Server 2016), so I needed to find a copy to install on my machine.  To do that, I need to go into Visual Studio Dev Essentials.  Visual Studio Dev Essentials is free, but it does require you to create an account.  If you don’t have an account, you will be prompted to create one when you click “Join Now.”

Once your account is established (or if you already have one), you are redirected to the Visual Studio Dev Essentials page.

SQL Server can be downloaded from the Downloads menu at the top of the page, as well as the Microsoft SQL Server icon under Tools.

I went to the Dev Essentials page, and saw a note that SQL Server 2016 had a critical prerequisite, so I started with downloading and installing the patch.  Select the download that matches your system (for me, it was English-United States x64.exe).  Note that this requires a restart after setup completes.  Once the patch was installed, I went ahead and downloaded SQL Server 2016.  It’s a 2.1GB download.

Once the download finished, I ran the setup.exe program, which brought me to the SQL Server Installation Center.

Clicking the Hardware and Software Requirements brought me to the corresponding page.  Reading through the requirements, I didn’t see anything that would immediately be an issue.  The only thing I saw was that it required .NET Framework 4.6, which supposedly is installed by the program.

I decided to go ahead with the installation.  After clicking the Installation tab, I clicked New SQL Server stand-alone installation or add features to an existing installation.  For the Product Key, I specified the free Developer edition, clicked Next, and followed the subsequent prompts.  I received a warning about Windows Firewall being enabled.  I opened my Settings, went into Network & Internet, and checked my Windows Firewall.  Interestingly, my Windows Firewall would not let me turn it off; it said it was controlled by Norton 360, which is installed on my computer.  This was interesting; I had no idea that Norton 360 was controlling my Windows Firewall.

For the moment, my notification regarding Windows Firewall was a warning, not a failure.  I decided to continue to see what would happen.  I went ahead and clicked Next.  This brought me to the Feature Selection window.

I went ahead and clicked Select All.  I had no idea what features I would use, I figured that I was installing SQL Server 2016 so I could practice and learn about its features, and it would only take 8GB of disk space — considering that I have 325GB available, it’s only a drop in the bucket.

However, when I clicked Next this time, I came across an error.

Interesting.  Should I install the Oracle SE Java Runtime Environment or not?  To answer that question, I asked myself whether or not I needed the PolyBase Query Service for External Data.  For that matter, what, exactly, is the PolyBase Query Service?  I found my answer here.  (Note that this feature is new to SQL Server 2016.)

For the moment, I decided that I didn’t need to work with data in either Hadoop or Azure blob storage, nor did I foresee an immediate need to interface with BI tools.  If I ever come across a need for them, I figure that I could always add it later.  So I unchecked the PolyBase Query Service (for now) and decided to proceed.  (Unchecking this, by the way, brought my disk requirement down to 6.5GB.)

I went with the default named instance (MSSQLSERVER).  In fact, I pretty much went with the default configurations (the one exception being selecting Mixed Mode instead of Windows authentication mode; I did add myself as a SQL Server administrator, as well as other services configurations).

I didn’t think to keep track of how long it took to install SQL Server 2016, but I would guess it took about an hour.  SQL Server installed with no further issues.

SQL Server Management Studio 2016

For as long as I can remember, SQL Server Management Studio (SSMS) was included as a standard part of installing SQL Server.  That is not the case with SQL Server 2016; SSMS must be installed separately.  Clicking Install SQL Server Management Tools in the SQL Server Installation Center takes you to this page.  Because SSMS tends to be my go-to tool for using SQL Server, I wanted to make sure it was included with my installation.

I downloaded the SSMS .EXE file, which is 825MB.  Installing SSMS is straightforward; click Accept to accept the license agreement, and the package installs.

Other SQL Server 2016 Tools

The SQL Server Installation Center also includes support for SQL Server data tools (SSDT) and for a new R server installation.  For now, I am bypassing these tools; I will likely install them at a later date.

After all was said and done…

Once everything was installed, I opened SSMS, which ran successfully.  (One thing I’ll add is that SSMS now looks like Visual Studio, which makes sense since it is powered by the VS engine.)

I now have SQL Server 2016 installed on my machine.

What’s next?

I will be implementing Sean Lahman’s baseball database into my SQL Server.  My exploits will be documented in a future ‘blog article called “Installing a Baseball Database.”  Stay tuned!

Ben Franklin had it wrong

“Ch-ch-ch-ch-Changes, turn and face the strain”
— David Bowie

“Change changing places, root yourself to the ground; word to the wise, well you get what’s coming; one word can bring you round: changes”
— Yes

When I was in grad school, I once wrote this in a paper for class.  (It’s been a long time since grad school, so I’m paraphrasing a little here.)

“Ben Franklin had it wrong.  There are not two sure things in life; there are three: death, taxes, and change.”

My professor loved this; in fact, he told me he might make use of this quote himself.

The fact is, change is inevitable (except, as the old joke goes, from a vending machine).  Change is going to happen.  A lot of people say how much they don’t like change, and a lot of people are resistant to change.

Let me say it again: change is going to happen.

I understand that people don’t like change — heck, I often don’t like it myself.  But being resistant to change is futile.  Change is like dying — it’s going to happen sooner or later, and there’s not a thing you can do about it.

I really think that the people who are the most successful in life are those who accept and adapt to change (whether they like it or not) — roll with the punches, if you will.  This is why I, as a technologist, need to keep up with technological trends.  As much fun as I had working with Windows 3.1, OS/2, or Pascal, that knowledge does me almost no good today.  I’ve practically made a career out of being able to adapt to my environment; it’s what has allowed me to survive in some of my working environments for so long.

Examples of adapting to change are everywhere.  Advancements and adjustments in knowledge about the changing world around us leads to improvements in science, engineering, and medicine.  An aging pitcher who can no longer rely on his fastball (as a Yankee fan, CC Sabathia comes to mind) adapts by throwing more effective cutters or breaking pitches.  People change their attitudes, behaviors, beliefs, and so on.

Think about it.  Where would we be if change didn’t exist?  We’d probably still be living out of caves and hunting for food with wooden spears.

Take a look at what’s coming down the pipe.  You might not like it.  But if you want to survive, most likely your best bet is to accept it, deal with it, and move on.

Mark your calendar: SQL Saturday #513, Albany, July 30

The calendar for SQL Saturday #513, Albany, NY is out.

And I’m on it!

I’ll be giving my presentation, “Whacha just say?  Talking technology to non-technical people.”  This is the same presentation I gave in Albany last year, and I’ve made some improvements since then.  I’ve also given this presentation a couple more times since last year, and have gotten great reviews for it!

Hope to see you there!

Two books that influenced my life

“Darkness cannot drive out darkness; only light can do that. Hate cannot drive out hate; only love can do that.”
— Martin Luther King, Jr.

“Hold me now; it’s hard for me to say I’m sorry; I just want you to stay…”
— Chicago, “Hard to Say I’m Sorry”

I play the piano in a Catholic church every Sunday morning.  (I consider myself spiritual, not religious.  I have some thoughts regarding my own faith and beliefs; this might be another article subject for another time.)  This morning’s scripture and homily had to do with forgiveness.  As the good Father was going through his homily this morning, for whatever reason, two books that influenced my life suddenly came into my mind.  While these two books are only partially related to forgiveness, they nevertheless made me think about how life should be lived.  I credit these books with teaching me about life’s lessons and helping me grow.

The first book is How To Stop Worrying and Start Living by Dale Carnegie.  Although Dale Carnegie wrote this book back in 1948, all the principles about which he wrote are still applicable today.  Anyone who’s taken the Dale Carnegie training courses will recognize the principles outlined in the book (I, myself, have taken the Dale Carnegie course; if you have an opportunity to take the course, I recommend it highly).  In his book, he talks about how worry can make you unhappy, adversely affect your health, and cause stress in your life.  I’ll admit that I don’t always stick to his principles (I’m human, after all), but his principles make perfect sense, and they make for a good fallback whenever things aren’t going my way.

The second book is Tuesdays with Morrie by Mitch Albom.  When Mitch discovers his old mentor, Morrie, is dying from Lou Gehrig’s disease, he makes it a point to visit him each Tuesday (hence, the book’s title) until Morrie succumbs to the disease.  With each visit, Mitch’s old mentor teaches him about life’s lessons, leaving Mitch a changed man.  It’s a good read that provides a good perspective about what life is about.

These are two books that, I believe, can make the world a better place, and I recommend them highly.  Hopefully, they’ll influence you the way that they influenced me.

SQL Saturday #517

Hi folks . . .

I’ll be on the road today.  I’m traveling down to southeast Pennsylvania for SQL Saturday #517 in Philadelphia, where I’ll be giving my presentation, “Disaster Documents: the role of documentation in disaster recovery.”  Come check it out!

See you on the road!

The importance of accepting critical feedback

A few weeks after giving two presentations at SQL Saturday #526 in Rochester, I received my session evaluations.

I was quite happy with my evaluations for my presentation on how to talk to non-technical people.  I received mostly positive reviews and high scores.

My disaster recovery presentation? Not so much.

My presentation got low scores.  Some of the comments I received included, “needs significantly more actionable & useful take-aways,” “attendees need to leave with action items, ” “very little in the way of reputable facts,” “needs to be updated for 2016,” and “paper is not the solution to documentation anymore” (my presentation was about the importance of documentation in disaster recovery, and this was a point of emphasis — more on that in a bit).

However, despite the negative feedback, it was that on which I focused — not so I could sulk, plot my revenge, or wallow in despair, but rather, so I could improve.  The fact is, the feedback I received was not what I wanted to hear.  It was what I needed to hear.

How else was I going to get better?

It seems like such a simple concept.  You want to get better at something.  You ask people to tell you what’s wrong.  Ideally, when people do tell you what’s wrong, you go back to fix it.

Unfortunately, we don’t live in an ideal world.  Too often, people who are told what’s wrong react adversely, sometimes violently.

Why?

The fact is, people listen to what they want to hear, not what they need to hear.  More often than not, people go looking for feedback, but instead of paying attention to red-flags that need to be addressed, they keep cycling through until they find feedback they like and justifies their position(s).

This is human nature.  It’s also a recipe for disaster.

If you don’t believe me, do a Google search on “groupthink.”  This mindset of ignoring negative feedback defines one of groupthink’s major symptoms.  And if you don’t believe that this type of thinking is destructive, look up the history behind the Bay of Pigs Invasion and the Challenger disaster.  Researchers cite these as prime examples of groupthink, and one of the major contributing factors in each of these disasters is the willful ignorance of certain facts — negative feedback that the people involved desperately needed to hear and address.  It is a phenomenon that impairs quality decision-making and incapacitates our ability to improve.

I am no expert in psychology, and I cannot adequately explain why people are so averse to feedback.  I’d guess that it might have something to do with the fact that people avoid things that are unpleasant.  It’s like experiencing pain.  If you feel pain, you need to find out what’s causing it and address it.  Pain is feedback.  We don’t like it, but we need to address it to improve.

As for my presentation, it includes a section where I list “takeaways from the experience.”  I went into my slides and reworked the section.  Instead of simply listing what I learned, I reworded the points as action items for the audience.  As part of those action items, I wrote them in a way that could be applied to today’s technical environment.  And as for the comment about “paper no longer a solution,” I realized that the person had a point (although I didn’t completely agree with him) and came up with a compromise that should satisfy both opponents and defenders of paper.

Hopefully, my next session evaluation will be better than the last!

(Note: The “next time” is coming up very quickly; I will be giving this presentation this coming Saturday, June 4, at SQL Saturday #517 in Philadelphia.)