Certification Galore > MCSE > SQL Server MCP
Microsoft SQL Server MCP Self-Study
70-028 - SQL 7.0 System Administration

What you need to know | Contributions

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.

What you need to know:

No info yet.

Contributions: