Introduction to SQL Server for Windows Administrators Presented

Introduction to SQL Server for Windows Administrators Presented to Wi. NSUG 02/05/09 Bret Stateham Owner, Net Connex bret@netconnex. com Blogs. netconnex. com Twitter: @BStateham

Agenda • • The SQL Server Product Family SQL Server Installation Initial configuration Client Tools Databases Security Backup and Restore

The SQL Server Product Family • • SQL Server Database Engine SQL Server Analysis Services (SSAS) SQL Server Integration Services (SSIS) SQL Server Reporting Services (SSRS) SQL Server Analysis Services SQL Server Integration Services SQL Server Database Engine SQL Server Reporting Services

SQL Server Database Engine • What people mean when they say “SQL Server” • Relational Database Management System (RDBMS) • Supports multiple databases per Instance • Supports multiple instances per server • Used for On Line Transaction Processing (OLTP) solutions

SQL Server Analysis Services • Multi-Dimensional database engine • Multiple databases with multiple cubes per instance • Multiple instances per server • Used for On Line Analytical Processing (OLAP) operations • Databases are created in “Business Intelligence Development Studio” (BIDS) • Includes Data Mining capabilities for discovering patterns in data.

SQL Server Integration Services • Used to move data between databases and platforms • Wizards: – “Import and Export Wizard” – “Copy Database Wizard” • More complex development done in “Business Intelligence Development Studio” (BIDS) • SQL Servers “Extract, Transform and Load” (ETL) tool

SQL Server Reporting Services • Enterprise reporting platform • Provides a website that users can go to for reports • Reports can also be delivered via email or as files in a folder share using subscriptions • Can report on data in platforms other than SQL Server • Again, Business Intelligence Development Studio is the development environment • Includes “Report Builder” for power users

SQL Server Editions Edition Description Enterprise Edition Everything that SQL Server offers. Standard Edition Departmental or small business use. All services, but some features are limited. Workgroup Edition For small workgroups. Limited features Developer Edition Same as express, but can be used in development only. Lists for $49. 00 Express Edition Free database engine. Legal to use in production environments but with limited features and power. Compact Edition For use on hand-held devices or as a small footprint in memory database engine.

Installation Demo

SQL Server Services Service Description SQL Server (<instance name>) The SQL Server Database Engine SQL Server Active Directory Helper Used for AD integration SQL Server Agent (<instance name>) Performs scheduled operations SQL Server Analysis Services (<instance>) The multi-dimensional database engine SQL Server Browser Helps clients find instances SQL Server Full. Text Search (<instance>) Creates indexes on large character fields SQL Server Integration Services ETL (Import and Export) SQL Server Reporting Services (<instance>) Centralized reporting platform SQL Server VSS Writer Volume Shadowcopy Service integration

SQL Server Files • Default Path: C: Program FilesMicrosoft SQL Server • 80, 90, 100 Folders are the client tools • SQL 2005 - MSSQL. x are the various instances – MSSQL = Database Engine – OLAP = Analysis Services – Reporting Services = Reporting Services • SQL 2008 – Folders are named: – MSSQL 10. <instance> = SQL Database Engine – MSAS 10. <instance> = Analysis Services – MSRS 10. <instance> = Reporting Services

Registry Keys • Software Configuration: HKLMSoftwareMicrosoft SQL Server * • Service Configuration: HKLMSystemCurrent. Control. SetServicesMSSQL*

Initial Configuration • SQL Server Configuration Manager – Services – Server Network Configuration – Client Network Configuration • Surface Area Configuration Manager – SQL 2005 Only (Goes away in 2008) – Enable features that are off by default – Some overlap with SQL Server Configuration Manager

Key Configurations • Set a secure password on the SA account regardless of the security configuration • Backup the system databases • Apply service packs • Backup the system databases again • Use the SQL Server Configuration Manager to enable remote access

Client Tools • SQL Server Management Studio (SSMS) – Replaces “Enterprise Manager” – Replaces “Query Analyzer” – Adds functionality that didn’t exist before (Analysis Services, Compact Edition, Reporting Services) • SQLCMD (Command Line Tool) – Replaces “OSQL” – Allows interaction with SQL Server from the command line or batch files.

Databases From an Admin’s Point of View • • The Physical Database The Logical Database System Databases Creating Databases

