Microsoft SQL Server 2012 deployments are administered by you. It needs to be ensured that the primary database requirements to another server can be differentiated from the up-to-date reporting requirements of OLTP database. Make sure that indexes can be added to the secondary database.

Choose the configuration for this purpose.

Microsoft SQL Server 2012 deployments are administered by you. It needs to be ensured that the non- SQL Server database server receives data change in real time. Also make sure that no data on the primary server gets affected in any way.

Choose your preferred configuration for the purpose.

All the deployments of Microsoft SQL Server 2012 are administered by you in your company. One of the databases features a big product catalog. This catalog is updated on a routinely basis. If you want to send the complete catalogue to all branch members monthly, which is the configuration you would prefer to choose?

All the deployments of Microsoft SQL Server 2012 are administered by you in your company. It needs to be ensured that the storage area network (SAN) which is used by OLTP database remains available in a case where all or any of the other servers fail.

The amount of storage that this database will use needs to be minimized. Which configuration do you think is right for this?

A Microsoft SQL Server database is administered by you. You go on to create “haContosoDbs”, an availability group. Its primary replica is at Server01\Contoso01. You are required prevent data loss by configuring an availability group. In case a database failure occurs, the secondary database should go online automatically.

Which of the following Transact-SQL statements are right for this task?

SQL database servers are administered by you. For an application, we have configured Merge replication. This application is distributed throughout WAN across offices.

A lot of tables that are involved in this merge replication use varchar (max) and XML data types. However, timeout errors become the cause of failure for merge applications occasionally. Your job is to reduce the number of these timeout errors to a minimum. How will you do it?

An availability group called HaContoso has been created by you. It has replicas called Server03/HA and Server01/HA, Server02/HA. Server01/HA currently is used as the primary replica. Your job is to make sure that backup operations only happen on Server02/HA. In case it is not available, these operations occur on Server03/HA. However, backup operations must not, at any cost, occur on Server01/HA.

For that, how should the availability group—HaContoso—be configured?

A Microsoft SQL Server 2012 instance is administered by you. A variety of SQL Server Agent jobs have been configured within them. When these jobs fail, it truncates the error messages that are returned by job steps. Here an an example of one of such messages.

"Executed as user CONTOSO\ServiceAccount....0.4035.00 for 64-bit Copyright (C) Microsoft Corp

1984-2011. All rights reserveD. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript... The package execution f

A.. The step failed."

Your job is to make sure that SQL Server Agent jobs get to see the job step failure details, which is why you need to retain these failures. How will you accomplish this task?

A Microsoft SQL Server with SQL Server with Integration Services (SSIS) is administered by you. New SSIS packages are to be deployed to the server as planned. Integration Services environment variables and Project Deployment Model along with parameters are used by the SSIS package. Your job is to make sure that these packages are supported by configuring SQL Server environment. How would you do that?

A Microsoft SQL Server with SQL Server is administered by you. Database administrators must receive an email by the SQL Server right when the traction logs grow! Your job is to configure an SQL Server that sends out these email messages. How would you do it?

Two instances of Microsoft SQL Server 2012 are being administered by you. An application has been deployed by you in which the database is used on the named instance. However, on the named instance, the application is not being able to connect to the database. Your job is to make sure that application and the named instance can connect. How will you do that?

A Microsoft SQL Server with SQL Server is administered by you. The database contains two folders called, “Orders owned by UserB” and “Customers owned by UserA”. In addition to that, a stored procedure owned by UserB called, “GetCustomerOrderInfo” is also present with you. Data from both the data tables is selected by “GetCustomerOrderInfo”. Now you create another user called UserC.

Your job is to make sure that GetCustomerOrderInfo can be called by UserC. Furthermore, minimum required permissions should be assigned to the UserC. Which of the following permission(s) should UserC be assigned? Each correct answer has a partial solution to the problem.

A Microsoft SQL Server 2012 database called ‘ContosoDB’ is administered by you. In Purchases schema, the database contains a column called, ‘IsActive’ and a table called, ‘Suppliers’. In ContosoDB, a new user is created by you named, ‘ContosoUser’. ContosoUser has no access or any sort of permission towards the ‘Suppliers’ table.

Your job is to make sure that ContosoUser has the ability to remove only the rows that are inactive from ‘Suppliers’. Furthermore, minimum required permissions are also needed to be granted to ContosoUser. Which of the following Transact-SQL statement would you use?

A contained database called ContosoDb is used by you with a domain. A user has to be created that can log into ContosoDb database. You also have to make sure that within the domain, this database can easily be ported to a different database without requiring further user account configurations. Which user type will you create?

A Microsoft SQL Server database with several tables present within a Sales schema is monitored by you. There are certain users that must be stopped from removing any records from any table present within the Sales schema. Your job is to manage users that are stopped from the deletion of these Sales scheme records.

This goal must be achieved using the least administrative effort possible. How would you do it?

