For the best web experience, please use IE11+, Chrome, Firefox, or Safari

What is SQL Server and how does it work?

What is SQL Server?

SQL Server is a relational database management system (RDBMS) from Microsoft. Database administrators (DBAs) and database developers connect business applications and tools to a SQL Server instance or database. They submit commands to SQL Server using a structured query language (SQL) called Transact-SQL, or T-SQL. 

As an RDBMS, SQL Server’s capabilities extend beyond the basics (definition, creation, query, update) of a DBMS. A database in an RDBMS stores data in tables that are related to one another; hence, it is relational. 

An RDBMS like SQL Server maintains relationships among tables using constructs such as foreign keys, Data Definition Language (DDL), Data Manipulation Language (DML) and Data Control Language (DCL). It allows simultaneous access by multiple users while resolving contention and enforcing transaction locking. Notably, it processes transactions according to ACID (atomicity, consistency, isolation, durability) properties to preserve the integrity of data against outages and other operational failures.

What is SQL Server used for?

Like any DBMS, SQL Server is useful when combined with a software application that meets a business need such as storing and retrieving customer records, sales transactions, scientific data or credit history. And, as the amount of data increases, so does the usefulness of the data to the business.

SQL Server is generally used as a database in medium-sized companies and large enterprises, where the ability to scale to millions or hundreds of millions of records is important. SQL Server editions range from SQL Server Enterprise, designed with high-end datacenter capabilities, to SQL Server Express, an entry-level database for learning about and building desktop and small applications.

Traditionally, companies have used SQL Server on premises in their own data center. As the computing landscape has evolved, so have options with SQL Server. In the cloud, companies can opt for Azure SQL, a family of managed products: Azure SQL Database, Azure SQL Managed Instance and SQL Server on Azure VMs. For the Internet of Things (IoT) and the network edge, Microsoft offers Azure SQL Edge, the SQL Server database engine in a small footprint with built-in artificial intelligence (AI).

The options allow companies to develop once and deploy in all environments: on premises, in the cloud and in IoT devices at the edge of the network.


Is SQL Server free?

As a proprietary RDBMS, SQL Server is subject to licensing fees; it is not free.

Note that Microsoft makes available two specialized, no-cost editions:

  • Made for database developers, SQL Server Developer is a full-featured edition for use in developing and testing databases in a non-production environment.
  • SQL Server Express edition is designed for developing and producing desktop, web, and small server applications. Ideal users include independent software vendors, hobbyists and developers who are building client applications. SQL Server Express edition offers a smooth upgrade path to higher-end versions of SQL Server with more capabilities.


Which database services does SQL Server provide?

The main components of SQL Server include the following:

  • The Database Engine embodies basic processes like storing, processing and securing data. The engine controls access to the database and processes transactions.
  • SQL Server Reporting Services (SSRS) fulfills the need to output useful reports on which decision makers can act. SSRS is designed to draw content from a variety of data sources and format it, then publish reports and manage subscriptions.
  • SQL Server Analysis Services (SSAS) provides tools that data analysts use for business intelligence. Because online analytical processing (OLAP) is the complement to online transaction processing (OLTP), SSAS enables analysts with OLAP solutions, tabular modeling, self-service, collaboration and data mining.
  • SQL Server Integration Services (SSIS) includes packages DBAs often use in data movement to extract, transform and load (ETL) database records.
  • Data Quality Services (DQS) is designed for data cleansing, starting with a knowledge base for correcting and deduplicating data.
  • Machine Learning Services is used for building machine learning into enterprise processes. Database developers can use R and Python languages to build, retrain and score models using SQL Server.


Which tools do DBAs and database developers use to manage SQL Server?

The most common tools are the management environment for configuring, monitoring and administering databases and the development environment for writing and running SQL code and scripts. For convenience, the environments are usually integrated in a single SQL Server database management tool.

Most database tasks break down into a few categories:

  • Development – Adding and editing SQL and schemas, collaborating on code review, creating unit tests, tuning (optimization), profiling code and automating tasks.
  • Administration – Moving schemas and data, controlling access, executing scripts, managing database objects, discovering sensitive data and protecting personally identifiable information (PII).
  • DevOps – Enabling execution of database development functions and implementing continuous integration and continuous delivery (CI/CD).
  • Analysis – Connecting to multiple sources, integrating data across a range of sources, cleaning up data and visualizing data for decision makers.
  • Data modeling – Defining efficient data structures, producing documentation and entity relationship diagrams, modeling data across database platforms and generating DDL scripts.
  • Performance testing – Capturing and replaying transaction workloads against benchmarks, finding applications and SQL statements that don’t scale well and revealing potential bottlenecks and resource shortages.
  • Backup and recovery – Protecting the database environment to ensure data is securely backed up and fully recoverable for minimal downtime in the event of an outage.

