Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition
This exam is core credit toward an MCDBA and elective credit toward an MCSE. I just passed the exam -- so I'm documenting what I studied & providing that list to you. I hope you find it and my other lists helpful. I have been working with SQL 7 for 1 1/2 years now, so that is some preaparation.
The passing score was 755 and the time allowed for the test itself was 1 hour and 45 minutes. To my surprise, I didn't get any questions on XML. There was a lot on filegroups, performance monitoring, replication, managing logins, and backup & restore. The questions on indexing dealt with what indexes to create for performance, when to rebuild them, and which ones to have clustered. I did not encounter any questions where I had to calculate the size of a table or index -- though that doesn't mean someone else won't get such questions.
Inside SQL Server 2000The software to play with
If you pass up Delaney's book, you can download the SQL Server 2000 evaluation edition from Microsoft -- or order it on CD for just a few dollars.General Information & Resources for SQL Server 2000
Resources by Exam Objective
Installing and Configuring SQL Server 2000
- Perform a custom upgrade. -- Haven't found information on this one yet.
- Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)
- Upgrading from SQL Server 7.0 to SQL Server 2000
- Upgrading Databases from SQL Server 7.0 (Copy Database Wizard)
Creating SQL Server 2000 Databases
Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases
- Indexes -- Inside SQL Server 2000, chapter 8
- Locking -- Inside SQL Server 2000, chapter 14
- Recompiling -- A stored procedure, that is.
- Recompiling a Stored Procedure
- Inside SQL Server 2000, pp. 862-63
- Bryan Kane of Eugene, OR highly recommends The Definitive Guide to SQL Server Performance Optimization. This 175-page ebook requires you to submit some personal information, but the book looks very good. Thanks, Bryan!.
Perform integrity checks. Methods include configuring the Database Maintenance Plan Wizard and using the Database Consistency Checker (DBCC).
Troubleshoot transactions and locking by using SQL Profiler, SQL Server Enterprise Manager, or Transact-SQL.
Extracting and Transforming Data with SQL Server 2000
Import and export data. Methods include the Bulk Insert task, the bulk copy program, Data Transformation Services (DTS), and heterogeneous queries.
Develop and manage DTS packages.
- Inside SQL Server 2000, pp. 50-52
- Types of Replication
- Implementing Replication
- In this discussion on merge replication and identity fields I learned a lot. Hope it helps you too. Hilary Cotter was good enough to answer most of my questions. At the end, I post a script that I developed for the purpose.
- ReplicationAnswers.com has answers to common replication questions.
Managing and Monitoring SQL Server 2000 Security
Configure mixed security modes or Windows Authentication. Considerations include client connectivity, client operating system, and security infrastructure.
Create and manage log ons.
Create and manage database users.
Create and manage security roles. Roles include application, database, and server.
- Add and remove users from roles.
- Create roles to manage database security.
Enforce and manage security by using stored procedures, triggers, views, and user-defined functions.
Set permissions in a database. Considerations include object permissions, object ownership, and statement permissions.
Manage security auditing. Methods include SQL Profiler and C2 auditing.
Managing, Monitoring, and Troubleshooting SQL Server 2000
Create, manage, and troubleshoot SQL Server Agent jobs.
Configure alerts and operators by using SQL Server Agent.
Optimize hardware resource usage. Resources include CPU, disk I/O, and memory.
- Monitor hardware resource usage by using System Monitor.
- Resolve system bottlenecks by using System Monitor.
- Tips for Using Performance Monitor CPU Counters -- very helpful tips
- Tips for Using SQL Server Performance Monitor Counters -- this one's even better than the last one. Helped me get to the bottom of a client problem. (That IS the real reason we study this stuff, isn't it?)
Optimize and troubleshoot SQL Server system activity. Activities include cache hits, connections, locks, memory allocation, recompilation, and transactional throughput.
- Monitor SQL Server system activity by using traces.
- What's New in SQL Server 2000 Profiler
- Monitoring Server Performance and Activity -- very broad overview
- Monitor SQL Server system activity by using System Monitor.
If you know of more resources which should be listed here, please email a link to me. I very much appreciate other resources to study and will be sure to acknowledge you on this page.
