Which TransactSQL statement should you use?
You have a table named Sales.PotentialClients. This table contains a column named EmailAddress.
You are tasked to develop a report that returns valid “.com” email addresses from
Sales.PotentialClients.
A valid email address must have at least one character before the @ sign, and one character after
the @
sign and before the “.com.”
You need to write a TransactSQL
statement that returns data to meet the business requirements.
Which TransactSQL
statement should you use?
What should you recommend?
###BeginCaseStudy###
Case Study: 4
Northwind Traders
Overview
Northwind Traders is a retail company.
The company has offices in Montreal and San Diego. The office in Montreal has 1,000 client
computers. The office in San Diego has 100 computers. The computers in the San Diego
office are often replaced. The offices connect to each other by using a slow WAN link. Each
office connects directly to the Internet.
Existing Environment
Active Directory Environment
The network contains an Active Directory forest named northwindtraders.com. The forest
contains two domains named northwindtraders.com and west.northwindtraders.com. All
servers run Windows Server 2012 R2.
All client computers run Windows 7.
Each office is configured as an Active Directory site. The site in the Montreal office is named
Site1. The site in the San Diego office is named Site2.
The forest contains four domain controllers. The domain controllers are configured as shown
in the following table.
DC1, DC2, and DC3 are writable domain controllers. R0DC1 is read-only domain controller
(RODC). All DNS zones are Active Directory-integrated. All zones replicate to all of the
domain controllers.
All of the computers in the San Diego office are configured to use RODC1 as their only DNS
server.
The northwindtraders.com domain contains a Group Policy object (GPO) named GPO1.
GP01 is applied to all of the users in the Montreal office.
All of the user accounts for the Montreal users are in the northwindtraders.com domain. All
of the user accounts for the San Diego users are in the west.northwindtraders.com domain.
Network Environment
Site1 contains the member servers in the northwindtraders.com domain shown in the
following table.
Server1 connects to SAN storage that supports Offloaded Data Transfer (ODX). All virtual
hard disks (VHDs) are stored on the SAN.
A web application named App1 is installed on Servers.
Server3 has a shared folder that contains sales reports. The sales reports are read frequently
by the users in both offices. The reports are generated automatically once per week by an
enterprise resource planning (ERP) system.
A perimeter network in the Montreal office contains two standalone servers. The servers are
configured as shown in the following table.
The servers in the perimeter network are accessible from the Internet by using a domain name
suffix of public.northwindtraders.com.
Each administrator has a management computer that runs Windows 8.1.
Requirements
Planned Changes
Northwind Traders plans to implement the following changes:
On Server1, create four virtual machines that run Windows Server 2012 R2. The servers will
be configured as shown in the following table.
• Configure IP routing between Site1 and the network services that Northwind Traders
hosts in Windows Azure.
• Place a domain controller for the northwindtraders.com domain in Windows Azure.
• Upgrade all of the computers in the Montreal office to Windows 8.1.
• Purchase a subscription to Microsoft Office 365.
• Configure a web application proxy on Server6.
• Configure integration between VMM and IPAM.
• Apply GPO1 to all of the San Diego users.
• Connect Site1 to Windows Azure.
Technical Requirements
Northwind Traders must meet the following technical requirements:
• All virtual machines must use ODX.
• Users must be able to access App1 from the Internet.
• GPO1 must not be applied to computers that run Windows 8.1.
• All DNS zones must replicate only to DC1, DC2, and DC3.
• All computers must be able to resolve names by using a local DNS server.
• If a WAN link fails, users must be able to access all of the sales reports.
• The credentials for accessing Windows Azure must be permanently stored.
• The on-premises network must be connected to Windows Azure by using Server4.
• The administrators must be able to manage Windows Azure by using Windows
PowerShell.
• The number of servers and services deployed in the San Diego office must be
minimized.
• Active Directory queries for the objects in the forest must not generate WAN traffic,
whenever possible.
Security Requirements
Northwind Traders identifies the following security requirements:
• Ensure that all DNS zone data is encrypted when it is replicated.
• Minimize the number of permissions assigned to users and
administrators, whenever possible. Prevent an Active Directory Domain
Services (AD DS) attribute named SSNumber from replicating to Site2.
• Ensure that users can use their northwindtraders.com user account to
access the resources hosted in Office 365.
• Prevent administrators from being required to re-enter their credentials
when they manage Windows Azure from approved management computers.
###EndCaseStudy###
HOTSPOT
You need to recommend a solution for communicating to Windows Azure services.
What should you recommend? To answer, select the appropriate options in the answer area.
Which Transact-SQL statement should you use?
You maintain a SQL Server 2008 Enterprise Edition instance that contains a database
named Finance. You need to reduce the size of the full database backup files of the Finance
database. Which Transact-SQL statement should you use?
You need to implement an IPAM solution
###BeginCaseStudy###
Case Study: 4
Northwind Traders
Overview
Northwind Traders is a retail company.
The company has offices in Montreal and San Diego. The office in Montreal has 1,000 client
computers. The office in San Diego has 100 computers. The computers in the San Diego
office are often replaced. The offices connect to each other by using a slow WAN link. Each
office connects directly to the Internet.
Existing Environment
Active Directory Environment
The network contains an Active Directory forest named northwindtraders.com. The forest
contains two domains named northwindtraders.com and west.northwindtraders.com. All
servers run Windows Server 2012 R2.
All client computers run Windows 7.
Each office is configured as an Active Directory site. The site in the Montreal office is named
Site1. The site in the San Diego office is named Site2.
The forest contains four domain controllers. The domain controllers are configured as shown
in the following table.
DC1, DC2, and DC3 are writable domain controllers. R0DC1 is read-only domain controller
(RODC). All DNS zones are Active Directory-integrated. All zones replicate to all of the
domain controllers.
All of the computers in the San Diego office are configured to use RODC1 as their only DNS
server.
The northwindtraders.com domain contains a Group Policy object (GPO) named GPO1.
GP01 is applied to all of the users in the Montreal office.
All of the user accounts for the Montreal users are in the northwindtraders.com domain. All
of the user accounts for the San Diego users are in the west.northwindtraders.com domain.
Network Environment
Site1 contains the member servers in the northwindtraders.com domain shown in the
following table.
Server1 connects to SAN storage that supports Offloaded Data Transfer (ODX). All virtual
hard disks (VHDs) are stored on the SAN.
A web application named App1 is installed on Servers.
Server3 has a shared folder that contains sales reports. The sales reports are read frequently
by the users in both offices. The reports are generated automatically once per week by an
enterprise resource planning (ERP) system.
A perimeter network in the Montreal office contains two standalone servers. The servers are
configured as shown in the following table.
The servers in the perimeter network are accessible from the Internet by using a domain name
suffix of public.northwindtraders.com.
Each administrator has a management computer that runs Windows 8.1.
Requirements
Planned Changes
Northwind Traders plans to implement the following changes:
On Server1, create four virtual machines that run Windows Server 2012 R2. The servers will
be configured as shown in the following table.
• Configure IP routing between Site1 and the network services that Northwind Traders
hosts in Windows Azure.
• Place a domain controller for the northwindtraders.com domain in Windows Azure.
• Upgrade all of the computers in the Montreal office to Windows 8.1.
• Purchase a subscription to Microsoft Office 365.
• Configure a web application proxy on Server6.
• Configure integration between VMM and IPAM.
• Apply GPO1 to all of the San Diego users.
• Connect Site1 to Windows Azure.
Technical Requirements
Northwind Traders must meet the following technical requirements:
• All virtual machines must use ODX.
• Users must be able to access App1 from the Internet.
• GPO1 must not be applied to computers that run Windows 8.1.
• All DNS zones must replicate only to DC1, DC2, and DC3.
• All computers must be able to resolve names by using a local DNS server.
• If a WAN link fails, users must be able to access all of the sales reports.
• The credentials for accessing Windows Azure must be permanently stored.
• The on-premises network must be connected to Windows Azure by using Server4.
• The administrators must be able to manage Windows Azure by using Windows
PowerShell.
• The number of servers and services deployed in the San Diego office must be
minimized.
• Active Directory queries for the objects in the forest must not generate WAN traffic,
whenever possible.
Security Requirements
Northwind Traders identifies the following security requirements:
• Ensure that all DNS zone data is encrypted when it is replicated.
• Minimize the number of permissions assigned to users and
administrators, whenever possible. Prevent an Active Directory Domain
Services (AD DS) attribute named SSNumber from replicating to Site2.
• Ensure that users can use their northwindtraders.com user account to
access the resources hosted in Office 365.
• Prevent administrators from being required to re-enter their credentials
when they manage Windows Azure from approved management computers.
###EndCaseStudy###
HOTSPOT
On Server2, you create a Run As Account named Account1. Account1 is associated to an
Active Directory account named VMMIPAM.
You need to implement an IPAM solution.
What should you do? To answer, select the appropriate configuration for each server in the
answer area.
Which is the correct answer?
You are managing a SQL Server 2008 instance which includes a database called TesterDB
for Home.com. The data file and the transaction log file which are located on the D: drive
that owns only 10 percent useful space. You should make sure that you move both files to
the G: drive.
Which is the correct answer?
Which statement should you use?
You have a table named Orders. OrderID is defined as an IDENTITY(1,1). OrderDate has a default
value of 1.
You need to write a query to insert a new order into the Orders table for CustomerID 45 with today’s
date
and a cost of 89.00. Which statement should you use?
You need to restore the Finance database by ensuring minimal data loss
You maintain a SQL Server 2008 instance that contains a database named Finance.
The most recent full backup was taken at 13:00 horns, a differential backup was taken at
15:00 hours, and database snapshots were created at 16:00 horns and at 17:00 hours. The
backups and the database snapshots are stored on a different disk from the database files.
The hard disk that contains the database files fails at 17:02 hours.
You need to restore the Finance database by ensuring minimal data loss.
What should you do?
Which TransactSQL statement should you use?
You have the following two tables.
The foreign key relationship between these tables has CASCADE DELETE enabled.
You need to remove all records from the Orders table.
Which TransactSQL
statement should you use?
Which is the correct answer?
You are managing your company database. You manage a SQL Server 2005 database
called ContractTEST, which is down.
You operate a restore by using the following Transact-SQL script. RESTORE DATABASE
ContractTESTFROM contracts_bu_deviceWITH CONTINUE_AFTER_ERROR,
CHECKSUM, RECOVERY After the restore, the SQL Server logs show errors in some page
restores.
What you should do is to repair the database pages, which are down, and facilitates repair
by using the fastest method.
Which is the correct answer?
Which type of virtual hard disk should you create on the LUN?
You have a server named Server1 that runs Windows Server 2012 R2. Server1 has the Hyper-V server
role installed.
An iSCSI SAN is available on the network.
Server1 hosts four virtual machines named VM1, VM2, VM3, and VM4.
You create a LUN on the SAN to host the virtual hard drive files for the virtual machines.
You need to create a 3-TB virtual hard disk for VM1 on the LUN. The solution must prevent VM1
from being paused if the LUN runs out of disk space.
Which type of virtual hard disk should you create on the LUN?