For a Microsoft SQL Server environment, you happen to be a DBA—Lead Database Administrator. Each DBA is part of the Active Directory group called DOMAIN\JrDBAs. DOMAIN\JrDBAs is granted SQL Server access. Now, your job is to create a server role called SpecialDBARole. Ensure that SpecialDBARole created can perform these functions:

View Server State
Assign GRANT, DENY, and REVOKE permissions on logins

To server role, you are now required to add DOMAIN\JrDBAs. Make sure that little to none of the privileges are provided. There are three SQL statements you need to use. Which of the following three are those? Each correct answer holds a partial solution to the problem.

A Microsoft SQL Server database having ‘Trustworthy’ on is administered by you. A stored procedure is created allowing for the Dynamic Management Views to return database-level information. User1 is given the access in order to execute this stored procedure. It needs to be made sure of that when User1 implements the stored procedure, the required information is returned by the stored procedure. This needs to be done with the least amount of permissions. To do this, which of the two actions would you prefer performing? Each correct answer offers a partial solution to the problem

A database called ‘Orders’ is being migrated to a new Microsoft SQL Server 2012 server. An attempt to include [Corpnet\User1] login into the database is made by you. However, it shows the following message:

"User already exists in current database."

[Corpnet\User1] login needs to be configured so it can access ‘Orders’ database while retaining original permissions. This needs to be achieve with minimum required permissions. Which of the following Transact-SQL statements will you use for this?

A Microsoft SQL Server 2012 database is administered by you.

Temporary securityadmin access is provided to User1 for the database server. Your job is to find out of User1 adds securityadmin logins. Choose your audit action group server-level you would use.

The Microsoft SQL Server 2012 instance is administered by you.

For a process with SPID of 6, what would be your approach to stop its locking, without stopping other processes?

The Microsoft SQL Server database is administered by you.

There are reports by the users about how the application that accesses the database displays an error which fails to provide important information. There are no error-related entries found in either the Windows event logs or the SQL Server log.

Through retrieval of the error message, what would be your approach to identify the basic cause of the issue?

The Microsoft SQL Server database server is under your administration. A highly active OLTP application is supported by one of the databases present on the server.

Complaints of abnormally long waits are reported by the users upon the submission of data

What would be your approach to identify the query which is estimated a time longer than 1 second in order to run over an extended time period?

The Microsoft SQL Server 2012 database is under your administration.

What must you do in order to make sure that the transaction log file’s size doesn’t exceed 2 GB?

The server you are administering is a Microsoft SQL Server. The domain account used for this service is called CONTOSO\SQLService.

You plan on configuring the Instant File Initialization.

What must you do in order to make sure that the Data File Autogrow operations use Instant File Initialization? Choose all the answers you find to be correct.

The Microsoft SQL 2012 instance is administered by you, and the drive containing tempdb fails after a routine shutdown.

What must you do in order to start the SQL server?

A single server that you administer has a Microsoft SQL Server 2012 default instance.

You plan a new application’s installation that would need the deployment of the server’s database. Sysadmin permission is required for the application login.

What would be your approach to ensure that the login of the application is not able to access the other production databases?

A Microsoft SQL Server 2012 Enterprise Edition server that you administer uses around 64 cores.

You find that, when large data is written under heavy load, performance issues tend to occur.

What must you configure in order to limit the number of cores that handle I/O?

The Microsoft SQL Server 2012 instance that you administer contains a financial database which is hosted on Storage Area Network (SAN). The following characteristics can be seen on this database:

• A dedicated LUN (drive E) is where a 10 GB transaction log is located. 1 terabyte disk space is remaining in Drive D.
• 5 GB of disk space is available on Drive E.
• A dedicated LUN (drive D) has a 2 terabytes data file present in it.

Modification on the database is continually made by the users from Monday to Fri0day between 09:00 hours and 17:00 hours. Each day, five percent of the existing data is modified.

Each business day at 11:15 hours and 15:15, a number of tables are loaded with large CSV files by the Finance department through the usage of the BULK INSERT or BCP commands. 3GB of data is added to the database by every data load.

The most minimal time must be needed for the data load operations to occur.

Every Sunday at 10:00 hours, a full database operation is performed, whereas backup operations is performed during business hours, every two hours (11:00, 13:00, 15:00, and 17:00).

What is the backup option that you must use in order to make sure that your backup continues regardless of the occurrence of invalid checksum?

The Microsoft SQL Server 2012 instance that you administer contains a financial database which is hosted on Storage Area Network (SAN). The following characteristics can be seen on this database:

• A dedicated LUN (drive E) is where a 10 GB transaction log is located. 1 terabyte disk space is remaining in Drive D.
• 5 GB of disk space is available on Drive E.
• A dedicated LUN (drive D) has a 2 terabytes data file present in it.

