Managing the Managing the DataData

SQL OverviewSQL Overview

SQL OverviewSQL Overview

Client\Server RDMS (relational Client\Server RDMS (relational database management system)database management system) Commands are sent to the server and Commands are sent to the server and

answers are returned to the clientanswers are returned to the client Very efficient – low network trafficVery efficient – low network traffic The server does most of the workThe server does most of the work Very stable (unsuccessful transactions Very stable (unsuccessful transactions

rollback)rollback) SQL is the industry standardSQL is the industry standard

ServersServers

SQL ServerSQL Server

It is the only thing that touches the It is the only thing that touches the datadata

The data is under the server’s The data is under the server’s controlcontrol

MSDE – Microsoft Desktop MSDE – Microsoft Desktop EngineEngine

Freely distributableFreely distributable Limited to 5 connections or lessLimited to 5 connections or less Can be installed with ComCashCan be installed with ComCash

Microsoft SQL ServerMicrosoft SQL Server

Includes many tools Includes many tools DTS – Data Transformation ServicesDTS – Data Transformation Services Query AnalyzerQuery Analyzer Enterprise ManagerEnterprise Manager

No connection limitationNo connection limitation Must be purchased and installed Must be purchased and installed

separatelyseparately

Default ServerDefault Servervs. vs.

Named InstanceNamed Instance Default Server installationDefault Server installation

Server name = computer nameServer name = computer name Named instance (ComCash installs Named instance (ComCash installs

as a named instance)as a named instance) Server name = computer name\instance Server name = computer name\instance

name (computer name\comcash)name (computer name\comcash)

Logon and ConnectionsLogon and Connections

TCP/IP connections onlyTCP/IP connections only Default system administrator logonDefault system administrator logon

Username = saUsername = sa Password = null (blank password)Password = null (blank password)

DatabaseDatabase

FilesFiles .mdf file = Primary data file - stores the .mdf file = Primary data file - stores the

system tables and the data system tables and the data .ldf file = Transaction log files - used to .ldf file = Transaction log files - used to

store transaction information and can store transaction information and can be used to recover the databasebe used to recover the database

Attached vs. DetachedAttached vs. Detached

Never copy, delete, rename, or move Never copy, delete, rename, or move .mdf/.ldf files unless detached.mdf/.ldf files unless detached

Sample Data vs. Sample Data vs. ProductionProduction

Sample data is used only for Sample data is used only for evaluationevaluation The data can be backed up and restored The data can be backed up and restored

to the comcash databaseto the comcash database Live installations always use Live installations always use

comcash (Production) databasecomcash (Production) database In multiple database installations one In multiple database installations one

might see comcash01, comcash02, etcmight see comcash01, comcash02, etc

TablesTables

Rows and Columns (or records and Rows and Columns (or records and fields)fields)

Keys (tables typically have a key the Keys (tables typically have a key the guarantees uniqueness)guarantees uniqueness)