SQLServer 2005.docx

PRO Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQLServer QUESTION You work as DBA at Certkiller com You administer two Windows Server computers named Certkiller A and Certkiller B You install SQL Server on both Certkiller A and Certkiller B to host a new company database Certkiller A hosts a read-write copy of the company database in which all changes are made Certkiller B subscribes to a publication on Certkiller A and is only used for reporting A Windows domain administrator provides you with a domain user account named SQLSRV to use as the security context for the SQL Server services A password policy of days exists in the Default Domain Group Policy object GPO You install the database on Certkiller A and Certkiller B and configure replication Everything works fine for six weeks but then all SQL Server services fail You need to correct the problem What should you do Ask the Windows domain administrator to grant the Log on as service right to the SQLSRV domain user account Configure the SQLSRV domain user account with a new strong password Configure the new password in the properties of each SQL Server service that failed Create a local user account on Certkiller A named Certkiller A and a local user account on Certkiller B named Certkiller B Configure Certkiller A and Certkiller B to run under the context of the appropriate local user account Ask the Windows domain administrator to grant the SQLSRV domain user account membership in the Domain Admins group Answer B Explanation The Default Domain Group Policy object requires that passwords are changed every days In order to correct this problem you have to change the password in the Active Directory AND configure the new password on both you SQL Servers to the newly changed password QUESTION You are a database administrator of two SQL Server computers named Certkiller A and Certkiller B You have a Microsoft NET application that has been modified so that it now accesses a database on Certkiller B in addition to Certkiller A You do not want the user application to connect directly to Certkiller B You need to enable the data retrieval from Certkiller B while maintaining the ability to assign different permissions to different users who use the NET application What should you do Change the NET application to define a new server connection to Certkiller B Configure a linked server on Certkiller A to point to Certkiller B Change the stored procedures called by the NET application to include the OPENXML command Configure a linked server on Certkiller B to point to Certkiller A Answer B Explanation SQL Server lets you access external data sources from your local Transact-SQL code You need to define a linked server for each external data source you want to access and then configure the security context under which your distributed queries will run After you create a linked server you can use the Transact-SQL OPENQUERY function to execute your distributed queries QUESTION You are a database administrator for Certkiller com Your company uses a different company's application that is based on SQL Server Standard Edition The application executes a query that uses an index query hint The index query hint is not suitable for your environment but you cannot modify the query You need to force the application to use a different query execution plan What should you do Create a plan guide for the query Clear the procedure cache Create a new covering index on the columns that the query uses Update the statistics for all of the indexes that the query uses Answer A Explanation Over the past few years Microsoft SQL Server has increased its presence in the industry and has reduced its TCO This reduced TCO is a direct result primarily of the numerous self-tuning mechanisms built into Microsoft SQL Server These mechanisms automatically perform tasks that would otherwise have to be performed by experienced database administrators One such mechanism is the cost- based optimizer CBO that is used to dynamically generate query execution plans The CBO probes several system-wide resource states and employs many complex heuristical algorithms to generate the best possible plan for a given query and the underlying table and index structures This mechanism works well for the vast majority of user queries but there are times when experienced users need to force a particular query plan based on some prior knowledge or insights into future uses QUESTION You are a database administrator for Certkiller com Your SQL Server computer contains one user database that holds sales transaction information Users report that the queries and stored procedures that they use every day are taking progressively longer to execute You also notice that the amount of free disk space on the SQL Server computer is decreasing You need to create a maintenance plan to correct the performance and storage problems What are two possible ways to achieve this goal Each correct answer presents a complete solution Choose two In the SQL Server Maintenance Plan Wizard use the Check Database Integrity option In the SQL Server Maintenance Plan Wizard use the Reorganize Index option In the SQL Server Maintenance Plan Wizard use the Shrink Database option In the SQL Server Maintenance Plan Wizard use the Clean Up History option In the SQL Server Maintenance Plan Wizard use the Execute SQL Server Agent Job option Answer B C Explanation When you indexes are not heavily fragmented you can reorganize indexes which uses few system resources and runs automatically online In SQL Server certain operations such as large delete operations or -one-time data loads might leave database files larger than they need to be SQL Server enables a DBA to shrink each file within a database to remove unused pages and regain disk space QUESTION You are a database administrator for Certkiller com You are responsible for a SQL Server database that has several indexes You need to write a stored procedure that checks the indexes for fragmentation Which Transact-SQL statement should you use DBCC INDEXDEFRAG SELECT FROM sys dm db index physical stats SELECT FROM sys indexes DBCC DBREINDEX Answer B Explanation The index physical stats function takes five parameters database id object id index id partition id and mode This function returns row size and fragmentation information QUESTION You work as DBA at Certkiller com You administer two SQL Server computers named Certkiller A and Certkiller B Certkiller A and Certkiller B contain a copy of a database named Sales The database is replicated between Certkiller A and Certkiller B by using transactional replication A full backup of each database is performed every night Transaction log backups are performed every hour Replication latency is typically less than two minutes One afternoon the Sales database on Certkiller A becomes corrupted You are unable to repair the database The Sales database on Certkiller B is unaffected You need to return the Sales database on Certkiller A to normal operation as quickly as possible You must ensure a minimum loss of data and minimal impact to users of either server What should you do Perform a full database backup on Certkiller B Restore the backup to Certkiller A Restore the most recent full database backup and all transaction logs made since the full backup was made Restore only the most recent transaction log backup Detach the Sales database on Certkiller B Copy the database file to Certkiller A and attach the database on both servers Answer A Explanation Detaching the database will not apply to the minimal impact on users as it will be offline during copy Restoring only the latest transaction log will not give you a working database and restoring last full backup and all transaction logs after that will not apply to the restore normal operations as quickly as possible part of the requirements QUESTION You are a database administrator for Certkiller com The company runs a popular database-driven Web site against a SQL Server computer named Certkiller B You need to ensure a quick response time and appropriate audit trail in the event that Certkiller B experiences excessive traffic due to denial-of-service DoS attacks Which two actions should you perform Each correct answer presents part of the solution Choose two Configure the new performance alert to start a SQL Server Profiler trace Create a new performance alert to monitor the Current Bandwidth counter Configure the new performance alert to start a Network Monitor capture Create a new performance alert to monitor the Bytes Total sec counter Answer C D Explanation Using alerts System Monitor tracks specific events and notifies you of these events as requested An alert log can monitor the current performance of selected counters and instances for objects in SQL Server When a counter exceeds a given value the log records the date and time of the event An event can also generate a network alert Characteristic for a typical DoS attack is a large number of Bytes sec Characteristic for a DDoS attack is also a large number of Bytes sec but also a large number of connections made from a large number of hosts QUESTION You are a database administrator for Certkiller com One of the databases on a SQL Server computer contains a stored procedure Users run this stored procedure to import data into a table The stored procedure needs to use the TRUNCATE TABLE command before importing new data into the table However the users who run the stored procedure do not have permission to truncate the table You need to provide a way for the stored procedure to truncate the table before it imports new data What should you do Configure the stored procedure to use the EXECUTE AS command Configure the stored procedure to be owned by the same database user as the table Assign the users DELETE permission in the table Add the users to the db datawriter fixed database role Answer A Explanation In SQL Server you can implicitly define the execution context of the following user-defined modules functions except inline table-valued functions procedures queues and triggers By specifying the context in which the module is executed you can control which user account the SQL Server Database Engine uses to validate permissions on objects that are referenced by the module This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules Permissions must be granted to users only on the module itself without having to grant them explicit permissions on the referenced objects Only the user that the module is running as must have permissions on the objects accessed by the module Syntax Functions except inline table-valued functions Stored Procedures and DML Triggers EXEC EXECUTE AS CALLER SELF OWNER 'user name' QUESTION You are a database administrator for Certkiller com You have separate SQL Server development and production environments You use the Business Intelligence Development Studio to create a SQL Server Integration Services SSIS package in your development environment Then you use the SSIS package to import data into your development environment from one of your company's trading partners You need to deploy the SSIS package to your production environment Your production environment uses different table names than your development environment What should you do Save the SQL Server Integration Services SSIS package to a file Copy the file to the production server Configure the SSIS package on the production server to use the new file Back up the master database and restore it to the production server Rename the appropriate tables inside the master database Create a SQL Server Integration Services SSIS package configuration Build a deployment utility Copy the deployment folder for your SSIS project to your production server Execute the manifest file Back up the msdb database and restore it to the production server Rename the appropriate tables inside the msdb database Answer C Explanation You have to use a SSIS package to accomplish this but you can not use the package built for your development environment as the table names differ QUESTION You are a database administrator for Certkiller com You notice that one of the data files on a SQL Server computer is corrupted You need to restore the database from the most recent set of backups You want to perform this task as quickly as possible with a minimum loss of data What should you do first Restore the most recent transaction log backup Restore the most recent full database backup Perform a full database backup Perform a transaction log backup Answer D Explanation In order to restore from the most recent set of backups you have to first ensure that you can restore all transactions that have occurred between the time of the backup and the time when the database went corrupt In order to accomplish this you should backup the transaction log QUESTION You are a database administrator for Certkiller com A SQL Server computer named Certkiller A has a database named Inventory Certkiller A is responsible for aggregating manufacturing part numbers from your company's trading partners The manufacturing part numbers are stored in the Product table in the Inventory database Every night data is sent as text files from each trading partner to Certkiller A You need to import data and ensure that no duplicate manufacturing part numbers exist in the data imported from the text files that are stored in the Inventory database on Certkiller A You want to achieve this goal with the minimum amount of impact on your company's trading partners and your IT department What should you do Ensure that each of your company's trading partners uses unique key values for manufacturing part numbers Then import the text files into the Product table Ensure that each text file is stored in an Extensible Markup Language XML file Use Extensible Stylesheet Language Transformations XSLT to automatically remove duplicates before data is imported into the Product table Import the data from each text file into a staging table Write a query to include a HAVING clause to remove duplicate values before inserting results into the Product table D Place a unique index on the PartNum column Answer C Explanation In this particular case a staging table that removes all duplicates with a T-SQL query including the HAVING clause would solve the problem with least impact on your company's trading partners HAVING specifies a search condition for a group or an aggregate HAVING can be used only with the SELECT statement HAVING is typically used in a GROUP BY clause QUESTION You are a database administrator for Certkiller com The company has a SQL Server computer named Certkiller A A database on Certkiller A stores sales history data for the company's Web-based order system Twenty of the business analysts in the company create ad hoc queries against the database The performance of Certkiller A is routinely slow You need to find out which business analyst is causing the problem What are two possible ways to achieve this goal Each correct answer presents a complete solution Choose two Run the sp who system stored procedure Use the Activity Monitor to view the activity of specific business analysts Run SQL Trace system stored procedures to gather database activity Create a trace that uses a predefined template by using SQL Server Profiler Answer C D QUESTION You are a database administrator for Certkiller com Your company owns a chain of retail stores Each retail store maintains point-of-sale transactions on its own SQL Server computer in a database table named Sales The Sales table also contains sales data from other stores to enable customer returns to any of the retail stores The sales data is refreshed from the main office to each retail store hourly A trigger named trg Coupon on the Sales table is used to generate sales coupons based on customer sales and buying patterns You need to configure replication between the server in each retail store and a central server in the main office by using the fewest number of steps Replication does not have to be in real time What should you do Use transactional replication between the server in each retail store and the central server in the main office Set up multiple merge publications one at each retail store and one on the central server in the main office Use snapshot replication Configure the trg Coupon trigger on the server in each retail store to use the NOT FOR REPLICATION option D Use merge replication Configure the trg Coupon trigger on the server in each retail store to use the NOT FOR REPLICATION option Answer D Explanation Merge replication like transactional replication typically starts with a snapshot of the publication database objects and data Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred Merge replication is typically used in server-to-client environments Merge replication is appropriate if Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers In some cases it is desirable for user activity in a replication topology to be treated differently from agent activity For example if a row is inserted by a user at the Publisher and that insert satisfies a check constraint on the table it might not be required to enforce the same constraint when the row is inserted by a replication agent at the Subscriber The NOT FOR REPLICATION option allows you to specify that some database objects are treated differently when a replication agent performs an operation QUESTION You are a database administrator for Certkiller com The company hosts SQL Server databases for subscription-based customers New customer databases are created frequently Databases are removed when customers subscriptions end Company managers want to track who creates each database and when each database is created To store this information you create a SQL Server database named Logging You need to ensure that the appropriate information is written to the Logging database What should you do Use the SQL Trace stored procedures to log database creation activity Create a stored procedure Configure the stored procedure to write the appropriate information to the Logging database Create a DDL trigger that runs when a new customer database is created Configure the DDL trigger to write the appropriate information to the Logging database Configure Service Broker to run a stored procedure that writes the appropriate information to the Logging database Answer C Explanation DDL triggers can fire in response to a Transact-SQL event processed in the current database or on the current server The scope of the trigger depends on the event For example a DDL trigger created to fire in response to a CREATE TABLE event will do so whenever a CREATE TABLE event occurs in the database A DDL trigger created to fire in response to a CREATE LOGIN event will do so whenever a CREATE LOGIN event occurs in the server You can use the CREATE DATABASE DDL statement to create a trigger that fires when a database is created QUESTION You are a database administrator for Certkiller com You are responsible for managing SQL Server computers that run Microsoft Windows Server Enterprise Edition The company's Microsoft Active Directory administrators handle all Group Policy object GPO deployments The Active Directory administrators have deployed a security template named SQL Security inf that has the appropriate settings to meet the company's security policy You need to identify the existing configuration of each SQL Server computer to ensure that it meets the company's security policy What should you do Use Network Monitor on each SQL Server computer to capture a detailed report of the types of network traffic on the local network adapter Use the Performance Logs and Alerts snap-in to create counter logs for the Network Interface performance object Use the Security Template snap-in to analyze the SQL Security inf security template Use the Security Configuration and Analysis tool on each SQL Server computer to identify discrepancies between system settings and database settings Answer D Explanation To simplify security configuration and give administrators a means to monitor security settings Microsoft developed the Security Configuration and Analysis tool for the Microsoft Management Console MMC and a corresponding set of security templates for specific scenarios Using these tools you can analyze a server against a known previously saved configuration or you can analyze it against a recommended security configuration template QUESTION You are a database administrator for Certkiller com A software developer in the company is running the following query against a SQL Server database SELECT Surname FROM Employees WHERE UPPER Surname LIKE 'COR ' The software developer reports that the query runs quickly on a test database that has a small number of rows However the query runs very slowly on the production database that has millions of rows The Surname column stores data in mixed case by using case- sensitive collation But the query needs to perform a case-insensitive search You need to improve the performance of this query However you do not have permission to change the collation orders And you cannot modify the application code that adds rows to the database Which three actions should you perform Each correct answer presents part of the solution Choose three Create an index on the Surname column Modify the query to include the following Transact-SQL statement SELECT Surname FROM Employees WHERE Surname LIKE UPPER 'cor ' Execute the following Transact-SQL statement ALTER TABLE Employees ADD UpperSurname AS UPPER Surname Execute the following Transact-SQL statement ALTER TABLE Employees ADD UpperSurname AS CAST Surname AS nvarchar Create an index on the UpperSurname column Modify the query to include the following Transact-SQL statement SELECT Surname FROM Employees WHERE UpperSurname LIKE 'COR ' Answer C E F Explanation As the database uses case-sensitive collation the easiest way to speed up the query is to create a new column in the database that stores the surnames in uppercase index the column you just created and the run the SELECT T-SQL query against the new column QUESTION You are a database administrator for Certkiller com Your SQL Server database contains a table named Customer with a column named PostalCode in it New PostalCode values are added regularly You need to ensure that values contained in the PostalCode column are verified You need to retrieve from the Postal Service in each country in which you have customers the domain of values for the PostalCode column Rapid response time for data access is of primary concern What should you do Implement a database trigger that looks up the postal code data from a server maintained by the Postal Service Import the Postal Service data on a nightly basis into a PostalCode table Then configure a foreign key on the Customer table to the PostalCode table Configure a CHECK constraint on the PostalCode field of the Customer table to allow only valid values Implement a CLR trigger that looks up the postal code data by using a Web service offered by the Postal Service Configure a new PostalCode data type and ensure that it conforms to the Postal Service specifications for the format for each country Answer B Explanation A foreign key FK is a column or combination of columns that is used to establish and enforce a link between the data in two tables You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table In a foreign key reference a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table This column becomes a foreign key in the second table A FK on the PostalCode column in the Customer table that points to a PostalCode table would enforce the use of correct postal codes QUESTION You are a database administrator for Certkiller com A user named Certkiller belongs to the Windows SalesManagers group Certkiller needs a Reporting Services report to display annual sales information by territory A user named Joe reports to Certkiller and belongs to the Windows Sales group He needs a report that is limited to his sales territory but still displays the same detailed sales data that is on Certkiller's report You need to design a secure solution that meets the reporting needs of the users and that consumes the minimal amount of server resources What should you do Design a report for Certkiller that includes two data regions One data region is used for summary sales data The other data region is used for the detailed sales data for each sales representative Then design a report for Joe that displays the detailed sales data Design two reports for Certkiller One report includes the territory information and one report includes the detailed sales data Then design a report for Joe that displays the detailed sales data for his territories Design a report for Certkiller that includes the territory information Then design a report for Joe that includes detailed sales data for his territories Finally configure Jack Bill report so that it includes the data in Joes report Design a parameterized report for Certkiller that includes all territory information Then design a linked report for Joe that links to Certkillers report Answer D Explanation Parameterized filters allow different partitions of data to be sent to different Subscribers without requiring multiple publications to be created parameterized filters were referred to as dynamic filters in previous versions of SQL Server A partition is simply a subset of the rows in a table depending on the settings chosen when creating a parameterized filter each row in a published table can belong to one partition only which produces nonoverlapping partitions or to two or more partitions which produces overlapping partitions A linked report is a report with settings and properties of its own but links to the report definition of another report Linked reports are useful when you have a base report that you want to vary for specifie groups or users for example regional report that returns different data based on a regional code A linked report is typically created from a parameterized report when you want to vary and then save different parameter values with each report instance QUESTION You are a database administrator for Certkiller com You receive alerts reporting that several transactions on your SQL Server database have terminated due to a deadlock error You need to find out the causes of the deadlocks What should you do Use System Monitor to trace the Application instance of the Number of Deadlocks sec counter in the SQL Locks object Use the sys dm tran locks dynamic management view DMV Run the Database Engine Tuning Advisor DTA and implement the recommendations Run SQL Server Profiler and create a trace with the Deadlock graph event group and extract deadlock events Answer D Explanation To trace deadlock events add the Deadlock graph event class to a trace This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock SQL Server Profiler can extract the XML document to a deadlock XML xdl file which you can view later in SQL Server Management Studio You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events or to separate files QUESTION You are a database administrator for Certkiller com You are configuring a new SQL Server computer named Certkiller A Certkiller A will run Reporting Services It will also be configured to automatically perform database backups and other maintenance tasks There are no other SQL Server computers in the network environment All access to Certkiller A will be made by using Certkiller As DNS name You need to disable any unnecessary services on Certkiller Which service or services should you disable Choose all that apply Microsoft Distributed Transaction Coordinator Internet Information Services SQL Server Agent SQL Server Browser Answer A D Explanation IIS or later is required for Microsoft SQL Server Reporting Services SSRS installations SQL Server Agent is a Microsoft Windows service that allows you to automate some administrative tasks like backups and maintenance tasks SQL Server Agent runs jobs monitors SQL Server and processes alerts The SQL Server Agent service must be running before local or multiserver administrative jobs can run automatically Microsoft Distributed Transaction Coordinator is used mainly for clustering SQL Servers SQL Server Browser is needed when you use named pipes to connect to a specific instance on the SQL Server QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A Certkiller A is a member of a Microsoft Active Directory domain You do not have any rights or privileges to perform domain administration However you have been granted membership in the local Administrators group on Certkiller A You perform most of the management of Certkiller A from your administrative workstation However for security reasons you want to track all attempts for interactive logons and network connections to Certkiller What should you do Configure the SQL Server service on Certkiller A to audit all successful and failed logon attempts Run the SQL Server Profiler and use a standard default template Edit the local security policy of Certkiller Then configure success and failure auditing on the Audit logon events setting D Create a Group Policy object GPO that is configured for success and failure auditing of the Audit account logon events setting Ask the domain administrator to link the GPO to the object containing Certkiller A Answer C QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller Certkiller A is configured to automatically perform transaction log backups database integrity checks and other maintenance tasks on a regular basis Another administrator uses the SQL Server Surface Area Configuration tool to reconfigure Certkiller You notice that Certkiller A no longer performs the automated maintenance tasks You need to ensure that the maintenance tasks on Certkiller A are completed automatically What should you do Manually perform a full backup of the msdb database B Reconfigure the server so that the SQL Server Agent service starts automatically Reconfigure Certkiller A to use Windows Integrated authentication Reconfigure the SQL Server service to log on by using an administrative user account Answer B QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A Certkiller A replicates with other SQL Server computers and manages multiserver automation jobs It also exports data to staging databases for export to a data warehouse After a few months without incident Certkiller A fails to start after a reboot You review the security log which returns the results that the Security Log Results exhibit shows You then review an individual audit entry which the Individual Audit Entry exhibit shows The Default Domain Group Policy object GPO is configured with a Password Policy and Account Lockout Policy as shown in the following table You need to ensure that Certkiller A runs properly What should you do Unlock the Certkiller sqlsrvc account Then configure the account with a strong password Configure the SQL Server services to use the new account password Delete the current Certkiller sqlsrvc account Then create a new Certkiller sqlsrvc account and ensure a strong password Finally configure the account so that users cannot change the password Configure the SQL Server services to use the new account password Configure the Certkiller sqlsrvc account as a member of the local administrators group on Certkiller Configure the SQL Server services to use the new account password Enable the Certkiller sqlsrvc account Then change the password to a strong one Finally configure the account so that users cannot change the password Configure the SQL Server services to use the new account password Answer A QUESTION You are a database administrator for Certkiller com The SQL Server computer has been in operation for more than one month This past week query performance problems have led you to investigate locking contention The sys dm os wait stats dynamic management view DMV is showing a high value in the max wait time ms column You need to find out if this value is a factor in the current performance problems You also must minimize the impact on database users What should you do Reset the statistics in the dynamic management view DMV Restart the SQL Server computer Execute the UPDATE STATISTICS command Restart the SQL Server Service Answer A QUESTION You are a database administrator for Certkiller com You manage a database named Invoicing The Invoicing database is backed up with a full backup nightly and transaction log backups once every two hours from to You implement database snapshots for the Invoicing database A database snapshot is created each day at At today a user inadvertently deleted all of the invoices that were entered into the CurrentInvoice table yesterday None of these invoices had been changed today prior to the deletion It is now and many other changes have occurred in the database You need to recover the lost rows with as few administrative steps as possible while minimizing data loss What should you do Use the SELECT subquery in the INSERT statement to move the deleted rows from yesterday mornings database snapshot to the CurrentInvoice table Restore last nights Full Backup and all transaction log backups until the backup Use the STOP AT statement on all restores to stop the restores at Use the SELECT subquery in the INSERT statement to move the deleted rows from this mornings database snapshot to the CurrentInvoice table Restore last nights Full Backup and all transaction log backups until the backup Use the STOP AT statement on the last restore to stop the restore at Answer C QUESTION You work as DBA at Certkiller com You administer a Microsoft Windows Server computer named Server SQL Server is installed on Server to host a database named MedDB The MedDB database serves as the backend database for a new client server application named MedDAT A Installation of the MedDATA application creates the database schema application roles and administrative accounts It also sets all of the default permissions You need to establish a permissions baseline for all objects in the MedDB database What should you do Use the SQL Server Management Studio to review the permissions of the default database objects Query the sys server permissions table on Server Query the sys database permissions and sys database principals tables in the MedDB database Execute the sp helplogins system stored procedure for each of the default users that the MedDATA application creates Answer C QUESTION You are a database administrator for an electronics distribution company You are documenting your company's requirements for data quality You need this information so that you can store part numbers components and stock levels from your company's trading partners in your SQL Server environment You need to determine the standards for data quality You need to consult with the business analysts and other database administrators DBAs in your organization to do so What are three possible ways to achieve this goal Each correct answer presents a complete solution Choose three Establish metrics for measuring data quality Identify how to keep data as small as possible Identify how to standardize data elements Identify how to monitor the quality of your data Have your company's trading partners be responsible for their own data quality Answer A C D QUESTION You are a database administrator for Certkiller com Developers need access to create alter and drop tables in your SQL Server database A list of production tables is maintained in a table named Valid Production Tables in the database You need to be able to automatically roll back any commands that drop or alter current production tables You need to do this while minimizing the processor resources being used What should you do Use a DML trigger to execute the FOR UPDATE DELETE command on the Valid Production Tables table Use Event Notification to execute the FOR DDL TABLE EVENTS command Use Event Notification to execute the FOR ALTER TABLE DROP TABLE command Use a DDL trigger to execute the FOR DROP TABLE ALTER TABLE command Answer D QUESTION You are the database administrator for Certkiller com You have a single processor in your SQL Server computer You run System Monitor and discover that your total processor utilization is averaging percent You need to use System Monitor to find out which application or service is causing the problem What should you do Add each instance for Processor Privileged Time Add the total instance for Process Processor Time Add each instance for Process Processor Time Add the total instance for Processor Privileged Time Add the total instance for Processor Processor Time Answer C QUESTION You are a database administrator for Certkiller com The company uses a SQL Server database that includes a table named Customers The table includes a column named EmailAddress that contains string data Company policy specifies a required format for data in the EmailAddress column Changes to the EmailAddress column are made several ways including by using ad hoc queries You need to ensure that the data in the EmailAddress column is properly formatted Your solution must provide the best possible performance when thousands of rows are added to the database What should you do On the EmailAddress column create a default constraint that specifies a string in the proper format Write a stored procedure that disallows changes to the EmailAddress column that violate company policy On the EmailAddress column create a check constraint that uses a user-defined function to validate the format of the data Write a trigger that rolls back changes to the EmailAddress column that violate company policy Answer C QUESTION You are a database administrator for Certkiller com The company uses a vendor company's application that is based on a proprietary database You query data from the application database and import the data into a SQL Server database The application vendor provides you with a new OLE DB driver to be used when querying the application database Company policy prohibits connections between the SQL Server database and the application database except when querying this data You need to provide a way to query data from the application database What should you Configure a Linked Server connection to the application database Configure a Remote Server connection to the application database Write a query that uses an OPENQUERY command Write a query that uses an OPENDATASOURCE command Answer D QUESTION You are a database administrator for Certkiller com You review the error log for a SQL Server Enterprise Edition computer You notice torn page errors for one database You need to resolve the torn page problem as quickly as possible What should you do Use the latest database backup to restore only the torn page Then restore any transaction logs that have been made since the full backup Run DBCC CHECKDB Restore the latest full database backup and then restore any transaction logs that have been made since the full backup Restore the database from the most recent database snapshot Answer A QUESTION You are a database administrator for Certkiller com The company has a SQL Server computer that contains a single user database named DB All company employees connect to DB Company policy requires that the database be fault -tolerant and that failovers require no administrative effort You need to provide fault tolerance for the database Your solution must ensure that client computers can connect to the database even if the SQL Server computer experiences a complete hardware failure including a failure of all disks What should you do Implement transactional replication to another SQL Server computer Implement database mirroring to another SQL Server computer Implement a two-node server cluster that contains the database Implement log shipping to another SQL Server computer Answer B QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A All databases on Certkiller A have been created with the default auto file growth properties on one RAID volume All transaction logs on Certkiller A have been created without file growth enabled on a second RAID volume You need to ensure that you are notified when the databases are approaching disk capacity You also need to prevent errors caused when any transaction log runs out of space Which two actions should you perform Each correct answer presents part of the solution Choose two Create a performance alert on the SQLServer Databases Data File s Size KB counter Have the alert notify you of the problem Create SQL Server performance condition alerts on the SQLServer Databases Log Growths counter for each database Define a response to automatically backup the transaction log and notify you about the problem Create a SQL Server performance condition alert on the SQLServer Databases PercentLogUsed counter on each transaction log Define a response to automatically backup the transaction log and notify you about the problem Create SQL Server performance condition alerts on the SQLServer Databases Data File s Size KB counter for each database Define a response to add MB to the existing data file size and notify you about the problem Create a performance alert on the SQLServer SQL Errors Errors sec counter Have the alert notify you of the problem F Create a performance alert on the LogicalDisk Free Space counter Have the alert notify you of the problem Answer C F QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A Users regularly report that they receive error messages stating that the transaction log is full When they get this message their data changes fail You notice that disk space on Certkiller A is limited You need to configure Certkiller A to automatically prevent users' data changes from failing due to full transaction logs What should you do Configure event notification at the database level for the LOG FILE AUTO GROW event Configure the Service Broker to automatically notify database administrators of the event Double the maximum size of all of the transaction logs Set the transaction logs to automatically grow by percent Create SQL Server performance condition alerts to run a job that backs up the transaction log and sends an e-mail message to database administrators by using Database Mail when the transaction log exceeds the counter Configure event notification at the server level for the LOG FILE AUTO GROW event Configure the Service Broker to automatically notify database administrators of the event Answer C QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller Every Sunday Certkiller A executes a job that performs a full backup of a database named HR Each weeknight Certkiller A executes a job that performs differential backups All jobs have the option enabled to verify data in the backup The HR database is often not able to be restored after a failure because of errors with the data stored on the tape You need to design a restore process that ensures that as much data as possible is restored even when errors occur What should you do Create a database restore process by using Transact-SQL Include the CONTINUE AFTER ERROR option Create a database restore process by using SQL Distributed Management Objects SQL-DMO Set the ReplaceDatabase property to True Create a database restore process by using SQL Server Management Studio Enable the option to Prompt before restoring each backup D Create a database restore process by using Transact-SQL Include the CHECKSUM option Answer A QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller The master database on Certkiller A becomes corrupted Certkiller A contains several SQL Server Agent jobs All databases are backed up regularly You rebuild and restore the master database You need to ensure that Certkiller A functions as it did before the database failure What should you do Re-create all SQL Server Agent jobs Restore all user databases Re-create all SQL Server logins Restore the model and msdb databases Answer D QUESTION You are an administrator of SQL Server computers Users report that one of the SQL Server computers named Certkiller F is unavailable for almost an hour starting at One of the other SQL Server administrators reports that during this time he performed a quick routine maintenance task that included a restart You view the SQL Server logs on Certkiller F for the time period in question as shown in the exhibit You need to ensure that this unavailability does not happen again What should you do Instruct the other SQL Server administrators to use a Windows domain user account to start the SQL Server service Instruct the other SQL Server administrators not to use the net start mssqlserver command to start the SQL Server service Instruct the other SQL Server administrators not to start the SQL Server service in single user mode for routine maintenance Instruct the other SQL Server administrators not to use the net start sqlserveragent command to start the SQL Server Agent service Answer C QUESTION You are a database administrator for Certkiller com A database named OLTP Report makes data available to Reporting Services reports You use Reporting Services to create a report named SalesDeviation which displays sales goals and sales representatives' actual sales You need to manage report subscriptions All sales representatives located in the Windows Sales group who do not meet sales goals must automatically receive a SalesDeviation report every Monday morning What should you do Create two new roles GoalMet for sales representatives who have met their goals and GoalMissed for sales representatives who have missed their goals Then assign the Windows Sales group to the GoalMissed role to allow those sales representatives to run reports Create a data-driven subscription to the SalesDeviation report Then for the subscription's recipients specify a Transact-SQL query that returns the names of sales representatives who have fallen short of their goals Create a data-driven subscription to the SalesDeviation report Then assign the Windows Sales group to the predefined Browser role Finally specify the Windows Sales group in the parameter of the subscription Assign the Windows Sales group to the predefined Browser role Then ask all sales representatives in the Windows Sales group to create user-defined subscriptions to the SalesDeviation report by using My Subscriptions Answer B QUESTION You are the database administrator of a SQL Server computer running SQL Server Enterprise Edition You support a database named OnlineSales This database exists on the following three filegroups The primary filegroupFG a read write filegroup FG a read only filegroup with historical data The database is set to the Full Recovery model You discover that a file from FG has been corrupted FG is offline All other filegroups are online You need to restore FG while minimizing the time when no data is available Which two actions should you perform Each correct answer presents part of the solution Choose two Restore the transaction logs since the most recent full or filegroup backup Restore the most recent full database backup Restore the most recent backup of the FG filegroup Restore the FG filegroup Restore the most recent backup of the Primary filegroup Answer A C Explanation For a database that uses the full or bulk-logged recovery model after using RESTORE DATABASE to restore one or more files filegroups and or pages typically you must apply the transaction log to the files containing the restored data applying the log makes those files consistent with the rest of the database QUESTION You work as DBA at Certkiller com You administer a SQL Server computer Your company's database auditing department must receive an e-mail message when the SQL Server service and the SQL Server Agent service are started stopped or paused They also need to receive an e-mail message when the EXECUTE AS option is used with stored procedures and other commands You need to ensure that the auditing department receives the e -mail messages that they need Which two actions should you perform Each correct answer presents part of the solution Choose two Create an Event Notification on the Audit Server Starts and Stops event class Use SQL Server Profiler to track the Audit Statement Permission event class Create a Windows Group Policy Object to audit System Events Use SQL Server Profiler to track the Audit Server Starts and Stops event class Create an Event Notification on the Audit Server Principal Impersonation event class Use SQL Server Profiler to track the Audit Server Principal Impersonation event class Answer A E Explanation The Audit Server Principal Impersonation event class occurs when there is an impersonation within server scope such as execute as login The Audit Server Starts and Stops event class occurs when the Microsoft SQL Server service state is modified QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A Certkiller A hosts a database named DB DB serves as the backend database for an application that tracks issues that help desk technicians receive and the associated trouble tickets When installed the help desk application loaded several stored procedures and views that are used for data manipulation and reporting purposes DB contains more than tables The bulk of the data is stored within the Employees Issues Assets and AssetTracker tables An application developer inadvertently deletes the AssetTracker table in DB The AssetTracker table is referenced by several views and functions in the database You need to ensure that users are not able to remove critical tables such as this that are referenced by views or functions What should you do Create a new read-only filegroup for DB and move all tables to the new filegroup Use the ALTER SCHEMA Transact-SQL statement to add all critical tables to the same schema Configure the DENY-DELETE permission for the Public role on the schema Identify the dependencies of each critical table Alter the syntax of each view and function to include the SCHEMABINDING syntax Identify the dependencies of each view and function Configure the DENY-CONTROL permission for the Public role on each critical table listed as a dependency Answer C Explanation SCHEMABINDING binds the view to the schema of the underlying table or tables When SCHEMABINDING is specified the base table or tables cannot be modified in a way that would affect the view definition The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified QUESTION You are a database administrator for Certkiller com You create a maintenance plan that includes shrinking your SQL Server databases and reorganizing your indexes once a week You test the plan and discover that the database does not shrink as expected You need to troubleshoot why the database is not shrinking What are two possible ways to achieve this goal Each correct answer presents a complete solution Choose two Configure yourself as an operator for failed jobs Review the job activity monitor Review the maintenance plan history log Configure an alert to respond to failed jobs Review the job history log Answer C E Explanation Use the Database Maintenance Plan History dialog box to find information about maintenance plans configured on the server Viewing a job's history allows you to see when the job ran the status of the job as a whole and the status of each job step in the job You can see whether the job ever failed in the past when the job last completed successfully and what output the job created each time the job ran QUESTION You are the database administrator for Certkiller com The company uses a SQL Server database to track online sales Employees who run the In -Stock report experience slow performance This performance problem is caused by the contention between the In-Stock report and high volume of data entry to the tables that are accessed in this report The company wants the In -Stock report returned immediately even though other employees might be changing the data within their data-entry transactions Any changes made in other transactions while the report is running must be visible to the employees who are running the In-Stock report You need to design a solution to achieve this goal What should you do Change the transaction isolation level for the report transaction to snapshot Change the transaction isolation level for the report transaction to serializable Change the transaction isolation level for the report transaction to Read Uncommitted Configure the database to enable the Read Committed snapshot option Answer C Explanation READ UNCOMMITTED Specifies that statements can read rows that have been modified by other transactions but not yet committed Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions When this option is set it is possible to read uncommitted modifications which are called dirty reads Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction This is the least restrictive of the isolation levels QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller All company servers run Windows Server and are part of an Active Directory domain Company policy states that all servers are shut down each night A SQL Server Agent backup job is configured to run on Certkiller A each morning at On the mornings when Certkiller A has not been turned on by the job fails to start and you must manually start it You need to ensure that Certkiller A is backed up automatically each morning Which two actions should you perform Each correct answer presents part of the solution Choose two Configure the SQL Server Agent service to start when Certkiller A starts Configure a SQL Server Agent backup job to start when the CPU of Certkiller A is idle Configure a SQL Server Agent backup job to execute when the SQL Server service starts Configure a SQL Server Agent backup job to execute when the SQL Server Agent service starts Configure the SQL Server service to start when Certkiller A starts Answer A D Explanation You should configure the SQL Server Agent to start when Certkiller A starts because it will be started each morning You should also configure the SQL Server Agent backup job to start when the SQL Server Agent starts because the backup job needs that the Agent is running in order to execute QUESTION You are a database administrator for Certkiller com You have separate SQL Server development and production environments You use the Business Intelligence Development Studio to create a SQL Server Integration Services SSIS package in your development environment Then you use the SSIS package to import data into your development environment from one of your company's trading partners You need to deploy the SSIS package to your production environment Your production environment uses different table names than your development environment What should you do Save the SQL Server Integration Services SSIS package to a file Copy the file to the production server Configure the SSIS package on the production server to use the new file Create a SQL Server Integration Services SSIS package configuration Build a deployment utility Copy the deployment folder for your SSIS project to your production server Execute the manifest file Back up the master database and restore it to the production server Rename the appropriate tables inside the master database Back up the msdb database and restore it to the production server Rename the appropriate tables inside the msdb database Answer B Explanation You want to create a new package configuration and a new deployment utility for the production environment as the tablenames do not match QUESTION You are a database administrator for Certkiller com Users report that a SQL Server application is performing poorly You use System Monitor and discover that disk activity on the volumes that contain the database data files is very high You need to find out which SQL Server activity is causing the high disk activity You want to accomplish this task by using SQL Server Profiler What should you do Search for deadlock events Search for events that have high duration values Search for events that have high read values or high write values Search for transaction log events that have high write values Answer C Explanation Only high read or write values will cause high disk activity QUESTION You are a database administrator for Certkiller com You administer a SQL Server database named Contracts The Contracts database becomes corrupted You perform a restore by using the following Transact-SQL script RESTORE DATABASE Contracts FROM contracts bu device WITH CONTINUE AFTER ERROR CHECKSUM RECOVERY After the restore the SQL Server logs show errors in some of the page restores You need to repair the database pages that are corrupt You need to achieve this goal by using the method that facilitates the fastest possible repair Which Transact-SQL statement should you use ALTER DATABASE Contracts SET TRUSTWORTHY ON DBCC CHECKDB Contracts REPAIR ALLOW DATA LOSS WITH TABLOCK ALTER DATABASE Contracts SET TORN PAGE DETECTION ON DBCC CHECKDB Contracts REPAIR ALLOW DATA LOSS WITH NO INFOMSGS Answer B Explanation REPAIR ALLOW DATA LOSS specifies that DBCC CHECKTABLE should repair the found errors To use a repair option the database must be in single-user mode WITH TABLOCK causes DBCC CHECKTABLE to obtain a shared table lock instead of using an internal database snapshot TABLOCK will cause DBCC CHECKTABLE to run faster on a table under heavy load but decreases the concurrency available on the table while DBCC CHECKTABLE is running QUESTION You are the database administrator for Certkiller com The company uses a database that contains a table named Sales The CREATE statement for the Sales table is shown in the exhibit The Sales table is very write-intensive and is commonly used in queries Both of the indexes on the Sales table become fragmented within the first two hours of being rebuilt Both leaf and intermediate level pages split frequently There is sufficient disk space available to fully optimize the indexes You need to optimize the nonclustered index on the Sales Amount column of the Sales table Which two actions should you perform Each correct answer presents part of the solution Choose two Set the FILLFACTOR option to when rebuilding the index Set the FILLFACTOR option to when rebuilding the index Set the PAD INDEX option to OFF when building the index Set the PAD INDEX option to ON when building the index Answer B D Explanation FILLFACTOR specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild PAD INDEX ON specifies that the percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index QUESTION You are a database administrator for Certkiller com You run SQL Server Profiler by using the SP Recompile event class You notice a large number of recompiles occurring You need to identify the possible cause of the large number of recompiles What is the most likely cause of the problem A database administrator created one new stored procedure Ad hoc batches are not being retrieved from the procedure cache The sp executesql batches are not being retrieved from the procedure cache A database administrator executed UPDATE STATISTICS statements on all tables that are referenced by the most common stored procedures Answer D QUESTION You are a database administrator for Certkiller com The company uses a SQL Server database A full backup of the database is made each night Transaction log backups are made each morning and each afternoon You notice that the database transaction log file grows very large throughout the day You need to minimize the size of the transaction log file You also need to minimize data loss in the event of a failure What should you do Configure the RECOVERY INTERVAL option to be a smaller value Run DBCC SHRINKFILE on the transaction log file Configure the database to use the simple recovery model Decrease the amount of time between transaction log backups Answer D Explanation The transaction log file shrinks every time it is backed up as it contains all transactions since last backup In order to minimize the transaction log filesize and mimnimize the data loss in the event of failure you should backup the transaction log more often QUESTION You work as DBA at Certkiller com You administer a SQL Server computer The performance of the server has degraded to a great extent over time You are troubleshooting the indexing environment You rebuild the clustered indexes that are used the most frequently You want to optimize the most frequently accessed nonclustered index You access the sys dm db index usage stats dynamic management view DMV The results are shown in the following table You need to identify which nonclustered index is being used the most frequently when the entire index is not read Which object id and index id combination should you use Use the object id and the index id Use the object id and the index id Use the object id and the index id Use the object id and the index id Use the object id and the index id Answer E QUESTION You are a database administrator for Certkiller com Users report that the performance of a SQL Server application seems slow You verify that the SQL Server computers CPU utilization disk utilization and memory utilization are normal You also verify that the network is operating normally You need to examine more detailed performance information for the appropriate instance of SQL Server What should you do Examine the distribution of the statistics in the databases that are hosted by the instance Examine the recovery model of the tempdb database Examine the appropriate dynamic management views DMVs Examine the output of a statistics update command Answer C Explanation Dynamic management views and functions return server state information that can be used to monitor the health of a server instance diagnose problems and tune performance QUESTION You are a database administrator for Certkiller com Your company is a large retail store chain with more than outlets Each retail store maintains its own SQL Server database that must replicate data to the corporate server each night You need to monitor replication for each store What are two possible ways to achieve this goal Each correct answer presents a complete solution Choose two Configure warnings and alerts by using Replication Monitor Monitor server activity by using SQL Server Profiler Monitor replication activity by using the Activity Monitor Monitor the current average and worst performance ratings by using Replication Monitor Answer A D Explanation MicrosoftSQL ServerReplication Monitor is a graphical tool that allows you to monitor the overall health of a replication topology Replication Monitor provides detailed information on the status and performance of publications and subscriptions allowing you to answer common questions such as Is my replication system healthy Which subscriptions are slow How far behind is my transactional subscription How long will it take a transaction committed now to reach a Subscriber in transactional replication Why is my merge subscription slow Why is an agent not running QUESTION You are a database administrator for Certkiller com You are responsible for monitoring the performance of your SQL Server computers You have a SQL Server Integration Services SSIS package that usually takes minutes to run Today the package is taking more than one hour to run You suspect memory is too low You need to identify the problem What should you do Redesign the SQL Server Integration Services SSIS package so that it runs only when no exclusive table locks are in use Check the time-out values in the SQL Server Integration Services SSIS package Use the System Monitor to monitor the SQL Server Integration Services SSIS pipeline counters Use SQL Server Profiler to monitor the Scans event category counters Answer C Explanation SQL Server Integration Services SSIS includes a set of performance counters for monitoring the performance of the data flow engine To add a new log setting that includes all performance counters do the following In Control Panel click Administrative Tools and then click Performance In the Performance dialog box expand Performance Logs and Alerts right-click Counter Logs and then click New Log Settings Type the name of the log For example type MyLog In the MyLog dialog box click Add Counters In the Add Counters dialog box select SQL Server SSISPipeline in the Performance object box Select the All Counters check box and then close the dialog box QUESTION You are a database administrator for Certkiller com A new SQL Server computer named Certkiller B hosts a mission -critical customer relationship management CRM database All of the companys employees in the sales and marketing departments access the CRM database Your department has an annual budget for the purchase of new hardware and software You need to devise a monitoring strategy that enables you to make decisions about the history of server performance The monitoring strategy should also advise you on how to best allocate funds for hardware upgrades What should you do A Create a series of performance alerts to notify you when Processor Time Average Disk Queue Length Pages sec and Bytes Total sec exceed acceptable thresholds Use System Monitor in graph mode to display information about Processor Time Average Disk Queue Length Pages sec and Bytes Total sec Perform this task at regular intervals during periods of peak server activity Use SQL Server Profiler to capture information to a SQL Server database table Perform this task at regular intervals during periods of peak server activity Configure a counter log to capture information about Processor Time Average Disk Queue Length Pages sec and Bytes Total sec Schedule the log to execute at regular intervals and store the information in a comma-delimited text file Answer D Explanation By the capturing the Processor Time Average Disk Queue Length Pages Sec and Bytes Total Sec you monitor all the relevant data needed to base your decisions on By running them on a reoccurring interval and saving the data you provide the possibility to create graphs showing decreased performance over time QUESTION You are a database administrator for Certkiller com Your company uses merge replication to replicate data between SQL Server computers Users sometimes report that data on some of the computers is out of date When this happens you discover that merge replication times out before the merge could complete You need to ensure that you are automatically notified by SQL Server when this problem occurs What should you do Enable the Warn if a merge length for LAN connections exceeds the threshold warning Enable the Warn if a subscription will expire within the threshold warning Create an operator in SQL Server Agent Configure and enable Database Mail Answer A Explanation To set thresholds and warnings for a merge publication Expand a Publisher group in the left pane expand a Publisher and then click a publication Click the Warnings and Agents tab Enable a warning by selecting the appropriate check box Warn if a subscription will expire within the threshold Warn if a merge length for dialup connections exceeds the threshold Warn if a merge length for LAN connections exceeds the threshold Warn if rows merged per second for LAN connections is less than the threshold Warn if rows merged per second for dialup connections is less than the threshold Set thresholds for the warnings in the Threshold column For example if you selected Warn if a merge length for dialup connections exceeds the threshold in step you could select a time of minutes in the Threshold column Click Save Changes QUESTION You are the database administrator for Certkiller com You create a new stored procedure You run the stored procedure and discover an error You want to gather and process any error information that is generated while running the procedure Error logging must use the minimal amount of resources while providing the needed functionality You need to know what caused the error You also need to include the appropriate error functions in the new stored procedure What should you do Use a GOTO statement Set the XACT ABORT command to off Use the error function Use TRY CATCH blocks Answer D Explanation Errors in Transact-SQL code can be processed using a TRY CATCH construct similar to the exception-handling features of the Microsoft Visual C and Microsoft Visual C languages A TRY CATCH construct consists of two parts a TRY block and a CATCH block When an error condition is detected in a Transact-SQL statement contained in a TRY block control is passed to a CATCH block where it can be processed After the CATCH block handles the exception control is then transferred to the first Transact-SQL statement that follows the END CATCH statement If the END CATCH statement is the last statement in a stored procedure or trigger control is returned to the code that invoked the stored procedure or trigger Transact-SQL statements in the TRY block following the statement that generates an error will not get executed If there are no errors inside the TRY block control passes to the statement immediately after the associated END CATCH statement If the END CATCH statement is the last statement in a stored procedure or trigger control is passed to the statement that invoked the stored procedure or trigger QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A Certkiller A contains a database that utilizes SQL Server log shipping Log shipping occurs every minutes Each night a full backup is made of the database and all database indexes are rebuilt The company's network administrator reports that Certkiller As network utilization becomes excessive after the nightly maintenance tasks are complete You need to reduce the network bandwidth utilization for Certkiller A What should you do Disable log shipping before the nightly maintenance Re-enable log shipping after nightly maintenance completes Configure the database to use the bulk-logged recovery model Increase the time between transaction log backups Each night rebuild only those indexes that require it Answer D Explanation Every rebuilt index will also be rebuilt on the recipient of the Log shipping Only rebuild the logs that needs to be rebuilt in order to minimize the logs that are shipped to the remote server QUESTION You work as DBA at Certkiller com You administer a Microsoft Windows Server computer Your company purchases a new enterprise sales application that runs SQL Server The application uses stored procedures that include the EXECUTE AS clause and that use Microsoft Windows accounts The company's written security policy states that all enterprise applications must run under the context of a service account that requires the minimum amount of privileges You need to configure the SQL Server service to run under the appropriate context for the installation of the new enterprise sales application Under which context should the SQL Server service run under the context of the domain user account under the context of the local service account under the context of the local Administrator account under the context of the local system account Answer A Explanation When using the EXECUTE AS clause and providing a username and password it is easiest to use a domain user account with the minimum amount of privileges needed to execute the task it should do QUESTION You are a database administrator for Certkiller com You are creating a SQL Server database that will contain many tables to store contact information for customers partners and vendors You need to ensure consistency in the data types that are used in all columns in the database tables What are two possible ways to achieve this goal Each correct answer presents a complete solution Choose two Create a DDL trigger that alerts you when the schema in a table changes Create a CHECK constraint to enforce values allowed in the columns in the database tables Create CLR user-defined types for use in the tables Create a DML trigger that alerts you when the data in a table changes Create Transact-SQL user-defined data types for use in the tables Answer C E Explanation You should use user-defined data types in the tables You create the type with either CLR or T-SQL By using the user-defined data types you specify how data has to be formatted in order to be accepted in the table QUESTION You are a database administrator for Certkiller com Users report that a SQL Server application stops responding for several seconds at a time during the day This problem occurs randomly throughout the day and affects many users at once When the problem occurs you notice that CPU utilization and disk utilization on the SQL Server computer are low but that the network is operating normally You need to examine additional information relevant to the problem What should you do Examine the sys dm tran locks dynamic management view DMV Examine the total number of lock requests on the SQL Server computer Examine the output of the DBCC SHOWCONTIG command Examine the total number of index and table scans on the SQL Server computer Examine the output of the DBCC SQLPERF IOSTATS command Answer A Explanation sys dm tran locks returns information about currently active lock manager resources Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted The columns in the result set are divided into two main groups-resource and request The resource group describes the resource on which the lock request is being made and the request group describes the lock request QUESTION You are a database administrator for Certkiller com You are creating a new SQL Server Integration Services SSIS package The package will import one or more text files into a database each night These text files are provided by company vendors and are not related to one another You need to ensure that the package always succeeds even if no text files are provided with the least amount of administrative effort What should you do In the SSIS package use a Merge Join transformation to combine the data from the files Configure the SSIS package precedence constraints to be dynamically allocated Configure the SSIS package to use transactions In the SSIS package use a For Loop condition to iterate through the available files Answer D Explanation Use the For Loop page of the For Loop Editor dialog box to configure a loop that repeats a workflow until a specified condition evaluates to false QUESTION You are a database administrator for Certkiller com The company's manufacturing floor has three automated testing areas for finished items Each testing area uses SQL Server to store test results You need to develop a replication strategy to ensure that test results flow continuously from the testing areas to the central SQL Server as quickly as possible The test results are used for reporting What should you do Create a separate transactional publication at each of the testing areas for the test results Create a merge subscription on each distributor to the central server Create a separate snapshot publication at each of the testing areas for the test results Create a merge subscription on each distributor to the central server Create a separate snapshot publication at each of the testing areas for the test results Create a pull subscription on the central server to each of the testing area publications Create a separate transactional publication at each of the testing areas for the test results Create push subscriptions on each of the testing areas to the central server Answer D Explanation Transactional replication typically starts with a snapshot of the publication database objects and data As soon as the initial snapshot is taken subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur in near real time The data changes are applied to the Subscriber in the same order and within the same publication transactional consistency is guaranteed By default Subscribers to transactional publications should be treated as read-only because changes are not propagated back to the Publisher Push subscriptions are typically used when the subscribers are treated as read only QUESTION You are the database administrator for Certkiller com You manage a SQL Server database named Sales You are given a SQL Server Profiler sample workload with queries to the Sales database on a typical day You need to evaluate the effectiveness of current indexing and identify changes that would be appropriate What should you do first A Create a query with the five stored procedures that are used the most frequently Then click the Analyze Query in Database Engine Tuning Advisor button Use the Database Engine Tuning Advisor DTA with the sample workload Use the Index Tuning Wizard with the sample workload Use SQL Server Profiler to run the sample workload Answer B Explanation To build a recommendation of the optimal set of indexes indexed views or partitions for your databases Database Engine Tuning Advisor requires a workload A workload consists of a Transact-SQL script or a SQL Server Profiler trace saved to a file or table If you are using a SQL Server Profiler trace file or table they must contain Transact-SQL batch or remote procedure call RPC event classes and the Event Class and Text data columns QUESTION You are a database administrator for Certkiller com The company has two SQL Server computers named Certkiller A and Certkiller B Certkiller A is located on the local network Certkiller B is located at a vendors site and is connected by using a VPN over the Internet You need to write heterogeneous queries on Certkiller A that access data from both Certkiller A and Certkiller B To ensure that your queries are functional you must configure access to the vendors site What should you do Configure an HTTP endpoint for Certkiller A on Certkiller B Configure Certkiller B as a linked server on Certkiller A Configure Certkiller A as a linked server on Certkiller B Configure an HTTP endpoint for Certkiller B on Certkiller A Answer B Explanation A linked server is used to execute queries over multiple SQL Servers Certkiller B should be created as a linked server on Certkiller A as it is Certkiller A that will execute the query QUESTION You are a database administrator for Certkiller com Users report that a SQL Server application is performing slowly You check the SQL Server error log and discover that the server frequently runs out of memory to manage locks The application designer informs you that dirty reads are acceptable You need to resolve the problem indicated by the error log What should you do Implement the Read Uncommitted isolation level Execute the sp locks stored procedure Increase the maximum number of worker threads Configure the SQL Server max server memory setting to be a lower value Answer A Explanation Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions When this option is set it is possible to read uncommitted modifications which are called dirty reads Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction This is the least restrictive of the isolation levels QUESTION You are a database administrator for Certkiller com You administer a SQL Server computer Customers send orders to your company over the Internet in an XML format The XML is submitted as an input argument of a stored procedure The stored procedure needs to produce a result set that includes all invalid product identification numbers IDs from the order A company database contains a list of all valid product IDs You need to ensure that the stored procedure can produce the desired result set Your solution must provide the fastest possible query performance What are two possible ways to achieve this goal Each correct answer presents a complete solution Choose two Write an OUTER JOIN query that includes a WHERE clause Write the WHERE clause to remove valid product IDs Write a query that uses a NOT EXISTS subquery Write a CROSS JOIN query that includes a WHERE clause Write the WHERE clause to remove valid product IDs Write a query that uses the HASH JOIN query hint Answer A B Explanation An OUTER JOIN would produce a list of ID's in the XML order but not in the company database over valid ID's a NOT EXISTS subquery would give the same result QUESTION You are a database administrator for Certkiller com The company uses a SQL Server database that includes a table named Inventory The table contains a column named Price A company policy states that the value in the Price column cannot be decreased by more than percent in any single database operation Updates to the Price column are made by various means including by using ad hoc queries You need to ensure that this company policy is enforced What should you do On the Price column create a foreign key constraint to a table that contains valid prices On the Price column create a check constraint that requires a specified minimum value Create a trigger that rolls back changes to the Price column that violate company policy Create a stored procedure that disallows changes to the Price column that violate company policy Answer C A trigger would be the easiest way to implement this When the trigger is fired just rollback to remove the change that violates the company policy QUESTION You work as DBA at Certkiller com You administer two SQL Server computers named Certkiller A and Certkiller B You were not responsible for the original installation of SQL Server on these computers Yet you now must ensure that both computers are configured according to the following requirements Administrators must be able to remotely connect to Certkiller A and Certkiller B for troubleshooting even if the system becomes unresponsive Certkiller A and Certkiller B must not be able to execute commands related to the operating system Database access must be permitted by using Simple Object Access Protocol SOAP messages over HTTP Certkiller A and Certkiller B must not be able to use stored procedures to send e-mail Use of the OPENROWSET and OPENDATASOURCE Transact-SQL statements must not be permitted Stored procedures for generating HTML must not be permitted You need to configure Certkiller A and Certkiller B to meet these requirements What should you do Use SQL Server Surface Area Configuration for Features Use SQL Server Configuration Manager to configure the appropriate server features on Certkiller A and Certkiller B Use SQL Server Surface Area Configuration for Services and Connections Use the sp configure system stored procedure to configure the appropriate server features on Certkiller A and Certkiller B Answer A Explanation Use SQL Server Surface Area Configuration to enable disable start or stop the features services and remote connectivity of your SQL Server installations You can use SQL Server Surface Area Configuration on local and remote servers QUESTION You are a database administrator for Certkiller com The company uses a SQL Server application Each night data is imported into a database and the database is then set to be read-only During business hours users run predefined and ad hoc queries You need to monitor the servers performance during the day What are two possible ways to achieve this goal Each correct answer presents a complete solution Choose two Use the Batches sec performance counter Use the Full Scans sec performance counter Use the Lock Waits sec performance counter Use the Transactions sec performance counter Use the Number of Deadlocks sec performance counter Answer A B Explanation The Full Scans Sec performance counter gives you the number of unrestricted full scans per second These can be either base-table or full-index scans The Batches Sec performance counter gives you the number of batches per second that where completed successfully Both these counters would give you the data needed to monitor the serves performance during daytime QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller All servers run Microsoft Windows Server and belong to a Microsoft Active Directory domain The companys written security policy mandates strong passwords for all domain local and application accounts The appropriate domain password policy has been deployed through the use of a Group Policy object GPO Many of the company's computers run MS-DOS-based or UNIX-based operating systems These computers require varying levels of access to a database named Services on Certkiller You need to ensure that these computers can access the Services database without violating the company's written security policy What should you do Create one SQL Server logon account for all of the company's computers running MS-DOS or UNIX-based operating systems Enable the Enforce password policy option for users' accounts Provide the logon accounts with access to the Services database Configure a virtual directory through Internet Information Services IIS for access to Certkiller Direct users to the virtual directory for data access Create a domain account for each of the company's users Provide each domain account with access to Certkiller A and the Services database Create a SQL Server logon account for each of the company's users Enable the Enforce password policy option for each logon account Provide each logon account with access to the Services database Create a local logon account on Certkiller A for each of the company's users Provide each logon account with access to Certkiller A and the Services database Answer D Explanation All UNIX and DOS users have to use SQL Server logon accounts in order to access the database You do not want to have multiple users chairing one logon account And you do not want the users to have a local logon account on the SQL Server QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller Certkiller A runs three instances of SQL Server Your company's disaster recovery plan includes having the ability to connect to the dedicated administrative connection on each instance of SQL Server While testing this ability you discover that you can connect to the dedicated administrative connection on Certkiller As default instance But you cannot connect to the dedicated administrative connection on the two other instances on Certkiller You verify that the instances are running and that client applications are able to access them You also verify that the dedicated administrative connection is enabled for all instances You confirm that no other administrators are attempting to connect to any dedicated administrative connections on Certkiller A You need to ensure that you can connect to the dedicated administrative connection on all three instances What should you do Start the SQL Server Browser service Configure the service to start automatically Reconfigure the default instance to use an administrative port number other than Use the Sqlcmd exe tool to make the dedicated administrative connection Stop and restart the non-default instances of SQL Server Answer A Explanation The DAC port is assigned dynamically by SQL Server during startup When connecting to the default instance the DAC avoids using a SQL Server Resolution Protocol SSRP request to the SQL Server Browser Service when connecting It first connects over TCP port If that fails it makes an SSRP call to get the port If SQL Server Browser is not listening for SSRP requests the connection request returns an error QUESTION You are the database administrator for a SQL Server computer named Certkiller A A user reports that his query has been running for a very long time and is still running You access the sys dm tran locks dynamic management view DMV The relevant results are shown in the following table You need to find the user name of the user with the blocking connection What are two possible ways to achieve this goal Each correct answer presents a complete solution Choose two Execute sp who Select from the sys syslockinfo compatibility view and locate spid Use the Activity Monitor in Microsoft SQL Management Studio and locate process Use the Activity Monitor in Microsoft SQL Management Studio and locate process Select from the sys syslockinfo compatibility view and locate spid Execute sp who Answer A C Explanation The sp who provides information about current users and processes in an instance of the Microsoft SQL Server Database Engine The information returned can be filtered to return only those processes that are not idle Use the Activity Monitor component of Microsoft SQL Server Management Studio to get information about users connections to the Database Engine and the locks that they hold QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A Certkiller A runs Windows Server and hosts a database named DB DB is used for a critical enterprise-level application that all sales representatives in the company use The database is currently GB and grows GB each quarter Certkiller A recently experienced a hard disk failure that resulted in significant system downtime You purchase two new hardware RAID controllers named Controller and Controller You also purchase five new hard disk drives to try to prevent reoccurrences of hardware-related downtime The new hard disk drives are configured as shown in the following table Disk drive Size RPM Disk GB Disk GB Disk GB Disk GB Disk GB The operating system is configured for redundancy You decide to build a RAID array on Controller and a RAID array on Controller You need to ensure that DB continues to perform in the event of a disk failure What should you do Answer Explanation RAID- requires at least disks The database files should be put on the RAID- array It is faster RAID- mirroring uses disks Transaction log files are sequential and well fit for mirrored disks QUESTION You are a database administrator for Certkiller com A database on one of the company's SQL Server computers stores transaction information for the company's Web-based order system The database is configured to use the full recovery model Your company receives very few orders between the hours of and Your maximum acceptable risk for data loss is one hour Database backups must be performed as quickly as possible You need to develop a maintenance plan to manage this risk What should you do Configure a Back Up Database task to do a full backup of the database every day at Configure a second Back Up Database task to do differential backups at and Configure a third Back Up Database task to do transaction log backups every hour Configure a Back Up Database task to do a full backup of the database every hour Configure a Check Database Integrity task to run every hour Configure a Back Up Database task to do a full backup of the database every day at Configure a second Back Up Database task to do differential backups at and Configure a Check Database Integrity task to run every hour Configure a Back Up Database task to do a full backup at and Configure a second Back Up Database task to do transaction log backups every hour Answer A Explanation It is not recommended to run a full backup during business-hours in this case Full backups should be scheduled to between and Checking Database Integrity every hour will not help to recover the data if the database crashes The only alternative that would work is the Full backup at and then transaction log backups every hour Doing differential backups at and will help QUESTION You work as DBA at Certkiller com You administer a Microsoft Windows Server computer named Certkiller A Certkiller A runs SQL Server and is a member of a Microsoft Active Directory domain named Contoso com The domain is configured with all of the default settings Several members of the information security team have requested access to Certkiller A The information security team uses the Microsoft Windows XP Professional Linux and Macintosh operating systems to perform vulnerability assessments The information security team members ask you to create two new logon accounts for their use They would like the logon accounts to have the following user names and passwords User name password for first account pentest P ssw rdUser name password for second account hacktest password You need to provide the information security team with the requested access to Certkiller A Which Transact-SQL script should you use CREATE LOGIN pentest WITH PASSWORD 'P ssw rd' CREATE LOGIN hacktest WITH PASSWORD 'password' CHECK POLICY OFF CREATE LOGIN pentest WITH PASSWORD 'P ssw rd' CREATE LOGIN hacktest WITH PASSWORD 'password' HASHED CREATE LOGIN pentest WITH PASSWORD 'P ssw rd' CREATE LOGIN hacktest WITH PASSWORD 'password' CREATE LOGIN CONTOSO pentest FROM WINDOWS CREATE LOGIN CONTOSO hacktest FROM WINDOWS Answer A Syntax CREATE LOGIN login name WITH FROM WINDOWS WITH CERTIFICATE certname ASYMMETRIC KEY asym key name PASSWORD 'password' HASHED MUST CHANGE SID sid DEFAULT DATABASE database DEFAULT LANGUAGE language CHECK EXPIRATION ON OFF CHECK POLICY ON OFF CREDENTIAL credential name DEFAULT DATABASE database DEFAULT LANGUAGE language QUESTION You are a database administrator for Certkiller com You are configuring a linked server named Certkiller on a local server named Certkiller You need to configure the options so that any administrator who is a member of the LocalDBAdmins group can connect to the linked server by using the remote logon of DBAdmin with a password of DBAdmin All other users should be able to connect by using the remote logon of Guest with a password of Guest What should you do To answer drag the appropriate security group login or password to the correct location or locations in the answer area Answer Explanation QUESTION You are a database administrator for Certkiller com A company vendor provides product inventory data by using a Web service Your company needs to create several reports based on this data You need to ensure that the reports have access to the necessary data Your solution must provide the best reporting performance What should you do Create a SQL Server Integration Services SSIS package that uses a Web service task to retrieve the data and store it in the database Create reports that utilize the data in the database Create Reporting Services reports that retrieve data directly from the vendor Web service Create Reporting Services reports that retrieve data by using XPath queries Create a SQL Server Integration Services SSIS package that uses an FTP task to retrieve the data and store it in the database Create reports that utilize the data in the database Answer A Explanation The Web Service task executes a Web service method You can use the Web Service task to for example writing to a variable the values that a Web service method returns For example you could obtain the highest temperature of the day from a Web service method and then use that value to update a variable that is used in an expression that sets a column value QUESTION You are a database administrator for Certkiller com Each night you perform full backups of your SQL Server computer During business hours you perform hourly transaction log backups You rebuild database indexes each night You need to modify your backup strategy You must ensure that the database recovers to a new server as quickly as possible What should you do Schedule an additional transaction log backup to run after the nightly index is rebuilt Schedule a marker to be added to the transaction log before the nightly index is rebuilt Schedule the full backups to run after the nightly index is rebuilt Schedule the full backup to run before the nightly index is rebuilt Schedule a differential backup to run after the nightly index is rebuilt Answer C Explanation If the index rebuild is done after the full backup the transaction log backups will be unnecessary large QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A Backups of all system databases are performed on Saturday nights Backups of all user databases are performed on Sunday nights All backups are stored in an off-site facility Certkiller A unexpectedly turns off due to a power outage When Certkiller A is brought back online the SQL Server service fails to start You view the SQL Server error log as shown in the exhibit Click the Exhibit button You need to return Certkiller A to working order as quickly as possible What should you do first Restore all of the user databases from backup Insert the SQL Server installation CD and run the setup utility to reinstall the default instance Restore all of the system databases Run Setup exe from a command line and specify a rebuild of the master database Answer D Explanation The log indicates that the master database master mdf can not be found Run Setup exe to rebuild verify and repair a SQL Server instance and rebuild the system databases This procedure is most often used to rebuild the master database for a corrupted installation of SQL Server QUESTION You are a database administrator for Certkiller com You notice that the following Transact-SQL query is performing very slowly SELECT VideoTitle UpcNum RetailPrice ReleaseDate FROM Srvideo VideoTitle WITH INDEX WHERE ReleaseDate BETWEEN ' ' AND ' ' There is a clustered index on the VideoTitle column There is a nonclustered index on the ReleaseDate column that includes the UpcNum and RetailPrice columns When you query the sys dm db index physical stats dynamic management function DMF or the VideoTitle table the avg fragmentation in percent is percent The graphical execution plan for the slow query is shown in the exhibit You need to ensure that the query performs quickly What should you do Remove the query hint from the query Re-create the index on only the ReleaseDate column Rebuild all indexes on the VideoTitle table Change the query hint to force the optimizer to force a clustered index seek or a scan Answer A Explanation The WITH INDEX is not necessary in this query QUESTION You are a database administrator for Certkiller com You migrate several SQL Server Data Transformation Services DTS packages to a -bit version of SQL Server Standard Edition After migrating the packages you notice that they do not run You verify that the packages run on a -bit version of SQL Server Standard Edition You need to ensure that the packages run You must minimize the amount of additional development time required What should you do Run the packages by using the Dtsrun exe command-line utility Rewrite the packages by using the -bit version of SQL Server Standard Edition Server Integration Services SSIS Run the packages in the SQL Server DTS runtime Configure the packages to run in -bit mode Answer D Explanation a DTS package created on a -bit architecture will not automatically run in an -bit architecture You have to emulate a -bit architecture for the DTS package to run in QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A Certkiller A has recently been upgraded from two to eight processors The Certkiller A configuration is summarized in the following table You need to optimize Certkiller A so that longer running queries can run parallel plans What should you do Change the AWE enabled setting to Change the max degree of parallelism to Change the cost threshold for parallelism to Change the affinity mask to Answer D Explanation To carry out multitasking Microsoft Windows and Windows Server sometimes move process threads among different processors To enable the enable one or more processors use the affinity mask option QUESTION You are a database administrator for Certkiller com The company's financial forecasting application uses a SQL Server database that enables business users to run both ad hoc and predefined queries You suspect that some queries consume an excessive amount of server resources You need to identify which queries consume the most resources You want to achieve this goal as quickly as possible What should you do Use the sqldiag utility Use the sys dm exec query stats dynamic management view DMV Use the DBCC INPUTBUFFER command Use the SHOWPLAN session option Answer B Explanation Sys dm exec query stats returns aggregate performance statistics for cached query plans The view contains one row per query plan and the lifetime of the row is tied to the plan itself When a plan is removed from the cache the corresponding row is eliminated from this view QUESTION You are the database administrator of a SQL Server computer named Certkiller A The following query is run frequently All tables referenced in the query are read-intensive with very few inserts and updates You cannot change existing objects in the database You can add new objects to the database You need to optimize the performance of the query What should you do Create an indexed view that contains all of the columns that are used in the query Create a partitioned table that contains all of the columns that are used in the query Create a view that contains all of the columns that are used in the query Alter the current indexes by setting the FILLFACTOR option to Answer A Explanation By creating a indexed view of the tables needed you don't need to access the tables themselves every time you execute the query and therefore you get a better performance on the query QUESTION You are a database administrator for Certkiller com Your company has SQL Server computers in a distributed environment The SQL Server computers each subscribe to a transactional replication publication Company policy defines service level agreements SLAs for maximum replication latency You need to verify that the SLAs are being met What should you do Ping each subscriber from the server that hosts the publication database Record the response time for each Record the time stamps on replication-related events in the application event log of each subscriber Use tracer tokens to measure and record latency of each subscriber D Create and execute a stored procedure that verifies the data on each subscriber Record the execution time of the stored procedure Answer C Explantion Transactional replication provides the tracer token feature which provides a convenient way to measure latency in transactional replication topologies and to validate the connections between the Publisher Distributor and Subscribers A token a small amount of data is written to the transaction log of the publication database marked as though it were a typical replicated transaction and sent through the system allowing a calculation of How much time elapses between a transaction being committed at the Publisher and the corresponding command being inserted in the distribution database at the Distributor How much time elapses between a command being inserted in the distribution database and the corresponding transaction being committed at a Subscriber QUESTION You are a database administrator for Certkiller com The company uses a SQL Server computer that runs Reporting Services Reporting Services is used to produce hundreds of reports each day You discover that the ReportServerTempDB database is growing You need to minimize the size of the ReportServerTempDB database What should you do Minimize the use of report snapshots in the reports Decrease the time between transaction log backups of the database Delete report snapshot history each week Minimize the use of session caching in the reports Answer D QUESTION You are a database administrator for Certkiller com You run SQL Server Profiler by using the SP Recompile event class You notice a large number of recompiles occurring You need to identify the possible cause of the large number of recompiles What is the most likely cause of the problem A database administrator created one new stored procedure Ad hoc batches are not being retrieved from the procedure cache The sp executesql batches are not being retrieved from the procedure cache A database administrator executed UPDATE STATISTICS statements on all tables that are referenced by the most common stored procedures Answer D Explanation The SP Recompile event class indicates that a stored procedure trigger or user-defined function has been recompiled The most probable cause for a massive recompiles occurring is that someone has issued an UPDATE STATISTICS command QUESTION You are a database administrator for Certkiller com The company uses a SQL Server database that contains a table named Production Product There are more than columns in the table The most common queries are shown in the following Transact-SQL statements --QUERY SELECT Name ProductLine ListPrice Class Style FROM Production Product WHERE ProductLine 'T' --QUERY SELECT FROM Production Product ORDER BY Name You need to choose the best indexes to support the most common queries to the Production Product table while minimizing the space required for the indexes Which two indexes should you use Each correct answer presents part of the solution Choose two Create a clustered index on the Name column Create a nonclustered index on the ProductLine column and include the ListPrice Class and Style columns Create a nonclustered index on the Name column Create a clustered index on the ProductLine ListPrice Class and Style columns Create a nonclustered index on the ProductLine ListPrice columns Answer A B Explanation The name is used frequently in a way that would benefit from a clustered index ORDER BY Name Next you se in the QUERY that ListPrice Class and Style is also used frequently but without an ORDER BY clause attached to them These would benefit from a nonclustered index on the ProductLine column including the other columns QUESTION You are a database administrator for Certkiller com You are responsible for SQL Server computers These computers are monitored by other administrators who are required to ensure that query response times meet company service level agreements SLAs You need to provide these administrators with a consistent way of monitoring query response times on the servers What should you do A Create SQL Server Profiler templates that include query start times and end times Distribute these templates to the database administrators Write a query that queries the sys dm db partition stats dynamic management view DMV Distribute this query to the administrators Create an XML document that contains the event and column names of the query response times that must be captured in SQL Server Profiler Distribute the XML file to the database administrators Instruct the administrators to use System Monitor to monitor cache hit ratio statistics Answer A Explanation You can use SQL Server Profiler to create templates that define the event classes and data columns to include in traces After you define and save the template you can run a trace that records the data for each event class you selected you can use template on many traces the template is not itself executed QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller B Certkiller B runs a single SQL Server instance You manage Certkiller B from an administrative workstation named DBA Certkiller B intermittently fails to respond to user queries Currently it is not responding to your connection through SQL Server Management Studio You need to correct the problem What should you do Log on interactively to Certkiller B as an administrator Establish a dedicated administrator connection to the SQL Server instance Log on to Certkiller B by using the Administrator logon account that is built into Microsoft Windows Log on to the SQL Server instance on Certkiller B by using the sa logon account that is built into SQL Server Restart the SQL Server Browser on Certkiller B Answer A Explanation SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible It allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests This dedicated administrator connection DAC is available and supported through the command-line interface sqlcmd using a special administrator switch -A QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A You create a security template that disables unnecessary services The template is appropriate only for Certkiller You need to ensure that the security template is applied to Certkiller Your solution must ensure that the template automatically overrides any manual configuration changes made by other administrators within an hour What should you do Configure Task Scheduler to import the security template by using Secedit exe Configure the task to run every half hour Configure Task Scheduler to run the Microsoft Baseline Security Analyzer tool every half hour Use the Local Security Policy editor to import the security template into the Local Security Policy object Use the SQL Server Surface Area Configuration tool to import the security template Answer A Explanation The Baseline Security Analyzer tool only analyzes the current security settings on a computer If you import the template with the Local Security Policy editor it will not guarantee that no modifications are done to the computer after the import SQL Surface Area Configuration tool only implies to SQL specific settings By importing the template every minutes with Secedit exe and a Scheduled task you ensure that any modifications will be undone within minutes QUESTION You are a database administrator for Certkiller com The company uses a SQL Server application Users report that the application sometimes responds very slowly for several minutes at a time This problem occurs throughout the day and typically takes place after a large number of database rows are deleted The database properties are configured as shown in the exhibit Click the Exhibit button You need to ensure that this performance problem does not recur What should you do Set Page Verify to None Set ANSI Warnings Enabled to True Configure the database so that the AUTO-SHRINK option is disabled Configure the database to be the simple recovery model Answer C Explanation When a large number of rows is deleted the SQL Server will try to AUTO-SHRINK the database and will temporarily respond slowly due to the increased workload QUESTION You are a database administrator for Certkiller com You are configuring a new SQL Server computer The computer contains five hard disks You need to ensure that the server can continue functioning in the event that a single hard disk fails Which two actions should you perform Each correct answer presents part of the solution Choose two Configure a non-RAID volume that uses one hard disk Install Microsoft Windows Server and SQL Server on the non-RAID volume Configure a RAID- volume that uses two hard disks Install Microsoft Windows Server and SQL Server on the RAID- volume Configure a RAID- volume that uses four hard disks Place the transaction log and data files on the RAID- volume Configure a RAID- volume that uses three hard disks Place the transaction log and data files on the RAID- volume Configure a RAID- volume that uses three hard disks Place the transaction log and data files on the RAID- volume Answer B D Explanation By creating a mirrored disk set RAID- for the OS and the SQL Server installation you ensure that there is a copy of the installation if a hard disk error occurs By putting the data and transaction logs on a Striped set with parity RAID- you ensure that the data is available even if one of the three disks in the RAID- configuration crashes RAID- or non-RAID volumes provides no fault tolerance And configuring a RAID- over disks would mean that you have to use one non-RAID volume QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller The SQL Server service and the SQL Server Agent service are configured to start automatically You need to configure Certkiller A to audit all user names and application names that attempt to access information from Certkiller Certkiller A must perform auditing at all times and the results must be kept for years What should you do Create a SQL Server Agent job to run the SQL Trace stored procedure Configure the job to start when the SQL Server Agent service starts Set the Login auditing setting to Both failed and successful logins Disable the C Audit Tracing setting on the server properties Configure the server authentication mode as SQL Server and Windows Authentication mode Answer A Explanation Microsoft SQL Server provides Transact-SQL system stored procedures to create traces on an instance of the SQL Server Database Engine These system stored procedures can be used from within your own applications to create traces manually instead of using SQL Server Profiler To ensure that the trace is always running start the trace as a SQL Server Agent job configured to start when SQL Server Agent start which should be when the operative system starts QUESTION You work as DBA at Certkiller com You administer a SQL Server computer named Certkiller A Users report that all client applications that connect to Certkiller A are responding slowly You discover that Certkiller As CPU and memory utilization is low and that disk activity is low You need to identify the cause of the performance problem What should you do Use System Monitor to examine Certkiller As cache hit ratio Examine the sys dm db index physical stats dynamic management function DMF Use Task Manager to examine utilization of Certkiller As network adapter Examine the sys dm db partition stats dynamic management view DMV Answer C Explanation The Task Manager will show you the current status of all network adapters and their workload If the Server experiences a lot of network activity that could cause the applications to respond slowly QUESTION You need to design a stored procedure to import the registry information from AppServer You want to accomplish this goal without allowing the execution of unmanaged code What should you do Use a regular stored procedure that calls xp cmdshell Use an extended stored procedure Use a CLR stored procedure with the SAFE permission set Use a CLR stored procedure with the EXTERNAL ACCESS permission set Use a CLR stored procedure with the UNSAFE permission set Answer D Explanation The set of CAS permissions granted to assemblies by the SQL Server host policy level is determined by the permission set specified when creating the assembly There are three permission sets SAFE EXTERNAL ACCESS and UNSAFE SAFE Only internal computation and local data access are allowed SAFE is the most restrictive permission set Code executed by an assembly with SAFE permissions cannot access external system resources such as files the network environment variables or the registry EXTERNAL ACCESS SAFE with the additional ability to access external system resources such as files networks environmental variables and the registry UNSAFE UNSAFE allows assemblies unrestricted access to resources both within and outside SQL Server Code executing from within an UNSAFE assembly can call unmanaged code QUESTION You are the administrator of a SQL Server computer named SQL SQL contains two user databases SQL also contains the publisher database for a transactional replication publication Every night a full backup is made of all databases Transaction log backups are made twice each day for both user databases SQL is configured to use SQL Server and Windows authentication Client applications connect by using SQL Server login accounts The disk subsystem on SQL experiences a complete failure You replace the failed hardware reinstall Microsoft Windows Server and reinstall SQL Server You need to ensure that SQL is operating normally as quickly as possible and with a minimum loss of data Your solution must make the user databases available to client applications as soon as those databases are restored What should you do first Restore all of the system databases Restore the two user databases only Restore the publisher database only Re-create the transactional replication publication Answer A Explanation If you choose to perform any of the other options you miss all login information used to connect to any of the databases QUESTION You are a database administrator for Certkiller com Your SQL Server computer contains a user database named Products You use SQL Server Integration Services SSIS packages to export data from the Products database to a text file You use FTP to send the text file to your company's trading partners You need to ensure that you can recover your SSIS packages if a disaster occurs What should you do Back up the Products database Back up the msdb database Configure your server to use the full recovery model Configure your server to use the bulk-logged recovery model Answer B Explanation The msdb database is used by SQL Server SQL Server Management Studio and SQL Server Agent to store data including scheduling information and backup and restore history information QUESTION You are a database administrator for Certkiller com You administer a SQL Server computer that contains a database named Customers The database includes a table named CustInfo The table includes an identity column named CustID and an nvarchar column named CustName Company policy requires that rows containing duplicate CustName values be removed from the CustInfo table once each year However rows cannot be removed from the CustInfo table until they have been manually examined and verified as being duplicates You need to provide a way to identify potential duplicate rows for manual examination You need to accomplish this goal by using the least amount of administrative effort What should you do Create a trigger for the CustInfo table that rolls back INSERT queries that contain duplicate data Use a SELECT INTO query that includes GROUP BY and HAVING clauses to create a new table that contains duplicate rows from the CustInfo table Create a new unique index on the CustName column Specify IGNORE DUP KEY ON when creating index On the CustName column create a foreign key constraint that references a distinct list of CustName values Answer B QUESTION You are a database administrator for a financial services company You are configuring replication between two SQL Server computers named SQL and SQL SQL stores real-time transactional data SQL is used for reporting SQL must not change data on SQL The publication contains a large amount of data You need to ensure that during replication data is copied from SQL to SQL in as near real-time as possible to ensure accurate reporting You need to ensure that this data is copied securely What should you do Configure snapshot replication on SQL Configure SQL as a standard subscriber for a standard subscription Configure merge replication on SQL Configure SQL as a standard subscriber for an updateable subscription Configure transactional replication on SQL Configure SQL as a standard subscriber for an updateable subscription Configure transactional replication on SQL Configure SQL as a standard subscriber