240 likes | 256 Views
SQL statements are used to perform tasks such as update data on a database or retrieve data from a database. Before you learn SQL you need to learn how relational databases work from the design point of view.
E N D
Content • Introduction of SQL Server DBA • What is SQL Server DBA? • SQL Server DBA Architecture • SQL DB Files • SQL Server Components • What is SQL Server DBA roles? • Transaction Isolation Levels • System databases in SQL Server • SQL Server DBA Features
Introduction of SQL Server DBA • SQL provide high quality database support which exceed performance expectations. SQL Database Administrator perform the task of monitoring of database server. SQL DBA manages the hundreds of SQL Server instances, SAN storage and security.
What is SQL Server DBA? • Database administrators (DBAs) use specialized software to store and organize data. The role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as backup and data recovery.
SQL Server DBA Architecture • SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. • Database architecture: All the data is stored in databases which is organized into logical components visible to the end users.
SQL Server Components SQL Server works in client-server architecture, hence it supports two types of components − (a) Workstation and (b) Server. • Workstation components are installed in every device/SQL Server operator’s machine. These are just interfaces to interact with Server components. Example: SSMS, SSCM, Profiler, BIDS, SQLEM etc. • Server components are installed in centralized server. These are services. Example: SQL Server, SQL Server Agent, SSIS, SSAS, SSRS, SQL browser, SQL Server full text search etc.
What is SQL Server DBA roles? • Database administrators (DBAs) use specialized software to store and organize data. The role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as backup and data recovery.
Transaction Isolation Levels • Dirty Reads A dirty read occurs when a transaction reads data that has not yet been committed. • Nonrepeatable Reads Anonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. • Phantoms A phantom is a row that matches the search criteria but is not initially seen.
System databases in SQL Server Master Database Master Database contains information about SQL server configuration. Without Master database, server can’t be started. This will store the metadata information about all other objects(Databases,StoredProcedure,Tables,Views,etc.) which is Created in the SQL Server .
System databases in SQL Server Model Database • The model database sets a template for every database that was newly created . It serves as a template for the SQL server in order to create a new database. When we create a new database, the data present in model database are moved to new database to create its default objects which include tables, stored procedures, etc. • Primarily, the requirement of model database is not specific to creation of new database only. Whenever the SQL server starts, the Tempdb is created by using model database in the form of a template. By default it does not contain any data.
System databases in SQL Server Msdb • The msdb database is used mainly by the SQL server Management Studio, SQL Server Agent to store system activities like sql server jobs, mail, service broker, maintenance plans, user and system database backup history, Replication information, log shipping .We need to take a backup of this database for the proper function of SQL Server Agent Service.
System databases in SQL Server TempDB • From the name of the database itself, we can identify the purpose of this database. It can be accessed by all the users in the SQL Server Instance. • The tempdb is a temporary location for storing temporary tables(Global and Local) and temporary stored procedure that hold intermediate results during the sorting or query processing and cursors.
SQL Server DBA Features • High Performance. • High Availability. • Scalability and Flexibility. • Robust Transactional Support. • High Security. • Comprehensive Application Development. • Management Ease. • Open Source.
Thank you For more information: https://www.gangboard.com/