Skills measured by exam 70-028 |
Course 832 |
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. |
|