Database programming requires an interface to work with basic elements of the database such as design, configurations, tables, views, queries and indexes. Beyond those basics, DBAs and developers look for advanced functions:

  • Compare and sync – Compare data between two tables or queries and bring them into parity.
  • Log reading – Retrieve changes from log files and reconstruct data in the event of loss.
  • Refactoring – When updating code, database developers need to reformat SQL, manage wildcards and safely rename database objects and parameters without breaking dependencies.
  • Standardizing – To promote collaboration, it’s necessary to enforce standards of quality across teams of developers, especially when they don’t work in the same room.
  • Version control – Essential to the software delivery lifecycle, version control ensures that the intended code is always the one put into production.


What is the architecture of SQL Server? What makes it run smoothly?

The architecture of SQL Server aligns with the services/components described above.

The Database Engine comprises a relational engine and a storage engine. The relational engine performs the work of parsing queries, then optimizing and executing them. The storage engine manages data access as the query is executed and interacts with the data and log files to process records in tables. SQL Server databases are collections of objects such as user, role, schema, table, index, view and stored procedure.

Analysis Services (SSAS) are structured similarly to databases, with collections of objects such as cube, dimension, measure, mining structures and role.

Reporting Services (SSRS) use the ReportServer to store metadata for each report configuration, including report data sources and definition.

In the context of that architecture, most DBAs are concerned with keeping SQL Server databases optimized, secure and highly available to meet the demands of the business. That means focusing on both the internal and the external factors that affect availability and performance.

Among the biggest internal factors is the quality of the SQL statements and queries running against the database. Since poorly formed SQL makes the database work harder than necessary, DBAs rely on SQL Server performance monitoring and tuning tools. They investigate performance problems by drilling into the most-executed queries and examining the most frequently accessed objects and files, along with disk I/O statistics and wait-events. They analyze and compare execution plans for SQL statements and check usage and contention of the TempDB database.

If performance problems remain after the SQL has been tuned, then DBAs turn to monitoring, diagnosis and optimization of their broader database environment, including virtual machines and the operating system. They run diagnostics on physical resources like CPU utilization, memory usage, disk capacity, network throughput and system uptime. They perform wait stat analysis to see what processes – down to individual statements – are awaiting resources. They examine workloads for bottlenecks and look for deadlocks by host, database, user or SQL statement.


How does SQL Server compare to MySQL?

As companies seek to reduce licensing costs and avoid vendor lock-in, the appeal of open-source databases like MySQL becomes stronger. Here are some of the main points of comparison between SQL Server and MySQL.

  • SQL Server is a proprietary product owned by Microsoft, with no-cost editions for developers and non-production users. MySQL Community products are available from Oracle as open source under the GNU Public License. Oracle also licenses the MySQL Enterprise Edition.
  • Both are relational database management systems (RDBMS) available in on-premises and cloud editions.
  • Both run on Windows and Linux; MySQL adds FreeBSD, OS X and Solaris.
  • Both support dominant programming languages (C#, C++, Delphi, Java, JavaScript, PHP, Python and Ruby). SQL Server adds R and Visual Basic; MySQL adds C, Objective-C, Perl and others.
  • Both support foreign keys and ACID properties.
  • For server-side scripting, SQL Server supports Transact SQL, .NET languages, R, Python and Java. MySQL supports its own proprietary syntax.
  • In popularity, DB-Engines ranks SQL Server and MySQL consistently among the top three RDBMSes.

There are differences in the development capabilities between SQL Server and MySQL. In cases where it is more important to have a solid data store than a fully featured programming environment, many DBAs opt for MySQL.

In general, the decision between proprietary and open-source software depends on more than licensing cost. The traditional advantage of proprietary software is that it is developed and marketed by large companies that maintain product roadmaps, support, security and patching. As going concerns, they have a vested interest in keeping the products safe for their customers.

When opting for open-source products, DBAs and database developers must take into account the state of the community. Can the creator provide long-term development, support and management of the database? How large and devoted is the community? How often do its members and followers add to the code base?

Many companies choose an open-source database to avoid licensing costs. They should keep in mind that, in addition to maintaining their own software, they may end up having to maintain that database – configurations, patches, fixes – as well. The costs of doing so – whether internally or externally – can be on par with licensing proprietary software.


Foglight for Databases

Proactively monitor the health and performance of all your database platforms