Getting Started with SQL Server

Problem

I am new to SQL Server and have heard the term used in various capacities and contexts. So, what exactly is it? What are the common components? How does it work? How do people use it? What is the best way to learn this technology?

Solution

Learn the core database engine features and get your SQL Server questions answered.

Table of contents

What is SQL Server

It is a relational database engine from Microsoft that has supported business applications for decades. Over time the relational database management system (RDBMS) has grown to include several new technologies including the items listed below. SQL Server 2025 is the latest version.

  • Relational Engine – Data storage, management and real-time query processing
  • SQL Agent – Scheduling and notification engine that ships with the relational engine
  • SSIS – Export, import, transformation and loading of data
  • SSRS – Report authoring, management and delivery toolset
  • SSAS – Build, manage, analyze, aggregate and roll-up data for Business Intelligence
  • Power BI – Reporting tool with both desktop and cloud-based options
  • SSMS – Administration and development tool

Cloud Platforms

In addition to running your database on premises, you also have the following cloud options:

  • Azure – Public cloud offering from Microsoft
  • Azure SQL MI – Azure SQL Managed Instance includes shared responsibilities for daily management between Microsoft and the user
  • Azure SQL Database – Public single database as a service offering from Microsoft
  • Microsoft Fabric – Cloud based end to end unified data analytics platform
  • AWS EC2 – Amazon Web Services Elastic Compute Cloud is a public cloud offering from Amazon
  • AWS RDS – Amazon Web Services Relational Database Service is a public cloud offering from Amazon including managed services
  • GCP – Google Cloud Platform

With this basic set of products outlined, let’s outline the SQL Server platform features. SQL Server is a large suite of products, and this tip will try to cover the basic concepts with URLs for additional information to start learning.

Microsoft SQL Server Database Engine

SQL Server Download

Download from Microsoft and install per the licensing agreement.

  • Evaluation edition can be used for free for 180 days to evaluate SQL Server.
  • Developer edition can be downloaded and used for free in non-production environments.
  • Express edition can be downloaded and used for free.

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:

Relational 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 as well as 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 that can be started, stopped, paused or disabled. The two primary 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 for storing data and securing objects. Assign permissions at the database, role and object levels. Generally, a single database can support one or more business application and a SQL Server instance has numerous user defined and system databases.

There are two types of databases. First, are system databases – Master, Model, MSDB, TempDB and ResourceDB. These are installed by default with each installation and are used to manage SQL Server. Second, are user defined databases. DBAs\Developers build these to support an application need by the business. The user defined databases are the first place you should start learning. Most of your time will be spent working on applications using user defined databases.

The database is comprised of tables (SSMS or T-SQL), code, indexes, security, etc. Each table has 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 on how to Create a SQL Server Database using SQL Server Management Studio.

Database Files

On to the physical side of the database. SQL Server databases typically have two files when they are built. First is the database file which typically has an extension of MDF. This stores all of the objects (i.e. tables, views, stored procedures, etc.) associated with the database. Second is the transaction log file which usually 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 to maintain data integrity. As databases grow, they can include additional data files (*.NDF files) as well as additional transaction log files. However, this is not a configuration to be concerned about when starting off.

Additional Features

This explanation is intentionally at a high level because SQL Server is such a large product. Still, 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. to improve data access and performance

What is the most common SQL Server tool?

Management Studio (SSMS) would be the tool for administering and developing new databases. Here are a few SSMS resources to start with:

SQL Server Agent Overview

SQL Agent is the second Windows service we will outline relative to the SQL Server engine. Its primary responsibility is scheduling Jobs to execute particular operations at specific points in time. SQL Agent can also notify operators based on specific errors, Job failures or business conditions.

SQL Programming Languages

The primary programming language is Transact-SQL or T-SQL. SQL is an abbreviation for structured query language and can be divided into two broad categories. First is DDL or 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.

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.

Programming Options

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 the 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 or 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 the .NET Framework. (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.

On-Premises Business Intelligence

SSIS Overview

In a nutshell, Integration Services is a engine for performing 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 Visual Studio.

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 learning the technology. Once you have worked through the tutorial, review the SQL Server Integration Services tips.

SSRS Overview

Reporting Services provides report authoring (development), rendering and management features. Install Reporting Services on a separate server to handle only reporting needs. Keep in mind Reporting Services is a separate installation option when you install SQL Server. During the installation process, it adds two databases to support the report metadata and temporary objects. After the installation, configure the report metadata and to setup features to email, encrypt the data, etc.

Check out the SSRS Tutorial as a steppingstone to learning the technology. Once you have worked through the tutorial, explore the SSRS tips.

SSAS Overview

Historically, Analysis Services was the primary business intelligence tool for the Microsoft Data Platform. 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 “Rubik’s Cube” with a number of different ways to access the data to determine trends, opportunities, etc.

Analysis Services is a separate installation option like Integration Services and Reporting Services.

Here are some tips to begin learning about Analysis Services:

Power BI

Power BI has taken the community by storm with a simple and intuitive means to report on data from on-premises and cloud based applications:

  1. Gain access to data from a variety of data sources
  2. Consolidate the data analysis into a single report
  3. Numerous visualization options to best tell the data’s story
  4. Combine reports to form dashboards
  5. Distribute, secure and manage data

Start learning about Power BI:

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:

Versions and Editions

Versions

Regarding SQL Server’s history, the product started in the 1990’s in collaboration with Sybase, which 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 version 7.0 in the late 1990s then released 2000 and 2005 in the early 2000s laying the groundwork for many of the current features. Today, organizations use the following versions:

Also, Microsoft periodically releases Cumulative Updates and Service Packs to add new functionality to the platform as well as to correct known issues.

For a complete picture of the versions check out – How to tell what SQL Server version you are running.

Editions

SQL Server includes the following editions:

  • Enterprise – Includes all features to scale for the most demanding environments and the most expensive licensing option
  • Standard – The most common edition used in production environments, with less functionality than the 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 the platform with limited capabilities

Check out this tip for Deciding Between Editions of SQL Server for Production.

Version Recommendation

While 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, but not licensed for production use
  • Express Edition – Relational Database Engine with limitations on CPU, Memory, Database Size, etc., but available for production usage

Careers

In reality more people work with SQL Server in their day-to-day tasks than anyone probably realizes. The Microsoft Data Platform supports many websites and core business applications. DBAs, Developers, Data Modelers, Network Admins, System Admins, Storage Admins, etc. design, build, maintain and enhance SQL Server applications. In a business setting, users interact with the platform in the following ways:

  • Core Business applications – CRM, ERP, Accounting, etc.
    • Web and desktop
  • SharePoint apps
  • Data Warehousing
  • Reporting apps
  • Decision support applications
  • Dashboards and scorecards
  • IOT devices

Career Development Tips

Next Steps

  • This article serves as a steppingstone to learning the platform. If you are new, check out the URLs for the particular section of tips that is of most interest to you.
  • If you still have questions or need help figuring out the next steps in your learning process, please post your comments below.
  • As you learn more return to MSSQLTips to expand your knowledge.

Related Posts

3 Comments

  1. Hi, in my research I came across this platform which I find very interesting. I need advice and guidance to get started in SQL Server for data analysis purposes. What do I need to know in terms of training, tools and other things. thank you for your help

  3. Hello,
    Always enjoy reading sql server tips . Section sql version etc needs to be updated. the latest version is 2022.

    Regards,
    Otties

Leave a Reply

Your email address will not be published. Required fields are marked *