Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What are the most useful open source SQL Server projects on GitHub?


By:   |   Read Comments (1)   |   Related Tips: More > Tools

Attend a SQL Server Conference for FREE >> click to learn more


Problem

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?

Solution

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:

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.

github main screen

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.

github project jdbc driver for sql server

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 Projects

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:

Microsoft/mssql-jdbc

The Microsoft JDBC Driver for SQL Server.  It’s shown in the example above.

Microsoft/msphpsql

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

Microsoft/vscode-mssql

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)

Microsoft/sql-server-samples

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.

Microsoft/SQL-Server-R-Services-Samples

Samples for using the advanced analytic capability of SQL Server R Services.

Microsoft/PowerBI-visuals

Documentation for building Visuals for Power BI.

Microsoft/PowerBI-CSharp

.Net tools for interfacing with PowerBI

Microsoft/PowerBI-JavaScript

JavaScript library for embedding Power BI into your web apps.

Microsoft/tigertoolbox

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

Microsoft/SQLSupportTools

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:

Olahallengren/sql-server-maintenance-solution

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.

BrentOzarULTD/SQL-Server-First-Responder-Kit

Brent’s sp_Blitz and other tools for health checks and performance tuning.

Sqlcollaborative/dbatools

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.

ErikEJ/SQLQueryStress

This is Adam Machanic’s tool to generate a test load for performance testing.  I’ve used it several times.

anovick/sql_job_scripter

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

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Andy Novick Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, July 06, 2017 - 7:38:03 AM - Christoph Back To Top

 I think you are missing this one: https://github.com/Microsoft/sql-xplat-cli

Nice CLI tool to export a database to script.

 


Learn more about SQL Server tools