IT 456-01IT 456-01System AdministrationSystem Administration

Lesson 3Lesson 3

Dr Jeffrey A RobinsonDr Jeffrey A Robinson

Overview of CourseOverview of Course

Week 1 – IntroductionWeek 1 – IntroductionWeek 2 – Installation of SQL and management Tools Week 2 – Installation of SQL and management Tools Week 3 - Creating and Using a DatabaseWeek 3 - Creating and Using a DatabaseWeek 4 - Using the SQL Server Agent Service Week 4 - Using the SQL Server Agent Service Week 5 - Disaster RecoveryWeek 5 - Disaster RecoveryWeek 6 - SQL Server SecurityWeek 6 - SQL Server SecurityWeek 7 - Performance MonitoringWeek 7 - Performance MonitoringWeek 8 - Performance OptimizationWeek 8 - Performance OptimizationWeek 9 - High AvailabilityWeek 9 - High Availability

Unit 3Unit 3Programmable objects allow efficient reuse of code. Programmable objects allow efficient reuse of code. User-defined functions can return a single value or a User-defined functions can return a single value or a tabular value set. tabular value set.

A stored procedure can provide security and A stored procedure can provide security and performance benefits. performance benefits.

Triggers can extend business rule enforcement Triggers can extend business rule enforcement beyond the realm of constraints. beyond the realm of constraints.

These programmable objects, as well as data types These programmable objects, as well as data types and aggregates, can be created from common and aggregates, can be created from common language runtime (CLR) code that can be integrated language runtime (CLR) code that can be integrated into SQL Server. into SQL Server.

SQL Server Management Studio (SSMS) has the SQL Server Management Studio (SSMS) has the capability of capturing the Transact-SQL (T-SQL) capability of capturing the Transact-SQL (T-SQL) code corresponding to an administrative task, code corresponding to an administrative task, such as database creation, performed in the such as database creation, performed in the graphical user interface (GUI). This allows the graphical user interface (GUI). This allows the administrator to perform tasks using the familiar administrator to perform tasks using the familiar and intuitive GUI interface, but also to capture and intuitive GUI interface, but also to capture the code for disaster recovery or other purposes. the code for disaster recovery or other purposes.

The fully qualified name of a database object is The fully qualified name of a database object is server.database.schema.objectserver.database.schema.object..

SQLSQL

SQL is a set-based query language used SQL is a set-based query language used to retrieve and modify data in a relational to retrieve and modify data in a relational database. There are American National database. There are American National Standards Institute (ANSI) standards that Standards Institute (ANSI) standards that most versions of SQL adhere to. The most versions of SQL adhere to. The version of SQL used by Microsoft SQL version of SQL used by Microsoft SQL Server is Transact-SQL, or T- SQL.Server is Transact-SQL, or T- SQL.

Stored ProceduresStored Procedures

There are two aspects of stored procedures that There are two aspects of stored procedures that make them powerful:   make them powerful:   – sprocs are efficient, sprocs are efficient, – and they can have optional input and output and they can have optional input and output

parameters.   parameters.  

Stored ProceduresStored Procedures

The efficiency comes from the fact that after the first time The efficiency comes from the fact that after the first time they are executed, the query plan for their execution is they are executed, the query plan for their execution is stored in SQL Server's procedure cache in RAM. This stored in SQL Server's procedure cache in RAM. This eliminates the steps of parsing (checking for correct eliminates the steps of parsing (checking for correct query syntax), creation of a query tree (all referenced query syntax), creation of a query tree (all referenced objects are translated into their object IDs), and objects are translated into their object IDs), and compilation (the query optimizer's deciding on a query compilation (the query optimizer's deciding on a query plan and placing the query plan in procedure cache). plan and placing the query plan in procedure cache). Efficiency of network traffic is achieved because when a Efficiency of network traffic is achieved because when a front-end application on a client computer calls a stored front-end application on a client computer calls a stored procedure on a back-end SQL Server, only the call is procedure on a back-end SQL Server, only the call is sent across the network. sent across the network.

Stored ProceduresStored Procedures

A stored procedure can take input parameters. Here A stored procedure can take input parameters. Here is an example that would produce a list of all is an example that would produce a list of all orders placed by a particular customer and since a orders placed by a particular customer and since a particular dateparticular date

