You have a computer running Microsoft Windows Server 2012 on which you support a default instance of SQL Server 2012. You want to reduce the load form the current instance for which you need to offload reporting to a different instance of SQL Server 2012. The impact on the current SQL Server 2012 instance from reporting must be kept to a minimum. Users are not allowed to modify the data that is used for reporting. A full backup of SQL Server and a transaction log backup is run by you. What will be your next step of action?

You are working for a company where you maintain a server running SQL Server 2012. Some routine maintenance is planned and it may cause some performance degradation. You want to identify the time when there are least amount of database connections, for which you will be tracking the number of user connections at various times of day. What do you should be your step of action?

You work for a company as a database administrator. Your company network is configured as a single Active Directory Domain Services (AD DS) domain. There are two default instances of SQL Server 2012 supported by you. Windows Server 2008 R2 is running on a server one of the instances in on and Windows Server 2012 R2 is running on a server on which the other instance is running on. You have to transfer a large amount of data between the two instances, and you plan to use bcp for it. You need to have the transfers run as quickly as possible. There is no extended or double-byte character set (DBCS) characters included in the data. What data format should you use for the transfer? (More than one answers could be correct. (opt for the BEST answer)

An instance of SQL Server 2012 is supported by you. There are also other SQL server instances in the network. You are required to configure certificate-protected Service Broker transport security so communication with other SQL Server instances can be protected. What will be your first step when you have already created a Service Broker endpoint?

You are working for a company managing a server running SQL Server 2012. In order for the security settings of the server to be audited, a declarative management framework (DMF) policy is created. It is also to be ensured that Database Mail is not enabled. Evaluation mode of the policy is set to On demand. When the policy is evaluated and Event Viewer Log is checked, there are no violations entries spotted. You discover the Database Mail status to be enabled. In case there is a policy violation you would want events to be logged to Event Viewer. You need to ensure that the current state is evaluated for violations. What would be your first step of action? (opt any TWO)

You work for an organization managing an instance of SQL Server 2012. There are three user databases hosted by the instance. The user databases are named as Accounts, SalesData, and Ops. You run a full backup of each database every weekend. What would be your step of action if you want to reduce the size of the backup file created, when you run a full backup of SalesData, to a minimum? You also have to make sure backups for Accounts or Ops are not compressed?

You have a computer running Windows Server 2012. A default instance of Microsoft SQL Server 2012 is installed in it. There is an Active Directory Domain Services domain which the computer is a member server in. You specify a domain user account as the SQL Server service account. There is a periodic maintenance Every other Saturday, and so the database server is shut down for that purpose. You come to a situation where the SQL Server service is not able to restart, and it has been six weeks that the operation is going on. You suspect the issue is relevant to the service account upon reviewing the error logs. You have to make sure if your assumption about the issue being related to service account is correct or not. You are sitting on a Saturday, ad have to rectify the issue before start of Monday. What will be your first step of action?

You work for an organization managing a SQL Server 2012 database. Marketinf is a schema owned by RockyM. GeorgeY replaces RockyM as his responsibilities change. What would you do to change the ownership of Marketing schema?

You are working for a company as an administrator looking after a default SQL Server 2012 instance. A new table is created and the table has a single non-clustered index. To load a large quantity of data to the table, you use the bcp utility. You want to check the data between each batch while performing the import, so you do it in three phases. You configure the target database to import the data as a minimally logged operation and import the first batch. Once the operation is finished, you run a transaction log backup. You are preparing to import the second batch. You are required to make sure all bulk import related operations are logged minimally. What will be you first step of action?

Your company has a network that includes an instance of SQL Server 2000. The computer that has this instance on is named Data1 and is running Windows Server 2008. There is user database hosted by the instance and supporting a retail application. You install a default instance of SQL Server 2012 on a computer named Data5 running Windows Server 2012. The user database is now planned to be migrated from the SQL Server 2000 instance to the SQL Server 2012 instance. You need to make sure the time of database unavailability is kept to the minimum. What would you do?

You work for a company where you support a database on a named instance of SQL Server 2012. statistical research data for analysis is collected using the database. The database is configured for the full recovery model. Following is the backup plan currently in place: -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 lead researcher decided to keep the database in bulk-logged mode at all times. The purpose of this decision was to facilitate the data loads and keep storage space requirements to a minimum. Can you identify the potential risk this action may have to backup and data recovery?

MapDB is database you maintain. An instance of SQL Server 2012 is hosting this database. There is a table called Maps is the database and the table contains Mapinfo, a column of type geography, Description, a column named Description of type varchar(50), MapID, a column of type char(10). You are required to have queries using the STDistance function optimized. What would you do?

You work for a company managing a server running SQL Server 2012. Mixed-mode authentication is used by SQL Server as per its configuration. HelpDesk department members must be able to reset passwords for SQL Server logins and terminate processes involved in a deadlock condition. A login associated with the HelpDesk group is created by you and you have to make sure minimum necessary permissions are granted. What action would you take?

While managing an instance of Microsoft SQL Server 2012 at your job, you are required to create a SQL Server Agent job. The job has several job steps. While testing execution, you come to a situation where a Transact-SQL (T-SQL) job step fails. You realize insufficient permission is causing the job step to fail. 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 would be your step of action?

You work for a company maintaining database servers. There is a database server that is a member of the domain. You plan on installing SQL Server 2012 on that database server. Following are the requirements the database server must support: -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 are required to identify the service accounts you will use. What would you do keeping in mind you have to select the most secure option?

You work for a retail company as a database administrator. There are two SQL servers, SQLServerA and SQLServerB. A database backup from SQLServer A is taken and restored to SQLServer B. The users face the following error when connect to SQLServerB using their SQL logins: 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 want the problem to be fixed and have the users connect to the database. Identify the two possible ways to achieve the goal? (opt any TWO)

You work for a company managing an active-passive cluster. The cluster is running SQL Server 2012 and full-text search. a member of the Domain Admins group as the service account is used by the SQL Server service as it is configured. A security audit has been carried and after that it is instructed to you to change the SQL Server service account to a lower-privileged domain user. You have to use fewest steps possible to to perform the necessary configuration. What would you do?

You are working for an organization as an administrator for a default instance of Microsoft SQL Server 2012. You want help in managing the Inventory database for which you designate a user. A login and database user account is created for the user. You want the user to add and remove access to the database for which you grant the user the permission. You have to ensure no extra permissions are granted to the user. What would you do?

Your company has applications that will use the database engine. You need to benchmark the performance of the disk system of your SQL Server deployment to ensure that it meets the needs of those applications. Which tools can you use to perform the benchmarking? (opt any TWO)

You work for an organization maintaining an instance of SQL Server 2012. Multiple alerts have been set to notify you via email upon certain conditions being met. Event and performance condition alerts are both included. You stop receiving event alert notifications while trying to correct an intermittent problem. You continue to receive performance condition alert notifications. To verify this, you force an event. You want to receive event alert notifications as you would normally receive, and so you need to correct the problem. What step would you take?

You work for a company managing a default Microsoft SQL Server 2012 instance. SKU is a schema owned by dbo. The Inventory database contains the schema. There are several database objects that the schema contains. You are tasked to transfer ownership of the schema to InvData_owner. You have to make sure that while doing this, administrative effort is minimum and almost no interference occurs in database operations. What would be your step of action?

You work for an organization maintaining a server running SQL Server 2012. A performance problem occurs and you decide to troubleshoot for that performance problem. You identify a long-running query by using sys.dm_exec_query_stats. The the syntax of the long running query needs to be viewed. What would you do?You work for an organization maintaining a server running SQL Server 2012. A performance problem occurs and you decide to troubleshoot for that performance problem. You identify a long-running query by using sys.dm_exec_query_stats. The the syntax of the long running query needs to be viewed. What would you do?

You are working as a database administrator managing a server running SQL Server 2012. Sam and Michelle will be notified by several email alerts when certain conditions are met. Michelle is on a leave for a couple weeks, and you want to configure those notifications to be received by Sam only, until Michelle gets back. What would you do, keeping in mind the solution must need least amount of effort?