Overview 4249663
This presentation is the property of its rightful owner.
Sponsored Links
1 / 64

Overview PowerPoint PPT Presentation


  • 74 Views
  • Uploaded on
  • Presentation posted in: General

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:

Download Presentation

Overview

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?


  • Login