From the course: Microsoft SQL Server 2019 Essential Training

Core concepts - SQL Server Tutorial

From the course: Microsoft SQL Server 2019 Essential Training

Start my 1-month free trial Buy for my team

Core concepts

“

- [Instructor] Before we get into working with SQL Server, it's probably a good idea to talk about some of the core concepts surrounding what SQL Server actually is. SQL Server 2019 is a relational database management system or RDBMS. It uses a database engine to handle all the tasks associated with storing and retrieving data from a database, creating new databases and their components such as tables, and acts as a gatekeeper through the management of users and their access permissions. Because of its nature as a server, it's typically always running on the computer that it's installed on, listening for incoming requests and commands. When it receives communication the server performs the required tasks and then serves data or information back out to the user that requested it. In most production environments, SQL Server will be installed on a dedicated machine and users would log in remotely from their own computers to access the databases that they wanted to use. This is similar to accessing any other computer on your network, however SQL Server can also be installed on a local computer. This approach simplifies the connections so that we don't need to be concerned with the network configuration, and it's the method that we're going to be using here in this course. A SQL Server installation is called an instance and a single 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 computer. When connecting to a computer running multiple 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. When communicating with a server, users will take on several different roles. These roles provide a layer of security and allow users to only access areas of the system that they need in order to do their jobs. At the top of the list are system administrators. They oversee the configuration and management of the server itself. They'll configure and maintain its hardware and manage the user accounts that are allowed to access the system. Then each database will have one or more database administrators that are in charge of creating and maintaining tables and populating them with data, performing backups, and establishing user permissions within the database itself. Finally we have the database user accounts. Users at this level are granted specific permissions to access data within the database. For instance, a database user might only need to read records from a specific table and should be blocked from viewing information stored in other tables. In this course we'll look at some of the many tasks of the system administrators and a database administrators will be responsible for performing. Of course, depending on the security requirements of your organization, all of these roles can be performed by a single employee or they could be used to carve out specialized areas of responsibility. At the heart of SQL Server, is as it's name would imply, something called SQL. SQL stands for structured query language. It's the standardized way that users interact with most database systems 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 it as Transact-SQL or simply T-SQL, depending on who you talk 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 permissions granted to a user or creating backups of a database. Recently Microsoft introduced the ability to install SQL Server on Linux computers in addition to PCs running the Windows operating system. Regardless of your platform, the underlying server software is the same and the same T-SQL commands will apply. However, there's a slight difference in the way that you'll interact with a server depending on the platform that it's installed on. By itself, SQL Server offers a command-line interface. Most system and database administrators will issue commands to SQL Server using scripts sent directly to the server to process. Microsoft also 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. If you're interested in running SQL Server on Linux, or if you want to connect to a SQL Server instance from an Apple computer, then you're going to want to take a look at my course on Azure Data Studio, which is a cross-platform alternative to Management Studio. SQL Server 2019 is an exciting release with lots of new and advanced features that work together to protect and secure your data and make it easier to quickly access your information from an ever-expanding number of connection options. So now that we know what SQL Server is used for and some of the components of the system, it's time to see how we can leverage its capabilities to manage our own data.

Contents