SQL Server 7.0 Certification Exams? Here’s some help!

Certification Exams for the MCDBA and MCSE

ExamTips70028 – article covering focus areas and tips for the 70-028 exam.

ExamTips70029 – article covering focus areas and tips for the 70-029 exam.

 

70-028 Exam Tips

by Amber Sitko

email: overthrow@mediaone.net

Microsoft finally released the SQL Server 7 Administration test, 70-028 in April. To date, there still is very little information available to help students focus their studies. Hopefully, the information in this article will shed some light on the test.

Background

I have been a SQL Server DBA for over 2 years and working with SQL Server 7.0 for about seven months, yet still found the test a tough one. I spent at least 65+ hours preparing for the exam, took the 832 Admin class and still found the 70-028 a difficult exam to pass. Most of the difficulties I attribute to the lack of study material and advice available at the time I took the exam (June 2). At that time, Books On Line and “Inside SQL Server 7.0” by Ron Soukup and Kaley Delaney were the only reliable guides. However, neither focus on the exam but both remain excellent resources to gain an understanding of SQL Server 7.0.

If you have taken the NT Enterprise exam, the 70-028 exam is about the same level of difficulty (assuming

you are not an NT whiz).

The exam is 52 questions, with 680 score required to pass. The test is timed and you are allowed 90 minutes. There are a lot of scenario based questions that can be time consuming to read through and understand. The questions can contain many different concepts that you must know each one to answer correctly. Some people have had trouble finishing the test on time due to the length of the questions and the difficulty. However, I believe if you understand the concepts and don’t panic, you will be fine. The test is very passable with a through understanding of SQL Server.

Focus Areas

Microsoft seems to have an underlying motive when giving this exam. Much of the focus of the exam is about features that most DBAs probably do not use, although they could be very beneficial. My initial thoughts on the exam were that it would be mainly focused on features that are most heavily used and needed in general day-to-day activities. Instead, much of it is on replication and full-text indexing. There is really nothing wrong with this approach, it does force test takers to know a broad range of SQL Server’s capabilities instead of just the more mundane subjects.

The exam is not heavy on syntax. Knowing every comma, period and parameter is not important. However, do make sure that you know what commands to use for DBCC and for the full text index build and search.

Get hands on experience with the product. Implement replication and know how it works. You should know how replication works and be able to draw a diagram with where the publisher, distributor, subscriber’s live, what databases and tables live where in what scenarios. Know the replication model to use for what BUSINESS needs. Check out www.cramsession.com for some good examples.

Implement full text indexing and practice with it. I did not do this and regret my lack of experience. You don’t have to spend days with it, but even an hour or two will help you visualize the process and commands.

Know the backup and recover strategy for various scenarios, when to use what and where. Know when to use the transaction logs, database backups versus differential backups. You’ll need to understand the business issues of using the different backup strategies. Also, understand filegroups, data placement and the impact on the backup strategy.

Understand how to upgrade to SQL Server 7.0 from all previous SQL Server versions and NT versions. Know what hardware and software SQL Server 7.0 requires.

Roles are new to SQL Server 7.0 and its important to know what permissions are associated with each role. This is a fairly straight forward topic to memorize and doesn’t take long, so don’t make the mistake of skipping it. Know the Application Role, what it does, when to use it. Know Server Roles versus Database Roles, what you can change, create and what you cannot.

NT Security and SQL Server security are tightly integrated. This can be a very difficult area to get your thoughts around, but you must know it. Know when SQL Server and SQL Agent uses to start up with and what it should be used to start up with. Know what permissions SQL Agent runs under and when it uses the SQLAgentCmdExec account. I have had the benefit of working extensively with NT Security and SQL server because my account has many different domains and ways to access SQL Server, so this was an easy topic for me. However, if you had little to no experience, spend some extra time on the topic.

