SQL Server
By: Jeremy Kadlec | Updated: 2021-02-02 | Comments (14) | Related: More > Professional Development Career Planning
Problem
I am new to SQL Server and I have heard the term used in a number of different capacities and in different contexts. So, what exactly is it? What are the common components? How does it work? How do people use it? I am brand new to the technology, so where do I get started?
Solution
What is Microsoft SQL Server
SQL Server is a relational database engine from Microsoft that has been supporting business applications for multiple decades. Over time the Microsoft SQL Server relational database management system has grown to include a number of new technologies including the items listed below. SQL Server 2019 is the latest version.
- SQL Server Relational Engine - Data storage, management and real-time query processing
- SQL Server Agent - Scheduling and notification engine that ships with the relational engine
- SQL Server Integration Services (SSIS) - Perform export, import, transformation and loading of data
- SQL Server Reporting Services (SSRS) - Report authoring, management and delivery toolset
- SQL Server Analysis Services (SSAS) - Build, manage, analyze, aggregate and roll-up data for Business Intelligence
- Power BI - Reporting tool with both desktop and cloud based options
In addition to running SQL Server on-premises, you also have the following cloud options:
- Azure - Public cloud offering from Microsoft
- Azure SQL Database - Public database as a service offering from Microsoft
- AWS - Public cloud offering from Amazon
- GCP - Google cloud platform for SQL Server
With this basic set of products outlined, let's try to define what SQL Server is used for and who uses it, at a high level. SQL Server is a large suite of products and this tip will try to cover the basic concepts with URL's for additional information. It is intended to serve as a stepping stone to learn SQL Server.
Microsoft SQL Server Database Engine
SQL Server Download
SQL Server can be downloaded from Microsoft based on your licensing agreement. SQL Server Evaluation, Express and Developer Editions can be downloaded free of charge or for a small fee to begin using the database engine.
Install SQL Server
SQL Server includes an installation wizard to complete the installation process for the main products as well as the Cumulative Updates (CU). Check out these installation guides:
SQL Server Database Engine
The relational engine is most often referred to as 'SQL Server' in most DBA, Developer, IT and Business circles. The purpose of the relational engine is to store and manage SQL Server data, secure data and code through security permissions, to meet broad business needs. At a high level, SQL Server is installed on a Windows Server or Linux Server. SQL Server is managed by a set of services which can be started, stopped, paused or disabled. The two main services are the SQL Server service (database engine) and SQL Server Agent (scheduling and notification).
SQL Server Database
A SQL Server database is a logical container to store data and secure objects. Permissions can be assigned at the database, role and object levels. Generally, a single database can support one or more business applications.
In general, you have two types of databases. First, are system databases (such as Master, Model, MSDB, TempDB and ResourceDB)) that installed by default with each installation and are used to manage SQL Server. Second, are user defined databases that a DBA\Developer would build to support an application need by a business. The user defined databases are your first place to start learning about SQL Server. Generally, you will spend most of your time working on applications using user defined databases.
The SQL Server database is made up of tables, code, indexes, security, etc. The tables have rows and columns storing the data. The most common coding objects are stored procedures, views, functions, referential integrity, triggers, etc. Indexes are built on tables to improve the access to the data. Security is established to configure access to data and/or execute particular commands.
Check out this tip to Create a SQL Server Database using SQL Server Management Studio..
SQL Server Database Files
On to the physical side of the database. SQL Server databases typically have 2 files when they are built. First is the database file which typically has an extension of MDF. All of the objects (i.e. tables, views, stored procedures, etc.) associated with the database are stored in this file. Second is the transaction log file which typically has an extension of LDF. At a high level, the transaction log is responsible for storing versions of the data before and after the changes in order to maintain the data integrity. As databases grow, they can include additional data files (*.NDF files) as well as additional transaction log files. However, this is usually not a configuration you need to be concerned with when you start working with SQL Server.
Additional SQL Server Features
This explanation is intentionally at a high level because SQL Server is such a large product, but we would be remiss not to include additional key components of the relational engine:
- Backup and Restore - Ability to issue backups and restores of the databases as needed for disaster recovery purposes
- Full Text Search - Ability to create a catalog to improve complex free form querying
- Service Broker - Queuing based technology internal to the database engine
- Availability Groups - High availability tool to maintain multiple copies of a complete database
- Replication - Ability to replicate a portion of a database to multiple SQL Servers
- Maintenance - Ability to rebuild indexes, statistics, etc. in order to improve data access and performance
What is the most common SQL Server tool?
SQL Server Management Studio (SSMS) would be the tool to start off with to administer and develop new databases. Here are few SSMS resources to get started:
- SQL Server Management Studio Tutorial
- How to Install SQL Server Management Studio on your Local Computer
- SQL Server Management Studio Productivity Tips
- All SQL Server Management Studio Tips
SQL Server Agent Overview
SQL Server Agent is the second SQL Server service we will outline relative to the SQL Server engine. It's primary responsibility is scheduling Jobs in order to execute particular operations at specific points in time. SQL Server Agent also has the ability to notify operators based on specific errors, Job failures or business conditions.
SQL Server Programming Languages
The main programming language in SQL Server is called Transact-SQL or T-SQL. SQL is an abbreviation for structure query language. This language can be divided into two broad categories. First is DDL, which is an acronym for data definition language. These commands are to CREATE, ALTER and DROP database objects such as tables, views, functions, indexes, etc. Second is DML, which is an acronym for data manipulation language. These commands are primarily SELECT, INSERT, UPDATE, DELETE and MERGE. This portion of the language is where programming logic like IF, IF...ELSE, WHILE, etc. would be used.
In general T-SQL is the most widely used language for DBAs\Developers and probably the best place to start learning SQL Server. However, SQL Server does support other programming languages internal to the database engine and in some cases, these languages are preferred. Here is a brief explanation:
- T-SQL - Perform data retrieval, additions, updates and removals
- DAX - Programming language for SSAS Tabular Models
- BIML - Markup language to automate creation of SSIS Packages
- Python - High-level interpreted programming language for general-purpose programming with many libraries for data science
- R - Open source programming language used for statistical computing, statistical graphics and data science
- CLR is the acronym for Common Language Runtime which extends executing compiled .NET code directly from the database engine.
- LINQ is a set of extensions to the .NET Framework that encompasses language-integrated query, set and transform operations. It extends C# and VB with native language syntax for queries and provides class libraries to take advantage of these capabilities, available only in .NET Framework 3.5. (Source - Introduction to Language Integrated Query (LINQ))
- SMO is an acronym for SQL Server Management Objects which has an object hierarchy built on the .NET Framework. Check out this tip - Getting started with SQL Server Management Objects (SMO).
- PowerShell has gained a great deal of popularity recently with Network, System and Database Administrators since its introduction in 2006. It is also built on the .NET Framework and leverages SMO when working with SQL Server objects directly.
SQL Server Business Intelligence
SSIS Overview
In a nutshell, Integration Services is a engine to perform data extraction, transformation and loading (ETL) for a data warehouse. This is a complicated way of saying moving data from one location to another. The locations can be SQL Server databases, flat files or other database platforms such as Oracle, DB2, Access, Sybase, PostgreSQL, cloud, etc. The SQL Server Integration Services development is conducted inside of the Visual Studio.
The Visual Studio offers a feature rich development tool to efficiently manage the code, change management, error handling, etc. The Integration Services Package can be executed directly or scheduled with SQL Server Agent. Although Integration Services offers a very feature rich solution, you may encounter other T-SQL commands that also meet the ETL needs of many organizations to include BCP, BULK INSERT, OPENROWSET, etc. Keep in mind Integration Services is a separate installation option when you install SQL Server.
Check out the SQL Server Integration Services Tutorial as a stepping stone to learn the technology. Once you have worked through the tutorial, check out the SQL Server Integration Services tips..
SSRS Overview
Reporting Services provides report authoring (development), rendering and management features. In many environments, Reporting Services is installed on a separate SQL Server just to handle reporting needs. Keep in mind Reporting Services is a separate installation option when you install SQL Server. During the installation process, new SQL Server programs are installed in addition to two databases to support the report metadata and temporary objects. After the installation, configurations are needed to access the report metadata and to setup features to email, encrypt the data, etc.
Check out the SQL Server Reporting Services Tutorial as a stepping stone to learn the technology. Once you have worked through the tutorial, check out the SQL Server Reporting Services tips..
SSAS Overview
Historically, Analysis Services was the primary business intelligence tool in SQL Server. Analysis Services provides the means to build and query multi-dimensional data and more recently Tabular data format. Analysis Services and the relational engine have a number of parallel concepts. Both have databases, programming languages, security, backup and recovery features, etc. The key concept with Analysis Services are cubes. If you are a visual person, you can think of cubes as a very large "Rubix Cube" with a number of different ways to access the data to determine trends, opportunities, etc.
Analysis Services is a separate installation option just like Integration Services and Reporting Services.
Here are some tips to begin learning about Analysis Services:
- SQL Server Analysis Services Tutorial
- SQL Server Analysis Services (SSAS) Administration Tutorial
- My First SQL Server Business Intelligence Project Tutorial
- How To Build a Cube From an Existing Data Source Using SQL Server Analysis Services
- How To Build a Cube Without a Data Source Using SQL Server Analysis Services
- Create Tabular Model Sample from SQL Server Database - Part 1
- All Analysis Services tips
Power BI and SQL Server
Power BI has taken the SQL Server community by storm with a simple and intuitive means to report on data from on-premises and cloud based applications:
- Gain access data to a variety of data sources
- Consolidate the data into a single report
- Numerous visualization options to best tell the data's story
- Combine reports together to form dashboards
- Distribute, secure and manage data
Start learning about Power BI:
- How to Build your First Power BI Dashboard
- Getting Started with Power BI - Part 1
- Power BI Data Insights
- Power BI Bubble Map, Shape Map and Filled Map Examples
- SQL Server Power BI Tips
SQL Server in the Cloud
SQL Server in the Cloud introduces a number of different options (virtual machines, instances, databases, ETL, data warehousing, reporting, docker, hadoop, kubernetes, etc.) that offer a variety of features, flexibility and costs to organizations. The popular public cloud offerings are from Microsoft, Amazon and Google. Check out the following resources to get started:
- Azure
- Amazon
SQL Server Versions and Editions
SQL Server Versions
In terms of a brief SQL Server history, the product started back in the 1990's in collaboration with Sybase that did not run on the Windows operating system. Microsoft ported the code to Windows and started gaining popularity with version 6.0 and 6.5. Microsoft made numerous updates to stabilize the platform in versions 7.0 in the late 1990's then released SQL Server 2000 and 2005 in the early 2000's which laid the ground work for many of the current features. Today, the following versions are generally used today by organizations:
- SQL Server 2019
- SQL Server 2017
- SQL Server 2016
- SQL Server 2014
- SQL Server 2012
- SQL Server 2008 R2
- SQL Server 2008
- SQL Server 2005
Also keep in mind that Microsoft periodically releases Cumulative Updates and Service Packs to add new functionality to the SQL Server platform as well as to correct known issues.
For a complete picture of the SQL Server versions check out - How to tell what SQL Server version you are running.
SQL Server Editions
SQL Server includes the following editions:
- Enterprise - Includes all features to scale for the most demanding environments and most expensive licensing option
- Standard - The most common edition of SQL Server used in production environments, with less functionality than Enterprise edition and with lower licensing costs
- Web - Only offered by hosting companies for cloud based installations with functionality akin to the Standard edition
- Developer - Only licensed for development usage, but includes all Enterprise edition features
- Evaluation - Intended only for evaluation purposes for a 180 day period and includes all Enterprise edition features
- Express - Free edition of SQL Server with limited capabilities
Check out this tip for Deciding Between Editions of SQL Server for Production.
Which version should I use to get started with SQL Server?
Starting off with learning SQL Server, I would recommend downloading one of the following:
- Evaluation Edition - Fully functional version of the Enterprise edition for 180 days
- Developer Edition - Enterprise edition functionality, that is not licensed for production use
- SQL Server Express Edition - Relational Database Engine with limitations on CPU, Memory, Database Size, etc., but available for production usage
SQL Server Professionals
In reality more people work with SQL Server in their day to day tasks than anyone probably realizes. Many websites and core business applications are supported by SQL Server. Typically SQL Server based applications are design, built, maintained and enhanced by DBAs, Developers, Data Modelers, Network Admins, System Admins, Storage Admins, etc. In a business setting, users interact with SQL Server in the following ways:
- Core Business applications
- CRM, ERP, Accounting, etc.
- Web and desktop
- SharePoint applications
- Data Warehousing
- Reporting applications
- Decision support applications
- Dashboards and score cards
- IOT devices
SQL Server Career Development Tips
- SQL Server Career Planning
- Resume Cookbook for Technology Job Seekers
- Interview Preparation
- How to find a SQL Server DBA Job
- Starting a SQL Server DBA Job
Next Steps
- This tip is intended as a stepping stone to learn SQL Server. If you are new to SQL Server check out the URLs for the particular section of tips that is of most interest to you.
- If you still have questions about SQL Server or need to figure out the next steps in your learning process, please add post your comments below.
- As you learn SQL Server stop back to MSSQLTips to expand your knowledge.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor’s degree from SSU and master’s from UMBC.
View all my tips
Article Last Updated: 2021-02-02