SQL Server Instance FAQ

SQL Server is relational database management system software that runs on Windows or Linux operating systems. SQL Server can be installed on a physical or virtualize hardware platform to support applications and users. The SQL Server would be considered the highest level in the overall hierarchy. This supports databases, data, security, high availability, reporting and business intelligence.

Current versions include SQL Server 2025, SQL Server 2022, SQL Server 2019, SQL Server 2017, SQL Server 2016, etc.

Enterprise Edition, Standard Edition, Developer Edition, Express Edition, Evaluation Edition and Web Edition.

A SQL Server instance is an installation of the relational database platform. The instance can be considered the highest level in the software hierarchy.  The SQL Server instance encapsulates all of the database concepts – database, tables, data, stored procedures, security, etc.

A single server (i.e. hardware platform) can have up to 16 (1 default and 15 named) SQL Server instances installed. Each instance can be completely autonomous, but share the underlying CPU, memory, network and storage resources.  This configuration can be used for Production, Quality Assurance \ Testing \ User Acceptance Testing and\or Development.  This is a separate configuration from virtualization. Numerous instances can be a significant hardware, Windows operating system and SQL Server licensing savings.

SQL Server is one of many Relational Database Management Systems on the market. Others include Oracle, DB2, PostgreSQL, MySQL, MariaDB, etc. The Relational Database Management System manages all of the databases, data, transactional integrity, security, user access, etc. This could be for an operational system, data warehouse, business intelligence, analytics, AI, time series, and more.

The SQL Server Service is the main Windows service that controls whether the SQL Server instance is started or stopped.  Each SQL Server instance has a corresponding Windows service.  This Windows service can be configured to run with a domain account to access resources on the network or a local system account.

SQL Server Agent is responsible for running Jobs on a predefined schedule. Jobs can include T-SQL, PowerShell, SSIS, Python, R, DOS, MDX, etc. commands. Operators can be configured and notified via email or text when a Job completes, fails or succeeds. SQL Server Agent also includes Alerts that can be configured for when performance or operational thresholds are met. Learn about the SQL Server Agent system tables.

A Virtual Machine is an independent operating system and SQL Server installation with management similar to a SQL Server.  The difference is that there is a virtualization layer between the physical hardware and the virtual machine.  A single Host will support numerous Guests (virtual machines).

Virtualization is used to consolidate numerous independent machines onto a single hardware platform to reduce power and resource costs.  Each server remains a separate entity that still requires management and maintenance.  Windows and SQL Server licensing is still required for each virtual machine. At the time of writing, VMWare is the most used virtualization platform to support virtualized environments. Alternatives include Docker and Kubernetes.

SQL Server Management Studio (SSMS) has been the long-standing tool available from Microsoft to administer and develop database objects with T-SQL code. SQL Server Management Studio enables you to connect to instances of the database engine, Integration Services, Reporting Services and Analysis Services that are on premises, at data centers and in the cloud. SSMS originally shipped with the database engine, but has subsequently become a separate download directly from Microsoft.

SQL Server Management Studio 22 is the latest version at the time of writing. New features include GIT integration, query hints, Copilot integration and productivity tips. Read this article to learn how to download and install SSMS 22 as well as an offline option.

From an administration perspective, SSMS enables DBAs to write T-SQL code to perform tasks. This includes backups, restores, configure servers and databases, tune performance, schedule jobs and more. SSMS also includes user interfaces and wizards to perform many of these same tasks. On many of these interfaces, you can also get a copy of the T-SQL code that is generated. This can be used to automate the process across numerous instances. This code should be included in your source control system for change control.

From a development perspective, SSMS enables Developers and Business Intelligence Professionals to build tables, stored procedures, views, functions, etc. There are also user interfaces to visually build database diagrams and tables to better understand relationships among objects. SSMS also includes visual query plan analysis to understand the interworking’s of your T-SQL code in order to optimize it.

SQL Server Profiler has been a long-standing client side tool run by DBAs or Developers to capture each and every line of code, in the form of events, being executed against a SQL Server instance to tune performance. Profiler can generate a tremendous amount of data and potentially impact the performance of the monitored system if not configured correctly on an already taxed SQL Server instance. Profiler can be fine-tuned to only capture event data for specific users, databases, time periods, etc. Profiler data can be written to a table for further analysis or to a text file.

