Databases Lecture 2 PDF

Databases Lecture 2 Dr. Mohammed M Abozahhd Electrical Engineering Department 1  2 MS SQL Server Microsoft SQL Server is a relational database management system (RDBMS) that supports a wi...

Databases Lecture 2 Dr. Mohammed M Abozahhd Electrical Engineering Department 1  2 MS SQL Server Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. Microsoft SQL Server is one of the three market-leading database technologies, along with Oracle Database and IBM’s DB2. Like other RDBMS technologies, SQL Server is primarily built around a row-based table structure that connects related data elements in different tables to one another, avoiding the need to redundantly store data in multiple places within a database.  3 MS SQL Server  4 MS SQL Server Components MS SQL Server Consists of several features. A few are: 1. SQL Server Profiler / Query Analyzer - Monitoring tool - Used for performance tuning - Uses traces – an event monitoring protocol - Event may be a query or a transaction like logins etc. 2. SQL Server Service Manager - Helps us to manage services - More than one instance of SQL server can be installed in a machine - First Instance is called as default instance - Rest of the instances (16 max) are called as named instances - Service manager helps in starting or stopping the instances individually 3. SQL Server Management Studio  5 SQL Server Service Manager  6 SQL Server Query Analyzer  7 SQL Server Management Studio - SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. Use SSMS to access, configure, manage, administer, and develop all components of SQL Server. - SSMS provides a single comprehensive utility that combines a broad group of graphical tools with many rich script editors to provide access to SQL Server for developers and database administrators of all skill levels.  8 SQL Server Management Studio  9 SQL Server Management Studio  10 SQL Server Version  11 SQL Server Version  12 Collation A Collation specifies the bit patterns that represent each character in a dataset. Collations also determine the rules that sort and compare data. SQL Server supports storing objects that have different collations in a single database.  13 SQL Server Authentication Types SQL Server supports two authentication modes, Windows authentication mode and mixed mode. Windows authentication: is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials. Mixed mode: supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server.  14 SQL Server Database Engine Instances  15 SQL Server Database Engine Instances  16 SQL Server Database Engine Instances  17 SQL Server Database Engine Instances - An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. - Each instance manages several system databases and one or more user databases. - Each computer can run multiple instances of the Database Engine. - Applications connect to the instance in order to perform work in a database managed by the instance. - An instance of the Database Engine operates as a service that handles all application requests to work with the data in any of the databases managed by that instance.  18 SQL Server Database Engine Instances - It is the target of the connection requests (logins) from applications. The connection runs through a network connection if the application and instance are on separate computers. If the application and instance are on the same computer, the SQL Server connection can run as either a network connection or an in-memory connection. - When a connection has been completed, an application sends Transact-SQL statements across the connection to the instance. The instance resolves the Transact-SQL statements into operations against the data and objects in the databases, and if the required permissions have been granted to the login credentials, performs the work. Any data retrieved is returned to the application, along with any messages such as errors.  19 SQL Server Database Engine Instances  20 SQL Server Database Engine Instances  21 SQL Server Database Engine Instances  22 SQL Server System Databases By default SQL server has 4 system databases: – Master : System defined stored procedures, login details, configuration settings etc – Model : Template for creating a database – Tempdb : Stores temporary tables. This db is created when the server starts and dropped when the server shuts down – Msdb : Has tables that have details with respect to alerts, jobs. Deals with SQL Server Agent Service  23 SQL Server System Databases  24 Creating Database We need to use Master database for creating a database By default the size of a database when created is 1 MB A database consists of : Master Data File (.mdf) Primary Log File (.ldf) Database Operations - Changing a database Use - Creating a database Create database - Dropping a database Drop database Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;  25 Backup Database BACKUP DATABASE testDB TO DISK = 'D:\bak\testDB.bak';  26 Backup Database  27 Restore Full SQL Server database backup RESTORE DATABASE Adventureworks FROM DISK = ' D:\DB_BAK.bak' Restore DB using SQL Server Management Studio 1. Connect to your SQL Server, right-click on the “Databases” directory, and choose “Restore Database” 2. Click the button beneath the “Source” section next to “Device” 3. In the “Select backup device” press “Add” 4. Select the backup file or files (.bak) you are going to restore, then click “OK” 5. In the “Restore Database” window specify the database’s name you will restore and click “OK” to start  28 Restore DB using SQL Server Management Studio  29 Restore DB using SQL Server Management Studio