Profiler is important to know how to use it and when to use it. Know how to use in multiple server scenarios. There are situations when to use Performance Monitor instead of Profiler, understand Performance Monitors counters and what they are used for. You probably won’t be asked to understand each and every counter, but more on a conceptual level. For example, Profiler is used to trace specifics (like actual queries in particular databases) and Performance Monitor is used on a higher level for server and database statistics.

You should understand how to calculate how much space a table will occupy in the database. In Books On

Line, there is about a 40 step procedure on how to do this but you can ignore it for the most part. Obviously

that would consume too much time, instead know how to calculate given the number of rows and table width. I doubt that you would ever be required to calculate how much space the indexes will take.

Understand the configuration options for SQL Server. How to display system tables in Enterprise Manager,

how to set the query governor and when it applies. You can set the query governor on a server level and at a query level.

Know how to use bcp and what are the available options. You should know what you can bcp in and out, such as tables and temporary tables (local versus global).

Summary

This test covers a broad range of areas within SQL Server and is not be take lightly. However, it is fair test and quite passable. Make sure you understand the concepts, practice and spend time reviewing available resources.

Check out the 70-029 exam tips as well – 70-029


Resources

There are a variety of resources, but the ones I have found most helpful are:

Books On Line – comes with SQL Server 7.0

** “Inside SQL Server 7.0” by Ron Soukup and Kaley Delaney

** Transcender practice exams – www.transcender.com

** “Administering SQL Server 7” Exam Cram published by Certification Insider Press – the first release seems a bit sparse in its coverage and questions. There is a great deal of coverage of syntax, which is probably unnecessary for this exam.

** Cramsession – www.cramsession.com has concise study sheets and tips available. A great sheet to review to refresh with before you walk into to take the test.

** ” Microsoft SQL Server 7.0 System Administration Training Kit” by Microsoft – This seems to be very similar to the 832 class. The 832 class is 5 days and can be a great source of information if you have the right instructor (I did not, so it was a waste of time for the most part). If you are short on time and/or money, focus on the training kit instead.

** Microsoft SQL Server white papers on Replication, Full Text searching –

www.microsoft.com/sql/DeployAdmin.htm

** New Riders Publications also have received good reviews

There are a number of materials that are either not worth mentioning or I have not had time to review. I’m

also maintaining a web site of other’s comments and updated information at http://people.mw.mediaone.net/overthrow if you would like additional information.

Top

*******************************

70-029 Exam Tips

by Amber Sitko

email: overthrow@mediaone.net

Microsoft finally released the SQL Server 7 Administration test, 70-029. To date, there is even less available material for this test than the 70-028 exam. Hopefully, this article will help test takers focus their efforts for the exam.

Background

I have been a SQL Server DBA for over 2 years, working with SQL Server 7.0 for about seven months, and have about four years of data modeling experience. Still, even with what I would regard as considerable experience and day to day hands on, the exam was still difficult. I spent more time preparing for this exam, 80+ hours (more than 70-028), took the 832 Admin class and still found the 70-029 a difficult exam to pass. Most of the difficulties I attribute to the multiple checks (get one check wrong and the whole question is wrong) and the length of the questions.

Resources Used

More study materials were available to me than for the 70-028, the most notable being the Transcender tests. Transcender was extremely helpful as a review tool, but you probably will not be able to pass the test just using Transcender. I also used the Exam Cram study guide, but found it less helpful than I did for other tests. Unfortunately there is no Cramsession available. Books On Line and “Inside SQL Server 7.0” by Ron Soukup and Kaley Delaney remain reliable guides. However, neither focus on the exam but both remain excellent resources to gain an understanding of SQL Server 7.0. No one book or practice exam is enough for this exam, and three or four resources would not be overkill for most people (if you have time to get through them all).

Difficulty Level

If you have taken the 70-028 exam or the NT Enterprise exam, the 70-029 exam is about the same level of difficulty (assuming you are not an NT whiz).

Test Format

The exam is 49 questions, with 693 score required to pass. The test is timed and you are allowed 150 minutes. The test is NOT adaptive.