Each business day at 11:15 hours and 15:15, a number of tables are loaded with large CSV files by the Finance department through the usage of the BULK INSERT or BCP commands. 3GB of data is added to the database by every data load.

The most minimal time must be needed for the data load operations to occur.

Every Sunday at 10:00 hours, a full database operation is performed, whereas backup operations is performed during business hours, every two hours (11:00, 13:00, 15:00, and 17:00).

At 10:00 hours on Wednesday, you are requested by the development team to use the most recent version to refresh the database on a development server.

What is the backup option that you must use in order to perform a full database backup which will be restored on the development server?

The Microsoft SQL Server 2012 instance that you administer contains a financial database which is hosted on Storage Area Network (SAN). The following characteristics can be seen on this database:

• A dedicated LUN (drive E) is where a 10 GB transaction log is located. 1 terabyte disk space is remaining in Drive D.
• 5 GB of disk space is available on Drive E.
• A dedicated LUN (drive D) has a 2 terabytes data file present in it.
Modification on the database is continually made by the users from Monday to Fri0day between 09:00 hours and 17:00 hours. Each day, five percent of the existing data is modified.

Each business day at 11:15 hours and 15:15, a number of tables are loaded with large CSV files by the Finance department through the usage of the BULK INSERT or BCP commands. 3GB of data is added to the database by every data load.

The most minimal time must be needed for the data load operations to occur.

Every Sunday at 10:00 hours, a full database operation is performed, whereas backup operations is performed during business hours, every two hours (11:00, 13:00, 15:00, and 17:00).

What is the recovery model that you choose to use in order to make sure the most minimal data is lost?

A storage area network (SAN) is hosts a financial database that is present in the Microsoft SQL Server instance which you administer.

The following characteristics are present in the financial database:

• A data file of 2 terabytes is located on a dedicated LUN (drive D).
• A transaction log of 10 GB is located on a dedicated LUN (drive E). Drive D has 1 terabyte of free disk space.
• Drive E has 5 GB of free disk space.

Modification on the database is continually made by the users from Monday to Friday between 09:00 hours and 17:00 hours. Each day, five percent of the existing data is modified.

Each business day at 11:15 hours and 15:15, a number of tables are loaded with large CSV files by the Finance department through the usage of the BULK INSERT or BCP commands. 3GB of data is added to the database through each data load.

The most minimal time is needed for these data load operations to occur.

Every Sunday at 10:00 hours, a full database backup is made; during the business hours, these operations tend to be performed every two hours (11:00, 13:00, 15:00, and 17:00).

Your job is to make sure that the backup size is as minute as possible; what backup must be performed every two hours?

The Microsoft SQL Server 2012 instance is administered by you.

This contains a database supporting an application of retail sales, which not only creates hundreds of transactions per second, but is also online 24/7.

In order to define a database backup strategy, you need to make sure that the requirements met are amongst the following:

• The transactions lost must be no more than 5 minutes worth.
• The most minimal administrative effort must be used in recovering the data.

What would be your approach? (Pick the three most appropriate ones)

A table called OrderDetail is present in the Microsoft SQL Server 2012 database which you administer. In that, you find out that the NCI_OrderDetail_CustomerID non-clustered index is fragmented.

What is the Transact-SQL batch that you must use in order to decrease fragmentation? Keep in mind that this goal must be achieved without taking the index offline.

While you administer a Microsoft SQL Server, you also plan on deploying a few new features to an application.

What must you do in order to evaluate both the existing as well as the potential non-clustered and clustered indexes which would eventually help improve the performance?

Contoso is the Microsoft SQL Server 2012 database that you are administering on a server known as Server01.

What must you create in order to be able to write messages to the Application Log as soon as users are either removed from or added to the Server01’s server role?

Contoso is the Microsoft SQL Server 2012 database that you are administering on a server known as Server01.

What must you create in order to receive immediate notifications of fatal errors occurring on Server01?

Contoso is the Microsoft SQL Server 2012 database that you are administering on a server known as Server01.

Deadlocks often occur during the execution of specific stored procedure sets through recording events, as well as their playback on a different test server. What must you create in order to diagnose these deadlocks?

Contoso is the Microsoft SQL Server 2012 database that you are administering on a server known as Server01.

What must you create in order to prevent the disabling of Server01 server audits by the users?

The Microsoft SQL Server 2012 is administered by you.

After examining the application log, when more than an hour is taken by a process normally running for less than 10 seconds, you find that the session ID 60 is being used by the process.

What is the Transact-SQL statement that you must use in order to figure out whether blockage is occurring in the process?

During Microsoft SQL Server 2012 deployment administration, you have two separate servers that are hosting production within the same data center.

In a case where a catastrophic failure occurs, it needs to be ensured that your database does not become unavailable. This needs to be assured alongside the maintenance of data’s transactional consistency across servers. While achieving these targets, you must prevent manual intervention.

Which of the following configuration would you use?