Font size
WorksheetsAdministering a SQL Database Infrastructure - PP Questions
Total questions: 63
Worksheet time: 1hrs 3mins
Which one of these statements is incorrect?
To connect to SQL Server, the principal must supply information that matches the credential data held by SQL Server.
In a Windows-based environment, administrators can enable policies for Windows users that enforce password complexity and expiration. SQL Server can enforce similar restrictions for SQL Server logins.
The trusted server application model is commonly used in large-scale enterprise applications, websites, and Internet services.
You can only reset passwords using the ALTER LOGIN Transact-SQL statement.
There are two levels at which you configure Azure Firewall Rules: Server and Database.
Put the following steps in order by numbering each to indicate the correct order.
(1)Import SQL PowerShell module.
(2)Use the SQL PowerShell provider to navigate to an SMO object.
(3)Assign an SMO object to a variable.
(4)Discover the object’s properties using Get-Member.
(5)Amend a property.
(6)Apply the amendment using the Alter method.
(1)Use the SQL PowerShell provider to navigate to an SMO object.
(2)Import SQL PowerShell module.
(3)Discover the object’s properties using Get-Member.
(4)Assign an SMO object to a variable.
(5)Amend a property.
(6)Apply the amendment using the Alter method.
What advantages are there to using PowerShell with Microsoft Azure?
Advantages include being able to create standard, tested scripts to set up identical databases or virtual machines.
True or false? PowerShell providers offer an alternative to using SQL Server Management Objects.
FALSE
TRUE
What is an SMO object?
An object with which part of SQL Server can be managed programmatically.
The top level object in the SQL Server hierarchy.
Part of the Windows operating system.
A SQL Server feature that improves performance.
A SQL PowerShell provider.
Can you name three ways of getting information about a cmdlet?
Using Get-Help cmdlet,
Using Get-Command cmdlet,
Using Get-Member cmdlet
Using Get-Info cmdlet,
Using Get-Command cmdlet,
Using Get-Member cmdlet
Using Get-Help cmdlet,
Using Get-Info cmdlet,
Using Get-Member cmdlet
Using Get-Help cmdlet,
Using Get-Command cmdlet,
Using Get-Info cmdlet
When would you use a PowerShell provider?
To access a data store such as SMO objects, the file system, or the registry. The SQL PowerShell provider is included with the SQLPS module. You must import it before you can access SMO objects.
What tasks might benefit from automating with PowerShell for your SQL Server environment?
reporting issues from error logs, reporting issues from other Microsoft installed products that have an impact on SQL Server, and reporting on SQL Server instances.
If an Extended Events session has no targets defined, how would you view the data generated by the session?
Use the Watch Live Data feature in SSMS.
Which of the following sources does not contain detailed information about Extended Events event definitions?
The SQL Server Technical Documentation.
The DMV sys.dm_xe_objects.
SQL Server Management Studio Extended Events GUI.
Why might you use the sys.dm_os_performance_counters system DMV, instead of Performance Monitor, to access SQL Server counters?
Its use enables you to compare information from system performance counters and from internal performance tools such as other DMOs
True or false? You can use the SQL Server data collector for real-time monitoring.
TRUE
FALSE
Which system data collection set report would you use to get the history of memory usage?
The Server Activity report.
The Query Statistics report.
The Disk Usage report.
What are the benefits of using a central data warehouse for SQL Server performance data, instead of local collection on each server?
Performance data is easier to access and report on if it is held in a single central location.
Number each of the following troubleshooting phases to indicate their correct order.
(1)Investigation Phase
(2)Analysis Phase
(3)Implementation Phase
(4)Validation Phase
(5)Create Documentation
(1)Investigation Phase
(2)Analysis Phase
(3)Validation Phase
(4)Create Documentation
(5)Implementation Phase
(1)Investigation Phase
(2)Analysis Phase
(3)Create Documentation
(4)Validation Phase
(5)Implementation Phase
You want to define an alias for a named instance of the database engine. The alias will be used by clients using both 32-bit and 64-bit native client drivers. In SQL Server Configuration Manager, where should you define the alias?
Under both SQL Native Client 11.0 Configuration (32-bit) and SQL Native Client 11.0 Configuration.
Under SQL Native Client 11.0 Configuration (32-bit).
Under SQL Native Client 11.0 Configuration.
What tools might you use to monitor an intermittent or long-term issue?
SQL Server Data Collector
Extended Events
SQL Trace
All of the above
Which log(s) will give you the most information when the SQL Server service will not start?
The SQL Server error log and the Windows system log.
The Windows system log.
The Windows application log.
The SQL Server error log.
The SQL Server error log and the Windows application log.
What are the unique features of transaction log restore?
If there is a system failure, you will need that log to bring your database back to a consistent state
A SQL Server feature that improves performance.
A high-load master server won't have any adverse performance impact by having a number of target servers.
Numbered and temporary procedures are supported in partially contained databases.
What are the three phases of the restore process?
Redo
Do
Undo
Data Paste
Data Copy
What are the four core objects types provided by SQL Server Agent?
Jobs
Alerts
Operators
Schedules
Logs
Which of the following statements is false?
A master server can have multiple target servers.
A high-load master server won’t have any adverse performance impact by having a number of target servers.
Each target server can only connect to a single master server.
Changing the name of a target server requires it to be registered with the master server.
Which of the following fixed roles are ordered from least privileged to most privileged? (Least priv at the top)
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
sysadmin
sysadmin
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
SQLAgentUserRole
SQLAgentOperatorRole
SQLAgentReaderRole
sysadmin
What happens to a credential when the password of the Windows user that the credential references is changed?
Option 1: The credential is automatically deleted.
Option 2: The credential is disabled.
Option 3: Attempts to use the credential fail until the password is updated.
Option 4: The credential continues to operate normally.
Why are credentials stored in the master system database and proxy accounts stored in the msdb system database?
Credentials are not solely used for security in SQL Server Agent.
Proxy accounts are exclusively used for SQL Server Agent job steps, so like all other SQL Server Agent configurations, they are stored in msdb. Credentials offer a general-purpose security tool that has several uses, so they are stored in master.
The SQL Server Integration Services package in this lab uses SQL Server Authentication to connect to the MIA-SQL instance to extract data. If the SQL Server Integration Services package were configured to use Windows authentication for its database connection, under what security context is the connection made when the Generate Sales Log job is executed by ADVENTUREWORKS\Administrator?
Assume that the exercise was successfully completed when you are selecting your answer.
ADVENTUREWORKS\Administrator
The SQL Server Agent service account
ADVENTUREWORKS\Student
PromoteApp
As a general rule, why should proxy accounts not be assigned access to all of the job step subsystems?
Because it increases the risk of unauthorized access and to adhere to the principle of least privilege.
If an error message is for information only, which of the following ranges will its severity fall into?
0 to 10
17 to 19
20 to 24
11 to 16
You are troubleshooting Database Mail. You want to see a list of the email messages that have been successfully sent and a list of email messages that could not be sent. Where can you find this information?
Query the dbo.sysmail_sentitems and dbo.sysmail_faileditems views in the msdb database.
True or false? SQL Server Database Mail can only be used for sending alerts and notifications.
FALSE
TRUE
Which of the following metrics cannot be used as the basis for an Azure SQL Database alert?
Total database size
CPU percentage
Blocked by Firewall
Server error number 9002
DTU percentage
You are planning to send notifications from SQL Server, and think it might be easier to use NET SEND notifications instead of email. Why should you not do this?
Broadcast messages are generally disabled on most modern operating systems, so NET SEND messages might not appear. Notification by NET SEND is marked for deprecation, and will be removed in a future release of SQL Server.
What is a PowerShell alias?
A way of getting more information about a cmdlet.
A PowerShell variable.
The full version of a cmdlet.
A familiar command line shortcut for a PowerShell cmdlet.
True or false? You want to designate a colleague in the IT team as an operator, but this colleague does not have a login in the SQL Server instance. You must define a login for your colleague before they can be configured as an operator.
TRUE
FALSE
What are Other database activities that can be undertaken to troubleshoot failed jobs?
Start and Stop SQL Server
Check Free Disk Space
Check Activity Monitor
All of the above
What are four steps that can be undertaken to troubleshoot failed jobs?
Review Job History
Check Access to Dependencies
Check Job Execution
Check SQL Server Agent Status
Turn it off and on again
What are the four core objects types NOT provided by SQL Server Agent?
Maintenance Plans
SCOM Reporting
Logs
Backup Tasks
Operators
What kind of database might be a good candidate for a full backup strategy?
Size is small and not changed frequently
A small database that changes frequently
The largest database you can find
Medium in terms of size and scope
What are the unique features of transaction log restores? Choose 2
Improved security
Point-in-time recovery
The ability to restore up to the point of failure if only data files are corrupt
Faster backups
True or false? To allow connection to the Azure Database, your local firewall rules must allow TCP Port 1344.
TRUE
FALSE
True or false? You can view security tokens using sys.login_token and sys.user_token system views.
FALSE
TRUE
True or False? The following code can be used to add SalesMan1 to the master database:
USE master
GO
CREATE USER SalesMan1 WITH PASSWORD = 'Pa55w.rd'
GO
TRUE
FALSE
True or false? You can query the sys.missing_sids table to identify orphaned users in a database.
TRUE
FALSE
Which option does not apply to partially contained databases?
You can use the ALTER statement to convert a noncontained database to a partially contained database.
CDC and CT are not supported in partially contained databases.
Numbered and temporary procedures are supported in partially contained databases.
Option 4: Replication is not operational in partially contained databases.
What happens when a login does not have access to its default database and is used to open a connection to a SQL Server instance?
The login is automatically granted access to its default database.
The login is automatically disabled.
The login cannot connect to SQL Server.
The login can connect to SQL Server, but an error message is reported.
Which fixed server role should be regarded as equivalent to sysadmin because of its ability to assign server principals to server roles?
securityadmin
setupadmin
bulkadmin
processadmin
serveradmin
Your organization wants to track data access by individual Windows users. Does this mean you cannot base logins on Windows groups?
YES
No. SQL Server can access the individual Windows identity by using the SUSER_NAME function, even when the login used to access SQL Server is based on a Windows group. The built-in auditing capability in SQL Server (which is discussed later in this course) takes advantage of this, and audits all actions at the individual user level.
True or False - The REFERENCES permission is used before a foreign key relationship can specify the object as a target, if no other permissions exist on the referenced object?
TRUE
FALSE
What permission enables a user to change the definition of a stored procedure?
CHANGE DEFINITION
ALTER
ALTER DEFINITION
CHANGE
True or false? If a user does not have a default schema, SQL Server assumes the guest schema.
TRUE
FALSE
Which permission is required to view the contents of the query plan cache for an on-premises SQL Server instance?
SHOWPLAN (database level)
SHOWPLAN (server level)
VIEW SERVER STATE (database level)
VIEW SERVER STATE (server level)
True or False? When sp_setapprole is called with the @encrypt = 'odbc' parameter, the application role password is encrypted with strong encryption.
TRUE
FALSE
Which of the following statements is incorrect (select one)?
By default, dbo is a member of the db_owner role.
The db_owner role can have more than one member.
db_owner is a fixed database role with full administrative permissions over a database.
: dbo is a database user that is an alias for the login that owns the database.
dbo and db_owner are terms that refer to the same thing.
Which of the following is not a target for SQL Server Audit?
File
Windows Security Log
Ring Buffer
Windows Application Log
Which of the following is not a component of the SQL Server Audit architecture?
Option 2: Target Group
Option 5: Server Audit Specification
Option 4: Database Audit Specification
Option 3: Server Audit
Option 1: Target
What are the advantages of using SQL Server Backup with Azure Blob storage?
Increased hardware requirements
Limitless storage capacity.
No need to purchase or manage hardware.
Offsite backup without the need for transportation of tapes.
Which type of Always Encrypted encryption will consistently encrypt the same plain text value to the same cypher text (assuming the same encryption key is used)?
Randomized encryption
Neither of the above
Deterministic encryption
True or false? The Data Import and Export Wizard can only be used to import and export data to Microsoft formats (such as Excel, Access, and SQL Server).
TRUE
FALSE
True or false? By default, bcp and BULK INSERT ignore check constraints, foreign key constraints, and triggers when importing data.
TRUE
FALSE
Which of the following is not an action you can carry out on a DACPAC?
UPGRADE
DEPLOY
EXPORT
REGISTER
EXTRACT
If the HR.JobCandidate table has included a column for a resumé in Microsoft Word document format, which of the following commands could you use to import the document into a column in a table?
The BULK provider in the OPENROWSET command with the SINGLE_BLOB option.
The BULK provider in the OPENROWSET command with the SINGLE_NCLOB option.
None of the above.
The BULK provider in the OPENROWSET command with the SINGLE_CLOB option.
What is the effect of disabling the clustered index on a row store table?
The table becomes read-only.
The table is completely inaccessible.
The table is deleted.
The index is ignored, but the table can be updated.
