Docker And SQL Server

July 29, 2018

Containers are a way to virtualize a single application, as opposed to virtualizing an entire server. We can run an application in this container without installing the product or worrying about all of the dependencies of that application. Everything needed to run an application is in the container.

Docker is the most popular platform for running and managing containers.

Prerequsites for Installing Docker:
To install Docker on Windows, we’ll need to have Microsoft Windows 10 Professional or Enterprise 64-bit(You can also run on Windows Server), we need a version that will support virtualization. We also need virtualization enabled on the machine (You’ll need to go into the BIOS to enable this).
We’ll need Hyper-V and Containers features enabled as well. We can do this in the Control Panel, or use Cortanta to search for ‘Turn Windows features on and off’. Make sure the Containers and the Hyper-V items are checked. The Docker installation will check to see if these features are enabled, and will give you the option to enable these features then, but setting these before hand will save you a reboot.
Notes that if Hyper-V is enabled, then Virtual Box will not work, if you have that installed.

Installing Docker:
The Docker Community Edition for Windows is a free download, but you’ll need to set up a Docker login.
The install is pretty straight forward. For my example, I checked to Use Windows containers instead of Linux containers.

Basic Docker Commands:
We can use the Command Prompt or the Powershell Window to run Docker command, I used Powershell for these examples.

docker --version

: This will return the version and build number for the Docker install.

docker --help

: This will give us a list of available Docker commands. We can also use this to get additional information on a specific command, for example if we wanted more information on the version command, we would run docker version –help.

Sample Docker Container:
We can run a sample Hello World container just to make sure everything is set up on running correctly. We can run the command:

docker run hello-world

This will pull down the Hello World image and run it. If everything is running correctly, you’ll see a message ‘Hello From Docker!’. If things aren’t set up correctly you’ll see an error message.
Initially I got an error:
“container xxx encountered an error during CreateContainer: failure in a Windows system call: No hypervisor is present on this system.”
I had let the Docker install enable the Containers and Hyper-V components for me. When I went to check on the options, The Hyper-V Platform option under Hyper-V was greyed out with the message “Hyper-v cannot be installed: Virtualization support is disabled in the firmware”. It turned out that Virtualization was turned off on my laptop, which prevented this option from being enabled. I didn’t get an error during the install when it trying to enable these features, and it took me a while to figure out the issues. So I would recommend running through the prereqs yourself, instead of relying on the install to do this.

Other Docker Commands:

docker images

: List all of the installed images.

docker rmi hello-world -f

: To remove an image (hello-world). The -f flag will force removal.

docker container ls

: List running containers

Running SQL Server:
Now to a more practical use. We can run SQL Server in Docker. A container for SQL Server Developer edition is available through Docker Hub . We can view some information on the container here.
We can run this command to download the container:

docker pull microsoft/mssql-server-windows-developer

The download is 4-5 gigs.
Once we have pulled the container down, we can start it up:

docker run -d -p 1433:1433 -e sa_password=WeakPW12 -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

It will take a few seconds to start up. Once the container is running, we’ll see an ID printed to the console. Make a note of this ID.
So there are a few parameters that have been set in the run command. -d means detached, which means that the container runs in the background. -p (for publish) designates the port for the service, we’ll stick with the SQL Server default of port 1433. We list two ports, one for the container and another for the host. -e will set an environment variable, in this case we’ll indicate that we accept the user agreement. We’re also using -e to set the sa password for this session. At first, I set a very simple sa password, and then I had issues trying to connect to SQL Server. When creating the sa password, you’ll have to follow the complexity requirements for a sa password, you won’t get an error message for an invalid password. The sa password has a 8 character minimum, plus you’ll need at least 3 from upper case letters, lower case letters, numbers and special characters.
We can connect to our SQL Server container either with the SQLCMD command line tool, or with SSMS. For this example, we’ll use SSMS.
To connect with SSMS, we’ll need to get the IP address for our running SQL Server. Using the ID we got when starting the container, we’ll run this command:

docker inspect {ID}

You can also use the 12 character Container ID. We can get that ID by running:

docker ps

In the output, we’ll go to Network Settings / Network / IP Address , and make a note of this address.
To connect with SSMS, we’ll use the IP Address as the Server Name (include a comma then the port number if not using the default port). Select SQL Server Authentication, then use sa and the sa password we passed in when starting the container.
Once we’ve connected, we can use SSMS to create objects, or open a query window, just as we would with any SQL Server.

Once we’ve completed, we can stop the container passing in the Container ID for {ID}:

docker stop {ID}

