This exam tests your skills required to install, configure and administrate MS SQL Server 2000, Enterprise Edition. The targeted audience is candidates with one or more year of experience administrating and implementing relational data base in a medium to large computing environment. In other words, you should be fairly familiar with the SQL relational concept before you start this exam. It will be a rough road if you have no idea what a tables or data bases or indexes means!
Why take the SQL exam? There is no question that MS SQL Server is tied in the .net world. Furthermore, if you look at Windows Rights Management System, that safeguard digital information from unauthorized use and protection of sensitive information, such as Web content, documents, and e-mail, you will notice that it employed SQL Server as its data base. In other words, more and more applications will employ SQL server as its database engine! Before you start the project, check the official MS Ex am objective at http://www.microsoft.com/learning/exams/70-228.asp for detail information. You can obtain the necessary material or training by attending the following Instructor-led courses:
Course 2071: Query MS SQL Server 2000 with Transact-SQL
Course 2072: Administering a MS SQL Server 2000 Database
Course 2073: Programming a MS SQL Server 2000 Database
Exam Vital Statistics
Number of Questions:..........................45
Time:............................................. ..105 min
Level of Difficulty:..............................8/10
Score format:....................................Numeric score
Passing grade:...................................700
Preparation Setup
Two PC(P4) for two SQL servers connected through Cable modem router
MS Press: Microsoft SQL Server 2000 System Administration Exam 70-228
SAMS: Microsoft SQL Server 2000 unleashed
MSPress: Microsoft SQL Server 2000 Technical Reference Libra ry
MSPress: Book-On-line.
Addison-Wesley: SQL Queries for Mere Mortals
Apress: SQL Server Stored Procedures Handbook
Addison-Wesley: The Gurus Guide to SQL Server Stored Procedures, XML, and HTML
Reading data base books can drive your eyes red and dry some time. There is so much material in relational data base technology arena that you can read for weeks or months without ever approaching your exam target date! We all have different educational background and study habit, check out your local libraries and book stores for the best study material suitable for you!
A practical approach would use MSPress: Exam 70-228 as a starter and SAMS: Microsoft SQL Server 2000 unleashed as a secondary source. First read and do the lab exercises in MSPress: Microsoft SQL Server 2000 System Administration Exam 70-228 book. Pay careful attention to the setup parameters or options. Do not skip a single exercise! The exercises are designed with certain amount of dependence. The exercises are designed to run on a single SQL server, however, you will gain more insight if you change the exercises with the two SQL Servers approach! It is a good idea to try this on your second time and not on the first time lab exercise especially for linked servers and replications testing.
Books On Line or BOL came with the SQL Server software. It is indispensable and free! The Microsoft SQL Server 2000 Technical Reference Library is a six volumes set BOL without the on line portion. I got a used copy from EBay for only $35. In the MSPress 70-228 exam book, many references were made to MSPress: Microsoft SQL Server 2000 Administrators companion . It is very readable reference and a good item to add to your library. The only reason I did not pick up the book because I have too many DB reference books in my library. Next read the SAMS: Microsoft SQL Server 2000 unleashed. Make sure you load the sample data bases to practice your SQL commands and u tilities. You will find the reading at faster pace. Read the other books as you approaching the subject of your interest, such as stored procedure. You may pick up additional material on Data Transformation Services (DTS). If your Transact-SQL experience is light, consider to buy a copy of SAMS: Teach yourself Transact-SQL in 21 Days to get a more comprehensive hold on Transact-SQL. It is not a bad idea to start coding your own stored procedures and more esoteric Transact-SQL queries. Although you will not need a working knowledge of stored procedures and Transact-SQL for 70-228 exam, Youll need that skill for the 70-229 exam!
After you completed the intensive reading, go back and do the Readiness Review Exam in part 2 of MSPress 70-228 exam. I also used the Transcender exam as an additional practice source.
Here is some note I gathered during my study you may find it useful in your own preparation.
Installing and Configuring SQL Server 2000
Careful consideration and planning are required for your installation. It would be wise to have an installation plan and objectives in place. Not only used as documentation, but for performance tuning and disaster recovery later. Do you need fault tolerant for your installation? Can it be done with stand-by server, or cluster? What is the default collation, file locations, number of instance and service accounts in the setup?
How can you install SQL Server 2000 to co-exist with SQL 6.5 and SQL 7.0? What is the migration path?
What are net-libraries and their limitations? When do you need to use Multiprotocol instead of TCP/IP? How do you assign the proper rights for SQL Server Service and SQLAgentMail?
Can you execute your queries against your linked server(s) or formerly known as remote server(s)?
Creating SQL Server 2000 Databases
When you configure the databases pay attention to performance options such as disk capacity, network connectivity, and physical drive placement. One of key to database performance is the location of transaction log, primary and secondary file group. Youll be bombarded with many questions on this topic alone! What is the purpose of this database? Are they read-only or transactional?
SQL Server 2000 supports a number of methods to enforce data integrity; this includes data types, NOT NULL, DEFAULT definition, IDENTITY properties, constraints, rule, indexes, primary, foreign keys, and triggers. Make sure you know when to apply them. You will be test on these concepts in more detail later in exam 70-229.
Managing, Monitoring and Troubleshooting SQL server 2000 Databases
Monitor your database performance by using System Monitor, SQL Profiler and Enterprise Manager. What can you monitor using System Monito r vs SQL Profiler? Can you use both methods to monitor your SQL server? Do you know how to setup alerts so you will be notified?
Do you have a backup/recovery plan in place? Can you fully recover up to the time of failure? Can you recover the magical system state data?
Practice and rehearse your disaster recovery plan! Execute your DBCC CHECKDB, CHECKTABLE, SHOWCONTIG and other DBCC commands until you have them on your finger tips.
Do you know how to troubleshoot SQL transaction locking by using Transact-SQL, SQL Profiler or SQL Server Enterprise Manager?
Extracting and Transforming Data
When you setup IIS Virtual directories to support XML dont forget the security! Know how to export and import data by using Data Transformation Services (DTS), Bulk copy program, Bulk Insert and distributed queries.
Develop your schedule your DTS packages. When interacting with other SQL database or data source, be careful about the data type and precision , and collation.
Check the replication services! Know how to setup DTS using GUI is important but how about UDF?
Know your replication inside out. You must be fully comprehend snapshot, merge, and transactional replication without any hesitation!
Managing and Monitoring SQL Server 2000 Security
What is the difference between Windows Authentication and Mixed modes? Allowance must be made to clients connectivity (internal vs internet), Client operating system, security requirement and infrastructure.
How is spgrantlogin differs from spdbaccess?
How do you control your database access? Are you using server role, database, and application or user role to control security access? May be your application required more granular control by going into object permissions?
Can you audit your security setting by using SQL profiler or C2 Auditing. What will happen to your SQL Server if your audit log is full?
Managing, Monitoring and Troubl eshooting SQL Server 2000
How is your SQL server behaving? Notice any system bottlenecks in Disk I/O, network connectivity, memory, paging, and transactional throughput from your monitoring software?
What SQL transaction caused that system lockup? Does this problem stemmed from coding, database structure or programming logic? Perhaps, it is time to review that database schema and indexes!
Now, you can use your knowledge on DBCC INDEXDEFRAG, DBREINDEX, SHRINKDATABASE, and SHRINKFILE or modify that database schema or recode that Transact-SQL statement!
No doubt, the above note is just a tidbit and is far from complete. Exam 70-228 is one of enjoyable preparation I even did. It encompasses a fairly large territory involving Windows 2000 knowledge, IIS/XML, SQL database structure, and connectivity expertise. How involved will depend on your own personnel requirements. It is not an easy test! Please note that this exam required you have some hand on experi ence on the SQL Server 2000. So allocate sufficient time for study and lab exercises. Definitely, Ill not recommend this exam to be taken as a first MCSE test, youll struggle with server setup, MAPI profiles,..etc.
If you passed the test, it is only the beginning to the world of MS SQL, check some of the following web sites for additional SQL information:
MSDN SQL Server 2000
dbforums
SQL Server Magazine
SQL Server Central
SQL-Server-Performance.com
Hope you have established a good relation with MS SQL and start your journey on the right note!
http://www.mcseworld.com/forums/18441-exam-70-228-review-snoopy51.html
Jason Barrett http://www.mcseworld.com
Author:: Jason Barrett
Keywords:: Exam 70-228 Installing, Configuring, and Administrat ing MicroSoft SQL Server 2000, Enterprise Editio
Post by History of the Computer | Computer safety tips
No comments:
Post a Comment