Which type of user should you create?
You use a contained database named ContosoDb within a domain.
You need to create a user who can log on to the ContosoDb database. You also need to
ensure that you can port the database to different database servers within the domain
without additional user account configurations.
Which type of user should you create?
You need to redesign the dimension to enable the full historical reporting of changes to multiple student attr
You are reviewing the design of a student dimension table in an existing data warehouse
hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to student
attributes such as ParentOccupation.
You need to redesign the dimension to enable the full historical reporting of changes to
multiple student attributes including ParentOccupation.
What should you do?
Which Transact-SQL statement should you use before attempting the restore?
You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the
following statements:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MyPassword1!’
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = ‘TDE Certificate’;
BACKUP CERTIFICATE TDE_Certificate TO FILE = ”d:\TDE_Certificate.cer’
WITH PRIVATE KEY (FILE = ‘D:\TDE_Certificate.key’, ENCRYPTION BY PASSWORD =
‘MyPassword1!’);
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails. You copy the encryption
file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy the
encryption files to their original location. However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?
You need to reduce the report processing time and minimize the growth of the database
You are reviewing the design of an existing fact table named factSales, which is loaded
incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS)
package each hour. The fact table has approximately 4 billion rows and is dimensioned by
product, sales date, and sales time of day.
The database administrator is concerned about the rapid growth of the database and users
experience poor reporting performance against this database. Reporting requirements have
recently changed and the only remaining report that uses this fact table reports sales by
product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database.
What should you do?
Which Transact-SQL statement should you use?
You administer a SQL Server 2012 server that contains a database named SalesDB.
SalesDb contains a schema named Customers that has a table named Regions. A user
named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the
Select permission on the Customers schema.
You need to ensure that the Sales role, including UserA, is disallowed to select from any of
the tables in the Customers schema.
Which Transact-SQL statement should you use?
You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all
You are designing an enterprise star schema that will consolidate data from three
independent data marts. One of the data marts is hosted on SQL Azure.
Most of the dimensions have the same structure and content. However, the geography
dimension is slightly different in each data mart.
You need to design a consolidated dimensional structure that will be easy to maintain while
ensuring that all dimensional data from the three original solutions is represented.
What should you do?
Which Transact-SQL statement should you use?
You administer a SQL Server 2012 server that contains a database named SalesDB.
SalesDb contains a schema named Customers that has a table named Regions. A user
named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the
Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from any of the tables in the
Customers schema.
Which Transact-SQL statement should you use?
You need to add the new capability to all the packages without copying the code between packages
You are adding a new capability to several dozen SQL Server Integration Services (SSIS)
packages.
The new capability is not available as an SSIS task. Each package must be extended with
the same new capability.
You need to add the new capability to all the packages without copying the code between
packages.
What should you do?
Which Transact-SQL statement should you use?
You administer a SQL 2012 server that contains a database named SalesDB. SalesDb
contains a schema named Customers that has a table named Regions. A user named UserA
is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the
Select permission on the Customers schema.
You need to remove the Select permission for UserA on the Regions table. You also need to
ensure that UserA can still access all the tables in the Customers schema, including the
Regions table, through the Sales role permissions.
Which Transact-SQL statement should you use?
Which transformation types should you use?
You are creating a SQL Server Integration Services (SSIS) package to retrieve product data
from two different sources. One source is hosted in a SQL Azure database. Each source
contains products for different distributors.
Products for each distributor source must be combined for insertion into a single product
table destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each answer represents a complete solution.
Choose all that apply.)