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