SQL Server has been around for the past thirty years. As Microsoft’s flagship database engine, SQL Server continues to provide industry-leading security and performance for data storage needs. In this article I would like to highlight some core concepts surrounding what SQL Server is and how to get started creating and managing SQL Server databases.
The Role of SQL Server
SQL Server is a Relational Database Management System (RDBMS) that uses a database engine to handle tasks associated with storage and retrieval of data from a database, creation of new databases and their components such as tables acting at the same time as a gatekeeper for user management and access permissions.
The Server runs in the background on the dedicated hardware that is installed on awaiting user requests and commands. It performs the required tasks and then serves back the data or information to the user.
Users can log in directly on the dedicated machines or they can remotely access the databases from their own computers.
SQL Server Instance
After installing SQL Server on the computer, that installation is referred to as an instance which can hold many individual databases. There is also the possibility of having multiple SQL Server instances installed on a single computer. Having multiple instances of SQL Server on the same hardware enables admins to run different versions of SQL Server and to control access and manage resources.
User Roles
There are different roles that a user can be assigned according to the type of work needed to be done on the server.
System administrators configure and manage the server itself and its hardware. They manage user accounts and assign different levels of access to the system. Database administrators are responsible for creating and maintaining tables, performing backups and establish user permissions within the database. Database user accounts are granted specific permissions to access data within the databases.
Structured Query Language (SQL)
SQL is a standardized domain-specific language that allows users to interact with the databases. Microsoft SQL Server uses a version called Transact-SQL (T-SQL). Users type commands also known as queries. Transact-SQL is used to filter, sort, combine, add, update, and retrieve records from the database. It is also used for running administrative tasks on the system such as creating backups or changing user permissions.
Interfacing with SQL Server
Out of the box, SQL Server is a command-line system. The users type in commands using T-SQL scripts which are sent directly to the server. Microsoft launched back in 2005 SQL Server Management Studio which adds a Windows GUI and is used to configure, manage and administer all the components within Microsoft SQL Server.
Sources:
Microsoft SQL documentation – SQL Server | Microsoft Docs
Learning Microsoft SQL Server 2019 – Adam Wilbert
Microsoft SQL Server 2019 Essential Training – Adam Wilbert