Exam 70-028
Administering Microsoft^® SQL Server™ 7.0

This certification exam measures your ability to administer a Microsoft SQL Server version 7.0 database. Before taking the exam, you should be proficient in the job skills listed below.

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.