Microsoft SQL Server 2019 is a relational database management system (RDBMS). Managing SQL Server requires tasks performed by a system administrator and database administrators, and uses the Transact-SQL language to interact with data. In this video, learn the core concepts of Microsoft SQL Server 2019.
- [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 of 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 login 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 connection 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 systems 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 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 its 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 is 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 to the system, it's time to see how we can leverage its capabilities to manage our own data.
- Installing Management Studio
- Enabling features with SQL Server Configuration Monitor
- Creating a database
- Creating tables with Transact-SQL (T-SQL)
- Creating relationships between tables
- Creating views
- Creating user-defined scalar functions
- Backing up and restoring SQL Server
- Monitoring and management