Overview n n n What is SQL Server

Overview n n n What is SQL Server? Creating databases Administration Security Backup

What is SQL Server? n Database management software ¡ ¡ ¡ n Own file structure Own logins Own security Access through 2 main ways: ¡ ¡ Query Analyzer Enterprise Manager

What is SQL Server? n Consists of 3 Services: ¡ MSSQLServer n ¡ SQL Server Agent n ¡ Data & query processing Scheduled jobs & alerts Microsoft Distributed Transaction Coordinator n Handles data from multiple sources

Logging In n n Windows NT Authentication SQL Server Authentication

Query Analyzer n Command-based interface ¡ ¡ Complete access to SQL Server Useful for creating scripts

Enterprise Manager n Graphical User Interface ¡ ¡ Often more intuitive Easier to visualize

Viewing Available Databases n Query Analyzer ¡ Execute sp_helpdb system stored procedure

Viewing Available Databases n Enterprise Manager ¡ Expand Databases folder

Creating a Database n Enterprise Manager ¡ ¡ Right-click Database folder Select New Database…

Demonstration n n Creating medical database Set growth and file size options

Transaction Log n n n Records data modifications Rolls forward completed transactions Rolls back incomplete transactions

Data Types n At least 25 data types ¡ ¡ n char, int, float uniqueidentifier, datetime, image Can define own data types

User-Defined Data Types n n Based on existing data types Created with sp_addtype system stored procedure

Creating Tables n Query Analyzer

Creating Tables n Enterprise Manager ¡ ¡ Right-click Tables Click New Table…

Creating Tables n Enterprise Manager (cont. ) ¡ Choose name

Creating Tables n Enterprise Manager (cont. ) ¡ Create columns

Demonstration n Creating tables for medical database ¡ ¡ ¡ Product Patient Usage

Constraints n Maintain data integrity ¡ ¡ ¡ Domain Integrity Entity Integrity Referential Integrity

Constraints (cont. ) n Primary Key ¡ n Foreign Key ¡ n Entity Integrity Referential Integrity CHECK Constraint ¡ Domain Integrity

Add Primary Key n Query Analyzer

Add Primary Key n Enterprise Manager ¡ ¡ Right-click column of interest Set Primary Key

Add Foreign Keys n Query Analyzer

Add CHECK n Query Analyzer

View Constraints n Query Analyzer ¡ Execute sp_helpconstraint system stored procedure

Rules n n Specify allowed values for columns Example ¡ Allowed states: PA, WV, OH

Creating Rules n Query Analyzer ¡ ¡ ¡ Create rule with CREATE RULE statement Bind rule with sp_bindrule system stored procedure Unbind with sp_unbindrule

Create Rules n Enterprise Manager ¡ Input only rule name and definition

Create Rules n Enterprise Manager (cont. ) ¡ Bind rule n Open rule properties

Creating Rules n Enterprise Manager (cont. ) ¡ Bind Rule (cont. ) n n Click Bind Columns… on Rule Properties Select table and column

Inserting Data n Query Analyzer ¡ Use INSERT statement

Inserting Data n Enterprise Manager ¡ ¡ Right-click table name Select Open Table ► Return all rows

Inserting Data n Enterprise Manager (cont. ) ¡ Type values as needed

Stored Procedures n n n 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 n Query Analyzer ¡ ¡ Use CREATE PROCEDURE statement Define variables as @variable_name

Using Stored Procedures n n Use EXEC procedure_name Pass values by reference or position

Transactions n n Process statements as a group Must be committed ¡ n “All-or-nothing” – All statements are committed or none are If interrupted, transaction is rolled back automatically

Transactions n Query Analyzer ¡ ¡ Use BEGIN TRANSACTION and COMMIT TRANSACTION statements Use ROLLBACK TRANSACTION to cancel

Transaction Example n n n Committed transaction Rolled back transaction Interrupted transaction

Views n n n Specify how data is seen Focus data Security ¡ Grant permissions on Views

Creating Views n Query Analyzer ¡ Use CREATE VIEW statement

Creating Views n Enterprise Manager ¡ Add tables

Views n Enterprise Manager (cont. ) ¡ ¡ Select column names Enter Aliases

Security n User Management ¡ ¡ ¡ n Roles Logins Users Program Security

Roles n n Operations that members of role can perform Users are assigned roles

Creating Roles n n Type Role name Select Standard Role

Creating Roles n n Open Role properties Click Permissions button

Creating Roles n Set allowed actions ¡ ¡ ¡ Table actions Allowed Views Allowed Stored Procedures

Logins n n Allow users to log in to SQL Server Defined under Security folder

Creating Logins n n n Input Login name Set password Set defaults

Creating Logins n n Choose allowed databases Select Roles

Users n n Who is allowed to access database Role user has

Setting Users n Only necessary if you did not set during login creation

Program Security n SQL Server Home Page ¡ n http: //www. microsoft. com/sql News, tips, & tools

Program Security n 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 n MBSA can be downloaded at: ¡ http: //www. microsoft. com/technet/treeview/default. asp ? url=/technet/security/tools/Tools/mbsahome. asp

Backup n Create Backup Device ¡ n Container for your database Schedule Backup jobs

Creating a Backup Device n Enterprise Manager ¡ Look under Management folder

Creating a Backup Device n Enterprise Manager (cont. ) ¡ ¡ Set name Set location

Backing Up n Back up and restore from Databases folder

Backing Up n n Select your backup device as the Destination Set a schedule if desired

Check Jobs n Check backup jobs under SQL Server Agent under the Management folder

Thank you! n Slides and scripts available at: ¡ ¡ n http: //visc. sis. pitt. edu Look for “Tutorials and Resources” Any questions?