for nonDBAs Anil Desai SQL Saturday 35 Dallas TX Anil Desai Independent consultant Austin TX Author of several SQL Server books Instructor Implementing and Managing SQL Server 2005 Keystone Learning ID: 610893
Download Presentation The PPT/PDF document "SQL Server Basics" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
SQL Server Basics for non-DBAs
Anil Desai
SQL Saturday #35 (Dallas, TX)Slide2
Anil DesaiIndependent 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
Speaker InformationSlide3
SQL Server Platform
Overview
Managing Servers Databases
Database Maintenance and Data Protection
Securing SQL ServerManaging Database Objects / Best Practices
Overview and AgendaSlide4
SQL Server Platform Overview
Understanding SQL Server’s features, services, and administrative toolsSlide5
Relational Database Server GoalsSlide6
SQL Server Database EngineStorage EngineQuery Engine
Databases
Logical collections of related objectsInstancesSeparate running services of SQL ServerDefault instance and named instances
SQL Server ArchitectureSlide7
SQL Server Management StudioDatabase management GUI
Object browser; templates, reports, etc.
Based on Visual Studio IDESupport for writing and executing queriesSQL Business Intelligence Dev. Studio
Analysis Services, Reporting Services, SSIS
SQL Server Admin. ToolsSlide8
SQL Server ProfilerDatabase Engine Tuning AdvisorSQL Server Configuration Manager
Manages services and protocols
Surface Area ConfigurationReporting Services Configuration ManagerSQL Server Books OnlineBusiness Intelligence Development Studio (BIDS)
Report Builder 2.0 / 3.0
SQL Server ToolsSlide9
Default options are set during installationSQL Server Management StudioServer Properties:
Memory
ProcessorsSecurity (Windows, SQL Server); AuditingDatabase settings (default file locations)
Configuring SQL ServerSlide10
Managing Databases
An overview of working with physical and logical database filesSlide11
Database storagePrimarily table data and index dataDatabase Files:
Primary data file (*.mdf)
Secondary data files (*.ndf)Transaction log file(s) (*.ldf)Filegroups: Logical collections of filesObjects can be created on
filegroups
SQL Server Physical Data FilesSlide12
SQL Server Management Studio ReportsServer: Server DashboardDatabase: Disk Usage (several reports)
Transact-SQL
Stored Procedures:sp_Help,
sp_HelpDB,
sp_SpaceUsedSystem Tables / Views
Sys.Database_FilesMonitoring Disk UsageSlide13
Goals:Maximize performance by reducing contentionSimplify administration
Best practices:
Monitor and analyze real-world workloadsSeparate data files and transaction log files
Designing Data StorageSlide14
Monitoring Disk UsageSlide15
Moving and Copying Databases
Copy Database Wizard
Attaching and detaching databasesAllows directly copying data/log filesDatabase must be taken offlineBackup / RestoreOther methods:
SQL Server Integration Services (SSIS)Generating scripts for database objectsBulk copy / BULK INSERTSlide16
Monitoring Database Activity
Windows Performance Monitor (
PerfMon)SQL ProfilerDatabase Tuning WizardSQL Server Event LogsWindows Event Logs (Event Viewer)Slide17
Database Maintenance & Data Protection
Methods
for maintaining, backing up, and restoring databasesSlide18
Data Protection and HA Goals
Minimize data loss
Minimize costsMinimize performance overheadSimplify implementation and administrationAllow fast fail-over
Implementing transparency for end-usersSlide19
Planning for Backups
Backup plan should be based on recovery requirements
Factors:Type of data / workloadAcceptable downtimeAcceptable data lossPerformance requirementsAdministration overhead (manageability)Slide20
Backup Types
Full Backups
Differential BackupsTransaction Log BackupsOther Types:Copy-Only BackupsPartial BackupsFile BackupsSlide21
Restore / Recovery Process
Restore order:
Full backupLatest differential (if any)Chain of transaction log backups
NO RECOVERY / WITH RECOVERYSlide22
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
Recovery ProcessesSlide23
Restore / Recovery ExampleSlide24
Database Maintenance Plans Slide25
Scheduling Single schedule for all tasksMultiple schedules
Databases:
System, All, All User, or specific databasesWizard Options:Order of operationsManages logging and history of operations
Maintenance Plan WizardSlide26
Database MirroringLog-shippingSQL Server Fail-Over Clusters
Distributed Federated Servers
ReplicationLoad-Balancing (at network or OS level)Reliability & Availability OptionsSlide27
Securing SQL Server
Understanding SQL
Server’s security architecture and objectsSlide28
SQL Server Security Overview
Layered Security Model:
Windows LevelSQL Server LevelDatabase
Schemas (for database objects)Terminology:
PrincipalsSecurablesPermissionsScopes and InheritanceSlide29
Local Service AccountPermissions of “Users” group (limited)No network authentication
Network Service Account
Permissions of Users groupNetwork authentication with Computer accountDomain User AccountsAdds network access for cross-server functionality
SQL Server Service AccountsSlide30
Managing Logins
Windows Logins
Authentication/Policy managed by WindowsSQL Server LoginsManaged by SQL Server
Based on Windows policiesPassword Policy Options:
HASHED (pw is already hashed)MUST_CHANGECHECK_EXPIRATIONCHECK_POLICYSlide31
Database Users and Roles
Database Users
Logins map to database usersDatabase RolesUsers can belong to multiple roles
Guest (does not require a user account)
dbo (Server sysadmin users)Application Roles
Used to support application codeSlide32
Creating Database Users and Roles
CREATE USER
Replaces sp_AddUser and sp_GrantDBAccessCan specify a default schemaManaged with ALTER USER and DROP USERCREATE ROLEDefault owner is creator of the role
SQL Server Management StudioWorking with Users and RolesSlide33
Built-In RolesSlide34
Other Security Options
Database Encryption
Encrypting Object DefinitionsData encryptionSQL Server AgentProxies based on subsystems allow lock-down by job step typesPreventing SQL Injection attacks
Use application design best practicesSlide35
Managing Database Objects
Understanding database design, tables, and indexesSlide36
Overview of Database ObjectsSlide37
NormalizationReduces redundancy and improves data modification performanceDenormalization
is often done to enhance reporting performance (at the expense of disk space and redundancy)
Referential IntegrityMaintains the logical relationships between database objectsDesigning a databaseSlide38
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 additionsMost important data modification SQL statements:SELECT: Returning rows
UPDATE: Modifying existing rowsINSERT: Creating new rows
DELETE: Removing existing rows* Presenter makes no guarantee about the time spent on this slide
The 1-Minute* SQL OverviewSlide39
Index ConsiderationsCan dramatically increase query performanceAdds overhead for index maintenance
Best Practices
Base design on real-world workloadsSQL Profiler; Execution PlansScenarios: Retrieving ranges of dataRetrieving specific values
Indexing OverviewSlide40
Clustered indexControls the physical order of rowsDoes not require disk space
One per table (may inc. multiple columns)
Created by default on tables’ Primary Key columnNon-Clustered IndexPhysical data structures that facilitate data retrievalCan have many indexes
Indexes may include many columns
Index TypesSlide41
Database Management Best Practices
SQL Server
maintenance and optimizationSlide42
Monitor real-world (production) database usageCommunicate and coordinate with application developers and usersDevelop policies and roles for database administration
Optimize database administration
Automate common operationsGenerate scripts for routine maintenanceSQL Server MaintenanceSlide43
Regular tasksMonitor disk space usageMonitor application performance
Monitor physical and logical disk space
Maintain indexes and data filesReview backup and recovery operationsReview securityReview SQL Server Logs and/or Windows logsVerify the status of all jobs
SQL Server MaintenanceSlide44
AnilDesai.netPresentation slidesSQL Server-focused
blog posts
Sample code from presentationsMicrosoft Resources:SQL Server Web Site:
www.microsoft.com/sqlMicrosoft Developer Network:
msdn.microsoft.comSQL Server Product Samples: http://msftrsprodsamples.codeplex.com/
For Further InformationSlide45
Questions & DiscussionSlide46