Getting Started with SQL Server (Pt. 1 – Downloading & Installing)

Table of Contents
    Add a header to begin generating the table of contents

    Introduction

    This is the first tutorial in a series of three tutorials which aim to give students a basic working knowledge of Microsoft SQL Server. This tutorial series is based around the underlying concept of analysing football data and as such does not aim to cover all aspects of SQL Server as a result.

    This first tutorial aims to introduce the SQL Server database software itself and then walk students through the process of downloading and installing all necessary software onto a Windows PC.

    Prerequisites

    This tutorial requires a modern, internet enabled Windows PC with a web browser installed, most people will already have such a set-up.

    What is MS SQL Server

    Microsoft SQL Server is classified as a relational database management system (RDMS) – this means it is a piece of software used for effectively storing, managing and retrieving data on a computer system.

    There are many different database systems on the market but SQL Server is one of the most popular with many of the worlds biggest organisations using it for their day-to-day operations. SQL Server is also an especially powerful database due to Microsoft’s own implementation of the SQL programming language which brings many additional features used for processing data, advanced reporting and managing of the server itself.

    NB – We will be looking at this SQL Language in more depth later on in this series

    Despite offering a massively powerful set of data storage and management features one of the best things about SQL Server is that it’s completely free to use for personal use. This means we can, with a little learning about how the system works harness the power of this enterprise level software and use it to analyse our own football data.

    Preparing for and Downloading MS SQL Server

    During this tutorial we will be installing and configuring Microsoft SQL Server 2017. As one of Microsoft’s most prominent applications they regularly update SQL Server and you will see a new version released usually every 2 years or so. If you are reading this tutorial and the latest version is higher than 2017 please rest assured the process we are following should be more or less the same and the main differences will probably be that the interface might look slightly different.

    When installing SQL Server we also have to be aware of the different editions of SQL Server which are available, at the time of writing these are:

    • Enterprise
    • Standard
    • Developer
    • Express

    The Enterprise and Standard versions are for commercial use and as such require a licence to use, since we are analysing data for personal use we can make use of one of the two free versions (Developer and Express).

    The Developer version is a fully functional version of SQL Server which contains all the available features of SQL Server and is completely free to use. Although free to use it is strictly for non-commercial use and a license must be obtained if you ever plan to use your database software as part of a commercial application!

    The Express edition is a cut down version of SQL Server aimed mainly at driving small websites and desktop applications. This edition is free to use and can be used behind commercial projects without a licence however, it is missing some useful features found in the developer edition making it of limited use in some projects.

    Although we could go a long way with the functionality of the Express edition we will be using the Developer edition for the remainder of this tutorial. This is because some of the more advanced data analysis features are missing from the express edition and since this tutorial intended for personal, non-commercial training the licensing should not be a problem.

    The steps below are for finding and downloading SQL Server 2017 Developer Edition:

    Step 1 – Checking our PC is capable:

    In order to install SQL Server we must first check that our PC meets the requirements for doing so, these requirements are:

    • A Minimum of 4GB RAM
    • A 64bit CPU running at a minimum of 1.4GHz
    • A Minimum of 9GB free disk space

    If you are not too PC savvy don’t worry about these specifications too much! Most modern PCs and laptops will have at least 4GB of RAM, a 64bit processor and much more than 6GB of usable storage however, it is worth double checking if your PC is an older machine!

    We might also need to have some other software installed at this point, including the Microsoft .NET framework amongst others. These can be installed automatically during the SQL Server installation itself so there is no need to worry about them right now.

    Step 2 – Downloading SQL Server 2017 (Develop Edition):

    The next step is to download the SQL Server 2017 installer program to our PC, this can be found at the following page at time of writing:

    https://www.microsoft.com/en-gb/sql-server/sql-server-downloads

    NB: Microsoft do from time to time change their website around and the link above might not work. If this is the case, you may need to Google “SQL Server 2017 Developer download” to find the latest download page. If you do this please be extra careful to ensure you are on the official Microsoft website before starting the download!

    Performing the install and configuring the service

    Now we have the software downloaded we can begin the install, follow the steps below to do this:

    Step 1 – Run the Installer:

    Once the installer starts up choose the “Custom” install option first:

    choose install of sql server dev edition

    Next, we select the installation location (we can leave this as the default value for now) and click “install” to begin:

    sql server download location

    The installer will start downloading the software, this might take some time depending upon you internet connection.

    sql server download progress

    Finally, we will be presented with the Installation Centre tool to continue with our installation.

    sql server install action center

    Step 2 – Configuration using the Installation Centre

    Once we are in the Installation Centre (as shown above) start by clicking on “Installation” from the left side menu and then select “New SQL Server stand-alone installation…” as is shown below:

    sql server choose new install

    When asked for a product key select “Developer” under the “Specify a free edition” drop down box and then hit “next”:

    sql server install specify dev version

    Finally on the next screen agree to the licence terms and then click “next” to continue:

    sql server install agree to licence

    The installer will now perform a rule check to ensure your system meets the rules necessary to continue with the installation. If you system is up to date it is unlikely you will fail this test however, the tool might ask you to install / update the .NET framework at this point? If this is the case the installer will guide you through what is needed to do this.

    You might also see a warning at this point about Windows firewall failing a rule check, if so this warning can be ignored:

    sql install fw rule check error

    Next, we will be taken to the “Feature Selection” screen, here we can specify all of the (many) features of SQL Server we want to install. For now we will select only the “Database Engine Services” and leave the install locations on their default values. After selecting this (as shown below) click on “next” to continue:

    sql server install feature selection

    On the next screen we can then configure the SQL Server “instance”. Instances can be useful in certain environments when we have multiple systems using a single database server, they are however beyond the scope of this book so leave this screen on the default settings as shown below and click next:

    sql server install instance configuration

    Next, we will see the “Server Configuration” screen, this is where we configure the account and start-up parameters for the SQL Server services (these run in the background on your PC). If left on the default values the SQL Server Database Engine will start automatically (in the background) whenever you start your PC starts up. This shouldn’t be a problem for most PCs but if you have an older machine you might want to set this to “Manual” and start the service only when needed, see below for details:

    sql server install automatic startup manual

    If you do (optionally) set this to manual it can be changed back at any time, although it is recommended to leave the above screen on default settings and click “next” to continue.

    Next, we arrive at the “Database Engine Configuration” screen. On this screen the tabs entitled “data dictionaries”, “tempDB” and “FILESTREAM” are all beyond the scope of this book so can all be left on their default settings for now. We can also leave the authentication mode on its default of “Windows authentication mode” since we are primarily going to be working on the same PC which also hosts our database.

    Further down on this same page we need to set an administrator account for our database installation, for simplicity we can use our current Windows user account. To do this simply click on the “Add Current User” button as shown below and await your account name to be added to the list box automatically:

    sql server install set win authentication

    It might feel like this is installation is never going to end at this point but don’t panic (yet)! Next, we will see the installation summary screen telling us everything is ready to go:

    sql server install ready summary

    Click “finish” and once the install has finished we should see a confirmation of this on the following screen:

    sql server install complete summary

    Downloading and installing the user interface

    Now we have the database engine installed the next step is to install the management studio, this is a separate tool used for managing databases and running SQL queries against them.

    Since SQL Server is primarily used in industry it is common for organisations to install only the database engine on a server in order to ensure as many of the server resources as possible are reserved for the running of the database itself rather than for graphical management tools. Database administrators typically use the management tools on their own local PC from a remote location and connect to the database server as and when it is required.

    Since we are building our project on one PC we will install both the database engine and the management studio together, this is absolutely fine to do, just a little uncommon in a commercial context is all.

    To start with, go back to the SQL Server Installation Centre, if you have since shut it down it can be opened up again from the start menu like so:

    sql server re-open sql instalation center

    Once in the installation centre click on “Installation” from the left menu and then select “Install SQL Server Management Tools”:

    sql server install management tools

    You will then be taken to a web page to download the latest installer for the management studio, download the latest version (currently the link labelled: “Download SQL Server Management Studio 17.9.1”) and then run the installer once it has downloaded.

    Once the installer is running click “Install” to begin:

    sql server install management studio install button

    The installer will now download and install the management studio automatically, we should get a confirmation message once it has finished:

    sql server install set-up complete

    Testing our install and logging in

    Now we have finished installing SQL Server and the management tools we need to check everything is working properly. Follow the steps below to check the software has installed correctly:

    Step 1 – Start the database engine

    Whilst configuring the database engine earlier on if you remember choosing to (optionally) start the database manually you will need to perform this step first (if you choose automatic start you can skip this step and move onto step 2).

    Start by opening up “SQL Server Configuration Manager”, this can be found on the start menu:

    sql server start sql manager

    Once loaded click on “SQL Server Services” on the left tree menu:

    sql server start sql service

    Then to the right we will see the installed SQL Server services appear along with their current running state:

    sql server service running

    From the image above the only service we are interested in is te top one named “SQL Server (MSSQLSERVER)”, don’t worry about the other two being in a stopped state. If “SQL Server (MSSQLSERVER)” has a state of “Stopped” then we must start it before going any further. To start the service simply right click on it and select “Start”.

    Again, if your service is set to start automatically you don’t need to worry about this step!

    Step 2 – Open SQL Server Management Console:

    Now we have the database engine running we can (finally) log into the management console, start by opening “SQL Server Management Studio” which is found on the start menu like below:

    open sql server management studio

    NB: This will be under the start entry titled “Microsoft SQL Server Management Tools 17” not the main “Microsoft SQL Server 2017” menu entry!

    Once the software has loaded we will be asked to login. If you remember back to when we set the software up we elected for “Windows authentication”, this means we will use our Windows computer account for logging in to the database with. As such with this setup we don’t need to worry about remembering any usernames and passwords and can simply click on “Connect” to login with our Windows account:

    sql server management login

    Once we are logged in there won’t be much to see, however, on the left we will see the object explorer which is one of the main tools used for exploring our database server with.

    The image below shows a green play symbol next to the server name which indicates to us that our database engine is running without any issues. If we expand “Databases” and then “System Databases” we can see we have some system databases already setup on our server (don’t worry about what these are for right now!):

    sql management studio show databases

    And finally, we now have a fully working SQL Server database installation and management studio all ready to use!

    Where to Next?

    As was mentioned earlier this is the first in a three part series of tutorials for getting started using SQL Server. After completing this tutorials you can move on to the second tutorial Getting Started with SQL Server Pt.2.

    If you have experienced any problems whilst following this tutorial please get in touch via the Pro-Data support form.

    Scroll to Top