To say that the questions are long and time consuming is an understatement. There are many scenario-based questions that can be time consuming to read through and understand. I have heard rumors that the questions are several screens long, but I think the longest question I had was only about two and half screens. The questions can contain many different concepts that you must know each one to answer correctly. Some people have had trouble finishing the test on time due to the length of the questions and the difficulty. However, I believe if you understand the concepts, have lots of hands on experience and don’t panic, you will be fine. The test is very passable with a through understanding of SQL Server and relational concpts. I used the Transcender to review for the test and it helped a great deal. Since I was already familiar with the layout and format of the questions, I finished the test with an hour to spare.

There is overlap between the 70-028 and 70-029, so if you plan on taking both tests I would take them as close together as possible.

Focus Areas

The exam is not heavy on syntax, but you must know the correct format of the create database statement, especially in regards to filegroups. Knowing every comma, period and parameter is not important, but you must know the syntax to create filegroups, transaction logs, and primary filegroups.

As with any exam, get hands on experience with the product. Implement replication and know how it works. You should know how replication works and be able to draw a diagram with where the publisher, distributor, subscriber’s live, what databases and tables live where in what scenarios. Know the replication model to use for what BUSINESS needs. Check out www.cramsession.com for some good examples in the 70-028 study guide section. This is one area where I believe the 70-028 and the 70-029 overlap quite a bit.

Implement full text indexing and practice with it. You won’t need to know as much as you did for the 70-028 exam but you will need to know the FREETEXT, CONTAINS, FREETEXTTABLE syntax, results and purposes. Get some hands on with these commands, they will help you visual the commands and the required output. Full text indexing is probably pretty new to most people and it would be beneficial to know what these commands do and the output they generate. Know what types of commands to use on which columns, and how to search all the columns.

Understand filegroups, data placement and the impact on the backup strategy. Know the impact on placement of data and the transaction logs on the same drive, as well as how to separate the indexes from the data and the effect on performance.

DBCC SHOWCONTIG has a few questions. One is concerning external versus internal fragmentation. So far, no one I know of can find any substantial documentation on the issue from Microsoft but Mr. Paul Matthews has provided the clearest explanation that I know of so far – worthwhile to check out Script: SP To Run DBCC SHOWCONTIG On All Tables. Know the DBCC SHOWCONTIG output, and what to do to correct low/high numbers. Practice with building clustered/non clustered indexes with fill factors, inserting data and seeing how DBCC SHOWCONTIG is affected.

Know DBCC REINDEX and when to use it, especially in regards to clustered indexes and foreign keys. Books On Line and the Training kit differed in their recommendations and was a bit confusing. I decided Books On Line as the authoritative source and final word on it. The important thing is that you understand the DBCC REINDEX and how to use it. Although sources may differ in their recommendations, if you spend some time thinking about it you are going to learn more that way versus just memorizing one recommendation or an answer.

Know how to maintain referential integrity, entity integrity, domain integrity and what is redundant data. Essentially, you need to know Logical Data Modeling, the first three normal forms and how to verify you can produce the intended results (like a report by month sales by employee). This is a difficult part of the exam to capture easily, its not an area where you just memorize answers or do a few practices and understand it. It is also difficult to just read about it and gain any understanding. I would recommend working through the Northwind database, use “Microsoft SQL Server 7.0 Database Implementation Training Kit” and Transcender (although even Transcender is confusing and has some errors). Since I came into the exam having a few years of experience I have a difficult time imaging how anyone would pick this up without doing a great deal of practice.

Okay, now that you know how to logically model a database, you now have to know how to de-normalize it. In other words, balance the logical data model with physical requirements of the business queries. You’ll have to decided how to implement a few logical tables in the physical database based on a business scenario.

Profiler is important to know how to use it and when to use it. Know the different options that you have like “Find Worst Performing Queries”, identifying table scans, monitoring applications separately, and deadlocks.

