SQL Server Basics

Category: Entertainment

Presentation Description

No description available.


Presentation Transcript

SQL Server Basics for non-DBAs : 

SQL Server Basics for non-DBAs Anil Desai

Speaker Information : 

Speaker Information Anil Desai Independent consultant (Austin, TX) Author of several SQL Server books Instructor, “Implementing and Managing SQL Server 2005” (Keystone Learning) Info: http://AnilDesai.net or Anil@AnilDesai.net

Overview and Agenda : 

Overview and Agenda SQL Server 2005 Platform Overview Managing Databases Database Maintenance and Data Protection Securing SQL Server Managing Database Objects / Best Practices

SQL Server 2005 Platform Overview : 

SQL Server 2005 Platform Overview Understanding SQL Server’s features, services, and administrative tools

Relational Database Server Goals : 

Relational Database Server Goals

SQL Server 2005 Architecture : 

SQL Server 2005 Architecture SQL Server Database Engine Storage Engine Query Engine Databases Logical collections of related objects Instances Separate running services of SQL Server Default instance and named instances

SQL Server Services : 

SQL Server Services Instance-Specific (one service per instance): SQL Server SQL Server Agent Analysis Services Reporting Services Full-Text Search Instance-unaware Notification Services Integration Services SQL Server Browser SQL Server Active Directory Helper SQL Writer

SQL Server 2005 Admin. Tools : 

SQL Server 2005 Admin. Tools SQL Server Management Studio Database management GUI Object browser; templates, reports, etc. Based on Visual Studio 2005 IDE Support for writing and executing queries SQL Business Intelligence Dev. Studio Analysis Services, Reporting Services, SSIS

SQL Server 2005 Admin. Tools : 

SQL Server 2005 Admin. Tools SQL Server Profiler Database Engine Tuning Advisor SQL Server Configuration Manager Manages services and protocols Surface Area Configuration SQL Server Books Online

Configuring SQL Server : 

Configuring SQL Server Default options are set during installation SQL Server Management Studio Server Properties: Memory Processors Security (Windows, SQL Server); Auditing Database settings (default file locations)

Managing Databases : 

Managing Databases An overview of working with physical and logical database files

SQL Server Physical Data Files : 

SQL Server Physical Data Files Database storage Primarily table data and index data Database Files: Primary data file (*.mdf) Secondary data files (*.ndf) Transaction log file(s) (*.ldf) Filegroups: Logical collections of files Objects can be created on filegroups

Monitoring Disk Usage : 

Monitoring Disk Usage SQL Server Management Studio Reports Server: Server Dashboard Database: Disk Usage (several reports) Transact-SQL Stored Procedures: sp_Help, sp_HelpDB, sp_SpaceUsed System Tables / Views Sys.Database_Files

Designing Data Storage : 

Designing Data Storage Goals: Maximize performance by reducing contention Simplify administration Best practices: Monitor and analyze real-world workloads Separate data files and transaction log files

Comparing RAID Levels : 

Comparing RAID Levels

Monitoring Disk Usage : 

Monitoring Disk Usage

Moving and Copying Databases : 

Moving and Copying Databases Copy Database Wizard Attaching and detaching databases Allows directly copying data/log files Database must be taken offline Backup / Restore Other methods: SQL Server Integration Services (SSIS) Generating scripts for database objects Bulk copy / BULK INSERT

Database Maintenance & Data Protection : 

Database Maintenance & Data Protection Methods for maintaining, backing up, and restoring databases

Database Backup Types : 

Database Backup Types Recovery Models Full Bulk-logged Simple Backup operations Full Backups Differential Backups Transaction Log Backups Allows point-in-time recovery

Recovery Processes : 

Recovery Processes Recovery process: Latest full backup (Required) Latest differential backup (Optional) Unbroken sequence of transaction log backups (Optional) All transaction logs should be restored with NO RECOVERY option (except for the last one) Prevents database from being accessed while restore process is taking place

Database Maintenance Plans : 

Database Maintenance Plans

Maintenance Plan Wizard : 

Maintenance Plan Wizard Scheduling Single schedule for all tasks Multiple schedules Databases: System, All, All User, or specific databases Wizard Options: Order of operations Manages logging and history of operations

Reliability & Availability Options : 

Reliability & Availability Options Database Mirroring Log-shipping SQL Server Fail-Over Clusters Distributed Federated Servers Replication Load-Balancing (at network or OS level)

Securing SQL Server : 

Securing SQL Server Understanding SQL Server 2005’s security architecture and objects

SQL Server Security Overview : 

SQL Server Security Overview Layered Security Model: Windows Level SQL Server Level Database Schemas (for database objects) Terminology: Principals Securables Permissions Scopes and Inheritance

Security Overview : 

Security Overview (from Microsoft SQL Server 2005 Books Online)

Security Best Practices : 

Security Best Practices Make security a part of your standard process Use the principle of least privilege Implement defense-in-depth (layered security) Enable only required services and features Regularly review security settings Educate users about the importance of security Define security roles based on business rules

SQL Server Service Accounts : 