Once we stop the container, we’ll lose whatever objects and data that we’ve set up. It is possible to map the container to use data and log files on the host machine, this page goes through the setup for that.

Get started with Docker
Windows Containers
Docker Documentation
SQL Server 2016 Express Edition in Windows containers

Degree Completion

November 30, 2016

This post won’t be a technical one, but I recently went back and completed my bachelor’s degree, 29 years after I first entered college. I wanted to put together a write up of the path I took to complete, in case this information was useful to anyone else trying to complete their degree.

My Educational Background
Out of high school, I attended two different schools for 2 years each, so I had plenty of credits built up. I studied political science at the first school and philosophy at the second. I also had some accounting classes from a local tech school as well. To be honest, I was looking for the shortest route to completion, as I was working full time and was already deep into my career. Most schools have a residency requirement, meaning that you need to complete the last 1-2 years or so of your education at that school in order to graduate.

Selecting A School
Reputable US schools will have a regional accreditation. Georgia is covered by the Southern Association of Colleges and Schools. Most schools will require a school to be regionally accredited in order to accept transfer credits from them, or to enter a graduate program. So I was looking for a school with regional accreditation.
I also hoped to find a school that didn’t have a residency requirement, so that I would be able to use as many of my previously earned credits as possible, as well as shorten that amount of time I would spend in completion.
I ended up finding two schools that met these requirements and applied to both of those:
Excelsior College
Thomas Edison State University (TESU)

When I applied, the schools gave me a rough idea of which classes would be accepted as transfer, as well as which category they would be credited as. Once I enrolled at the school, I got an official evaluation of my credits.
Thomas Edison offered to transfer more credits so I enrolled with them.
I later learned of a third school that only requires two classes to be taken at their institution: Charter Oak State College

Once I enrolled at TESU, I met with a guidance counselor to go over my requirements. Everything laid out in my initial evaluation was approved. I had to pay an enrollment fee good for one year, as well as a fee for each class taken.

Selecting A Major
My credits were spread across several areas in the liberal arts, so after checking Thomas Edison’s requirements, I was the closest to earning a Liberal Studies degree.

Earning Credits
There was one Capstone class that I had to take at TESU, but I was free to earn the rest of the credits any way that I could, I didn’t necessarily have to take them from the school.
I had most of my pre-requisites completed, but one good resource for these classes is Georgia’s ECore site.
I ended up taking several classes from a third party company, Straighterline. Here you pay a monthly subscription fee ($99 when I took classes) plus a fee per class($50 or so). You take classes at your own pace, taking as long as you want or working through as quickly as you like. Each class used a textbook, which were easily bought used online. Readings in the text were assigned, and each module had a quiz that was administered online. Most classes had a midterm as well. Each class had a final exam, which was proctored by a third party company. You would take a final exam at home, but the proctor would monitor you via webcam as you tested, as well as monitor your desktop while you tested. You would schedule a time with the proctor, setup with your assigned proctor, and then take the test. Everything was graded immediately. Their site posted a list of all of their classes that TESU would accept, along with the class that it would be credited as, which was very useful in planning. Once you pass a class, you can have StraighterLine send a transcript to the school. Even though you are given a letter grade for the class, the credits will be transferred in as pass/fail. I found this the easiest and cheapest way to earn credits.
There are also several ways to earn credits by exam. For these, you’ll travel to a testing center to take the exam. The testing centers are mostly colleges that open their testing center to outside students (not all schools allow that). I took exams from two agencies:
DSST(formerly known as DANTES)

Both sets of exams were under $100 an exam. As long as you receive a passing grade, you will receive credit. Since the exams are pass/fail you don’t get a letter grade, it will just show up as credit on your transcript.
TESU publishes lists of the exams that they accepted for credit, and the class that you would get credit for.
To study for the exams, I used a site Instacert to prepare for the exams. They are a subscription site ($20 a month when I used them) that have practice questions with answers, I found them very useful. The site Free CLEP Prep also had links to other resources as well.
The exams were tough, but with a good bit of studying I was able to pass every attempt. I stuck to areas that I already knew very well, like history.
TESU and Exclesior also offer their own sets of exams for credit, but I didn’t take any of those.
Most exams are credited for lower level (100 or 200 level) classes. I did find a few that would transfer in for upper level credit. The History of the Vietnam War DSST exam was one, as well as the Introduction to Religion StraighterLine class.

