From the course: Microsoft SQL Server 2022 Essential Training

Core concepts - SQL Server Tutorial

From the course: Microsoft SQL Server 2022 Essential Training

Start my 1-month free trial Buy for my team

Core concepts

“

- [Instructor] SQL Server is used to manage databases that range in size from a small personal database up to enterprise-scale data estates that support some of the largest corporations in the world. So before we just dive in and start working with the platform, it's important to get familiar with a few key concepts first. First, let's talk about what SQL Server is. You might think that SQL Server is a database, but that's only partially correct. Instead, it's better to think of SQL Server as a relational database management system, or what's commonly abbreviated as an RDBMS. The key word here is management. SQL Server manages all of the tasks associated with creating new databases and their components, such as tables, storing and retrieving data from those database tables and acts as a gatekeeper through the management of end user logins and the verification of access permissions. SQL Server also provides options for automating services, such as creating database backups on a regular schedule, and it continuously monitors activities and can reconfigure itself to ensure that actions that end users are performing are optimized for peak performance. So you can see that SQL Server is much more than just a place to store your valuable data. It's an entire management system that provides a number of services to ensure that your data is protected, secure and accessible. Because of its nature as a server-class software, SQL Server is typically always running on the computer that it's installed on, and it listens for incoming requests and commands from connected users. When it receives communication, the server performs the required tasks and then serves data or information back to the user that requested it. In most production environments, SQL Server would be installed on a dedicated machine or a clustered group of interconnected machines. End users would log in remotely from their own computers to access the databases that they wanted information from. However, SQL Server can also be installed on a local personal computer, in the cloud or in an isolated container. These approaches provide a lot of flexibility in where the server software is actually running and how users can access them, and I'll show you how to install SQL Server using each of these methods in upcoming movies. As a bonus, installing SQL Server on your own PC, in the cloud or using a container is also a much cheaper way to get experience with the platform, without having to purchase dedicated and expensive server-grade hardware. A single SQL Server installation is called an instance, and each instance can hold many individual databases under its umbrella. You might have one database to track your product offerings and inventory and a separate database to contain employee personnel records, all operating from the same SQL Server instance. Multiple instances of SQL Server can be installed on a single computer, which helps administrators control access and manage resources, or even run different versions of SQL Server on the same hardware. When connecting to a computer writing multiple SQL Server instances, you'll need to specify the name or the IP address of the computer as well as the name of the specific instance that you want to access before you can log in. Once logged in, you can start sending commands that you want the server to perform. So how do you do that? Well, at the heart of SQL Server, as its name would imply, is something called SQL. SQL stands for Structured Query Language. It's the standardized way that users interact with most database platforms on the market today. The specific implementation of SQL that Microsoft SQL Server uses, or the dialect, if you prefer, is called Transact SQL. You'll often hear people refer to this as Transact SQL or Simply T-SQL, depending on who you're talking to. With the T-SQL language, you'll be able to ask or query the database for information, filter and sort records and combine tables together in order to retrieve exactly the information that you need in the order and format that's required for any situation. You'll also use T-SQL to run administrative commands, such as changing the permission granted to a user or creating new tables or other database objects. To work with SQL Server and send Transact SQL commands, you'll need to install an additional piece of software. By itself, SQL Server offers a command line interface. Many system administrators will issue commands using transact SQL scripts sent directly to the server using this command line interface. However, it's not a very intuitive environment to work with when you're first getting started with the platform. As an alternative, you can install a graphical interface. Microsoft provides an additional program called Management Studio that most database professionals use in order to get a graphical user interface similar to the Windows File Explorer. It's a separate add-on program that isn't required, but we'll install it to help us get used to the server's operation and ease us into writing our own T-SQL commands in a more beginner-friendly environment. The problem is, is that Management Studio is a Windows-only application. I'll be using a Windows computer and Management Studio throughout this course, but if you're interested in working with SQL Server on a Linux or a macOS computer, you'll want to take a look at a different graphical interface called Azure Data Studio. It provides much of the same functionality as Management Studio but is cross-platform and can run on any operating system. So that's enough to get started. The next step is to install both of the components that we need to set up our working environment. The SQL Server instance will provide the backend database management services, and Management Studio will give us a user-friendly graphical front end interface.

Contents