Overview 4249663
Download
1 / 64

Overview - PowerPoint PPT Presentation


  • 110 Views
  • Uploaded on

Overview. What is SQL Server? Creating databases Administration Security Backup. What is SQL Server?. Database management software Own file structure Own logins Own security Access through 2 main ways: Query Analyzer Enterprise Manager. What is SQL Server?. Consists of 3 Services:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Overview' - phiala


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Overview
Overview

  • What is SQL Server?

  • Creating databases

  • Administration

  • Security

  • Backup


What is sql server
What is SQL Server?

  • Database management software

    • Own file structure

    • Own logins

    • Own security

  • Access through 2 main ways:

    • Query Analyzer

    • Enterprise Manager


What is sql server1
What is SQL Server?

  • Consists of 3 Services:

    • MSSQLServer

      • Data & query processing

    • SQL Server Agent

      • Scheduled jobs & alerts

    • Microsoft Distributed Transaction Coordinator

      • Handles data from multiple sources


Logging in
Logging In

  • Windows NT Authentication

  • SQL Server Authentication


Query analyzer
Query Analyzer

  • Command-based interface

    • Complete access to SQL Server

    • Useful for creating scripts


Enterprise manager
Enterprise Manager

  • Graphical User Interface

    • Often more intuitive

    • Easier to visualize


Viewing available databases
Viewing Available Databases

  • Query Analyzer

    • Execute sp_helpdb system stored procedure


Viewing available databases1
Viewing Available Databases

  • Enterprise Manager

    • Expand Databases folder


Creating a database
Creating a Database

  • Enterprise Manager

    • Right-click Database folder

    • Select New Database…


Demonstration
Demonstration

  • Creating medical database

  • Set growth and file size options


Transaction log
Transaction Log

  • Records data modifications

  • Rolls forward completed transactions

  • Rolls back incomplete transactions


Data types
Data Types

  • At least 25 data types

    • char, int, float

    • uniqueidentifier, datetime, image

  • Can define own data types


User defined data types
User-Defined Data Types

  • Based on existing data types

  • Created with sp_addtype system stored procedure


Creating tables
Creating Tables

  • Query Analyzer


Creating tables1
Creating Tables

  • Enterprise Manager

    • Right-click Tables

    • Click New Table…


Creating tables2
Creating Tables

  • Enterprise Manager (cont.)

    • Choose name


Creating tables3
Creating Tables

  • Enterprise Manager (cont.)

    • Create columns


Demonstration1
Demonstration

  • Creating tables for medical database

    • Product

    • Patient

    • Usage


Constraints
Constraints

  • Maintain data integrity

    • Domain Integrity

    • Entity Integrity

    • Referential Integrity


Constraints cont
Constraints (cont.)

  • Primary Key

    • Entity Integrity

  • Foreign Key

    • Referential Integrity

  • CHECK Constraint

    • Domain Integrity


Add primary key
Add Primary Key

  • Query Analyzer


Add primary key1
Add Primary Key

  • Enterprise Manager

    • Right-click column of interest

    • Set Primary Key


Add foreign keys
Add Foreign Keys

  • Query Analyzer


Add check
Add CHECK

  • Query Analyzer


View constraints
View Constraints

  • Query Analyzer

    • Execute sp_helpconstraint system stored procedure


Rules
Rules

  • Specify allowed values for columns

  • Example

    • Allowed states: PA, WV, OH


Creating rules
Creating Rules

  • Query Analyzer

    • Create rule with CREATE RULE statement

    • Bind rule with sp_bindrule system stored procedure

    • Unbind with sp_unbindrule


Create rules
Create Rules

  • Enterprise Manager

    • Input only rule name and definition


Create rules1
Create Rules

  • Enterprise Manager (cont.)

    • Bind rule

      • Open rule properties


Creating rules1
Creating Rules

  • Enterprise Manager (cont.)

    • Bind Rule (cont.)

      • Click Bind Columns… on Rule Properties

      • Select table and column


Inserting data
Inserting Data

  • Query Analyzer

    • Use INSERT statement


Inserting data1
Inserting Data

  • Enterprise Manager

    • Right-click table name

    • Select Open Table ► Return all rows


Inserting data2
Inserting Data

  • Enterprise Manager (cont.)

    • Type values as needed


Stored procedures
Stored Procedures

  • Collection of SQL statements

  • Can accept variables

  • Can be used for security

    • Users can be granted right to use stored procedure, even if they do not have access to underlying tables


Creating stored procedures
Creating Stored Procedures

  • Query Analyzer

    • Use CREATE PROCEDURE statement

    • Define variables as @variable_name


Using stored procedures
Using Stored Procedures

  • Use EXEC procedure_name

  • Pass values by reference or position


Transactions
Transactions

  • Process statements as a group

  • Must be committed

    • “All-or-nothing” – All statements are committed or none are

  • If interrupted, transaction is rolled back automatically


Transactions1
Transactions

  • Query Analyzer

    • Use BEGIN TRANSACTION and COMMIT TRANSACTION statements

    • Use ROLLBACK TRANSACTION to cancel


Transaction example
Transaction Example

  • Committed transaction

  • Rolled back transaction

  • Interrupted transaction


Views
Views

  • Specify how data is seen

  • Focus data

  • Security

    • Grant permissions on Views


Creating views
Creating Views

  • Query Analyzer

    • Use CREATE VIEW statement


Creating views1
Creating Views

  • Enterprise Manager

    • Add tables


Views1
Views

  • Enterprise Manager (cont.)

    • Select column names

    • Enter Aliases


Security
Security

  • User Management

    • Roles

    • Logins

    • Users

  • Program Security


Roles
Roles

  • Operations that members of role can perform

  • Users are assigned roles


Creating roles
Creating Roles

  • Type Role name

  • Select Standard Role


Creating roles1
Creating Roles

  • Open Role properties

  • Click Permissions button


Creating roles2
Creating Roles

  • Set allowed actions

    • Table actions

    • Allowed Views

    • Allowed Stored Procedures


Logins
Logins

  • Allow users to log in to SQL Server

  • Defined under Security folder


Creating logins
Creating Logins

  • Input Login name

  • Set password

  • Set defaults


Creating logins1
Creating Logins

  • Choose allowed databases

  • Select Roles


Users
Users

  • Who is allowed to access database

  • Role user has


Setting users
Setting Users

  • Only necessary if you did not set during login creation


Program security
Program Security

  • SQL Server Home Page

    • http://www.microsoft.com/sql

  • News, tips, & tools


Program security1
Program Security

  • Microsoft Baseline Security Analyzer (MBSA)

    • Runs under Windows 2000 or XP

    • Checks program settings & security patches

    • Useful for many Microsoft programs, including SQL Server 7.0 and 2000


Program security2
Program Security

  • MBSA can be downloaded at:

    • http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/tools/Tools/mbsahome.asp


Backup
Backup

  • Create Backup Device

    • Container for your database

  • Schedule Backup jobs


Creating a backup device
Creating a Backup Device

  • Enterprise Manager

    • Look under Management folder


Creating a backup device1
Creating a Backup Device

  • Enterprise Manager (cont.)

    • Set name

    • Set location


Backing up
Backing Up

  • Back up and restore from Databases folder


Backing up1
Backing Up

  • Select your backup device as the Destination

  • Set a schedule if desired


Check jobs
Check Jobs

  • Check backup jobs under SQL Server Agent under the Management folder


Thank you
Thank you!

  • Slides and scripts available at:

    • http://visc.sis.pitt.edu

    • Look for “Tutorials and Resources”

  • Any questions?