You support an instance of SQL Server 2012. You want to configure certificate-protected Service Broker transport security to protect communication with other SQL Server instances across the network. You have already created a Service Broker endpoint. What should you do first?

The user johndoe is unable to log in to a SQL Server database. He uses a SQL login to access the database. When you inspect the status of the account, you see the information in the exhibit. You need to allow johndoe to access the database using the least amount of administrative effort. What should you do?

You manage a server running SQL Server 2012. A declarative management framework (DMF) policy has been created to audit security settings for the server, including ensuring that Database Mail is not enabled. The policy's evaluation mode is set to On demand. You evaluate the policy and check the Event Viewer log. No violations entries appear. You check the status of Database Mail and find that it is enabled. You need to ensure that events are logged to Event Viewer if a policy violation exists. You need to ensure that the current state is evaluated for violations. What should you do? (Each correct answer presents a complete solution. (Choose two.)

You support a default instance of SQL Server 2012 on a computer running Microsoft Windows Server 2012. You need to offload reporting to a different instance of SQL Server 2012 to reduce the load on the current instance. You want to keep the impact on the current SQL Server 2012 instance from reporting to a minimum. Users are not allowed to modify the data that is used for reporting. You run a full backup of SQL Server and a transaction log backup. What should you do next?

You maintain a server running SQL Server 2012. You are planning to perform some routine maintenance that will result in some performance degradation. You need to track the number of user connections at various times of day to identify the time period when there are the fewest database connections. What should you do?

