MCITP SQL Server 2005 Database Administration All-in-One Exam Guide in VS .NET

Generator QR Code in VS .NET MCITP SQL Server 2005 Database Administration All-in-One Exam Guide

Additionally, we would use dedicated user accounts for proxy accounts in SQL Server 2005 Neither the sp_activex_cmdexec_proxy nor the sp_add_proxy_ from_subsystem system stored procedures exists A DatabaseProxyRole does not exist 2 D The local SQLServer2005SQLAgentUser$InstanceName group is created when SQL is installed It has all the rights and permissions required by the SQL Server Agent service The administrators account has full rights and permissions and so would be adequate but in this question, the decision has been made to grant only limited access to the SQL Server Agent Within a domain, it is recommended to use a domain account; using the local system account or the network service account is not recommended in a domain 3 B Both SQL Mail and Database Mail support answering queries with a result set However, SQL Mail is deprecated Database Mail is the preferred choice in SQL Server 2005 ActiveX scripts or CLR-based stored procedures could not be easily configured to meet this need 4 B, C, F Database Mail is a replacement for SQL Mail SQL Mail is deprecated and Database Mail is considered a more secure solution SQL Mail required Outlook Outlook should be removed The SQL Server Surface Area Configuration tool can be used to disable SQL Mail and enable Database Mail Additional steps will be required, such as adding the SQL Server Agent service account to the DatabaseMailUserRole in the msdb database 70-444 1 C Since the database has high transaction activity, it makes sense that the indexes would become fragmented and would benefit from reorganizing None of the other choices addresses the indexes 2 A Creating your own templates in SQL Profiler would allow you to measure exactly what you want/need Templates can be created, exported from one server, and imported into other servers System Monitor and Agent jobs would have to be re-created on each of the 15 servers There is no such thing as sp metrics 3 B SQL Server 2005 includes many built-in dynamic management views and functions that provide server state information beyond the core four resources Use these to monitor the health of an instance, diagnose problems, and tune performance The two-part naming structure is sysdm_* 4 B Read and write values directly relate to disk activity None of the other choices relates to disk activity 5 B, D A classic purpose of SQL Profiler is to identify slow running queries The Standard (Default) and T-SQL duration templates will capture the duration of queries, including information on who ran them New to SQL Server 2005 are SQL Trace system stored procedures They can be used from within applications to manually create traces on any of the same events that can be captured with SQL Server Profiler

While the MCITP: Business Intelligence certification is outside the scope of this book, some Business Intelligence (BI) tools cross over into the MCITP: Database Administrator certification And they help you learn today s methods so you can help the people you work with stay in business tomorrow In this chapter, we ll cover some basics of: Business intelligence (BI) Basics SQL Server Integration Services (SSIS) SQL Server Reporting Services (SSRS)

One of the catchphrases in database circles is business intelligence or BI Databases today are used for much more than just raw data They provide intelligence for the business BI refers to different applications and technologies used to collect, access, and analyze data about the business The goal is to help decision makers make better business decisions Successful businesses don t happen by accident Instead, successful businesses typically have goals (targets), strategies (long-term plans), and tactics (individual steps taken to fulfill strategies) At any point, business decision makers ask How are we doing If the business is on track, they continue as they ve been doing If instead they find they aren t meeting goals or tactics aren t fulfilling the strategies, adjustments need to be made The key is having accurate data that is presented in a meaningful way to answer the question of How are we doing As an example of how BI may help decision makers, consider a car dealership that sells cars, trucks, and SUVs in three different locations We could easily glean some generic information such as we see in Table 14-1 from the database While, the simple table is a start, decision makers need more By drilling down into the details, we can look at many different aspects of the data How many white cars were sold How many red cars Consider the dimension of price How many cars were sold in the price range of $10K 20K How about the range