There are situations when to use Performance Monitor instead of Profiler, understand Performance Monitors counters and what they are used for. You probably won’t be asked to understand each and every counter, but more on a conceptual level. For example, Profiler is used to trace specifics (like actual queries in particular databases) and Performance Monitor is used on a higher level for server and database statistics.

Understand ANSI SQL joins, what a RIGHT and LEFT join is, self joins, cross joins, and full joins. Know sub selects, the difference using EXISTS, and UNION. Understand the UNION and how ORDER BY is applied and the GROUP BY.

Know how to set up relationships, foreign keys, primary keys, with the correct syntax. Know the order you need to implement the relationships. For example, you must create the primary key table before you can create a relationship to it.

Understand the SET options, like SET ANSI_DEFAULTS option which is used for making different applications behave alike by complying to ANSI standards. The other options you should know are SET ARITHABORT, SET NUMERIC_ROUNDABORT, and SET ANSI_WARNINGS.

Although according to “Inside SQL Server” the COMPUTE BY clause is rarely used, Microsoft does require you know the syntax. I was surprised the CUBE and ROLLUP were not really covered, but my guess is they are saving it for the SQL Data Warehousing exam (70-019).

DTS is not covered in any detail, just know what data sources you can import and export too. Also, know that it is much easier to use than bcp’s and preferred.

Know the following:

* DEFAULTS, CHECK constraints, TRIGGERS and in what situations you would use each.

* Statistics, how they are maintained and when to update them.

* Isolation levels like REPEATABLE READ, READ COMMITTED, etc. Also know their effects on possible deadlocks.

* Order to complete processing to prevent deadlocks which is essentially to complete the transaction in the same order.

* Concatenate fields in the SELECT statement and how the SUBSTRING function works.

* Use of functions such as DATEADD, DATEPART in the SELECT statement.

* Stored procedures using output variables, called from other stored procedures and printing out the output.

 

Summary

As I was putting this article together it occurred to me how much material this exam covers and how difficult it is to learn much of it without actual work or hands on experience. I have heard that you will not pass this exam without extensive hands-on experience and I think that for most people it is true. The test does test a wide range of concepts and does so within very long questions. However, if you are fairly knowledgeable about SQL or get lots of hands on experience writing somewhat complex queries, you have been using SQL Server and are familiar with relational database concepts it is certainly passable. Make sure you understand the concepts, practice, practice, practice and spend as much time as you can reviewing available resources.

Resources

There are a variety of resources, but the ones I have found most helpful are:

Books On Line – comes with SQL Server 7.0

** Transcender practice tests – http://www.transcender.com, well worth it.

** “Inside SQL Server 7.0” by Ron Soukup and Kaley Delaney

** Cramsession – http://www.cramsession.com has concise study sheets and tips available. None at this time for 70-029 but keep checking.

** “Microsoft SQL Server 7.0 Database Implementation Training Kit” by Microsoft – This seems to be very similar to the 833 class. The training kit was helpful in that it gave opportunities to practice and includes a 120 trial version of SQL Server so you can install it on your desktop. However, to install full text searching, you must have NT Server.

** Microsoft SQL Server white papers on Replication, Full Text searching –

http://www.microsoft.com/sql/DeployAdmin.htm

** “Transact-SQL Programming” By Kline, Kevin / Gould, Lee / Zanevsky, Andrew. Personally I did not use it but I hear from reliable sources that it is one of the best books for Transact SQL review. Focus is more on 6.5, but most applies to 7.0.

** New Riders Publications also have received good reviews

** ” MCSE Database Design on SQL Server 7 : Exam Cram” published by Certification Insider Press – the first release seems a bit sparse in its coverage and questions. I was disappointed that it was not as good as the NT versions, but it was helpful.

There are a number of materials that are either not worth mentioning or I have not had time to review. I’m also maintaining a web site of other’s comments and updated information at http://people.mw.mediaone.net/overthrow if you would like additional information.

Previous article
Next article

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles