ITC 226 Labs

Surface area configuration

Find the current settings for remote connections. what is the startup type and server status. Find and enable CLR integration.

SQL command

Use the sqlcommand tool to quer the server to find out what databases are on the server. Just query the "name" column. Get the names of the tables. Try entering and executing this SQL

Create Database Test
Go
Use test
go
Create table TestTable
(
  TestID int identity(1,1) primary key,
  TestInfo xml
 )

Create database and database objects

Remember to use the Lab worksheet and to list each step you do along with any problems

Here is a diagram of a simple database

Create the database. Call it CustomerOrder. Add a second database file.Set the maximum on the autogrow property to 50 megabytes. Make sure the database recovery type is full. What are the other recovery types. List them.

Add the tables with their primary keys and relationships. Choose datatypes for the columns that you think are appropriate. Make a database diagram. Save it to clipboard and paste it into the lab sheet.

Use the template browser to find a template for create view. Create a view that simply returns all the customer information (something like SELECT * from Customer). Save it.

Script the all database objects to a new query window. Do it as a single action rather than individually scripting each object. (Also do this even if you created the database in SQL.

Detach the database. LOcate the database files and give the path. Re-attach the database

Security Lab

First get this script and load it into a new query window: project sql

Run the script to create the database

The goal of this lab is to set up the security parameters for this database. This lab also counts as one of the exams.

for this database

Only those in the human resources department should be able to view or edit all the Employee Information
An employee should be able to view their own record.
An employee should be able to view the information for any project they are assigned to, but no other projects
Managers can view all project information and the names addresse info and phones of all employees assigned to the projects, but not their social security numbers or hiredates
the department information is available to all to view but no one but the database administrator can change the information
the company considers it critical to protect project information from falling into competitor's hands

This is complex and there are several possible solutions to this. In the notes area of the lab form explain why you set up the security stragegy you did

Backup and Restore

Back up the project Database. Connect to the SQLExpress server and restore the database to it.

Add some data to the Project Database (in the original server.) do a differential backup

Assume this database belonged to a small IT company that produces business software. IT has only one office in Seattle. What kind of disaster management plan would you develop for the database

Monitoring Sql Server

logs

first look at the current SQL Server Event logs. What filters can you apply? Look at the Windows NT Application Logs. Filter them to show just errors and warnings

A couple system procedures

Log in as other users (use the windows accounts that we made and maybe also the sql server logins) return to ITStudent.Open a new query window and run these stored procedures. Run them one at a time and tell what they do

Use MitchellPaperCo
sp_who

sp_helpdb

use Master

Select * from sysmessages

performance monitor

Open the Performance Monitor. Look at pages 412 through 424. Run the performance monitor--choose some measures of SQL Server Processes (Your choice) Create and save a performance log. Close it, then open replay the performance log.

Profiler

Create a trace in the profiler, choose 5 or six points to monitor. run the trace and save it as a file. Clear the profiler and then load and run the profiler file.

activity monitor

Use the following to createa lock. Open a query window and write the following code

Use MitchellPaperCo

Begin Tran
Update Customer
Set CustomerName="Tracy Killian"
Where CustomerId=12

Open a second Query window and type in this SQL

Begin tran
Update Sale
Set SaleDate="1/1/2009'
Where SaleID=1000

Select * from Customer
where customerID=12

Use the Activity Monitor to find and kill the deadlocked process

Query optimization

Type in this query

Select month(SaleDate) as [Month], count(SaleID) as [Number of Sales], sum(SaleTotal) as [Sales Total]
From sale
Group by month(Saledate)
Having SaleTotal > avg(SaleTotal)

look at execution plan

Send the Query to the Database tuner to see if it is has any suggestions

run the query with statistics

Redo the query as follows

Select month(SaleDate) as [Month], count(SaleID) as [Number of Sales], sum(SaleTotal) as [Sales Total]
From sale
where saletotal > (Select Avg(Saletotal) from Sale)
Group by month(Saledate)

What are the differences if any?

Automating the database

Install service pack 2 Make sure that the service agent is started and that it is set to automatic Create a job to back up MitchellPaperCo. Both database and log Schedule the job to run weekly at whatever time Create an operator who is to be notified by Net send when the job succeeds (You will have to turn on the messaging service in computer management services) Execute the job Check the history of the job--what does it say?

Next create a new management plan that backs up the Project database Give it a schedule and have it notify an operator Execute it and look at the history--what does it say?

Full Text Catalogs

This lab contains a walk through using SQL commands and then has you create and query a new full text catalog on your own

New File Group

First add a new file group for the full text catalog

USE master
GO
ALTER DATABASE AdventureWorks ADD FILEGROUP FTFG1
GO
ALTER DATABASE AdventureWorks ADD FILE ( NAME = N'AdventureWorksFT_data',
FILENAME = N'C:\TEST\AdventureWorksFT_data.ndf' , SIZE = 2048KB , FILEGROWTH =
1024KB ) TO FILEGROUP [FTFG1]
GO

Create a Full Text Catalog

Next create the actual catalog

USE AdventureWorks;
GO
CREATE FULLTEXT CATALOG AWCatalog ON FILEGROUP FTFG1 IN PATH 'C:\TEST' AS DEFAULT;
GO

Create Indexes

Now create indexes on the product tables

CREATE FULLTEXT INDEX ON Production.ProductDescription (Description) KEY INDEX
PK_ProductDescription_ProductDescriptionID ON AWCatalog WITH CHANGE_TRACKING AUTO;

CREATE FULLTEXT INDEX ON Production.Document (Document TYPE COLUMN FileExtension) KEY
INDEX PK_Document_DocumentID ON AWCatalog WITH CHANGE_TRACKING AUTO;

Populate Indexes

The following commands populate the indexes

ALTER FULLTEXT INDEX ON Production.ProductDescription START FULL POPULATION;
ALTER FULLTEXT INDEX ON Production.Document START FULL POPULATION;

Full Text Queries

A FREETEXT query:

SELECT ProductDescriptionID, Description FROM Production.ProductDescription
WHERE FREETEXT(Description, N'bike');

the same query with CONTAINS

SELECT ProductDescriptionID, Description FROM Production.ProductDescription
WHERE CONTAINS(Description, N'bike');

What is the difference in the results?

Here is a FREETEXTTABLE example

SELECT PD.ProductDescriptionID, PD.Description, KEYTBL.[KEY], KEYTBL.RANK
from Production.ProductDescription AS PD
INNER JOIN FREETEXTTABLE(Production.ProductDescription,Description,N'bike')
AS KEYTBL ON PD.ProductDescriptionID = KEYTBL.[KEY];

the following example uses FORMSOF. INFECTIONAL returns any grammatical form of the specified word. THESAURUS returns synonyms of the specified word

SELECT ProductDescriptionID, Description FROM Production.ProductDescription
WHERE CONTAINS(Description, N' FORMSOF (INFLECTIONAL, drive) ');
SELECT ProductDescriptionID, Description FROM Production.ProductDescription
WHERE CONTAINS(Description, N' FORMSOF (THESAURUS, metal) ');

The NEAR operator (the two queries are equivalent"

SELECT ProductDescriptionID, Description FROM Production.ProductDescription
WHERE CONTAINS(Description, N'mountain NEAR bike');
SELECT ProductDescriptionID, Description FROM Production.ProductDescription
WHERE CONTAINS(Description, N'mountain ~ bike');

This query use ISABOUT to place more empasis on "mountain" than on bike

SELECT Description FROM Production.ProductDescription
WHERE CONTAINS(Description,'ISABOUT (mountain weight (.8), bike weight (.2) )' );

Extending the lab

Create a new full text catalog on the customer tables. Create the indexes. Populate them and run three or four Queries using FREETEXT and CONTAINS with any of the other additions