Site hosted by Angelfire.com: Build your free website today!

SQL Server 7 Admin Objectives

Skills Measured by the Admin Exam
Planning
Develop a security strategy.
  • For server access, choose NT accounts or SQL Server logins appropriately.
  • Leverage the NT group structure where appropriate.
  • Know how and when to map Windows NT groups directly into a database or into a role.
  • Plan for SQL Server and database roles.
  • Including fixed server, fixed and user-defined database.
    • Given a situation, plan and implement appropriate application roles.
  • Choose the appropriate NT accounts to run SQL Server services.
  • Plan the security requirements for linked databases.
Develop a SQL Server capacity plan.
  • Plan the physical placement of files. data files
  • transaction log files.
  • Plan/use filegroups.
  • Plan for growth.
  • Plan the physical hardware system.
  • Assess communication requirements.
Develop a data availability solution.
  • Choose appropriate backup and restore strategy. Know related issues including:
    • full backup
    • differential backup
    • 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.
  • Given a situation, move data or databases from V6.5 to V7.
  • Plan data migration 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.
  • Know correct order to upgrade each.
  • Choose the replication type. Including:
    • Snapshot
    • Transactional
    • Merge
Installation and Configuration
Install SQL Server 7.0.
  • Choose appropriate:
    • character set.
    • Unicode collation.
    • sort order.
    • Know procedure to change each setting.
  • 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 account
  • SQL Server logins
  • 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 including:

  • fixed server
  • fixed database
  • public
  • user-defined database
  • application
Grant to database users and roles appropriate database objects and statement permissions.

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:
  • INSERT statement
  • SELECT INTO statement
  • bcp utility
  • Data Transformation Services (DTS)
  • BULK INSERT statement.

Back up system 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 upgrading from SQL Server 6.x.
  • Backup and restore problems.
  • Replication problems.
  • Job or alert failures.
  • Distributed query problems.
  • Client connectivity problems.
  • Problems in accessing SQL Server, databases, and database objects.
These are the same objectives that Microsoft publishes on their website, I've tried to simplify them a bit and to group them logically.

If you have the skills listed here, then you shouldn't have any problem with the certification exam. Be sure that you have confidence in your knowledge and good luck!