Transcript Lecture 10 MS-SQL Server Basics

IT:Network:Applications

“Business runs on databases…”
◦ Understatement!




Requirements
Installation
Creating Databases
SIMPLE query
◦ Just enough to ensure its there

Hand-off to DBA




OS: 7, Server 2003, Server 2008
32- and 64-bit
IIS needed for reporting
.Net 2.0 or later (ASP.net)




Run ‘splash.hta’ from CD
Select Platform
Install ‘Server components…’
System check runs – ensures proper
environment

Components
◦ SQL DB Services
◦ Analysis Services (Online Analytic Processing (OLAP)
and data-mining)
◦ Reporting Services
◦ Notification Services (Send reports in “timely”
fashion)
◦ Integration (talk to other db)

Select basics and fine-tune via Advanced

“default” is first SQL server on this box
If need more SQL servers (virtual hosts), use
“Named Instance”

Use system account or use domain account

◦ domain must be “old-style” (YOURDOMAIN) not
DNS-style (YOURDOMAIN.local)

Reboot when done!

Database
◦ “Unit of Knowledge”

Table
◦ Sub-component of Database
◦ “Unit of Storage”

Database may be multiple tables
◦ Database for NWTC
 Table for Students
 Table for Programs
 One field in Students “points” to correct record in
Programs


SQL Server Management Studio
R-Click on Databases > New Database
◦ Give it a name

Now need tables and data
◦ Could create them in similar fashion to above
◦ Import data from a “flat” database (CSV,txt)

LOOK AT FILES FIRST
◦ Does data make sense?
◦ What fields do we have?
◦ What data types will we use?
 string, integer, real number, money,…
◦ Anything in there we don’t need?


R-Click on your database > Tasks > Import
Data
Data source will be Flat File Source
◦ Pick file

Will show you what it read
◦ Did it read it right?
◦ Watch embedded commas in data
◦ Watch quoting

Select destination (accept defaults)

On “Select Source Table and Views” edit your
mapping
◦ Specify field name and data type






string = varchar[n] (Variable Character non-unicode)
integer = int, smallint, bigint
decimal = float, real
$$ = money, smallmoney
Dates = datetime
Set Field name to “ignore” for fields we don’t
need

Table needs one field that uniquely identifies
a particular record
Must not be null!

If needed, add field, set to “int” or “bigint”

◦ Mark as NOT allow Null
◦ Set as Identity Column



BACKUP DATABASE CustomerDB
TO DISK = ‘C:\Backups\CustDB.BAK’
or
In SQL Server Mgmt Studio
◦ Rclick on Database > Tasks > Backup
◦ Delete “default” backup destination and Add new
one


SQL Server Mgmt Studio
R-Click on DATABASES (or your DB)
◦
◦
◦
◦



Restore DB
Specify DB to restore to & Specify source (our file)
Click box to restore this set
OPTIONS!
or
RESTORE DATABASE CustomerDB
FROM DISK = ‘C:\Backups\CustDB.BAK’



In Mgmt Studio
Configure backup as before
Script menu > Script Action to Job
◦ Left-side, select “steps” then Edit
 Make sure job is correct
◦ Left-side, select “schedules” then New
 Give name, pick schedule, Ok