After filling in my requirements, I was left with one class to take at TESU, their capstone class. Here, the sole activity of the class was to write a research paper, kind of a mini-thesis. We were free to select any topic in our major area. Computer Science fell under the Liberal Studies umbrella here, so I was free to select a programming-related topic. Each class has a professor assigned, who will help to formulate a topic. Every two weeks I would submit a chapter and would receive feedback on it. At the end of the class we would submit the full paper. It wasn’t a typical research paper, a lot of the writing was about my research methods as well. The paper had to be at least 25 pages, not including the bibliography and other supporting writings. A minimum grade of ‘C’ was needed.

Before the class was over, I was eligible to go ahead and apply for graduation, of course pending on getting a high enough grade. Once I completed the class, I was eligible to graduate in September of 2016.

SQL Code Guard

March 13, 2015

I ran across a free SSMS add-in SQL Code Guard. The add-in can run analysis on scripts to detect possible code issues, as well as display the dependencies for a database object. The add-in is also available through Redgate(along with some other free add-ins).

After downloading, unzip the file and run the EXE. The add-in will be installed in SSMS(Any version from 2005 onward). Both Chrome and Norton identified the file as a possible risk, since it doesn’t have a large number of downloads. Also, I needed to add a line to the SSMS.exe.config file under configuration/runtime(otherwise SSMS takes a long time to open).

Add-Ins: This is just a link to the Redgate site to list the other SSMS add-ins.
Run Analyze: This was the most useful function to me. This will analyze a script (or database object) for possible issues, things like an object reference without a schema name, an asterisk in a SELECT, deprecated SET options, etc.
Show Dependencies: Once a database is selected in the Object Explorer, this will list each database object with the objects it depends on as well as objects that refer to it. For a table, you can see the referring objects broken down by insert, update and so on.
Code Outline: This will break down a script into the operations that it performs. For example, if a table is being created, the outline will show CREATE TABLE dbo.Table. Clicking on that item will take you to that section of the script. So if you have a complicated script, this will help you to navigate to certain operations.

Database Unit Testing With tSQLt

February 25, 2015

Most .Net developers have probably written unit tests to test their code. Once tests are in place, refactoring and adding functionality can be done with more confidence, because the coder will be able to demonstrate that a given method will still function correctly. However, these unit tests remove the dependency on the database, so we still would want to set up tests for the database and database objects. Unit testing a database proves to be a little more difficult, since the data can change so frequently and we can’t count on the data to be in a given state.
tSQLt provides a framework for creating and running database-level tests. The tests are set up in T-SQL, so database developers are still able to work with the language that they’re most efficient in.
This framework will provide a way to create stored procedures that will test specific aspects in the database. The framework can execute all of the testing procs and alert us to any errors that arise. tSQLt will use transactions for the tests and will roll back any data changes, so the user can re-run tests without collision.

The tSQLt home page provides a link to download a set of tSQL scripts that will set up the testing framework. On the testing database, run the SetClrEnabled.sql first, which will set the database to TRUSTWORTHY and will enable CLR functions to be executed. The tSQLt.class.sql will set up the testing framework. The package also includes a Sample.sql script with some sample code.

Sample Database:
There are two scripts on GitHub: One to create tables, stored procedures and data for a sample database and a second to create the testing procs.

There is plenty of documentation on The tSQLt site on using the framework. In the UnitTest_Tests.sql script there are some simple examples.
First, we create a test class that will server as a namespace for the testing procedures. This will create a class TestClass.

exec tSQLt.NewTestClass 'TestClass';

Then we’ll create our stored procedure to test code. We’ll use the test class we created as a namespace for the proc.

create procedure TestClass.[TestDB_InsertPlayer]

Within the testing proc, we’ll use an assertion to compare the data we expect to the actual data. In one case, we’re testing an insert proc, so we’ll create the table as we expect it to the actual table. Within the test class, we’ll create an ‘Expected’ table with a schema that matches the table we’re testing. It will be compared to the ActualTable. If they don’t match, then the framework will throw an exception that will let us know the rows that don’t match.

exec tSQLt.assertEqualsTable 'TestClass.expected', 'ActualTable';

In other cases, we may have a function or a procedure that returns a value. We can compare that actual value to the expected value with an assertion.

exec tSQLt.AssertEquals @ExpectedValue, @ActualValue;

The tSQLt User Guide lists other assertions that can be used.
Finally, we execute all of the tests within our test class:

exec tSQLt.Run 'TestClass';

The tests won’t be executed in any particular order, so we can’t arrange the tests that depend on changes made by another test(the data changes are rolled back in any case).

Redgate GUI:
Redgate has created a GUI called SQL Test that provides a test runner user interface for the tSQLt framework. It is a paid application, but there is a 28 day free trial available.
I haven’t used the SQL Test program, but Troy Hunt has a good post on using it.

