What are the most useful open source SQL Server projects on GitHub?
The world of open source software is booming and much of it lives on GitHub. For someone who’s been living in the SQL Server world for a long time this is new. What SQL Server projects can be found on GitHub and how does one get to them?
GitHub is site for hosting projects that use the Git source control system. Git was developed to support development of Linux and is different than earlier source control systems because it doesn’t rely on a central repository. Instead it uses a distributed repository. Sooner or later work must be committed to a central repository so it can be shared. That’s where GitHub comes in. It acts as the central site for many open source and closed source projects. Open source projects can be hosted on GitHub for free. If you’d like to host a private project on Git there are fees associated with that. In this article we’re going to look at some of the open source projects that are relevant to SQL DBAs and developers.
Lets’ take a look at what you’ll see on GitHub. We’ll use the Microsoft JDBC drive project to illustrate. When you first look at each project you’ll see the GitHub banner. Github uses a combination of octopus and cat as a logo that they call Octocat:
At the top of each screen you’ll see the silhouette version and the main menu for the project. If you’ve created an account and uploaded a profile picture a miniature version of your profile is on the right had side along with notifications indicated by the bell. The + sign lets you get to repository level actions.
The top of the screen is a summary followed by a list of the files. The green “Clone or download” button is the easiest way to access the projects code and download to your desktop, Open in Visual Studio or just Download a ZIP with the current project files.
Below the file list you’ll find several colored links. The most important one is to the license. You may have heard about the General Public License or GPL. It can be quite restrictive, requiring anyone using GPL software to contribute any changes back to the public. GPL isn’t the only license that is used by open source projects. In fact Microsoft usually uses the MIT license which is much more permissive, requiring only that the copyright and permissions notice be reproduced with any project or product that includes the software. This allows MIT licensed projects to be included in bespoke applications and commercial products. If there isn’t a licensing link on this page there’s usually a LICENSE file with the license terms as plain text. If licensing is a worry, you should consult with your attorney for advice.
Below the links you’ll find the description of the project from the README.md file. Additional information about the project may be found on the WIKI tab. If you want to ask a question about the project or see the questions that others have been asking, you’ll find them on the issues tab. You can also see the updates made to the project on the “Pull requests”
Microsoft hosts many of their open source projects on GitHub. You can find them for searching from the home page for “Microsoft/”. Here are some that are relevant to SQL Server DBAs and developers:
The Microsoft JDBC Driver for SQL Server. It’s shown in the example above.
PHP drivers for SQL Server
Microsoft/Visual Studio Code
This is the source code for the Visual Studio Code tool that Microsoft makes available for Windows, Linux and Mac. You’d start with the source code if you’re curious or if you wanted to port VSC to other platform. To download a usable version of VSC use the link https://code.visualstudio.com/Download
If you’re going to use Visual Studio Core with SQL Server you’ll want to add the Visual Studio Extensions for SQL Server. This adds features to Visual Studio Code for connecting to SQL Server and Azure SQL Databases and to write and execute T-SQL code. Microsoft is putting a lot of effort into this project and it’s reached general availability (GA)
This includes sample databases, such as the WorldWideImporters database. If you haven’t heard, the AdventureWorks Bicycle Company, which provided the data for so many sample databases, has gone out of business. Their project to create the autonomous bicycle proved to be too expensive and they’ve been forced into bankruptcy. In their place, Microsoft is providing the WorldWideImporters and you can download .bak or .bacpak versions to be used on-premises or in Azure SQL Server.
The project also includes various sample code for SQL Server such as the samples from the “SQL 2016 It Just Runs Faster” series and samples for Extended Events.
Samples for using the advanced analytic capability of SQL Server R Services.
Documentation for building Visuals for Power BI.
.Net tools for interfacing with PowerBI
“As-is” solutions including tools/scripts from the Microsoft Tiger team that supports SQL Server. You find just gems as the Waits and Latches scripts to identify where your system is using Waits and Latches. A script to fix VLFs and compression analysis tools. I’m going to be spending a lot of time on this one.
The CSS Team’s tools such as PSSDIAG, SQL Nexus, and the RML Utilities
This is just a small sample of the projects that Microsoft has hosted on GitHub. There are quite a few more and the existing ones are frequently updated. Microsoft isn’t the only source for SQL Server projects on GitHub. There are many members of the SQL community that have contributed projects.
Projects from the Community
GitHub hosts thousands of projects that involve SQL Server. Some are good database examples. Many are tools for DBAs or developers. There are many Visual Studio extensions that can be used or used as an example. Here are some of the projects that you might want to consider first:
One of the most widely used maintenance libraries is from Ola Hallengren’s. It supports versions of SQL Server from 2005 to 2016, and includes backups, Integrity Checking, and Index and Statistics Maintenance. These maintenance scripts have been available from https://ola.hallengren.com but are now on GitHub.
Brent’s sp_Blitz and other tools for health checks and performance tuning.
A group of developers have created this library of PowerShell modules for managing SQL Server. If you’re a PowerShell user this library has many commands that a DBA can use to automate their job.
This is Adam Machanic’s tool to generate a test load for performance testing. I’ve used it several times.
Script out jobs so they can be added to source control. I wrote about moving this project from CodePlex to GitHub in this article: How to move a project from CodePlex to GitHub
- Browse Github at http://www.GitHub.com . You don’t even need to create an account. Start with search-terms like: mssql, sqlserver, sql
- Watch the GitHub intro video https://github.com/features
- Read the introductory guide https://guides.github.com/activities/hello-world/
- The NSA has released at trove of their projects as open source on GitHub. I bet you there’s something interesting in there: https://nationalsecurityagency.github.io/
About the author
View all my tips