Microsoft Virtual Academy - SQL Server Basics

SQL Server via Microsoft Virtual Academy

Database Fundamentals: https://www.microsoftvirtualacademy.comhttps://www.microsoftvirtualacademy.com/en-US/training-courses/database-fundamentals-8243

01 Introducing Core Database Concepts (50 minutes)Define databases example of relational database tables and introduce common database terminology

02 Relational Concepts (50 minutes)Normalization, referential integrity, and constraints

03 Creating Databases and Database Objects (50 minutes)Data types, database objects, DDL statements, and creating scripts

04 Using DML Statements (50 minutes)DML statements, using the SELECT statement, using INSERT, UPDATE, and DELETE to manage data, indexes, and triggers

05 SQL Server Administration Fundamentals (50 minutes)SQL Server security, securing database and objects, performing database backups, and database restores

Database: a database (db) is an organized collection of data, typically stored in electronic format. It allows you to input, manage, organize, and retrieve data quickly. Traditional databases are organized by records (rows), fields (columns) stored in tables which are stored in the database files.

Table: a database table is a collection of rows and columns that is used to organize information about a single topic. Each row within a table corresponds to a single record and contains several attributes that describe the row. These tables are stored in databases.

Relational Databases: a relational database is a collection of tables of data all of which are formally described and organized according to the relational model. Each table must identify a column or group of columns, called the PRIMARY KEY, to uniquely identify each row.

Database Management System (DBMS): is used by the users to access the data stored in database fiels. A DBMS is also used to perform administrative tasks on the databases and objects contained within the database. DBMS is a collection of applications that allows users and other programs to capture and analyze data by providing additional functionality like reporting services to help you create, deploy, and manage reports for your organization. A RDBMS is a software system designed to allow the definition, creation, querying, and updating of data stored in relational databases. A few examples of RDBMS include: Microsoft SQL Server, Microsoft Access, and MySQL.

Database Servers: databases are stored on database servers which are dedicated physical or virtual servers that host the database files and provide high-level performance for users who are accessing the data.Database servers contain the DBMS used to manage the data and administer the SQL Server envrionment.A database server can have one default instance and several instances of SQL Server. A SQL Server instance is a copy of the sqlservr.exe program that runs as a Windows operating system service. Often multiple database servers are deployed to provide high availability and improve performance. In order for us to install or use this content, we have to have what is called a database server. This is a server that's dedicated to hosting our content. Now this could be a physical server or it could be a virtual server, but it's used to manage that content...from creating the databases to managing the content that is stored in those databases. This database server can have multiple instances. What does that mean? I can install SQL Server several times and manage them separately. We have the same tools that we use for managing all those instances, but I can install SQL Server several times (one may be hosting my SharePoint content and another instance may be hosting my line of business applications) and I can install that multiple times on a single server and that's what the idea of an "Instance" is. It's just multiple installations that reside on a single server. Every time I do that installation, there's a new copy of a new version of the SQL Server (sqlservr.exe) that's used for managing that content.So often what we'll do is create multiple database servers and the idea of this is to provide high availability and improve performance. If we have multiple servers, we are able to do that.

SQL Server Management Studio (SSMS): a graphical user interface (GUI) used to browse, select, and manage the SQL Server instance and any of the objects within the SQL Server instance. The "Instance" would appear in the SSMS application at the top level. If you had multiple "Instances" of SQL Server, they would appear via the SSMS. You could the same SSMS tool for managing multiple "Instances" of SQL Server.