| Planning |
Develop a security
strategy.
- Assess whether to use Microsoft
Windows NTŪ accounts or Microsoft SQL
Server logins.
- Assess whether to leverage the
Windows NT group structure.
- Plan the use and structure of SQL Server
roles. Server roles include fixed server, fixed
database, and user-defined database.
- 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.
- Plan the security requirements for linked
databases.
|
Develop a SQL Server
capacity plan.
- Plan the physical placement of files,
including data files and transaction log files.
- Plan the use of filegroups.
- Plan for growth over time.
- Plan the physical hardware system.
- Assess communication requirements.
|
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.
- Assess whether to use clustering.
|
Develop a migration
plan.
- Plan an upgrade from a previous version of
SQL Server.
- Plan the migration of data from other data
sources.
|
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.
- Choose the replication type. Replication
types include snapshot, transactional, and
merge.
|
| Installation and Configuration |
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.
- Upgrade from a SQL Server 6.x
database.
|
Configure SQL Server.
- Configure SQL Mail.
- Configure default American National
Standards Institute (ANSI) settings.
|
| Implement full-text
searching. |
| Configuring and Managing Security |
| 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 |
Create and manage
databases.
- Create data files, filegroups, 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. |
Manage replication.
- Configure servers, including Distributor,
Publisher, and Subscriber.
- Create publications.
- Set up and manage subscriptions.
|
Automate
administrative tasks.
- Define jobs.
- Define alerts.
- Define operators.
- Set up SQLAgentMail for job notification and
alerts.
|
Enable access to
remote data.
- Set up linked servers.
- Set up security for linked databases.
|
| Monitoring and Optimization |
| Monitor SQL Server
performance. |
| Tune and optimize SQL
Server. |
| Limit resources used
by queries. |
| Troubleshooting |
| 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. |