Work Sabbatical and Continuing Education

January 28, 2015

Over the Summer of 2014 my employer decided to close down their US Office. After several months of transitional work, I decided to take some time away from the work force and pursue some other goals. Some of those goals involved continuing education.

My biggest goal was to complete the Microsoft certification for SQL Server (MCSE: Data Platform). I wrote about that experience a little more in depth here. I took the upgrade path since I already held a SQL Server 2008 certification. There aren’t many books or articles that specifically address the upgrade exams, so in studying for the exams I relied heavily on MSDN and some posts on the regular track exams.

A lot has been written over the last year on Hadoop and big data, so I wanted to get some hands-on experience with this platform. I found a class on Udacity that covered the basics of Hadoop and Map Reduce. The course is available for free, but I decided to take the paid version which included a final project and a verified certificate upon completion. The Map-Reduce functions were written in Python, so I got an introduction to that as well. The class consisted of video lectures with quizzes to gauge your progress. The course took 20-25 hours to complete (you can work at your own pace) including a final project where you write functions and process some data. To pass, you’ll meet with a reviewer online who will go over the project and ask questions about it before you are verified.

Data Science:
I took a series of classes from Coursera on Data Science. This was 9 classes plus a project. The classes could be taken for free, but I signed up for the paid track, which gave you a verified certificate for each class, and then a specialization certification if you successfully completed the final (capstone) project. The classes covered R programming, machine learning, statistics and regression analysis. Each class was meant to run over one month, about 20 hours to complete. You could work at your own pace but there were deadlines to meet each week to keep you on track. The final class was over 7 weeks (you had to complete the first 9 classes first) where we constructed a web page and created an algorithm to predict the next word for a given set of words.
I found the R programming fairly easy to pickup (a lot of it is set-based like SQL) but the statistics classes were very challenging to me. I got a lot out of this specialization. None of the classes carry any college credit, but they are sponsored by John Hopkins.

Currently I’m taking a MongoDB for Developers class from MongoDB. I’m taking the Python class, although a .Net version will be available starting in March 2015. This is a free class that goes over 7 weeks. Each week the assignments are released, so you can work on each week’s assignments at your own pace, but only one week at a time. So far, it’s taken me 4-5 hours a week to watch the video lectures and complete the assignments. The 7th week is a project that counts as the final exam.
(Updated 4/8): I completed the course in February. I certainly recommend the course to anyone looking to learn about MongoDB.

College Coursework:
I never finished my degree when I went to college out of high school so I’ve looked into getting back on track to complete that. I’ve taken and passed a CLEP exam in Sociology, and I’m currently taking a class through Straighterline which can be transferred to a degree-granting institution.
(Updated 4/8): I ended up taking 4 classes from Straighterline. Their final exams are proctored, but you can take them at home, and have a proctor monitor you via webcam. They offer a very convenient way to take classes. I’m also taking some DSST exams(similar to CLEP exams) for some upper level credit.

Non-Educational Activities:
I took up running a couple of years ago, so I’ve been able to dedicate more time to that. Currently I’m training for a marathon and I’m targeting the Publix Georgia Marathon in March 2015.
(Updated 3/22): I completed the marathon, so now I’m looking forward to the Peachtree Road Race on the 4th of July.

E-Books, Podcasts and NoSQL

January 12, 2015

Some database odds and ends:
1) The DotNet Rocks podcast recently had an episode with David Simons. He went over 10 different database systems, ranging from relational to NoSQL. Slides from his presentation are available on Slideshare.

2) A couple of free E-books – Free but they requires setting up a login or filling out your contact information:

A. Time Series Databases: New Ways to Store and Access Data by Ted Dunning and Ellen Friedman
An intro to storing Time-Series data (recording a value over a period of time)

B. MarkLogic – Enterprise NOSQL For Dummies
A basic overview of NoSQL and how it compares to a RDBMS

C. Some database-related books from Syncfusion – These are 100 page books (PDF or Mobi) that are great introductions to a topic:

3) I’ve added a Database Systems page to list and link to open source relational and NoSQL database systems. I’ll update this list periodically.

Enable Directory Browsing Of GAC

August 2, 2014

I wanted to include some DLLs in a package, I found a trick to allow you to get those DLLs from the GAC from Geeks With Blogs

In RegEdit, navigate to:

Add a DWORD (32-bit) value ‘DisableCacheViewer’: decimal value – 1

Then navigate to the GAC at c:\Windows\Assembly to find the DLL.