In general, Profiler has been replaced by Extended Events due to the performance implications, but some SQL Server Professionals still prefer Profiler and Server Side Trace for ad-hoc or specific performance tuning cases.

A related process to SQL Server Profiler is the Server Side Trace. This is a set of T-SQL code which can be executed against a SQL Server instance either via SQL Server Management Studio or a SQL Server Agent Job. The Server Side Trace captures the same events and writes them to a file. The file is generally loaded to a table to perform analysis. The Server Side Trace generally has less of a performance impact on the monitored SQL Server instance as compared to Profiler.

In many respects, SQL Server Extended Events have grown out of the original SQL Server Profiler and Server Side Trace functionality. Many of the same events and configurations are available in Extended Events in the SQL Server Management Studio Wizard or T-SQL code. Extended Events can be run to perform data collection for performance tuning or monitor for operational items such as a database that goes offline. SQL Server also ships with some default Extended Event sessions and templates which can be used as a starting point to collect data. Keep in mind this data can become large very quickly, so it is not a configure it and forget it situation. It is necessary to only capture the needed events and associated data then monitor this process as a portion of your daily tasks.

Performance Monitor (or sometimes referred to as Perfmon or System Monitor) is a free tool that ships with Windows to capture real time (or via a predefined schedule) macro level performance statistics related to the Windows operating system and SQL Server. The Performance Monitor counters provide either a usage percentage or a count of a particular event for a specific resource or application. Performance Monitor counters do not drill into a specific line of code. At a high level, the Windows counters are related to memory, CPU, disks, etc. and from a SQL Server perspective are related to scan methods, recompiles, buffer cache hit ratio, memory grants, etc.

Using both Performance Monitor and Profiler was a long time performance monitoring trick to understand the macro level statistics such as a CPU spike with Performance Monitor and then see the corresponding code executed during that time frame with Profiler. Here is one tip covering that technique – Correlating Performance Monitor and SQL Server Profiler Data.

SQLCMD is a command line tool that is run at the Windows command prompt to connect to the SQL Server relational engine and execute T-SQL code. SQLCMD can be used to automate execution of particular script files or for troubleshooting purposes. Everything that can be performed in SQL Server Management Studio can also be run with SQLCMD with the corresponding T-SQL commands including using variables.

BCP is a command line tool to import or export data between a text file and a SQL Server table or view. Bulk Copy or BCP can accept a number of parameters to format the data as needed. BCP in many respects has been replaced by SQL Server Integration Services and Azure Data Factory due to the graphical nature of the newer tools and additional functionality. However, BCP remains a consistent tool that is easy to integrate for scripted deployments including PowerShell.

SQL Server Backups are a critical process which generate point in time snapshots of a database or transaction log.  Backups types include: Full, Differential, File, Transaction Log, Copy_Only and Snapshot.  Backups can be created in SQL Server Management Studio or via Transact-SQL code. SQL Server Backups are generally scheduled on a regular basis to provide point in time recovery in case an issue occurs.  Depending on the organizational or application requirements, Full backups are executed daily and transaction log backups are issued throughout the business day, perhaps every one to fifteen minutes.

Keep in mind that SQL Server Backups are a point in time version of your online database, so these files need to be protected and secured to ensure your data is not compromised.  Often times, SQL Server Backups are the last resort to get a database operational and continue business operations.  It is prudent to verify backups run accurate on a predefined basis, to issue backups prior to any major changes to a database and to retain a backup prior to retirement for long term retention.

A SQL Server Restore is the process of using a previously generated SQL Server Backup to create a new database or overwrite the existing database.  The SQL Server Restore options include Full, Differential, File, Transaction Log and Snapshot.  A SQL Server database may need to be restored if there is an issue with the online database, data needs to be recovered, testing needs to be performed or a new development environment needs to be setup.  Keep in mind all of the production data will be restored, so it is imperative to properly secure the database once restored and either obfuscate or delete sensitive data, if it is not appropriate for the new environment.  Also, it is prudent to test backups by restoring them on a regular basis to ensure the restore process is understood and can be executed under pressure with automated scripts.