22-10-2012, 03:24 PM
A Tutorial on SQL Server 2005
A Tutorial.pdf (Size: 1.66 MB / Downloads: 280)
Versions of SQL Server 2005
• Enterprise (only support Windows Server OS)
– Includes all of the features of SQL Server 2005 and meets the hi
high demands of
gh enterprise online transaction processing and data warehousing ap applications plications
• Standard (mostly support Windows Server OS)
– Includes the essential functionality needed for e
e-commerce, data warehousing,
and line line-of of-business solutions
• Workgroup
– Includes the core database features of the SQL Server product li
line, and is the
ne, data management solution for small organizations that need a dat database with no
abase limits on size or number of users
• Express (free)
– A free, easy
easy-to to-use, lightweight, and embeddable version of SQL Server 2005,
includes powerful features such as SQL Server 2005 Reporting Serincludes Services and
vices SQL Server 2005 Management Studio Express
• Developer (can support Windows XP OS)
– Includes all of the functionality of Enterprise Edition, but is
licensed only for development, test, and demo use
• Compact
– A free, easy
easy-to to-use embedded database engine that lets developers build robust
Windows Desktop and mobile applications that run on all Windows platformsplatforms
A Simplified Installation Process
• Starting from
setup.exe
• Click buttons other than
“Cancel Cancel” in the Wizard
(using most of the default setup)
– Select components to install:
• SQL Server Database Services
• Workstation components, books online and development
tools
– User
“Advanced Advanced” option to setup installation path and
include sample databases
– Create a default instance
– Use the built
built-in System account: Local System
– User Windows Authentication Mode
Create A Database
• Start the Management Studio
• Connect to your SQL Server
• Right
Right-click the Databases folder in the
console tree, choose New Database from
the context menu
• Fill in the boxes in the database properties
sheet
• Click OK when you are finished
Create A Table
• Open Management Studio, drill down to
the DB354 database, and expand it
• Right
Right-click on Tables and select New
Table
• Type the column name and data type, and
setup column properties (in the window at
the bottom of the screen)
• Click on the Save button, enter a name for
the table and click OK
Create Views
• Open Management Studio and drill down to the
target database
• Expand the database and locate View
• Right
Right-click on View and select New View
• In Tables page, select target table and click Add
• Edit the view definition in the appearing GUI
• Click the Save button
• Name the view and save it
Security Modes
• Windows Authentication Mode
– The user logs on to a Windows domain; the user
name and password are verified by Windows
– The user then opens a trusted connection with SQL
Server
– Since this is a trusted connection, SQL does not need
to verify the user password
• Mixed Mode (SQL Server and Windows)
– The user logs on to their network, Windows or
otherwise
– Next, the user opens a non
non-trusted connection to SQL
Server using a separate user name and password
– The user name and password should be verified by
SQL Server
Create a standard login
• Open Management Studio and expand your server
• Expand Security and then click Logins
• Right
Right-click Logins and select New Login from the context
menu
• In the Logic name box, type Cmpt354
• Select SQL Server Authentication mode
• In the Password text box, type a complex string and
confirm it
• Uncheck
“User must change password at next login login”
• Under Default database, select your target database as
the default database
• Click the OK button
Granting, Revoking, and Denying
Permissions
• Open Management Studio, expand your server and
Databases, then select the target database
• Expand the database, then expand Security and Users
• Double
Double-click the target user, and select the Securables
page from the dialog window
• In
Securables section, click Add, and in the Add Objects
window click OK
• In the Select Objects window, click Object Types, then
check Tables and click OK
• Browse available table and check the target table, then
click OK
• If necessary, define more detailed permissions on the
target table
• Click OK to return to Enterprise Manager.
SQL Database Backup Modes
• Three Recovery Model
– Full recovery: everything gets logged in the
database
– Bulk
Bulk-logged recovery: Inserts, updates, and
deletes get logged, but bulk copies, SELECT
INTO statements, and index creations do not
– Simple recovery (
default mode mode) : nothing is
) held in the transaction log
• You can set the mode by using the
Options tab of the database property sheet