What is SQL Server and how does it work?

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.

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.

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.

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.