My “naive” Microsoft SQL Server desktop installation and usage tutorial
Just a brief introduction, as it is the custom in this series of “na?ve” educations, and then we go directly to point. ??
In this tutorial, we will learn a few simple steps on how to install one of the popular relational databases: Microsoft SQL Server.
A relational database is a digital database based on the relational data model.
The relational data model organizes data into one or more tables, which are representing "relations" between data. Tables consist of columns (data attributes) and rows (data records), with a unique key identifying each row. These data records in the table denote a real-world entity or relationship.
Microsoft SQL Server is a relational database whose primary language for queries is Transact SQL (T-SQL). That means that in addition to classic SQL queries, we can do more complex things like changing the program flow (IF command) and other cool stuff.
From my personal experience learning curve regarding the Microsoft SQL server is very motivating for beginners since it is easy to start and do advanced database design in a short time.
Server and related tools follow Microsoft application ergonomics with visual tools in the common Windows environment. Regular desktop users often appreciate that.
So, I assume that you never used database software and that you are using the Microsoft Windows environment for your work.
In this tutorial, you will have a detailed description that will allow you that in just a few hours install Microsoft SQL Server, Management tool, and two sample databases with data (one from Microsoft called “Northwind Traders” and one made by me called “Innovations”). With a few SQL queries that will also be presented, you will be ready for your SQL journey. ??
Ready? Here we go!
***
Step 1: Download and start the installation process
We will use SQL Server 2019 Express edition that is a free edition of SQL Server, ideal for development and production for desktop, web, and small server applications.
a) download Microsoft installation package
and move down to “specialized edition” part of the download page
- select “Express” edition and start downloading installation program by clicking “Download now”
download link is: https://go.microsoft.com/fwlink/?linkid=866658
b) run installation
- open your “File Explorer” program and locate downloaded installation program
- run the installation by double-clicking the file and select “Yes” on the next screen:
The installation window will open.
Step 2: Configure the installation and install features
- on the first screen of the installation dialog select custom installation
- use the default location for installation or change it if you find it necessary
- click “Install” and that will trigger a download of the install package
- after receiving success massage, the following dialog will appear, and you can select the first installation option
- accept “License Terms” with checkbox and use “Next” to move forward with the installation process
- accept “Microsoft Update” and click “Next”
Installation program will check the environment
and soon you will receive a report about your setup.
Note: for now, you can ignore the “Windows Firewall” warning. When the time comes for deployment and remote access this will be configured. A full explanation about required ports and firewalls are given on link:
- click “Next” and “Feature Selection” dialog will appear
Note: here, you can optimize installation to your needs and drop or add some features. If you click on some feature, on the right you will see the description of the specific feature.
- in our case, we will use the default, since it is a learning journey after all ??, and just click “Next”
- on the screen above you can also use default instance name “SQLExpress” and click “Next”
The feature installation process will now start.
- click “Next” button will install Java Runtime libraries (JRE) that we selected in feature list
- if you are fine with Windows services settings and with collation (e.g. SQL_Latin1_General_CP1_CI_AS) just accept details and click “Next”
- there are many important settings on the following screen that will concern database administrator on the live production, but in our “naive” learning path ?? I suggest using defaults and click “Next”
- on the next screen accept “R” language requirements and click “Next”
- on the next screen do the same with “Python” language support requirements and click “Next”
Wait until the installation process is completed (this can take a while ??).
When everything is installed, you will get a dialog with success messages. You can review the results of your labor.
- click “Close” when you are done and close “SQL Server Installation Center” dialog since this will be the only installation scenario so far
New applications are added to your Windows system and you can see it on the Windows “Start” menu.
You can also search for “Services” on Windows Search and look for Microsoft SQL Server running services on the list.
Congratulation you have successfully installed Microsoft SQL Server on your system!
Step 3: Download, install and run SQL Server Management Studio
OK, now you have SQL Server service running, but let’s make this accessible by easy to use application. For this purpose, we will install SQL Server Management Studio (SSMS).
a) download installation program
Direct download link is:
b) run installation program
- locate the installation file on your file system and start the installation by double-clicking it
- confirm installation by clicking “Yes”
- if you are fine with proposed installation location, just click “Install”
This will start the installation process and you can relax and wait until it is over.
- the installation program will require a restart of your Windows operating system and suggest doing so by clicking “Restart” (alternately you can restart SQL Server service, etc. but let’s keep it simple)
c) start Microsoft SQL Server Management Studio for the first time
When you log back to your Windows desktop you will notice a new application in the Windows "Start" menu: Microsoft SQL Server Management Studio.
- click on the application menu item to run it the first time
The first run of the application can take some time to load, but soon you will see the main application window. For most of the regular Windows users, this is a moment when they are officially introduced to the Microsoft SQL Server world. ??
In front of the main application window, the “Connect to Server” dialog is displayed, and you will notice that instance “DESKTOP-Q2136SU\SQLEXPRESS” is suggested as the server name. This is the Instance ID that we selected in the installation process above and it is the only SQL Server instance currently running on your Windows Desktop: service “SQL Server (SQLEXPRESS)”.
- leave the default sections and click “Connect”
You will log you into the SQL Server instance and you can take a look at the content of the “Object Explorer”. The most interesting part is the "Databases" node where “System databases” needed for the SQL Server are displayed. On this node, you will also have access to your other databases that you develop or import.
Now is time to do some things that will make this useful training environment. We will add real sample databases!
Step 4: Add sample databases to your SQL Server instance
As mentioned above we will add two sample databases:
- “Innovations” made by me ?? – used to support the corporate innovation management process for a fictitious company that is promoting innovations initiatives by employees.
- “Northwind Traders” made by Microsoft - contains sales data for a fictitious company, which imports and exports foods on the world market.
a) download SQL scripts with database model and sample data
Both databases (schema & data) are exported in SQL scripts that will create database models and import sample data. For the purpose of this tutorial, I copied those scripts on my GIT space. Link to scripts for the creation of the sample databases is:
https://github.com/nevendujmovic/Innovations
- download both SQL scripts (“Code” -> “Download ZIP”) and locate them on your system
This will download a .zip file with both SQL scripts that will create a database model and import sample data. In my case, the .zip file is located at the “Downloads” directory of my Windows user profile.
- extract files in the .zip archive (use any archiver program you like 7-Zip, WinRAR, etc.)
The required SQL scripts are extracted to the folder:
“C:\Users\<user name>\Downloads\Innovations-master”.
b) import sample database “Innovations” via Microsoft SQL Server Management Studio
- start Microsoft SQL Server Management Studio and log in to your SQL server instance (~\SQLEXPRESS)
- go to “File” -> “Open” -> “File…”
- locate “Innovations” .sql file at
“C:\Users\<user name>\Downloads\Innovations-master” and open it
The SQL script will open in the editor window. Be free to explorer it to locate the install location, database model with tables, and sample data that will be inserted.
- click on “Execute” command to import database data
That’s it! Sample database “Innovations” is created on your SQL Server instance and you can see it on the left tree view of your “Object Explorer”.
c) explore database objects and make simple data extraction from the “Innovations” database
- expand the “Innovations” database to locate tables
- you can make simple data extraction to see results, for example, “Methodologies” table, by right-clicking on the table and selecting “Select Top 1000 Rows”
The corresponding SQL statement and the results will be displayed on the right editor window.
Now you can explore further to expand tables to see columns.
d) make Diagram for “Innovations” database
An excellent way to review the relational database design is to see it in the form of a diagram (ER -> Entity-relationship model). Microsoft SQL Server Management Studio has just what you need for this purpose.
- right-click on “Database Diagrams” and select “New Database Diagram”
- click “Yes” to enable support for the database diagramming
- in the “Add Table” select all tables by holding Ctrl key (Control) and clicking on them
Click “Add” when all tables are selected.
The entity-relationship model of the “Innovations” database is presented before you and you can visually review relationships and constraints between tables in the model. This view can also be used to edit and design database objects.
- I recommend saving this diagram for later use (specify the name)
e) run sample queries to extract data from “Innovations” database and practice SQL language
- open the new query file by going to “File” -> “New” -> “Query with Current Connection”
The new editor window will open to the right and you can pass following SQL queries:
/*List Innovations that are graded 5 or higher by the corporate sponsors order by highest grade and display their related categories*/ SELECT a.TITLE, b.GRADE, c.NAME AS CATEGORY_NAME FROM INNOVATIONS a INNER JOIN CREDITS b ON a.INNOVATION_ID = b.INNOVATION_ID INNER JOIN Categories c ON a.CATEGORY_ID = c.ID WHERE b.GRADE >= 5 ORDER BY b.GRADE DESC /*Group innovations by authors in order to get top inventors in the company. Only Innovations that ended up as a corporate project will be listed. I used this SQL query to present concepts like JOIN, Aggregation, and Subqueries.*/ SELECT (b.SURNAME + ', ' + b.NAME) AS Author, COUNT(a.INNOVATION_ID) as NUMBER_OF_INNOVATIONS FROM AuthorsInnovations a INNER JOIN Employees b ON a.EMPLOYEE_ID = b.EMPLOYEE_ID WHERE a.INNOVATION_ID IN (SELECT INNOVATION_ID FROM Projects) GROUP BY b.SURNAME, b.NAME ORDER BY NUMBER_OF_INNOVATIONS DESC
Note: SQL language syntax and usage are not in the scope of this tutorial since the main focus is to learn to install and set up a database environment. However, this point should be the start of the SQL language journey and there are many resources online to help you with that.
- paste the SQL code above to the new editor window that will open
- click “Execute” to run the SQL scripts
The results of our SQL query will be displayed on the right bottom section of the editor.
f) use steps above to import “Northwind Traders” database made by Microsoft
- start Microsoft SQL Server Management Studio and log in to your SQL server instance (~\SQLEXPRESS)
- go to “File” -> “Open” -> “File…”
- locate “instnwnd” .sql file at
“C:\Users\<user name>\Downloads\Innovations-master” and open it
The SQL script will open in the editor window. Be free to explorer it to locate the install location, database model with tables, and sample data that will be inserted.
- click on “Execute” command to import database data
That’s it! Sample database “Northwind Traders” is created on your SQL Server instance and you can see it on the left tree view of your “Object Explorer”. You can make a test query to extract data.
I encourage you to make a database diagram as explained above and practice SQL statements on the database. Don’t worry if you delete or change something. You can always delete the sample database and import it again.
So, we come to the end of our tutorial! Now you know how to in a short time prepare your desktop environment to serve as a database server for testing and exploring. Databases are everywhere and it is very important to have the first taste of this technology at the most common desktop environment.
I hope you enjoyed using it as much I enjoyed writing it!
I wish you all the best!
Author
Neven Dujmovic, Microsoft Certified Database Administrator (MCDBA)
P.S. If you like this tutorial be sure to check out other tutorials in my “naive” series on the LinkedIn platform.
During the COVID-19 pandemic, I provided free education via the LinkedIn articles platform. All tutorials are covering useful and advanced information technology concepts but are presented on the "easy to learn" way and are adjusted for beginners.
Each tutorial is:
- completely free to use
- only non-commercial software is used
- tutorials are documented in a high level of details
- live screenshots from used IT systems are provided for each tutorial step
- tutorials are quick to finish (duration max 6-8 hours) and adopted knowledge has direct application
- provided materials can serve as a reference in further usage of the technology.
Following IT educations are available: