Upgrading to SQL Server 2000

Kashef Mughal

Multiple Versions

SQL Server 2000 supports multiple versions of SQL Server on the same machine

It does that by either of the following methods

Version Switching– Lets you switch between 6.5 and 7 or between

6.5 and 2000 (not between 7 and 2000)

Named Instances– Lets you run either 6.5 and 7 as default

instance and 2000 as Named Instance

Before the upgrade

Anytime you do an upgrade, it helps to plan ahead before any problems arise

Make sure that the machine meets the hardware and software requirements

Backup the database to tape or another machine just incase the server dies on you during the upgrade process

Plan for enough downtime if you are upgrading the software and databases

Review the task list on Page 112

SQL Server 6.5 upgrade

You have only one way to upgrade - use the SQL Server Upgrade Wizard

The Upgrade Wizard will upgrade any or all of databases

You must have a default instance of SQL Server 2000 installed on your computer for the upgrade to work.

SQL Server Upgrade Wizard will not remove SQL Server 6.5 from the machine. In other words, you will have SQL 6.5 and 2000 and will have to uninstall 6.5 manually

SQL Server Upgrade Wizard

Prompts for:– Upgrade options– Logon options– Code page selection– Databases to upgrade– Database creation options– Configuration settings

SQL Server 6.5 upgrade

Methods– Local Drive (faster)– Tape backup

Upgrade requirements– Hard disk space (1.5 times size of db’s)– Windows NT with service pack 5– SQL Server 6.5 with service pack 5– Internet Explorer 5.0– Named Pipes (default \\.\pipe\sql\query)

SQL Server 7.0 upgrades

Upgrade and overwrite (Version Upgrade)– During default installation only– Converts all data to SQL Server 2000– SQL Server 7 must be offline

Upgrade data(Copy database wizard)– SQL Server 7 remains intact– Default (SQL Server 7) and named

instance (SQL Server 2000)– Uses DTS to migrate data– SQL Server 7 is online

SQL Server 7.0 upgrade

Methods– Version Upgrade– Online Database Upgrade

Upgrade requirements– Windows NT with service pack 5– No SQL sp’s– No additional hard drive space– Named Pipes (default \\.\pipe\sql\query)

Class Assignment #1

Uninstall SQL Server 2000 on your machine In order to save time, work with someone

else on the next steps Install SQL Server 7 on 1st computer (default

settings) - Key is all 1111111’s Do a version upgrade as shown in Lesson 2

on Page 114. While the machine is upgrading, install SQL

Server 7 on 2nd computer After the upgrade, review installation Take a break

Installation logs

Check these logs for errors and important messages

Sqlstp.log SQL Server Error log SQL Server Agent Error log Windows Application log

Common upgrade errors

Communication errors– Invalid Named Pipe– Invalid password– Invalid service account

Windows 98/Windows Me– Upgrade wizard not installed

Illegal objects– Illegal objects are not transferred

Insufficient resources

Class Assignment #2

In this one we will do an Online Database upgrade

Delete Northwind database from the computer you upgraded (SQL 2000)

Follow the instructions in Lesson #3 to upgrade Northwind database on 2nd computer (SQL 7)

After the upgrade, review installation results and compare them to the other upgrade

Finally uninstall and reinstall SQL Server 2000 with our original settings

Transact-SQL (T-SQL)

SQL (pronounced sequel or SQL) is the universal language for database. For SQL Server the dialect is T-SQL

TSQL has two types– Data Definition Language (DDL) – used

to create and modify databases e.g. CREATE DATABASE

– Data Manipulation Language (DML) – used to enter, modify, and extract data e.g. SELECT * FROM CUSTOMERS

SELECT statement

Simple syntax

SELECT results

FROM table_or_view

[WHERE search_conditions]

SELECT — columns to be retrieved FROM — data source (table, view, or

UDF) WHERE — qualifying conditions

SELECT statement

General syntax:SELECT [ALL|DISTINCT] select_list

[INTO[[database.]owner.]table_name]

FROM[[[database.]owner.]table_name|view_name|UDF]

[WHERE search_conditions]

[GROUP BY aggregate_free_expression]

[HAVING search_conditions]

[ORDER BY table_or_view_and_column]

[COMPUTE row_aggregate(column_name)]

[BY column_name]]

[FOR for_options]

[OPTION (query_hint)]

Simple SELECT statement

SELECT statement

Use Northwind

SELECT * FROM Customers

or

Pick Northwind from drop down

SELECT * FROM Customers

DISTINCT keyword

Using DISTINCT Specifies that only unique rows can

appear in the result set For Example SELECT DISTINCT city

FROM customers

WHERE clause

Specifies a search condition to restrict the rows returned

Basic Syntax is [WHERE <search_condition> = Value]

e.g. SELECT * FROM ORDERS WHERE CUSTOMERID=‘HILAA’

You can specify a number of conditions here using one or more operators

Comparison operators

Less than or equal to<=

Greater than or equal to>=

Not less than!<

Less than<

Not greater than!>

Greater than>

Not equal to!=

Not equal to<>

Equal to=

DescriptionOperator

More T-SQL operators

Arithmetic operators+ - / * %

Logical operatorsAND OR NOT

Concatenation operator +

IN - if a value matches items in a list Is Null - check if value is missing =Null

will not work

Wildcards - used for pattern

All phone numbers with an area code that ends in 12 and starts with a value other than 2

Phone like ‘[^2]12%’

Only the last names Bone or Cone Lname like ‘[BC]one’

First names ending in an like Jan, Nan, or Tan Fname like ‘_an’

All phone numbers in area code 212 Phone link ‘212%’

Any character except the one specified [^]

One character specified between [][]

A single character_

A string of any number of characters%

DescriptionWildcard

Aggregate functions

Operate on a collection of values but return a single, summarizing value

Calculate summary values– AVG(expr) – COUNT(expr) – MAX (expr) – MIN(expr) – SUM(expr)

expr is an expression, typically a field

ORDER BY

Specifies the sort order used on columns returned

ORDER BY syntax:

[ORDER BY [table.|view.]column |

select_list_no | expression [ASC|DESC]]

ORDER BY example:

select customername, city, region

from customers where country =

'USA’order by city, region

GROUP BY clause

Specifies the groups into which output rows are to be placed

GROUP BY syntax:

[GROUP BY [ALL] aggregate_free_expression

[, aggregate_free_expression...]]

[HAVING search_conditions] GROUP BY example:

SELECT region, COUNT customername)

FROM us_cust

GROUP BY region

GROUP BY example

ORDER BY HAVING example: Try this SELECT ProductID, SUM(Quantity)

FROM [ORDER DETAILS]

GROUP BY ProductID

HAVING SUM(Quantity) >1000

Class Assignment #3

Try the T-SQL provided on the handout

Next week - Chapters 5 and 6 All Done! Stay Warm ;-)