The Physical Database • A database is at least two files: – At least one database file (. mdf and. ndf) – At least one transaction log file (. ldf) – Most databases will only have one of each • Data files store – The data in the tables and indexes – The code for the stored procedures, etc. – The “meta data” (object definitions, security, etc. ) • Log Files store – Details about the transactions that occur in the database.

The Logical Database • Tables – Store the various types of data we want to work with (Employees, Products, etc) • Indexes – Help SQL Server find your data quickly (Index by last name, postal code, phone number, etc) • Views, Stored Procedures, Triggers, Functions – Code objects stored in the database for re-use – Adds a layer of security to the database

System Databases Database Use Master The only database SQL Server knows about at start up Documents all the other databases Stores the server configuration values (like memory config, etc) Stores “logins” to control access to the instance Model The template database used when each database is created MSDB Stores SQL Server Agent jobs, alerts, operators, schedules Stores backup and restore history Stores SSIS packages that are saved to “SQL Server” or “MSDB” Temp. DB Used by SQL Server when it needs extra room during sorts, etc. Can be used by developers to store temporary data Is used in certain cases to store previous versions of data

Creating a Database • SQL Server Management Studio (SSMS) • File Placement – Data files and log files should be in different drives – Data files work well on striped arrays – Log files work well on mirror sets • Choose an appropriate initial file size • Choose appropriate file growth settings

Creating a Database Demo

SQL Server Security • • Authentication methods Instance Level Permissions User Accounts Database Level Permissions

Authentication Methods • Windows Authentication – Recommended method – Uses Windows User Accounts and Groups – Easiest management in the long run • SQL Authentication – The “SA” Account – Can use the same password policies as Windows – Use only in special situations • Logins – Can be either “Windows” or “SQL” logins – Used to gain access to the instance

Instance Level Permissions • Are stored in the “master” database • Fixed Server Roles – Assign bundles of instance permissions to logins – Sysadmin: full control over the instance – Dbcreator: allowed to create databases • All permissions are assignable – The “CONTROL” permission grants full control over any resource

User Accounts • • Are stored in each database Used to grant access to individual databases Map to a login “dbo” stands for Database Owner – Maps to the login that owns the database – Full control over the database • “guest” exists, but is disabled by default

Database Level Permissions • Fixed Database Roles – Assign bundles of permissions to users – Db_owner: Full control over the database – Db_ddladmin: can create any object – Db_datareader: can select data from any table • Assignable Permissions – CONTROL, CREATE, SELECT, INSERT, UPDATE, etc. • User Defined Roles – You can create roles to simply the assignment of permissions

SQL Server Security Overview Active Directory Windows Server SQL Server Logins & Roles Administrator sa dbo Domain Admins Administrators sysadmin db_owner BUILTIN Administrators Database Users & Roles Schema & Objects Creator or Owner

Creating Logins and Users Demo

Backup and Restore • • • Backup Types Backup Media Third Party Backup Solutions Database Recovery Model Option Performing Backups Performing Restores

Backup Types • Database Backups – Backup data from the data files – Full Backups: Backup everything – Differential Backups: Backup changes since the last full backup • Transaction Log Backups – Backup any unbacked-up information in the transaction log – Act as “Incremental” backups – Allow us to restore our databases to the point of failure

Backup Media • Tape – Can backup to tape if there is a tape device visible to SQL Server • File – Can backup instead to a file on disk – Can be a local file or a unc path • Can “Append” or “Overwrite” media when backing up • Common solution – SQL Server to backup to disk – Enterprise backup solution backs up backup files

Third Party Backup Solutions • • Enterprise Backup Solutions support SQL Typically, and “Agent” is installed Most of them perform regular SQL Backups They store they backup data on their media

Database Recovery Model Option • Each database has a “Recovery Model” option • FULL – All transactions logged in detail, including bulk operations – Transactions are kept in the log until we backup – Best choice for most production databases • BULK_LOGGED – – All transactions are logged, Bulk operations only “lightly” Bulk data is included in the log backup, not the live log Transactions are kept in the log until we backup Use this when you do a lot of bulk loads • SIMPLE – – Transactions are logged just like BULK_LOGGED Transactions are cleared from the log when they are done You can not backup the log Good choice for development databases or databases where it is ok to lose some data.

Performing Backups

Performing Restores

Summary • • The SQL Server Product Family SQL Server Installation Initial configuration Client Tools Databases Security Backup and Restore

Thanks! • Stay in touch: – Email: bret@netconnex. com – Blog: blogs. netconnex. com – Twitter: @BStateham • Do you know about Microsoft Tag? gettag. mobi