|
Standard |
Posted:
09/10/07
|
R State Standard |
£ Institutionally Developed |
College: n/a |
CIS 2162 – Administering
Microsoft® SQL Server
This course provides instruction on how to
administer a Microsoft SQL Server.
Topics include: Planning,
Installation and Configuration, Configuring and Managing Security, Managing and
Maintaining Data, Monitoring and Optimization, and Troubleshooting
|
Competency Areas: |
Hours: |
|
|
|
|
|
|
Planning |
Class |
4 |
|
Installation
and Configuration |
D. Lab |
4 |
|
Configuring
and Managing Security |
P.
Lab/O.B.I. |
0 |
|
Managing
and Maintaining Data |
Credit |
6 |
|
Monitoring
and Optimization |
|
|
|
Troubleshooting |
|
|
|
Prerequisite: CIS 2149 and CIS 2150 |
|
Corequisite: |
|
Course Guide |
|
Competency |
After completing this section,
the student will: |
Hours |
||
|
Class |
D. Lab
|
P. Lab/ O.B.I. |
||
|
PLANNING |
8 |
0
|
0 |
|
|
Assess whether to use Microsoft
Windows NT accounts or Microsoft SQL Server logins. |
|
|
|
|
|
|
Assess whether to leverage the
Windows NT group structure. |
|
|
|
|
|
|
|
|
|
|
|
Assess whether to map
Windows NT groups directly into a database or to map them to a role. |
|
|
|
|
|
Assess which Windows NT
accounts will be used to run SQL Server services. |
|
|
|
|
|
Plan an n-tier
application security strategy, and decide whether to use application roles or
other mid-tier security mechanisms such as Microsoft Transaction Server. |
|
|
|
|
|
|
|
|
|
|
Develop a
SQL Server capacity plan |
Plan the physical placement of
files, including data files and transaction log files. |
|
|
|
|
|
Plan the use of file groups. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Develop a
data availability solution. |
Choose the appropriate backup and
restore strategy. Strategies include full database backup; full database
backup and transaction log backup; differential database backup with full
database backup and transaction log backup; and database files backup and
transaction log backup. |
|
|
|
|
|
Assess whether to use a standby
server. |
|
|
|
|
|
|
|
|
|
|
Develop a
migration plan. |
Plan an upgrade from a previous
version of SQL Server. |
|
|
|
|
|
|
|
|
|
|
Develop a
replication strategy |
Given a scenario, design the
appropriate replication model. Replication models include single Publisher
and multiple Subscribers; multiple Publishers and single Subscriber; multiple
Publishers and multiple Subscribers; and remote Distributor. |
|
|
|
|
|
|
|
|
|
|
2 |
6
|
0 |
||
|
Install
SQL Server 7.0. |
Choose the character set. |
|
|
|
|
|
Choose the Unicode collation. |
|
|
|
|
|
Choose the appropriate sort order.
|
|
|
|
|
|
Install Net-Libraries and
protocols. |
|
|
|
|
|
Install services. |
|
|
|
|
|
Install and configure a SQL Server
client. |
|
|
|
|
Perform an unattended
installation. |
|
|
|
|
|
|
|
|
||
|
Configure
SQL Server. |
Configure SQL Mail. |
|
|
|
|
Configure
default American National Standards Institute (ANSI) settings. |
|
|
|
|
|
Implement
full-text searching. |
|
|
|
|
|
CONFIGURING AND MANAGING SECURITY |
4 |
4
|
0 |
|
|
Assign
SQL Server access through Windows NT accounts, SQL Server logins, and built-in
administrator logins. |
|
|
|
|
|
Assign
database access to Windows NT accounts, SQL Server logins, the guest user account, and the dbo user account. |
|
|
|
|
|
Create
and assign SQL Server roles. Server roles include fixed server, fixed
database, public, user-defined database, and application. |
|
|
|
|
|
Grant to
database users and roles the appropriate permissions on database objects and
statements. |
|
|
|
|
|
Audit
server and database activity. |
|
|
|
|
|
MANAGING AND MAINTAINING DATA |
20 |
20
|
0 |
|
|
Create
and manage databases. |
Create data files, file groups,
and transaction log files. |
|
|
|
|
Specify
growth characteristics. |
|
|
|
|
|
Load data
by using various methods. Methods include the INSERT statement, the SELECT
INTO statement, the bcp
utility, Data Transformation Services (DTS), and the BULK INSERT
statement. |
|
|
|
|
|
Back up
system databases and user databases by performing a full database backup, a
transaction log backup, a differential database backup, and a filegroup backup. |
|
|
|
|
|
Restore
system databases and user databases from a full database backup, a
transaction log backup, a differential database backup, and a filegroup backup. |
|
|
|
|
|
Configure servers, including
Distributor, Publisher, and Subscriber. |
|
|
|
|
|
Create publications. |
|
|
|
|
|
|
|
|
||
|
Automate
administrative tasks. |
Define jobs. |
|
|
|
|
Define alerts. |
|
|
|
|
|
|
|
|
||
|
|
|
|
||
|
Enable
access to remote data. |
Set up linked servers. |
|
|
|
|
|
|
|
||
|
MONITORING AND OPTIMIZATION |
3 |
5
|
0 |
|
|
Monitor
SQL Server performance. |
|
|
|
|
|
Tune and
optimize SQL Server. |
|
|
|
|
|
Limit
resources used by queries. |
|
|
|
|
|
TROUBLESHOOTING |
3 |
5
|
0 |
|
|
Diagnose
and resolve problems in upgrading from SQL Server 6.x.
|
|
|
|
|
|
Diagnose
and resolve problems in backup and restore operations. |
|
|
|
|
|
Diagnose
and resolve replication problems. |
|
|
|
|
|
Diagnose
and resolve job or alert failures. |
|
|
|
|
|
Diagnose
and resolve distributed query problems. |
|
|
|
|
|
Diagnose
and resolve client connectivity problems. |
|
|
|
|
|
Diagnose
and resolve problems in accessing SQL Server, databases, and database
objects. |
|
|
|
|
|
Suggested Resources |
Books:
Microsoft SQL Server, Microsoft Press, ISBN: 0735621985