You are a database administrator for your company. Your company network is configured as a single Active Directory Domain Services (AD DS) domain. You support two default instances of SQL Server 2012. One is on a server running Windows Server 2008 R2. The other is on a server running Windows Server 2012 R2. You are planning to use bcp to transfer a large amount of data between the two SQL Server instances. You need to have the transfers run as quickly as possible. The data does not include any extended or double-byte character set (DBCS) characters. What data format should you use for the transfer? (More than one answer choice may be correct. (Choose the BEST answer.)

You manage an instance of SQL Server 2012. The instance hosts three user databases named Accounts, SalesData, and Ops. You run a full backup of each database every weekend. You need to minimize the size of the backup file created when you run a full backup of SalesData. You do not want to compress the backups for Accounts or Ops. What should you do?

You install a default instance of Microsoft SQL Server 2012 on a computer running Windows Server 2012. The computer is a member server in an Active Directory Domain Services domain. You specify a domain user account as the SQL Server service account. Every other Saturday, the database server is shut down for periodic maintenance. After six weeks of operation, the SQL Server service is unable to restart. You review the error logs and suspect that the problem is related to the service account. You need to verify whether or not the problem is related to the service account. You need correct the problem before start of business on Monday. What should you do first?

You configure an AlwaysOn availability group as shown in the exhibit. All nodes are located in the same office. You need to ensure that the availability group can support automatic failover if the primary replica fails. What should you do?

You manage a SQL Server 2012 database. Rocky owns a schema named Marketing. Rocky's responsibilities change and GeorgeY takes over his position. You need to change the ownership of the Marketing schema. What should you do?

You are an administrator for a default SQL Server 2012 instance. You create a new table with a single non-clustered index. You are using the bcp utility to load a large quantity of data into the table. You are performing the import in three phases to let you check the data between each batch. You configure the target database to import the data as a minimally logged operation and import the first batch. You run a transaction log backup after the operation is finished. You are preparing to import the second batch. You need to ensure that all operations relating to the bulk import are minimally logged. What should you do first?

Your network includes an instance of SQL Server 2000 on a computer named Data1 running Windows Server 2008. The instance hosts one user database that supports a retail application. You install a default instance of SQL Server 2012 on a computer named Data5 running Windows Server 2012. You want to migrate the user database from the SQL Server 2000 instance to the SQL Server 2012 instance. You need to keep time during which the database is unavailable to a minimum. What should you do?

You manage multiple instances of Microsoft SQL Server 2012. Your network is configured as a main office and a remote branch office connected through a routed wide area link. Each office hosts one instance of SQL Server 2012. The SQL Server 2012 instance in the main office includes a database named Mainlnventory. The SKUs table in Mainlnventory contains over one million rows. You configure merge replication between the SKUs table in Mainlnventory and a table named SKUs in a remote database hosted on the SQL Server 2012 instance in the remote office. The link to the remote office fails and by the time the link is repaired, the most recent merge update has expired. You need to bring the copy of the SKUs table in the remote office into synchronization with the copy in the main office as quickly as possible. You need to keep the network traffic generated by the solution to a minimum. You need to keep the effort necessary to correct the problem to a minimum. What should you do?

You maintain a database named OrderDB on a server running SQL Server 2012. The OrderDB database has table named Orders with a clustered index named IX_Orders. IX_Orders has three columns: OrderlD, OrderDate, and CustomerlD. OrderlD and CustomerlD are both columns of type char. OrderDate is of type smalldatetime. The Orders table also has an OrderStatus column of type varchar(20) and a ShipDate column of type smalldatetime. Several queries perform slowly. You view information about index fragmentation and find that IX_Orders is 40% fragmented. You need to improve performance. Your solution should not impact the availability of the Orders table more than necessary. What should you do?

You support a database on a named instance of SQL Server 2012. The database is used to collect statistical research data for analysis. The database is configured for the full recovery model. The backup plan currently in place is: * Full backup every Saturday night * Differential backup at 6:00 A.M. Monday through Friday * Transaction log backups one every two hours, on the hour Statistical data is imported through bulk loads several times during the day. The decision was made by the lead researcher that the database should be kept in bulk-logged mode at all times to facilitate the data loads and keep storage space requirements to a minimum. What is the potential risk to backup and data recovery from this action?

You maintain a database named MapDB. MapDB is hosted on an instance of SQL Server 2012. The MapDB database has a table named Maps that contains a column named Mapinfo of type geography, a column named Description of type varchar(50), and a MapID column of type char(10). You need to optimize queries that use the STDistance function. What should you do?

You manage a server running SQL Server 2012. SQL Server is configured to use mixed-mode authentication. Members of the HelpDesk department need to be able to reset passwords for SQL Server logins and terminate processes involved in a deadlock condition. You create a login associated with the HelpDesk group. You need to grant the minimum necessary permissions. What should you do?

You manage an instance of Microsoft SQL Server 2012. You are creating a SQL Server Agent job that includes several job steps. A Transact-SQL (T-SQL) job step fails when you test execution. You determine it is failing because of insufficient permission. You need to modify the job so that the T-SQL job step runs correctly. Your remaining job steps must be executed under the default security context for the job. What should you do?

You maintain database servers. You are preparing to install SQL Server 2012 on a database server that is a member of the domain. The database server must support the following requirements: * Execute stored procedures that access a linked server. * Execute a job that performs routine maintenance tasks. * Execute a third-party application that runs under the Network Service account. You need to identify the service accounts you will use. You must select the most secure option. What should you do?

You are the database administrator for a retail company. You take a database backup from SQLServer A and restore the backup to a newer server named SQLServer B. When the users try to connect to the database on SQLServerB with their SQL logins, the following error is returned: Msg 18456, Level 16, State 1 Login failed for user 'username'. You recreate the SQL logins and try to add them back to the database, but now you get the following error message: Error 15023: User already exists in current database You need to fix the problem and allow users to connect to the database. What are two possible ways to achieve the goal? (Each answer represents a complete solution. (Choose two.)

You are the primary administrator for a default Microsoft SQL Server 2012 instance. You create an Operator object for yourself and schedule your availability as 24 hours a day, 7 days a week. You have created several jobs that periodically perform automated management tasks. You configure each job to send you a notification at completion. You test to ensure that the jobs and notifications are working properly. You create a new Operator object for another administrator who should receive an e-mail notification instead of you when each job completes. You need to configure the operator to receive the notifications. You need to keep the administrative effort needed to complete this to a minimum. What should you do? (Each correct answer presents part of the solution. (Choose two.)

You manage an active-passive cluster running SQL Server 2012 and full-text search. The SQL Server service is configured to use a member of the Domain Admins group as the service account. After a security audit, you have been instructed to change the SQL Server service account to a lower-privileged domain user. You need to perform the necessary configuration using the fewest number of steps. What should you do?

You are the administrator for a default instance of Microsoft SQL Server 2012. You want to designate a user to help you manage the Inventory database. You create a login and database user account for the user. You need to grant permission for the user to add and remove access to the database. You need to minimize the additional permissions granted to the user. What should you do?

You need to benchmark the performance of the disk system of your SQL Server deployment to ensure that it meets the needs of the applications that will use the database engine. Which tools can you use to perform the benchmarking? (Each answer represents a complete solution. (Choose two.)

You maintain a server running SQL Server 2012. You are troubleshooting a performance problem. You identify a long-running query by using sys.dm_exec_query_stats. You need to view the syntax of the long running query. What should you do?

You manage a server running SQL Server 2012. Several alerts are configured to notify Sam and Michelle by e-mail when certain conditions are met. Michelle is going on vacation for two weeks. You need to ensure that only Sam is notified during her absence. Your solution should require the least amount of effort. What should you do?

You manage a database named CustomerAccounts. The database is configured to use the Latint_General_CS_Al collation. The InternationalCustomers table is defined as shown in the exhibit. You need to ensure that queries that sort the data based on Country or Name return an accent-sensitive result set. Your solution must not affect performance or database functionality more than necessary. What should you do?

You manage a server running SQL Server 2012. Users report performance problems at specific times of the day. They report that some queries also time out. You need to view information about threads that have been waiting for resources since the last time SQL Server restarted. What should you do?

You maintain an instance of SQL Server 2012. You have created multiple alerts designed to notify you by e-mail if various conditions occur. These include both event and performance condition alerts. While trying to correct an intermittent problem, you notice that you stop receiving event alert notifications. You continue to receive performance condition alert notifications. You force an event to verify this. You need to correct the problem so that you can receive event alert notifications. What should you do?

You manage a default Microsoft SQL Server 2012 instance. The Inventory database contains a schema named SKU owned by dbo. Several database objects are contained in the schema. You need to transfer ownership of the schema to InvData_owner. You need to do this with minimal administrative effort and with minimal interference with database operations. What should you do?