Galore > MCSE > SQL Server MCP
Microsoft SQL Server MCP Self-Study
70-028 - SQL 7.0 System Administration
What you need to know | Contributions
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
- 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
- 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
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
- Configure servers, including Distributor, Publisher, and
- 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
Diagnose and resolve replication problems.
Diagnose and resolve job or alert failures.
Diagnose and resolve distributed query problems.
Diagnose and resolve client connectivity
Diagnose and resolve problems in accessing SQL
Server, databases, and database objects.
Did you just pass a test? Would you like to get
paid to write about it? Click
here to Find out more!
What you need to know:
No info yet.