SQL Server Service Accounts Local Service Account Permissions of “Users” group (limited) No network authentication Network Service Account Permissions of Users group Network authentication with Computer account Domain User Accounts Adds network access for cross-server functionality

SQL Server Surface Area Configuration : 

SQL Server Surface Area Configuration Default installation: Minimal services SAC for Services and Connections Allow Remote Connections Access to Reporting Services, SSIS, etc. SAC for Features Remote queries .NET CLR Integration Database Mail xp_cmdshell

Managing Logins : 

Managing Logins Windows Logins Authentication/Policy managed by Windows SQL Server Logins Managed by SQL Server Based on Windows policies Password Policy Options: HASHED (pw is already hashed) MUST_CHANGE CHECK_EXPIRATION CHECK_POLICY

Creating Logins : 

Creating Logins Transact-SQL CREATE LOGIN statement Replaces sp_AddLogin and sp_GrantLogin SQL Server Logins Windows Logins SQL Server Management Studio Setting server authentication options Login Auditing Managing Logins

Database Users and Roles : 

Database Users and Roles Database Users Logins map to database users Database Roles Users can belong to multiple roles Guest (does not require a user account) dbo (Server sysadmin users) Application Roles Used to support application code

Creating Database Users and Roles : 

Creating Database Users and Roles CREATE USER Replaces sp_AddUser and sp_GrantDBAccess Can specify a default schema Managed with ALTER USER and DROP USER CREATE ROLE Default owner is creator of the role SQL Server Management Studio Working with Users and Roles

Built-In Server / Database Roles : 

Built-In Server / Database Roles

Understanding Database Schemas : 

Understanding Database Schemas Schemas Logical collection of related database objects Part of full object name: Server.Database.Schema.Object Default schema is “dbo” Managing Schemas CREATE, ALTER, DROP SCHEMA SQL Server Management Studio Can assign default schemes to database users: WITH DEFAULT_SCHEMA ‘SchemaName’

Configuring Permissions : 

Configuring Permissions Scopes of Securables Server Database Schema Objects Permission Settings: GRANT REVOKE DENY Options WITH GRANT OPTION AS (Sets permissions using another user or role)

Managing Execution Permissions : 

Managing Execution Permissions Transact-SQL Code can run under a specific execution context By default, will execute as the caller EXECUTE AS clause: Defined when creating an object or procedure Options: CALLER (Default) SELF: Object creator Specified database username

Other Security Options : 

Other Security Options Database Encryption Encrypting Object Definitions Data encryption SQL Server Agent Proxies based on subsystems allow lock-down by job step types Preventing SQL Injection attacks Use application design best practices

Managing Database Objects : 

Managing Database Objects Understanding database design, tables, and indexes

Overview of Database Objects : 

Overview of Database Objects

Designing a database : 

Designing a database Normalization Reduces redundancy and improves data modification performance Denormalization is often done to enhance reporting performance (at the expense of disk space and redundancy) Referential Integrity Maintains the logical relationships between database objects

The 1-Minute* SQL Overview : 

The 1-Minute* SQL Overview The Structured Query Language (SQL) defines a standard for interacting with relational databases Most platforms support ANSI-SQL 92 Most platforms provide many non-ANSI-SQL additions Most important data modification SQL statements: SELECT: Returning rows UPDATE: Modifying existing rows INSERT: Creating new rows DELETE: Removing existing rows * Presenter makes no guarantee about the time spent on this slide

Indexing Overview : 

Indexing Overview Index Considerations Can dramatically increase query performance Adds overhead for index maintenance Best Practices Base design on real-world workloads SQL Profiler; Execution Plans Scenarios: Retrieving ranges of data Retrieving specific values

Index Types : 

Index Types Clustered index Controls the physical order of rows Does not require disk space One per table (may inc. multiple columns) Created by default on tables’ Primary Key column Non-Clustered Index Physical data structures that facilitate data retrieval Can have many indexes Indexes may include many columns

Database Management Best Practices : 

Database Management Best Practices Maintenance and optimization of SQL Server 2005

SQL Server Maintenance : 

SQL Server Maintenance Monitor real-world (production) database usage Communicate and coordinate with application developers and users Develop policies and roles for database administration Optimize database administration Automate common operations Generate scripts for routine maintenance

SQL Server Maintenance : 

SQL Server Maintenance Regular tasks Monitor disk space usage Monitor application performance Monitor physical and logical disk space Maintain indexes and data files Review backup and recovery operations Review security Review SQL Server Logs and/or Windows logs Verify the status of all jobs

SQL Server Management Features : 

SQL Server Management Features SQL Server Agent Jobs Alerts Operators SQL Server Logs Database Mail Linked Servers

For More Information : 

For More Information www.microsoft.com/sql Resources from Anil Desai Web Site (http://AnilDesai.net) E-Mail: Anil@AnilDesai.net Keystone Learning Course: “Microsoft SQL Server 2005: Implementation and Maintenance (Exam 70-431)” The Rational Guide to Managing Microsoft Virtual Server 2005 The Rational Guide to Scripting Microsoft Virtual Server 2005

Questions & Discussion : 

Questions & Discussion

authorStream Live Help