Transcript Introduction to SQL Server

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:

MSSQLServer


SQL Server Agent


Data & query processing
Scheduled jobs & alerts
Microsoft Distributed Transaction
Coordinator

Handles data from multiple sources
Logging In


Windows NT
Authentication
SQL Server
Authentication
Query Analyzer

Command-based interface


Complete access to SQL Server
Useful for creating scripts
Enterprise Manager

Graphical User Interface


Often more intuitive
Easier to visualize
Viewing Available Databases

Query Analyzer

Execute sp_helpdb system stored
procedure
Viewing Available Databases

Enterprise Manager

Expand Databases
folder
Creating a Database

Enterprise
Manager


Right-click
Database folder
Select New
Database…
Demonstration


Creating medical
database
Set growth and file
size options
Transaction Log



Records data modifications
Rolls forward completed transactions
Rolls back incomplete transactions
Data Types

At least 25 data types



char, int, float
uniqueidentifier, datetime, image
Can define own data types
User-Defined Data Types


Based on existing data types
Created with sp_addtype system
stored procedure
Creating Tables

Query Analyzer
Creating Tables

Enterprise
Manager


Right-click Tables
Click New Table…
Creating Tables

Enterprise
Manager (cont.)

Choose name
Creating Tables

Enterprise
Manager (cont.)

Create columns
Demonstration

Creating tables for medical database



Product
Patient
Usage
Constraints

Maintain data integrity



Domain Integrity
Entity Integrity
Referential Integrity
Constraints (cont.)

Primary Key


Foreign Key


Entity Integrity
Referential Integrity
CHECK Constraint

Domain Integrity
Add Primary Key

Query Analyzer
Add Primary Key

Enterprise Manager


Right-click column of interest
Set Primary Key
Add Foreign Keys

Query Analyzer
Add CHECK

Query Analyzer
View Constraints

Query Analyzer

Execute sp_helpconstraint
system stored procedure
Rules


Specify allowed values for columns
Example

Allowed states: PA, WV, OH
Creating Rules

Query Analyzer



Create rule with CREATE RULE statement
Bind rule with sp_bindrule system stored
procedure
Unbind with sp_unbindrule
Create Rules

Enterprise
Manager

Input only rule
name and definition
Create Rules

Enterprise Manager (cont.)

Bind rule

Open rule properties
Creating Rules

Enterprise
Manager (cont.)

Bind Rule (cont.)


Click Bind
Columns… on Rule
Properties
Select table and
column
Inserting Data

Query Analyzer

Use INSERT
statement
Inserting Data

Enterprise Manager


Right-click table name
Select Open Table ► Return all rows
Inserting Data

Enterprise Manager (cont.)

Type values as needed
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

Query Analyzer


Use CREATE PROCEDURE statement
Define variables as @variable_name
Using Stored Procedures


Use EXEC procedure_name
Pass values by reference or position
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
Transactions

Query Analyzer


Use BEGIN TRANSACTION and
COMMIT TRANSACTION statements
Use ROLLBACK TRANSACTION to
cancel
Transaction Example



Committed transaction
Rolled back transaction
Interrupted transaction
Views



Specify how data is seen
Focus data
Security

Grant permissions on Views
Creating Views

Query Analyzer

Use CREATE VIEW statement
Creating Views

Enterprise Manager

Add tables
Views

Enterprise
Manager (cont.)


Select column
names
Enter Aliases
Security

User Management




Roles
Logins
Users
Program Security
Roles


Operations that members of role can
perform
Users are assigned roles
Creating Roles


Type Role name
Select Standard
Role
Creating Roles


Open Role
properties
Click Permissions
button
Creating Roles

Set allowed actions



Table actions
Allowed Views
Allowed Stored
Procedures
Logins


Allow users to log
in to SQL Server
Defined under
Security folder
Creating Logins



Input Login name
Set password
Set defaults
Creating Logins


Choose allowed
databases
Select Roles
Users


Who is allowed to
access database
Role user has
Setting Users

Only necessary if
you did not set
during login
creation
Program Security

SQL Server Home Page


http://www.microsoft.com/sql
News, tips, & tools
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 Security

MBSA can be downloaded at:

http://www.microsoft.com/technet/treeview/default.asp
?url=/technet/security/tools/Tools/mbsahome.asp
Backup

Create Backup Device


Container for your database
Schedule Backup jobs
Creating a Backup Device

Enterprise
Manager

Look under
Management folder
Creating a Backup Device

Enterprise
Manager (cont.)


Set name
Set location
Backing Up

Back up and
restore from
Databases folder
Backing Up


Select your backup
device as the
Destination
Set a schedule if
desired
Check Jobs

Check backup jobs under SQL Server
Agent under the Management folder
Thank you!

Slides and scripts available at:



http://visc.sis.pitt.edu
Look for “Tutorials and Resources”
Any questions?