MCTS (70-432) DBA Exam

By -
The MCTS Exam 70-432 focuses on 8 main areas:
  • Installing and configuring
  • Maintaining instances
  • Managing security
  • Database maintenance
  • Data management
  • Monitoring and troubleshooting
  • High availability
Let’s take a closer look at the 8 areas.
1. Installing and Configuring SQL Server 2008 (10%)
Installing and configuring Microsoft SQL Server 2008 takes up 10% of the exams questions and assumes you are familiar with:
  • installing and configuring server instances including using the configuration manager and SQL browser;
  • the standard of being able to install Microsoft SQL Server 2008 and related services as well as knowing the installation paths, file locations, etc. You can refer to this MSDN article onhow to install Microsoft SQL Server 2008;
  • being able to configure additional SQL Server components such as SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), etc.;
  • being able to implement database mail from installation, set up, and configuration;
  • and finally, it assumes you know how to configure, use, and populate full-text indexing.
2. Maintaining SQL Server Instances (13%)
The next focus point of the 70-432 exam is Maintaining SQL Server instances and makes up 13% of the questions on the exam. This focus point will test your knowledge and ability to:
  • manage SQL Server Agent jobs, operators, and alerts including creating, scheduling, enabling, and disabling jobs;
  • changing job step orders, managing notifications of job execution, and job logging;
  • managing performance condition alerts, SQL event alerts, and Windows Management Instrumentation (WMI) alerts;
  • understand and manage operator schedules, be able to fail safe and add new operators, and have a working knowledge of operator notification methods;
  • implement the Declarative Management Framework (DMF) including but not limited to creating, verifying, and enforcing a policy, scheduling a policy compliance check, and creating a condition;
  • and back up a SQL Server environment on an operating system level.
3. Managing SQL Server Security (15%)
The second largest focus point of the exam is based on managing SQL Server security and will make up 15% of the exam. This section of the exam will test your knowledge and ability to:
  • manage login, server, user, and database roles including being able to create, enable, and disable logins;
  • manage the security model in authentication mode, alter logins, enforce password policy, and manage fixed server roles;
  • manage user mapping, user-defined and fixed roles, and creating and deleting user roles;
  • SQL Server instance, database, schema, and object permissions including but not limited to logon triggers;
  • permissions vs. fixed role membership, cross-database ownership chaining, impersonation, and schema ownership;
  • understand transparent data encryption and impact of transparent data encryption on backups
  • use DDL triggers and logon triggers to audit SQL Server instances and be familiar with C2, common criteria in SQL Server instance audits, login failures, and event notifications
  • configure surface area through “sp_configure,” a function that displays and will allow you to change global configuration settings for the current SQL server (more on “sp_configure”here).
4. Maintaining a SQL Server Database (16%)
The next and largest focus point in the exam as listed by Microsoft’s learning center, is based on maintaining an SQL Server database and makes up about 16% of the exam’s total questions. This subject will require you to know:
  • how to backup and restore databases, as well as understand the different types of backups, such as differential backups, full backups, compressed backups, etc;
  • manage and configure databases and database snapshots, this includes creating, dropping, and reverting snapshots, as well as understanding database configurations options and recovery models;
  • maintain database integrity (using DBCC CHECKDB and suspect pages);
  • use maintenance plans along the way using the Maintenance Plan Wizard and Designer.
5. Performing Data Management Tasks (14%)
The next focus point takes up 14% of the exam and consists of questions related to performing data management tasks This includes importing and exporting data, managing data partitions, implementing data compression, maintaining indexes, and managing collations. These are, for the most part, just based on day to day use tasks such as working with indexes and using GUI tools to import and export data.
That’s a Lot of Expectations!
So far, there’s quite a bit of information to take in, don’t worry though, while the exam is very technical, it is also based on the most important aspects of SQL Server 2008. Which means during your training, while you may go over a lot you already know, you’ll be picking up a lot of great new information to help you along your career path. There are a lot of tasks, optimizations, and security aspects that you’ll learn along the way that will make your life easier in the long run, as well as help you pass the exam itself.
Back to the exam.
The final three sections of the 70-432 exam make up the remaining 32% of the exam:
6. Monitoring and Troubleshooting SQL Server (13%)
Next topic covers monitoring and troubleshooting, which makes up 13% of the exam. This focus point will test your ability to:
  • identify SQL Server problems in the database engine, SQL agent, and SQL browser services;
  • identify concurrency problems (blocks, locks, deadlocks, etc.) and SQL Agent job execution problems (proxy accounts, credentials, and job history);
  • locate error information such as error and agent logs, job execution history, and event logs.
7. Optimizing SQL Server Performance (10%)
Optimizing SQL Server performance is an important part of managing and running a successful SQL Server, and will take up 10% of the exam. This section mostly deals with using various sub-applications to optimize and analyze the server. You will be expected to be able to implement Resource Governor, use the Database Engine Tuning Advisor, collect trade data using SQL Server Profiler, collect performance data by using Dynamic Management Views (DMVs) and System Monitor, and finally, be able to use Performance Studio.
8. Implementing High Availability (9 percent)
The final section and focus point of the exam is based on Implementing High Availability (database replication/uptime assurance) and takes up the final 9% of the exam. This final section will ask that you understand how to implement database mirroring, clustered instances, log shipping, and replication as well as how to monitor, configure, and failover if necessary.



Comments

Post a Comment

Popular posts from this blog

Index Clean-Up Scripts

By -
dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Un-Used Indexes Script Declare @dbid int     , @dbName varchar(100);   Select @dbid = DB_ID()     , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count)  As (     Select [object_id]         , index_id         , Sum([rows]) As 'row_count'         , Count(partition_id) As 'partition_count'     From sys.partitions     Group By [object_id]         , index_id )    Select Object_Name(i.[object_id]) as objectName         , i.name         , Case              When i.is_unique = 1          ...
Read more

forgot sa password and no logins are added

By -
Image
Stop the SQL services Edit the properties of the SQL Service Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters Start the SQL services. These are now running in Single User Mode. Start CMD on tthe SQL server Start the SQLCMD command. Now we create a new user. Enter following commands CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy Go Now this user is created Now we grant the user a SYSADMIN roles using the same SQLCMD window. sp_addsrvrolemember ‘recovery’, ‘sysadmin’ go
Read more

The SQL Server DBA’s Guide to Teradata

By -
Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database . Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named. Release History: 2012-08-17 – Added UPDATE examples; reformatted 2012-08-12 – Added date examples 2012-08-08 – Initial Release SELECT Statements Simple SELECT SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...
Read more