What are the parameters?What are the parameters?

When the above sproc is called, the input variables may be When the above sproc is called, the input variables may be specified in one of two ways: (1) in orderspecified in one of two ways: (1) in order  EXEC Sales.pCustomerOrderList 25, '2007-01-01';EXEC Sales.pCustomerOrderList 25, '2007-01-01';

  

or (2) by giving the name of the input parameter, in which case or (2) by giving the name of the input parameter, in which case the parameters may be listed in any orderthe parameters may be listed in any order  EXEC Sales.pCustomerOrderList @SinceDate = '2007-01-01', @custid EXEC Sales.pCustomerOrderList @SinceDate = '2007-01-01', @custid

= 25;= 25;  

Of course, when using the by parameter option, you must know Of course, when using the by parameter option, you must know the names of the parameters within the sproc's definitionthe names of the parameters within the sproc's definition

TriggersTriggers

What is a trigger?What is a trigger?(Hint: they can occur with INSERT, UPDATE or (Hint: they can occur with INSERT, UPDATE or

DELETE queries)DELETE queries)

Types:Types:AFTERAFTER

INSTEAD OFINSTEAD OF

Unit 3 assignmentUnit 3 assignment

Write a 200–300 word response to each of the Write a 200–300 word response to each of the following topics. following topics.

1. Define five options used when creating a database 1. Define five options used when creating a database and identify the importance of each option. and identify the importance of each option.

2. Define the options for the recovery method 2. Define the options for the recovery method available when creating a database. Identify the available when creating a database. Identify the strengths and weaknesses of each option. strengths and weaknesses of each option.

3. Define the terms stored procedure and function.   3. Define the terms stored procedure and function.   Identify a scenario in which each is used.Identify a scenario in which each is used.

Unit 3 ProjectUnit 3 Project

For this Unit's Project you will: For this Unit's Project you will: – Develop a stored procedure within the Develop a stored procedure within the

NWTraders database to evaluate characteristics NWTraders database to evaluate characteristics of a customer and his/her purchases or of a customer and his/her purchases or employee and the purchases he/she have been employee and the purchases he/she have been involved in the procurement.   Examples include involved in the procurement.   Examples include the evaluation of purchases by customer by the evaluation of purchases by customer by region, zip code with items or evaluation of region, zip code with items or evaluation of employees by title, birthdate, region or zipcode. employees by title, birthdate, region or zipcode.

–    

Introduction to TlogsIntroduction to Tlogs

What is a transaction log?What is a transaction log?

What are COMMITS?What are COMMITS?

What is an UNCOMMIT?What is an UNCOMMIT?

What is the relevance of a TLOGWhat is the relevance of a TLOG

TLOGTLOG

TLOGS allow:TLOGS allow:

UNDOsUNDOs

Real Time recoveryReal Time recovery

Multi-threading of usersMulti-threading of users

Transaction history, traceability, security Transaction history, traceability, security auditingauditing

Multiple machine synchronization Multiple machine synchronization (transaction shipping) Secondary and (transaction shipping) Secondary and Tertiary machinesTertiary machines

More on TLOGSMore on TLOGS

What price TLOG?What price TLOG?

I/O bandwidthI/O bandwidth

Disk space and growthDisk space and growth

OverheadOverhead

PROS and CONSPROS and CONS

Next weekNext week

Week 4 - Using the SQL Server Agent Service

More stuff on TLOGs

Unit 4 Using the SQL Server Agent ServiceUnit 4 Using the SQL Server Agent Service

The SQL Server Agent service runs the The SQL Server Agent service runs the components of task automation. Jobs, components of task automation. Jobs, maintenance plans, and alerts may be maintenance plans, and alerts may be configured to ensure that repetitive tasks and configured to ensure that repetitive tasks and responses to problems execute automatically. responses to problems execute automatically.

Jobs can have multiple job steps, with flow from Jobs can have multiple job steps, with flow from one job step to another based on the success or one job step to another based on the success or failure of a step. A job step for a Database failure of a step. A job step for a Database Engine job may be a transact-SQL script, a Engine job may be a transact-SQL script, a Windows executable, or an ActiveX script. Windows executable, or an ActiveX script.