Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
743 Cards in this Set
- Front
- Back
|
Which SQL Server support only eight processor cores, database mirroring, and the SQL Server Import and Export Wizard. |
SQL Server 2012 Standard edition
If your organization needs more than 16 processor cores, you must deploy SQL Server 2012 Enterprise edition |
|
|
SQL 2012 x64 Enterprise, BI, Web
|
SQL 2012 x64 Enterprise, BI, Web
------------------------------------------------ Server 2008 r2 sp1 Datacenter, Enterprise, Standard, and Web Server 2008 sp2 Datacenter, Enterprise, Standard, and Web |
|
|
SQL 2012 x86 Enterprise, BI, Web
|
SQL x86 Enterprise, BI, Web |
|
|
SQL 2012 x64 Standard
|
SQL 2012 x64 Standard
--------------------------------- Server 2008 r2 sp1 Datacenter, Enterprise, Standard, Foundation, and Web Serve 2008 Sp2 Datacenter, Enterprise, Standard, and Web Windows 7 sp1 x64 Ultimate, Enterprise, and Professional Windows Vista sp2 x64 Ultimate, Enterprise, and Business |
|
|
SQL 2012 x86 Standard
|
SQL 2012 x86 Standard
---------------------------------- Server 2008 r2 sp1 Datacenter, Enterprise, Standard, Foundation, and Web Server 2008 (x64 & x86) Datacenter, Enterprise, Standard, and Web Win 7 sp1 (x64 & x86) Ultimate, Enterprise, and Professional Win Vista sp2 (x64 & x86) Ultimate, Enterprise, and Business |
|
|
Server 2012 x64 Developer, Express, Express with Tools, and Express with Advanced Services
|
Server 2012 x64 Developer, Express, Express with Tools, and Express with Advanced Services
------------------------------------------------------------- Server 2008 r2 sp1 Datacenter, Enterprise, Standard, Foundation, and Web Server 2008 (x64) sp2 Datacenter, Enterprise, Standard, and Web Win 7 sp1 (x64) Ultimate, Enterprise, Professional, Home Premium, and Home Basic Win Vista sp2 (x64) Ultimate, Enterprise, Business, Home Premium, and Home Basic |
|
|
Server 2012 x86 Developer, Express, Express with Tools, and Express with Advanced Services
|
Server 2012 x86 Developer, Express, Express with Tools, and Express with Advanced Services
-------------------------------------------------------------- Server 2008 R2 SP1 Datacenter, Enterprise, Standard, Foundation, and Web Server 2008 (x64 and x86) SP2 Datacenter, Enterprise, Standard, and Web Win 7 SP1 (x64 and x86) Ultimate, Enterprise, Professional, Home Premium, and Home Basic Win Vista SP2 (x64 and x86) Ultimate, Enterprise, Business, Home Premium, and Home Basic |
|
|
64-bit versions of SQL Server 2012 Enterprise, SQL Server 2012 Business Intelligence, SQL Server 2012 Standard, SQL Server 2012 Developer, and SQL Server 2012 Web (64-bit) all have the following hardware requirements:
|
CPU: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, or Intel Pentium IV with EM64T support is required.
CPU Speed: 1.4 Ghz (2.0 Ghz or faster recommended) RAM: 1 GB min (4 GB rec.) |
|
|
64-bit version of SQL Server 2012 Express, Express with Tools, and Express with Advanced Services:
|
CPU: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, or Intel Pentium IV with EM64T support is required.
CPU Speed: 1.4 Ghz (2.0 Ghz or faster recommended) RAM: 512 MB min (1 GB rec.) |
|
|
32-bit version of SQL Server 2012 Enterprise, SQL Server 2012 Business Intelligence, SQL Server 2012 Standard, SQL Server 2012 Developer, and SQL Server 2012 Web editions
|
CPU: Pentium 3 compatible
CPU Speed: 1.0 Ghz (2.0 Ghz or faster recommended) RAM: 1 GB min (3 GB rec.) |
|
|
32-bit version of SQL Serve 2012 Express, Express with Tools, and Express with Advanced Services
|
CPU: Pentium 3 compatible
CPU Speed: 1.0 Ghz (2.0 Ghz or faster recommended) RAM: 512 GB min (1 GB rec.) |
|
|
it is possible to deploy SQL 2012, given the following restrictions
|
■ SQL Service accounts on a domain controller cannot run under a local service account.
■ You cannot promote a member server that has SQL Server 2012 installed to become a domain controller. ■ You cannot demote a domain controller that has SQL Server 2012 installed to become a member server. ■ SQL Server Setup is unable to create security groups or provision SQL Server service accounts on a read-only domain controller (RODC). Setup fails in this situation. |
|
|
Software requirments
|
.Net 3.5 sp1 - for database engine, reporting services, replication master data service, data quality services and management studio
.Net 4 - for SQL Server 2012 .PowerShell 2.0 - for SQL Server 2012 IE 7 or up - MMC, SQL Server Data Tools, Report Designer |
|
|
instance features
vs shared features |
instance features can operate side by side and are separate for each instance of SQL Server 2012
shared features need to be installed only once and can be used, where appropriate, by all instances on a single server |
|
|
Database engine services def
|
provides the core service for storing, processing, and securing data; provides controlled access, rapid transaction processing, and high availability
|
|
|
SQL server replication def
|
allows for copying and distributing data and database objects from one database to another; support synchronization of databases for consistency
|
|
|
Full-test and Semantic extractions for search def
|
supports Full-Text Extraction for fast text search; supports Semantic Extraction for key phrases and similarity search
|
|
|
Data quality services def
|
data quality services facilitates both computer-assisted and interactive methods of managing data source quality and integrity
|
|
|
Analysis services def
|
analysis services supports OLAP and data mining
|
|
|
Reporting services native def
|
Reporting services native are used to create, manage, and delivery reports through email and interactive web-based formats
|
|
|
Reporting services share point def
|
Reporting services share point integrates report viewing and management through SharePoint
|
|
|
Reporting services add-in for SharePoint products def
|
Reporting services add-in for SharePoint products provides management and user interfaces that allow integration between SharePoint and SSRS
|
|
|
data quality client def
|
data quality client interacts with data sources quality and integrity services.
|
|
|
SQL Server Data Tools def
|
SQL Server Data Tools is a dev. environment, formerly known as the BI dev. studio.
Is a VS 2010 shell that enables your to create Analysis Services, Integration Services, and Report Server projects |
|
|
Client tools connectivity def
|
Client tools connectivity includes additional components for communication between servers and clients
|
|
|
Integration services def
|
Integration services is used to move, integrate, and transform data between data stores.
|
|
|
Client Tools Connectivity def
|
Client tools connectivity includes additional components for communication between servers and clients
|
|
|
Client tools backward compatibility def
|
Client tools backward compatibility Includes SQL distributed management Objects, Decision Support Objects, and Data Transformation Services, all of which are discontinued ore deprecated features that might be needed for backward compatibility.
|
|
|
Management Tools - basic
vs Management Tools - completed |
management tools basic include - SQL Server Management Studio support for Database Engine, SQLCMD, SQL Server PowerShell, and Distributed Replay Administration Tool
-------------------------------------------------------------------------- Includes SQL Server Management Studio support for Reporting Services, Analysis services, Integration Services, SQL Server Profiler, Database Tuning Adviser, and SQL Server Utility Manager |
|
|
Distributed replay controller def
Distributed replay client def |
Distributed replay controller manages the actions of distributed replay client
Distributed replay client enables multiple distributed replay clients to function in concert to simulate workload against SQL Server instances |
|
|
SQL Client Connectivity SDK
|
SQL Client Connectivity SDK Includes the SQL Server Native Client (ODBC/OLE DB) software development kit (SDK) for developing database applications
|
|
|
Master Data Services def
|
Master Data Services provides the platform for integrating data from separate
systems across an organization. |
|
|
scaling up
vs scaling out |
scaling up - involves increasing the system resources on the current server
scaling out - enables you to increase capacity by using multiple SQL server |
|
|
What stored procedure will you use to determine the database size, free space available on a database, display the number of rows, disk space reserved and disk space used by table, index view, or Service broker queue.
|
sp_spaceused
|
|
|
Cmd to increase the size of the AW2 database
|
USE [master];
GO ALTER DATABASE [AW2] MODIFY FILE ( NAME = N'AW2_Data', SIZE = 255MB ) GO |
|
|
What cmd can you use to shrink a database AW2
|
USE [AW2]
GO DBCC SHRINKDATABASE(N'AW2') GO |
|
|
What cmd can you use to add file named ExtraFile to AW2
|
ALTER DATABASE [AW2] ADD FILE (
NAME = N'ExtraFile', FILENAME = N'C:\AW2\ExtraFile.ndf', SIZE = 4096KB, FILEGROWTH = 1024KB) TO FILEGROUP [PRIMARY] ) |
|
|
What cmd can you use to add filegroup
|
ALTER DATABASE [AW2] ADD FILEGROUP [ADDITIONAL])
|
|
|
Cmd to add a filegroup
|
ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [Additional]
|
|
|
Database cannot exceed
Single data file cannot exceed Can host maximum of Columns pre primary key Maximum foreign key per table Maximum of user connections |
Database cannot exceed 524,272 tTB
Single data file cannot exceed 16 TB Can host maximum of 32,767 databases on single SQL Server Columns pre primary key 16 Maximum foreign key per table 253 Maximum of user connections 32,767 user connections |
|
|
On computers running Windows Vista or Windows Server 2008 operating systems, the following default service accounts are used:
|
NETWORK SERVICE Database Engine, SQL Server Agent, Analysis Services, Integration Services, Reporting Services, SQL Server Distributed Replay Controller, SQL Server Distributed Replay Client
-------------------------------------------------------------------- LOCAL SERVICE - SQL Server Browser, FD Launcher (Full-Text Search) -------------------------------------------------------------------- LOCAL SYSTEM - SQL Server VSS Writer |
|
|
On computers running Windows 7 or Windows Server 2008 R2 operating systems, the following
default accounts are used: |
Virtual Account or Managed Service Account Database Engine, SQL Server Agent, Analysis Services, Integration Services, Replication Services, SQL Server Distributed Replay Controller, SQL Server Distributed Replay Client, FD Launcher (Full-Text Search)
-------------------------------------------------------------------- LOCAL SERVICE - SQL Server Browser -------------------------------------------------------------------- LOCAL SYSTEM - SQL Server VSS Writer |
|
|
MSA def & abbr
|
Managed Service Account
Special type of account managed by DC assigned to a PC and used to run services MSA pass is managed by the DC MSA can register SPN with AD. $ is at the end of the account name |
|
|
SPN abr
|
Service Principal Name
|
|
|
Virtual accounts aka Managed Local Accounts
|
they can access the network in a domain env.
used by default service accounts during installation on Win 7 & Win Server 2008 R2 |
|
|
When you need to create the MSA accounts before or after installation of SQL Server 2012?
|
You need to create the MSA prior to deploying SQL Server
|
|
|
Requirements for SQL Server on Server Core
|
.Net Framework 2.0 sp1
.Net Framework 3.5 sp1 .Net Framework 4 Server Core Profile Windows Installer 4.5 Windows PowerShell 2.0 |
|
|
Windows Server 2008 R2 Server Core with SP1 supports the following SQL Server 2012 features:
|
Database Engine Service
SQL Server Replication Full-Text Search Analysis Services Client Tools Connectivity Integration Service Server Do Stop Full Analysis Client Integrate |
|
|
What tools can you use to connect remotely to the Database Engine installed on Server Core
|
Management Tools - Basic
Management Tools - Complete Distribute Replay Client |
|
|
List the features not support on SQL Server 2012 on Windows Server 2008 R2 Server Core with SP1
|
SQL Server Data Tools
Reporting Services Client Tools Backward Compatibility Client Tools SDK Books Online Data Quality Service Distributed Replay Controller Master Data Service SQL Client Connectivity SDK Stop Reporting Clients Can Book Data Distributed on Master Server |
|
|
To install SQL Server 2012 on Windows Server 2008 R2 Server Core with sp1 what command will you use
|
Setup.exe /qs /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER
/SQLSVCACCOUNT="" /SQLSVCPASSWORD="" /SQLSYSADMINACCOUNTS="" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS |
|
|
You can use the following options to specify which features you want to install when using Setup.exe
SQLENGINE REPLICATION FULLTEXT AS IS CONN |
SQLENGINE - DB engine
REPLICATION - Replication feature FULLTEXT - Full-text feature AS - Analysis Services feature IS - Integration Service feature CONN - Connectivity features |
|
|
To install DB engine, Replication, Integration Services and Connectivity features you can use what cmd with what switches
|
Setup.exe /qs /Action=install /Features=SQLEngine,Replication,IS,Conn
/InstanceName=MSSQLServer /SQLSYSADMINACCOUNTS="Contoso\kim_akers" /IAcceptSQLServerLicenseTerms |
|
|
SQLIO tool
|
used to determine capacity
It can help you identify hardware or I/O config issues before you deploy SQLIO does this by reading and writing files of different sizes. We can manipulate the sizes. |
|
|
SQLIOSIM
|
part of SQL Server 2012, can be found in the BINN directory.
It is replacement for SQLIOStrees Performs readability and integrity test on disk subsystem. It can simulate read, write, checkpoint, backup, sort, and read-ahead. |
|
|
SQLIO vs SQLIOSIM
|
If you only want to perform benchmark tests and determine I/O capacity of the storage system use SQLIIO
|
|
|
All editions of SQL Server 2012 have a minimum RAM requirement of ....
|
All editions of SQL Server 2012 have a minimum RAM requirement of 1 GB except the Express editions, which have a minimum RAM requirement of 512 MB.
|
|
|
SQL Server 2012 Enterprise, Business Intelligence, and Web editions of SQL Server 2012 can be run only on
|
SQL Server 2012 Enterprise, Business Intelligence, and Web editions of SQL Server 2012 can be run only on Windows Server 2008 R2 SP1 and Windows Server 2008 SP2 operating systems using SQL Server 2012 Datacenter, Enterprise, Standard, and Web editions. You can run the x86 version of these SQL Server 2012 editions on the x86 version of Windows Server 2008 SP2, but not on the x64 versions.
|
|
|
What is the minimum recommended amount of RAM for SQL Server 2012 Enterprise?
A. 512 MB B. 1 GB C. 2 GB D. 4 GB E. 8 GB |
B
|
|
|
Which of the following editions of SQL Server 2012 can you run on a computer that is running the Windows 7 Professional (x64) operating system? (Choose all that apply.)
A. SQL Server 2012 (x64) Developer edition B. SQL Server 2012 (x64) Web edition C. SQL Server 2012 (x64) Enterprise edition D. SQL Server 2012 (x64) Standard edition |
AD
|
|
|
Which of the following features can you install if you are installing SQL Server 2012
Enterprise edition on a computer running Windows Server 2008 R2 SP1 Enterprise edition in the Server Core configuration? (Choose all that apply.) A. Database Engine Services B. SQL Server Replication C. Analysis Services D. Reporting Services |
ABC
D Incorrect: You cannot install Reporting Services on a computer running Windows Server 2008 R2 SP1 Enterprise edition in the Server Core configuration. |
|
|
On which of the following operating systems can you deploy the 64-bit version of SQL Server 2012 Enterprise edition? (Choose all that apply.)
A. Windows 7 Ultimate (x64) edition B. Windows Server 2008 R2 SP1 (x64) Standard edition C. Windows Server 2008 SP2 (x64) Enterprise edition D. Windows Server 2003 R2 (x64) Enterprise edition |
BC
A Incorrect: You cannot deploy the 64-bit version of SQL Server 2012 Enterprise edition on Windows 7 Ultimate (x64) edition. You can install this edition of SQL Server 2012 on Windows Server 2008 and Windows Server 2008 R2 only. |
|
|
You want to simulate read, write, checkpoint, backup, sort, and read-ahead activities for your organization’s SQL Server 2012 deployment. Which of the following tools would you use to accomplish this goal?
A. SQLIO B. SQLIOSim C. SQLIOStress D. chkdsk |
B
|
|
|
Cmd to install .Net framework on Win Server 2008 and 2008 r2
|
Add-WindowsFeature NET-Framework-Core
need to have the ServerManager module loaded |
|
|
Setup.exe switches
To install To Accept SQL licensing conditions Specify config file |
To install /ACTION either INSTALL or UNINSTALL
To Accept SQL licensing conditions /IACCEPTSQLSERVERLICENSETERMS Specify config file /CONFIGURATIONFILE |
|
|
Setup.exe /FEATURES switch possible values
|
SQL - installs SQL Server Database Engine, Replication, Full-Text, and Data Quality Server
IS - integration services features AS - analysis services feature RS - reporting services feature DQC - data quality client |
|
|
Setup.exe /ROLE switch possible values
|
AllFeatures_WithDefaults - will install all feature that ship with the current edition
SPI_AS_ExistingFarm - installs Analysis Services as a PowerPivot named instance on existing SharePoint Server farm SPI_AS_NewFarm - Installs AS and Database Engine as a PowerPivot named instance on a new SharePoint Server 2010 Farm |
|
|
Setup.exe switches
To specify instance name To specify product key To specify quite mode |
To specify instance name - /INSTANCENAME
To specify product key - /PID if no key is specified SQL Server 2012 is installed as an evaluation edition. To specify quite mode - /QS |
|
|
Setup.exe switch to set Agent Startup mode
|
/AGTSVSSTARTUPTYPE
Automatic Manual Disable |
|
|
Setup.exe switches
To provision logons to be member of the sysadmin role To set the SQL Server service account To set the SQL Server service account password |
To provision logons to be member of the sysadmin role - /SQLSYSADMINACCOUNTS
To set the SQL Server service account - /SQLSVCACCOUNT To set the SQL Server service account password - /SQLSVCPASSWORD |
|
|
Setup.exe switches
To set integration service service account To set integration service service account password To set analysis services service account To set analysis services service account password |
To set integration service service account - /ISSVAACCOUNT
To set integration service service account password - /ISSVCPASSWORD To set analysis services service account - /ASSVCACCOUNT To set analysis services service account password - /ASSVCPASSWORD |
|
|
Setup.exe switches
To set administrator credentials for analysis services To set reporting services service account To set reporting services service account password |
To set administrator credentials for analysis services - /ASSYSADMINACCOUNT
To set reporting services service account - /RSSVCACCOUNT To set reporting services service account password - /RSSVCPASSWORD |
|
|
to install the SQL Server Database Engine, Replication, and Full-Text Search features, use the following syntax
|
Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="" /SQLSVCPASSWORD="" /SQLSYSADMINACCOUNTS="" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS
|
|
|
Notes about SSIS installation
|
Only one installation per host
Can be installed on host without SQL Server 2012 Can install SSIS for 2012 on a machine running SQL Server 2005/2008 Can upgrade SSIS for SQL Server 2005/2008 to SSIS for 2012 |
|
|
Complete installation of SSIS includes the following shared features
|
SQL Server Data Tool - used to design packages
Management Tools - Complete - Installs SQL Server Management studio, used to manage packages Client Tools SDK - enables you to install managed assemblies for IS programming |
|
|
To run 32-bit SSIS packages what do you need
|
You need to have installed 32-bit run time and tools
|
|
|
to add the SSIS feature to the default MSSQLSERVER instance, run the following command
|
Setup.exe /qs /Action=Install /Features=IS /InstanceName=MSSQLServer /IAcceptSQLServerLicenseTerms
|
|
|
If you want to have a dedicated server for ETL processes you should also....
|
If you want to have a dedicated server for ETL processes you should also....
deploy a local instance of the Database Engine because the Integration Services stores packages in the msdb database and uses SQL Server Agent for scheduling packages If you do not install a local instance of Database engine, SSIS packages will run on the server from which they were started rather than on the dedicated ETL Server. |
|
|
add the Integration Services feature to the default MSSQLSERVER instance, run the following command
|
Setup.exe /qs /Action=Install /Features=IS /InstanceName=MSSQLServer /IAcceptSQLServerLicenseTerms
|
|
|
to remove the Integration Services feature from the default MSSQLSERVER instance on a computer running either the traditional or Server Core version of Windows Server 2008 R2, use the command:
|
Setup.exe /qs /Action=Uninstall /Features=IS /InstanceName=MSSQLServer
|
|
|
Default location for Database Engine
and Analysis Services |
Database Engine: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Analysis Services: C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin\msmdsrv.exe |
|
|
Default location for Integration Services
and Reporting Services |
Integration Services: C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.exe
Reporting Services: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe |
|
|
Analysis services port
SQL Server Browser Service Reporting Service Web Service |
Analysis services port - TCP 2383 for default instance
SQL Server Browser Service - TCP 2382 for AS named instances Reporting Service Web Service - TCP 80 |
|
|
Reporting Services configured for use through HTTPS
Integration Service: Microsoft remote procedure calls Integration services run time MS DTC SQL Server Management Studio browse connection to browser service |
Reporting Services configured for use through HTTPS - TCP 443
Integration Services: Microsoft remote procedure calls - TCP 135 Integration services run time - TCP 135 Microsoft Distributed Transaction Coordinator (MS DTC) - TCP 135 SQL Server Management Studio browse connection to browser service - UDP 1434 |
|
|
Default instance port
Dedicated admin connection SQL Server Browser service |
Default instance port - TCP 1433
Dedicated admin connection - TCP 1434 SQL Server Browser service - UDP 1434 |
|
|
SQP Server instance running over an HTTP endpoint
Service Broker |
SQP Server instance running over an HTTP endpoint
---------------------------------------------------------------------- TCP port 80 for CLEAR_PORT traffic. TCP port 443 for SSL_PORT traffic. Service Broker - 4022 |
|
|
Query to determine the port for Database mirroring as this ports are arbitrary choose by the admin
|
SELECT name, protocol_desc,
port, state_desc FROM sys. tcp_endpoints WHERE type_desc = 'DATABASE_ MIRRORING' |
|
|
Replication - port
Transact-SQL Debugger port |
Replication - TCP port 1433 for default instance.
Transact-SQL Debugger - TCP port 135. |
|
|
You are planning on deploying a server that will be dedicated for ETL (Extraction, Transformation, and Loading) processes. You want to ensure that SSIS (SQL Server Integration Services) packages will run on this dedicated ETL server and not on any other server on which they were started. Which of the following features must you install on the ETL server in addition to SSIS to accomplish this goal?
A. Database Engine B. SQL Server Reporting Services C. SQL Server Analysis Services D. Client Tools SDK |
A
Integration Services stores packages in an instance of the Database Engine and uses SQL Server Agent for scheduling those packages. If you do not have a local instance of the Database Engine, SSIS packages will run on the server on which they were started |
|
|
You have installed the SQL Server Integration Services (SSIS) feature on a server running Windows Server 2008 R2 SP1. Which of the following features must you install if you want to ensure that you can run packages in 32-bit mode?
A. Client Tools SDK B. Data Quality Client C. SQL Server Data Tools D. Client Tools Backwards Compatibility |
C
Installing the SQL Server Data Tools shared feature allows you to run SSIS packages in 32-bit mode on a computer running a 64-bit operating system |
|
|
You want to allow access to SQL Server Analysis Services on a server running Windows Server 2008 R2 for a client running SQL Server Management Studio on Windows 7. You have installed SQL Server Analysis Services in the default location. Which of the following
paths should you use when creating the firewall rule by using Windows Firewall With Advanced Security? A. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn \sqlservr.exe B. C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin \msmdsrv.exe C. C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.exe D. C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services \ReportServer\bin\ReportingServicesService.exe |
B
|
|
|
You want to remove SQL Server Integration Services from a server running the Windows Server 2008 R2 operating system that also has the Database Engine and SQL Server Analysis Services installed. Which of the following tools can you use to accomplish this goal?
A. SQL Server Management Studio B. SQL Server Configuration Manager C. Add/Remove Programs in Control Panel D. SQL Server Installation Center |
C
|
|
|
You want to reproduce the same SQL Server 2012 installation configuration across five
servers. Which of the following files will you generate by using SQL Server Setup to accomplish this goal? A. Configuration.xml B. Setup.ini C. Setup.xml D. ConfigurationFile.ini |
D
|
|
|
Will the minimum reserve apply if the minimum reserved memory is not used?
Min amounts for 36/64 |
No
Min amount when running 32 bit is 64 MB Min amount when running 64 bit is 128 MB |
|
|
What sp will you use to configure the max and min amount of memory?
Cmd to config min of 1024 MB of memory and max of 8-96 MB of memory |
sp_configure
EXEC sys.sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sys.sp_configure 'min server memory', 1024; GO EXEC sys.sp_configure 'max server memory', 4096; GO RECONFIGURE; GO |
|
|
Processor affinity def
I/O affinity def |
Processor affinity assigns specific server processors to specific threads
I/O affinity binds an instance's disk I/O to a specific set of CPUs |
|
|
To distribute SQL worker threads across CPUs 2 and 3 based on server workload, use the following Transact-SQL statement
|
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3
|
|
|
To distribute SQL worker threads across all CPUs based on server workload, use the following command:
|
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO
|
|
|
Can you configure I/O Affinity by using the ALTER SERVER CONFIGURATION statement
|
No, you must use sp_configure with the affinity I?O mask option
|
|
|
Fill factor def
|
Fill factor determines the percentage of space on each leaf-level page that is filled with data when an index is create or rebuilt
|
|
|
You can set the instance-wide fill factor value by using what stored procedure
to set the instance-wide fill factor configuration option to 90, use the following code: |
You can set the instance-wide fill factor value by using the sp_configure stored procedure.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'fill factor', 90; GO RECONFIGURE; GO |
|
|
T-SQL cmd to modify the recovery model of the model database to FULL
|
USE [master]
GO ALTER DATABASE [model] SET RECOVERY FULL WITH NO_WAIT GO |
|
|
use the following Transact-SQL statement to enable Auto Close on
the model database: |
USE [master]
GO ALTER DATABASE [model] SET AUTO_CLOSE ON WITH NO_WAIT GO |
|
|
use the following Transact-SQL statement to enable Auto Shrink on the model
database: |
USE [master]
GO ALTER DATABASE [model] SET AUTO_SHRINK ON WITH NO_WAIT GO |
|
|
Database mail stored procedures are _____________ by default
|
Database mail stored procedures are disabled by default
You need to be memember of the DatabaseMailUserRole in msdb database |
|
|
To enable Database Mail by using sp_configure, use the following code:
|
sp_configure 'show advanced options', 1;
GO RECONFIGURE; GO sp_configure 'Database Mail XPs', 1; GO RECONFIGURE; GO |
|
|
You can create Database Mail accounts by using the __________ stored procedure
|
You can create Database Mail accounts by using the sysmail_add_account_sp stored procedure.
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Example', @email_address = 'example@contoso.com', @mailserver_name = 'smtp.contoso.com'; |
|
|
What stored procedure will you use to configure account retry attempts, account retry delay, attachment encoding, maximum attachment size, prohibited attachment extensions, and logging level
|
sysmail_configure_sp stored procedure
EXECUTE msdb.dbo.sysmail_configure_sp 'AccountRetryAttempts', '15'; |
|
|
Database mail profiles determine which users are a able to send messages by using Database mail. What are the two types of profiles
|
Public profiles can be accessed by all users of any mail-host database.
Private profiles can be accessed by a specific user of a mail-host database only. To configure a private profile, this user must be an msdb database user. |
|
|
Processor affinity ties an instance to specific processors rather than ______________
|
Processor affinity ties an instance to specific processors rather than to all processors on the host server.
|
|
|
What is required to run a transaction across multiple server.
|
MSDTC is required when transactions run across multiple servers. You should configure it as a cluster resource prior to installing the Database Engine on a cluster
|
|
|
1. You have recently removed a SQL 2012 Database Engine instance from a computer running the Windows Server 2008 R2 operating system. Prior to the removal of the instance, you had configured affinity so that the default instance used CPU 0 and 1 and the second instance used CPU 2 and 3. You want to ensure that the default instance can use all processors available to the host. Which of the following commands would you use to accomplish this goal?
A. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO B. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3 C. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1 D. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,4 |
а
|
|
|
You have run the following command on a SQL Server 2012 default instance:
EXEC sys.sp_configure 'show advanced options', 1; GO Which of the following commands must you run to configure the instance so that the maximum amount of memory the instance uses does not exceed 4,096 GB? (Each answer presents part of a complete solution. Choose two.) A. RECONFIGURE; GO B. EXEC sys.sp_configure 'min server memory', 1024; GO C. EXEC sys.sp_configure 'min server memory', 4096; GO D. EXEC sys.sp_configure 'max server memory', 4096; GO |
А
D |
|
|
You want to ensure that disk input/output operations of an instance are bound to a specific processor. Which of the following would you configure to accomplish this goal?
A. Minimum server memory B. Maximum server memory C. Processor affinity D. I/O affinity |
D
|
|
|
Max length for instance names
|
16 chars
not case sensitive can be cyrilic |
|
|
to install an additional instance of the Database Engine named Alternate and set the Contoso\Kim_Akers user account to hold the role of SQL Server Administrator, use the following command:
|
Setup.exe /qs /Action=Install /Features=SQLEngine /InstanceName=Alternate /
SQLSYSADMINACCOUNTS="Contoso\Kim_Akers" /IAcceptSQLServerLicenseTerms |
|
|
How many instances of SQL Server 2012 can you install on a failover cluster?
|
You can install a maximum of 25 instances of SQL Server 2012 on a failover cluster.
|
|
|
to update instance ALTERNATE, use the following cmd
|
.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch
/InstanceName=ALTERNATE |
|
|
To update all instances of SQL Server 2012 on a host computer and all shared components, use the following command:
|
.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch
|
|
|
You can also use the command line to remove updates from a specific instance and all
shared components. For example, to remove an update from instance ALTERNATE, use: |
.exe /sq /IAcceptSQLServerLicenseTerms /Action=RemovePatch
/InstanceName=ALTERNATE |
|
|
You can use resource governor with what SQL component
|
SQL Server Database Engine
You cannot use Resource Governor for Analysis Services, Integration Services Reporting Services |
|
|
Can you use Resource Governor to perform workload monitoring or workload management between SQL Server Database Engine instances
|
No
|
|
|
Does OLTP use Resource Governor
|
Online transaction processing (OLTP) queries are often short and do not use the CPU long enough for Resource Governor constraints to apply
|
|
|
To enable resource governor use following T-SQL cmd
or GUI |
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO ---------------------- SQL Server Management Studio > Management Nodes > Resource Governor > enable Resource governor |
|
|
Cmd to disable resource governor
|
ALTER RESOURCE GOVERNOR DISABLE;
GO |
|
|
Resource pool def
|
Resource pool is a subset of physical resources available to a Database Engine instance
Resource pool host workload groups |
|
|
How many built in resource pools SQL Server has and what are they used for
|
2: internal and default
internal pool - represent the resources the SQL Server instance uses. Cannot be altered. Preference over other pools. -------------------- default pool - first predefined user pool. Can be modified. |
|
|
The sum of min values across all resource pool cannot exceed
Max value limitations |
The sum of min values across all resource pool cannot exceed 100%
Max value needs to be more than min and up to 100% If a pool has min value the max of other pools can be 100% - min value of those pools |
|
|
to create a resource pool named poolAlpha and assign it a minimum CPU allocation of 20%, use the following statement
|
CREATE RESOURCE POOL poolAlpha
WITH (MIN_CPU_PERCENT = 20); GO ALTER RESOURCE GOVERNOR RECONFIGURE; |
|
|
Workload groups def
|
workload groups function as containers for session requests that share similar classification criteria
workload groups are assigned to pools |
|
|
How many built in workload groups are there?
|
2, one for each built in pool
internal and default workload group |
|
|
Requests are classified into the default group under the following circumstances:
|
There are no criteria to classify a request.
A request is classified into a nonexistent workload group. There is a classification failure. |
|
|
use the following to create a groupBeta workload group name associated with the poolAlpha pool
|
CREATE WORKLOAD GROUP groupBeta
USING poolAlpha; GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO |
|
|
resource governor classification def
|
resource governor classification allocated incoming sessions to a workload group based on session properties.
This is done via classifier functions |
|
|
After you have created the classifier function, register it and then update the in-memory configuration. You can do this for the preceding example by using the following Transact-SQL code:
|
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.DayNightClassifier)
ALTER RESOURCE GOVERNOR RECONFIGURE GO |
|
|
WSRM def & abbr
|
WSRM - Windows System Resource Manager
Enables you to allocate resources to specific processes such as different Database Engine instances. This enables you to allocate processor resource to different instances, something you cannot do with Resource Governor as he is based on per-instance bases. |
|
|
Equal Per Process method in WSRM
|
Equal Per Process - CPU bandwidth is divided evenly across matched processes. This management rule does not allow suballocation
|
|
|
Equal Per User method in WSRM
|
Equal Per User - CPU bandwidth is divided equally across matched processes run by an individual user.
|
|
|
Equal Per Session method in WSRM
|
This CPU bandwidth allocation scheme is used with Remote Desktop Services and allocates CPU bandwidth. This method of resource allocation is not appropriate for distributing CPU bandwidth across Database Engine instances
|
|
|
you can force the error log to cycle without restarting the Database Engine instance by running the following Transact-SQL code
|
EXEC sp_cycle_errorlog;
GO |
|
|
stored procedure exists that enables you to cycle the SQL Server Agent error log file. You must run the sp_cycle_agent_errorlog stored procedure from the msdb database. You can accomplish this by using the following Transact-SQL code:
|
USE msdb;
GO EXEC dbo.sp_cycle_agent_errorlog; GO |
|
|
You want to cycle the SQL Server Agent error log. From which of the following system databases must you run the sp_cycle_agent_errorlog stored procedure to accomplish this goal?
A. master B. msdb C. model D. tempdb |
b
|
|
|
Which feature should you enable and configure so session requests addressed to a specific instance can be allocated different processor resources based on session request properties?
A. Resource Governor B. Windows System Resource Manager C. Processor affinity D. I/O affinity |
A
|
|
|
A server that has four processors has three SQL Server 2012 Database Engine instances installed. Which feature should you configure to assign 60% of a host server’s processor resource to the first instance, 20% to the second instance, and 15% to the third
instance? A. Resource Governor B. Windows System Resource Manager C. Processor affinity D. I/O affinity |
b
|
|
|
Two modes that you can install Analysis Service in
|
Multidimensional and data mining mode
or Tabular mode |
|
|
Multidimensional and data mining mode
|
The default Analysis Services mode.
Supports online analytical processing (OLAP) databases and data mining models |
|
|
Tabular mode
|
Tabular mode Supports new tabular modeling features. When installed using this mode, Analysis Services can host solutions built in the tabular model designer. Analysis Services in tabular mode is necessary when you want tabular model data access over a network.
|
|
|
You can install Analysis Services from the command line by using the /FEATURES=AS
option. The /ASSERVERMODE can be set to |
MULTIDIMENSIONAL, TABULAR, or POWERPIVOT
|
|
|
cmd to create
instance ASMulti with AS installed in multidimensional and data mining mode with AS service account contoso\account with AS admin account contoso\kim_akers use the following cmd |
Setup.exe /q /IAcceptSQLServerLicenseTerms /Action=install /Features=AS
/ASSERVERMODE=MULTIDIMENSIONAL /INSTANCENAME=ASMulti /ASSVCACCOUNT=NetworkService /ASSYSADMINACCOUNTS=contoso\kim_akers |
|
|
cmd to create
instance ASTabular with AS installed in tabular mode with AS service account contoso\account with AS admin account contoso\kim_akers use the following cmd |
Setup.exe /q /IAcceptSQLServerLicenseTerms /Action=install /Features=AS
/ASSERVERMODE=TABULAR /INSTANCENAME=ASTabular /ASSVCACCOUNT=NetworkService /ASSYSADMINACCOUNTS=contoso\kim_akers |
|
|
To install Reporting Services in the default configuration for native mode from the command
line use NetworkService account for both service and admin account |
setup /q /IAcceptSQLServerLicenseTerms /ACTION=install /FEATURES=SQL,RS,TOOLS
/INSTANCENAME=RPTSVR /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /RSSVCACCOUNT=NetworkService /SQLSVCACCOUNT=NetworkService /AGTSVCACCOUNT=NetworkService /RSSVCSTARTUPTYPE="Manual" /RSINSTALLMODE="DefaultNativeMode |
|
|
Which mode should you select during the installation of Analysis Services if you want to support OLAP databases?
|
You should install Analysis Services in multidimensional and data mining mode if you want to use OLAP databases.
|
|
|
Integration Services enables you to ...?
|
Integration Services enables you to run and schedule Integration Services packages in SQL Server Management Studio.
|
|
|
Cmd to install integration services
|
Setup.exe /q /IAcceptSQLServerLicenseTerms /Action=Install /Features=IS
|
|
|
Full-text indexes def
|
Full-text indexes store information about significant words and their location within the columns of a database table.
|
|
|
FULLTEXT index on the Production.ProductReview table in the
AdventureWorks2012 database by using the ReviewerName, EmailAddress, and Comments columns in the existing unique key index PK_ProductReview_ProductReviewID while also creating a FULLTEXT catalog called production_catalog, use the following statement |
USE AdventureWorks2012;
GO CREATE FULLTEXT CATALOG production_catalog; GO CREATE FULLTEXT INDEX ON Production.ProductReview ( ReviewerName, EmailAddress, Comments ) KEY INDEX PK_ProductReview_ProductReviewID ON production_catalog; GO |
|
|
To delete a full-text index, right-click the table that hosts the full-text index, choose Full-Text Index, and then select Delete Full-Text Index. You can also delete a full-text index by using the DROP FULLTEXT INDEX statement
|
DROP FULLTEXT INDEX ON Production.ProductReview
|
|
|
FILESTREAM enables ... ?
|
FILESTREAM enables SQL Server–based applications to store unstructured data, such as images and documents, on the host computer’s file system. To use FILESTREAM, you must create or modify a database to host a special type of filegroup, after which you can create or modify tables so that they can use the varbinary(max) column with the FILESTREAM attribute.
|
|
|
When to use FILESTREAM
|
1)Objects that are to be stored are larger than 1 MB
2)Fast read access is importnat |
|
|
cmd to enable filestream
|
EXEC sp_configure filestream_access_level, 2
RECONFIGURE Setting filestream_access_level to 0 disables FILESTREAM access. Setting level 1 allows Transact-SQL only. Setting level 2 allows Transact-SQL and Win32 streaming after installing filestream restart |
|
|
to create a FILESTREAM filegroup named FileStreamFileGroup for the Litware2012 database, use the following
query: |
USE master
GO ALTER DATABASE Litware2012 ADD FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM; GO |
|
|
create and associate the C:\FSTRM directory with the FILESTREAM file named FileStrmFile in the FileStreamFileGroup FILESTREAM filegroup for the Litware2012 database, use the following query:
|
USE master
GO ALTER DATABASE Litware2012 ADD FILE ( NAME = FileStrmFile, FILENAME = 'C:\FSTRM' TO FILEGROUP FileStreamFileGroup |
|
|
FileTables def
|
FileTables are a special type of table that enables you to store files and documents within SQL Server 2012. These files and documents can be accessed from Windows applications as though they were stored normally in the file system.
|
|
|
A FileTable provides the following functionality:
|
A FileTable provides a hierarchy of files and directories.
Each row in a FileTable represents a file or directory. Each row holds the following items: A FILESTREAM column for stream data and a file_id (GUID) identifier. Path_locator and parent_path_locator columns. These represent the file and directory hierarchy. Ten file attributes. These include creation data and modification date. Type column that supports full-text and semantic search. You can update FileTables by using normal Transact-SQL queries. |
|
|
Enable FILESTREAM at the instance level. You can do this with the following query
|
EXEC sp_configure filestream_access_level, 2
RECONFIGURE |
|
|
You can create FileTables subject to the following conditions
|
You cannot convert an existing table into a FileTable.
You have specified a parent directory at the database level. A valid FILESTREAM filegroup exists. If you don’t specify a filegroup, the default FILESTREAM filegroup will be used. You cannot create a table constraint when creating the table, but you can add one after the table is created. ■■ You cannot create a FileTable in the tempdb database |
|
|
If you perform a file-only Reporting Services deployment, you must run ....
|
the Reporting Services Configuration Manager.
|
|
|
FILESTREAM enables you to store
|
FILESTREAM enables you to store BLOB objects in the file system
|
|
|
Which tool do you use to give a user access to Integration Services?
A. SQL Server Management Studio B. SQL Server Configuration Manager C. SQL Server Data Tools D. DCOM Configuration Tool (Dcomcnfg.exe) |
d Correct. Dcomcnfg.exe is the tool you can use to give non-administrative users access to Integration Services.
|
|
|
Which tool do you use to change the Reporting Services execution account?
A. SQL Server Management Studio B. Reporting Services Configuration Manager C. SQL Server Configuration Manager D. SQL Server Installation Center |
B Correct. You use Reporting Services Configuration Manager to change the
Reporting Services execution account. |
|
|
What is the maximum number of full-text indexes that you can configure for a partitioned table?
A. 1 B. 32 C. 1,024 D. 2,048 |
Correct. The maximum number of full-text indexes for a table is 1
|
|
|
Which of the following steps must you take to enable FILESTREAM on a SQL Server 2012 instance that has both the Database Engine and Analysis Services features installed? (Each correct answer presents part of the solution. Choose two.)
A. Edit the properties of the SQL Server service in SQL Server Configuration Manager. B. Edit the properties of the Analysis Services service in SQL Server Configuration Manager. C. Run sp_configure filestream_access_level, 2. D. Run sp_configure filestream_access_level, 0. |
bc
|
|
|
Where are secondary files that do not have an allocated group stored?
|
primary file group
|
|
|
to add a new filegroup named Tertiary to the AdventureWorks2012 database, use the following query
|
USE [master]
GO ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [Tertiary] GO |
|
|
Contained databases def
|
Contained databases include all the settings and metadata required to define the database. Contained databases have no configuration dependencies on the Database Engine instance
on which the database is deployed, so users connect to a contained database without authenticating at the Database Engine level. |
|
|
Partially Contained Databases def
|
Partially Contained Databases (Partial-CDBs), which provide a high degree of isolation from the Database Engine instance but are not fully contained. Partial-CDBs are a transitional step toward contained databases.
|
|
|
Limitation of PCD
|
Numbered procedures
Schema-bound objects that depend on built-in functions with collation changes Binding change resulting from collation changes, including references to objects, columns, symbols, or types replication, change data capture, and change tracking |
|
|
What view can you use to find information about uncontained objects and geatures
|
sys.dm_db_uncontained_entities and sys.sql_modules
|
|
|
To enable contained databases on an instance of SQL Server 2012 by using Transact-SQL, issue the following query:
|
sp_configure 'contained database authentication', 1;
GO RECONFIGURE ; GO |
|
|
to set the containment of the AdventureWorks2012 database to Partial, use the following query:
|
USE [master]
GO ALTER DATABASE [AdventureWorks2012] SET CONTAINMENT = PARTIAL GO |
|
|
You use the following syntax to compress a table by using row-level compression
and for page level |
ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)
ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE) |
|
|
You use the following syntax to configure an index with row-level compression:
|
ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)
|
|
|
Unicode Compression def
|
Unicode compression enables the database engine to compress Unicode values stored in page or row compressed objects
Use with nchar(n) and nvarchar() Auto applied when seen fit |
|
|
You use the following general syntax to apply page-level compression
|
ALTER TABLE name REBUILD WITH (DATA_COMPRESSION=PAGE)
|
|
|
The best way to determine the benefits of compression on an object is to use the ... ?
to configure an estimate of the compression benefits of using Row compression on the HumanResources.Employee table in the AdventureWorks2012 database, execute the following Transact-SQL statement |
The best way to determine the benefits of compression on an object is to use the sp_estimate_data_compression_savings stored procedure
--------------- USE AdventureWorks2012; GO EXEC sp_estimate_data_compression_savings 'HumanResources', 'Employee', NULL, NULL, 'ROW'; --or 'PAGE' for page level GO |
|
|
To use TDE to encrypt a database, you must perform the following steps:
|
1. Create the master encryption key.
2. Create the certificate protected by the master key. 3. Create a DEK and protect it by using the certificate. 4. Encrypt the database. |
|
|
The first step in deploying TDE involves creating a master encryption key. You can
accomplish that by using the following query |
USE master;
GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''; GO |
|
|
to create a certificate named ServerCertificate
that uses the subject name Server Certificate, use the following query |
CREATE CERTIFICATE ServerCertificate WITH SUBJECT = 'Server Certificate';
GO |
|
|
the following query creates a DEK for the AdventureWorks2012 database
|
USE AdventureWorks2012;
GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE ServerCertificate; GO |
|
|
After all the appropriate keys and certificates are in place, you can encrypt the database
by using the ALTER DATABASE statement. For example, to encrypt the AdventureWorks2012 database, use the following query: |
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON; GO |
|
|
the following code, when run from the master system database, creates a backup of the ServerCertificate certificate to a file called ServerCertExport and a
PrivateKeyFile private key |
BACKUP CERTIFICATE ServerCertificate
TO FILE = 'ServerCertExport' WITH PRIVATE KEY ( FILE = 'PrivateKeyFile', ENCRYPTION BY PASSWORD = '' ); GO |
|
|
Partition function def
|
Partition function Defines how the rows of an index or table map to specific partitions based on the values of partitioning columns
|
|
|
Partition scheme def
|
Partition scheme - Maps the partitions of a partition function to a collection of
filegroups |
|
|
Partitioning column def
|
Partitioning column - The column of an index or table that a partition function uses to partition the index or table
|
|
|
Aligned index def
|
Aligned index - An index that uses the same partition scheme as the table to which it belongs
|
|
|
Nonaligned index def
|
Nonaligned index - An index that is partitioned independently from the table to which it belongs
|
|
|
Partition elimination def
|
Partition elimination - The process through which the query optimizer will access only the appropriate partitions to satisfy a query’s filter criteria
|
|
|
to create a function named PartFunction that will divide a table into two partitions by using the number 50, use the following statement
|
CREATE PARTITION FUNCTION PartFunction (int)
as RANGE LEFT FOR VALUES (50); |
|
|
to create a partition scheme named PartScheme that applies the partition function PartFunction to the filegroups FgOne and FgTwo, use the following query
|
CREATE PARTITION SCHEME PartScheme
AS PARTITION PartFunction TO (FgOne, FgTwo); GO |
|
|
to create a table called Exemplar that uses the PartScheme partition scheme to partition col1,
use the following query: |
CREATE TABLE Exemplar (col1 int, col2 char(20))
ON PartScheme (col1); GO |
|
|
What command to monitor the amount of log space used
|
DBCC SQLPERF (LOGSPACE)
|
|
|
What cmd?
Reclaims space from dropped variable-length columns in indexed views or tables. The user must own the table or indexed view or be a member of the sysadmin fixed server role or the db_owner or db_ddladmin fixed database roles |
DBCC CLEANTABLE
|
|
|
What cmd?
Rebuilds one or more indexes for a table |
DBCC DBREINDEX
|
|
|
What cmd?
Removes all clean buffers from the buffer pool. The user must be a member of the sysadmin fixed server role. |
DBCC DROPCLEANBUFFERS
|
|
|
What cmd?
Removes all elements from the plan cache, specific plan from the plan cache, or all cache entries related to a specific resource pool. The user must have ALTER SERVER STATE permission in the Database Engine. |
DBCC FREEPROCCACHE
|
|
|
What cmd?
Defragments indexes. This feature will be removed in future versions of SQL Server, |
DBCC INDEXDEFRAG
|
|
|
What cmd?
Shrinks the size of all data and log files of the specified database. The user must be a member of the sysadmin fixed server role or the db_owner fixed database role. |
DBCC SHRINKDATABASE
|
|
|
What cmd?
Shrinks a specified data or log file. The user must be a member of the sysadmin fixed server role or the db_owner fixed database role |
DBCC SHRINKFILE
|
|
|
What cmd?
Updates page and row count data for catalog views to remove inaccuracies. The user must be a member of the sysadmin fixed server role or the db_owner fixed database role. This statement is provided for backward compatibility. |
DBCC UPDATEUSAGE
|
|
|
Shows the last statement forward from a client to the Database Engine.
|
DBCC INPUTBUFFER
|
|
|
What cmd?
Shows information about the oldest running transaction, oldest running distributed transaction, and oldest running non-distributed transaction. The user must be a member of the sysadmin fixed server role or the db_owner fixed database role. |
DBCC OPENTRAN
|
|
|
What cmd?
Displays the current output buffer in hexadecimal and ASCII format for a specific session_id. The user must be a member of the sysadmin fixed server role. |
DBCC OUTPUTBUFFER
|
|
|
What cmd?
Provides information about the procedure cache. The user must be a member of the sysadmin fixed server role or the db_owner fixed database role |
DBCC PROCACHE
|
|
|
What cmd?
Provides current query optimization statistics for a table or indexed view. The user must be a member of the sysadmin fixed server role or the db_owner or db_ddladmin fixed database roles |
DBCC SHOW_STATISTICS
|
|
|
What cmd?
Provides fragmentation information for tables, views, or indexes. The user must be a member of the sysadmin fixed server role or the db_owner or db_ddladmin fixed database roles. This statement is deprecated, and you should migrate to using appropriate dynamic management views for this information |
DBCC SHOWCONFIG
|
|
|
What cmd?
Displays transaction log space usage statistics for all databases hosted by an instance. Access requires the VIEW SERVER STATE permission on the server. |
DBCC SQLPERF
|
|
|
What cmd?
Provides information about trace flags. The user must be a member of the public role. |
DBCC TRACESTATUS
|
|
|
What cmd?
Provides information about currently set options on the connection. The user must be a member of the public role. |
DBCC USEROPTIONS
|
|
|
What cmd?
Performs a consistency check of disk space allocation structures. must be a member of the sysadmin or db_owner |
DBCC CHECKALLOC
|
|
|
What cmd?
Checks catalog consistency of online databases. must be a member of the sysadmin or db_owner |
DBCC CHECKCATALOG
|
|
|
What cmd?
Verifies the integrity of a specific constraint or all constraints on a table within the current database. must be a member of the sysadmin or db_owner |
DBCC CHECKCONSTRAINTS
|
|
|
What cmd?
Checks the physical and logical integrity of all objects in a specific database; runs DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG must be a member of the sysadmin or db_owner |
DBCC CHECKDB
|
|
|
What cmd?
Verifies the allocation and structural integrity of indexed views and tables in a specific filegroup must be a member of the sysadmin or db_owner |
■ DBCC CHECKFILEGROUP
|
|
|
What cmd?
Verifies and, if necessary, changes the identity value for a specific table. must be a member of the sysadmin - db_owner - db_ddladmin |
DBCC CHECKIDENT
|
|
|
What cmd?
Verifies the integrity of all pages and structures that make up a table or indexed view. must be a member of the sysadmin - db_owner - db_ddladmin |
DBCC CHECKIDENT
|
|
|
What cmd?
Unloads a specific extended stored procedure DLL from memory must be a member of the sysadmin or db_owner |
DBCC dllname (FREE)
|
|
|
What cmd?
Flushes the distributed query connection cache must be a member of the sysadmin |
DBCC FREESESSIONCACHE
|
|
|
What cmd?
lushes all unused cache entries from all caches. Access requires the ALTER SERVER STATE permission |
DBCC FREESYSTEMCACHE
|
|
|
What cmd?
Provides information on a specific DBCC command must be a member of the sysadmin |
DBCC HELP
|
|
|
What cmd?
Disables / Enable specific trace flags. must be a member of the sysadmin or db_owner |
DBCC TRACEOFF / DBCC TRACEON
|
|
|
Which statement would you use to add a filegroup to an existing database?
A. ALTER DATABASE B. CREATE DATABASE C. ALTER TABLE D. CREATE TABLE |
a
|
|
|
The STUDENTS table contains name, address, and contact information for students at a local college. Columns include Student_Name, DOB, Telephone, Email, Street_Address, Town, State, and Zip Code. IDX1 is an index on the Student_Name column. Given this information, which of the following statements will provide the greatest reduction in the amount of space required to store data for the STUDENTS table?
A. ALTER TABLE STUDENTS REBUILD WITH (DATA_COMPRESSION=ROW) B. ALTER TABLE STUDENTS REBUILD WITH (DATA_COMPRESSION=PAGE) C. ALTER INDEX IDX1 ON STUDENTS REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW) D. ALTER INDEX IDX1 ON STUDENTS REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE) |
b
|
|
|
Which of the following must you do before enabling Transparent Data Encryption for a database? (Each correct answer presents part of the complete solution. Choose three.)
A. Create a master encryption key. B. Create a certificate. C. Create a database encryption key. D. Enable page-level compression. |
abc
|
|
|
Which command would you run if you wanted to check the physical and logical integrity of all objects within a specific database?
A. DBCC CHECKFILEGROUP B. DBCC CHECKDB C. DBCC SQLPERF D. DBCC SHRINKDATABASE |
b
|
|
|
Previous SQL Server support Itanium architecture. DO SQL Server 2012 support it?
|
SQL Server 2012 does not support the Itanium
|
|
|
You cannot perform a cross-architecture upgrade,
|
You cannot perform a cross-architecture upgrade, so you cannot upgrade an x86 instance of SQL Server to an x64 instance, and you cannot upgrade an x64 instance to an x86 instance.
|
|
|
The only operating systems that support both SQL Server 2005 and SQL Server 2012 are Windows |
Server 2008 and Windows Vista
|
|
|
SQL Server 2005 SP4 Ent can be upgraded to ...
SQL Server 2005 SP4 Dev can be upgraded to ... SQL Server 2005 SP4 Std can be upgraded to ... |
SQL Server 2005 SP4 Ent > SQL Server 2012 Ent
SQL Server 2005 SP4 Dev > SQL Server 2012 Dev SQL Server 2005 SP4 Std > SQL Server 2012 Ent, BI, Std |
|
|
SQL Server 2005 SP4 Workgroup can be upgrade to ...
SQL Server 2005 SP4 Express can be upgrade to ... |
SQL Server 2005 SP4 Workgroup > SQL Server 2012 Ent, BI, Std, Web
SQL Server 2005 SP4 Express > SQL Serve 2012 Ent, BI, Std, Web, Express |
|
|
SQL Server 2008 SP2 Ent can be upgraded to ...
SQL Server 2008 SP2 Dev can be upgraded to ... SQL Server 2008 SP2 Std can be upgraded to ... |
SQL Server 2008 SP2 Ent > SQL Server 2012 Ent, BI
SQL Server 2008 SP2 Dev > SQL Server 2012 Dev SQL Server 2008 SP2 Std > SQL Server 2012 Ent, BI, Std |
|
|
SQL Server 2008 SP2 Web can be upgraded to ...
SQL Server 2008 SP2 Workgroup can be upgraded to SQL Server 2008 SP2 Express can be upgraded to ...... |
SQL Server 2008 SP2 Web > SQL Server 2012 Ent, BI, Std, Web
SQL Server 2008 SP2 Workgroup > SQL Server 2012 Ent, BI, Std, Web SQL Server 2008 SP2 Express > SQL Server 2012 Ent, BI, Std, Web, Express |
|
|
SQL Server 2008 R2 SP1 Datacenter can be upgraded to ...
SQL Server 2008 R2 SP1 Enterprise can be upgraded to ... SQL Server 2008 R2 SP1 Developer can be upgraded to ... |
SQL Server 2008 R2 SP1 Datacenter > SQL Server 2012 Ent, BI
SQL Server 2008 R2 SP1 Ent > SQL Server 2012 Ent, BI SQL Server 2008 R2 SP1 Dev > SQL Server 2012 Dev |
|
|
SQL Server 2008 R2 SP1 Std can be upgraded to ...
SQL Server 2008 R2 SP1 Web can be upgraded to ... SQL Server 2008 R2 SP1 Workgroup can be upgraded to ... SQL Server 2008 R2 SP1 Exprescan be upgraded to ... |
SQL Server 2008 R2 SP1 Std > SQL Server 2012 Ent, BI, Std
SQL Server 2008 R2 SP1 Web > SQL Server 2012 Ent, BI, Std, Web SQL Server 2008 R2 SP1 Workgroup > SQL Server 2012 Ent, BI, Std, Web SQL Server 2008 R2 SP1 Express > SQL Server 2012 Ent, BI, Std, Web |
|
|
SQL Server 2012 Ent can be upgraded to ...
SQL Server 2012 BI can be upgraded to ... SQL Server 2012 Std can be upgraded to ... SQL Server 2012 Dev can be upgraded to ... |
SQL Server 2012 Ent > 2012 BI
SQL Server 2012 BI > 2012 Ent SQL Server 2012 Std > BI, Ent SQL Server 2012 Dev > Ent, BI, Std, Web |
|
|
SQL Server 2012 Web can be upgraded to ...
SQL Server 2012 Express can be upgraded to ... SQL Server 2012 Evaluation can be upgraded to ... |
SQL Server 2012 Web > Ent, BI, Std
SQL Server 2012 Express > Ent, BI, Std, Web SQL Server 2012 Evaluation > Ent, BI, Std, Web |
|
|
Upgrade advisor def
|
tool included with the SQL Server 2012 installation media that enables you to identify issues that will potentially block upgrade
|
|
|
Distributed Replay Utility def
|
Distributed Replay utility enables you to simulate mission-critical workloads. You can use it to determine how a test server will function before upgrading it to SQL Server 2012.
|
|
|
detach database restrictions
|
db is not replicated and published
db is not mirrored db does not have snapshot sys dbs cannot be detached |
|
|
to detach the Planets database, execute the following statement
|
USE master;
GO EXEC sp_detach_db @dbname = [SpaceElevator]; GO |
|
|
to attach the SpaceElevator database, where the mdf and log file are located in the C:\SpaceElevator directory, execute the following statement
|
USE master;
GO CREATE DATABASE SpaceElevator ON (Filename = 'C:\SpaceElevator\SpaceElevator.mdf'), (FILENAME = 'C:\SpaceElevator\SpaceElevator_log.ldf') FOR ATTACH; GO |
|
|
Copy Database Wizard def
|
Copy Database Wizard simplifies the process of copying database from previous versions of SQL Server to SQL Server 2012
database becomes available immediately on the target instance after the transfer completes and is automatically upgraded |
|
|
Copy Database Wizard allows to:
|
1) Choose the src and dest instance (dest must be 2012)
2) Copy db and move them (move will del the db from src) 3) chose DB file locations on the dest instance 4) migrate sql logins, jobs, user-defined stored procedure 5) schedule what time the copy or move occurs |
|
|
Copy Database Wizard detach and attach transfer method
|
This method is faster, but the database on the source instance is taken offline.
Good for large DB. |
|
|
Copy Database Wizard SQL Management Object method
|
This methods is slower but enables the database on the source instance to remain online
|
|
|
Generate and Publish Scripts Wizard
|
You can use the Generate and Publish Scripts Wizard to publish a database to a web hosting provider. You can also use this wizard to produce a script that enables you to transfer a database.
|
|
|
How can you migrate SQL logins from one instance to another
|
Copy Database Wizard
Generate A Script func in SQL Server Management Studio |
|
|
Which of the following can you upgrade to SQL Server 2012 Standard edition without having to apply additional service packs? (Each correct answer presents a complete solution. Choose all that apply.)
A. SQL Server 2005 SP4 Standard edition installed on Windows Server 2008 with Service Pack 2 B. SQL Server 2008 SP2 Standard edition installed on Windows Server 2003 R2 C. SQL Server 2008 SP2 Enterprise edition installed on Windows Server 2008 R2 SP1 D. SQL Server 2008 R2 SP1 Standard edition installed on Windows Server 2008 R2 SP1 |
ad
A Correct: You can upgrade SQL Server 2005 SP4 to SQL Server 2012 if the host operating system is running Windows Server 2008 with Service Pack 2. D Correct: You can upgrade SQL Server 2008 R2 SP1 Standard edition to SQL Server 2012 Standard edition on a host running Windows Server 2008 R2 SP1. |
|
|
To which of the following editions and versions of SQL Server 2012 can you upgrade SQL Server 2008 R2 Datacenter edition (x64)? (Each correct answer presents a complete solution. Choose all that apply.)
A. SQL Server 2012 Enterprise edition (x86) B. SQL Server 2012 Enterprise edition (x64) C. SQL Server 2012 Business Intelligence edition (x64) D. SQL Server 2012 Standard edition (x64) |
bc
|
|
|
Which of the following tools can you use to migrate Windows-authenticated SQL logins from an x86 instance of SQL Server 2012 to an x64 instance of SQL Server 2012 if contained databases are not in use? (Each correct answer presents a complete solution.
Choose all that apply.) A. Copy Database Wizard B. Import and Export Wizard C. Backup and Restore Database D. Generate A Script |
a
d |
|
|
You want to migrate a database, including logins and user-defined error messages, from one SQL Server 2012 instance to another. Which of the following tools can you use to accomplish this goal?
A. Copy Database Wizard B. BACKUP DATABASE Transact-SQL statement C. Import and Export Wizard D. sp_detach_db stored procedure |
a
|
|
|
You want to use the detach and attach method of migrating a database to another SQL Server 2012 Database Engine instance. The database is currently published and replicated. Which of the following steps must you take prior to detaching the database
from the source instance? (Each correct answer presents part of a complete solution. Choose all that apply.) A. Drop database snapshots. B. Unpublish the database. C. Create a format file. D. Create a database snapshot. |
ab
|
|
|
bcp cmd
------------ BULK INSERT ----------------- OPENROWSET(BULK) |
bcp cmd exports data to or imports data from files.
---------------------------------------- BULK INSERT statement is used to import data from a file stored on the file system --------------------------------------- OPENROWSET(BULK) func to import data from an OLE DB data source. Good for linked servers |
|
|
Integration Services Import and Export Wizard used for...
------------------------------------------------- SELECT INTO statement |
Integration Services Import and Export Wizard is used to migrate data from SQL Server and OLE DB providers such as Access or Excel
---------------------------------- SELECT INTO statement used to create a new table and populate it with data based on the results of a SELECT query |
|
|
SQL Server Import and Export Wizard (DTSWizard.exe) def
|
SQL Server Import and Export Wizard (DTSWizard.exe) is a component of Integration Services. The SQL Server Import and Export Wizard enables you to migrate data to and from the sources
|
|
|
When Integration Services are installed on 64-bit OS they might have issues connecting to 32-bit sources. How you fix this?
|
Install Client Tools or SQL Server Data tools
|
|
|
You can use bcp with XML format files only if you have installed ...
|
You can use bcp with XML format files only if you have installed the SQL Server Native Client feature
|
|
|
to export a list of products and product numbers ordered alphabetically by product name to the products.txt file from the AdventureWorks2012 database, use the following bcp command
|
bcp "SELECT Name, ProductNumber FROM AdventureWorks2012.Production.Product ORDER BY
NAME" queryout products.txt -c -T -T trusted -U and -P user and password |
|
|
To insert the data from products.txt in ExampleDB.dbo.TableBeta
|
bcp ExampleDatabase.dbo.TableBeta in products.txt -T -c
|
|
|
BULK INSERT statement to import the data from the products.txt file stored in \\SQL-A\DATA to ExampleDb.dbo.TableAlpha
|
BULK INSERT ExampleDatabase.dbo.TableAlpha FROM '\\SQL-A\DATA\products.txt';
GO |
|
|
Format files def
|
Format file map data file fields to the columns of a table.
Use with bcp, BULK INSERT, and OPENROWSET(BULK) when the src file uses fixed-width or character-terminated fields -x extension to specify that the XML format is being used |
|
|
format file is being created for the AdventureWorks2012.HumanResources.Employee table in which the field terminator will be the comma (,) character
|
bcp AdventureWorks2012.HumanResources.Employee format nul -c -x -f Employee-c.xml -t, -T
|
|
|
create a copy of the HumanResources.Department table of the AdventureWorks2012 database named HumanResourcesDepartmentCopy in the dbo schema of the ExampleDatabase database on the same instance, issue the query:
|
SELECT * INTO ExampleDatabase.dbo.HumanResourcesDepartmentCopy FROM AdventureWorks2012.
HumanResources.Department; |
|
|
You must create an XML format file based on a database table to use later when bulk importing data on other instances. Which of the following tools would you use to accomplish this goal?
A. bcp utility B. Import and Export Wizard C. BULK INSERT statement D. OPENROWSET function |
a
|
|
|
Which of the following tools can you use to create an Integration Services package that enables you to repeat a specific data import or export operation?
A. OPENROWSET function B. BULK INSERT statement C. Import and Export Wizard D. bcp utility |
c
|
|
|
You must copy the complete contents of a table in the Hovercraft database into a new table in the Watercraft database. The destination table does not currently exist in the Watercraft database. The Watercraft and Hovercraft databases are hosted on the same SQL Server 2012 instance. Which of the following tools can you use to accomplish this goal? (Each correct answer presents a complete solution. Choose all that apply.)
A. bcp utility B. SELECT...INTO Transact-SQL statement. C. Import and Export Wizard D. Copy Database Wizard |
b
c Incorrect D Incorrect: Although you can use the Copy Database Wizard to copy an entire database, you cannot use this wizard to copy a single table. |
|
|
You must connect to a remote OLE DB data source and import a substantial amount of data into a table hosted on a local database. Which of the following tools can you use to accomplish this goal? (Each correct answer presents a complete solution. Choose all that apply.)
A. Import and Export Wizard B. Copy Database Wizard C. bcp utility D. OPENROWSET(BULK) function |
A D
|
|
|
You have a table that contains 20,000 customer records. Which of the following tools can you use to export these records to a file? (Each correct answer presents a complete solution. Choose all that apply.)
A. bcp utility B. BULK INSERT Transact-SQL statement C. Import and Export Wizard D. Copy Database Wizard |
a
c Incorrect d Incorrect: You can use the Copy Database Wizard to copy a database from one instance to another. You cannot use this wizard to export data from a table to a file. |
|
|
SQL Server 2012 supports the following login types
|
■ Windows-authenticated login
■ SQL Server–authenticated login ■ Certificate ■ Asymmetric key |
|
|
to create a SQL Server login by using the local account Local_One on the server SQL-A, use the Transact-SQL statement
|
CREATE LOGIN "SQL-A\Local_One" FROM WINDOWS;
|
|
|
To create a SQL Server login using the local security group Group_One on the server SQL‑A, use the Transact-SQL statement
|
CREATE LOGIN "SQL-A\Group_One" FROM WINDOWS;
|
|
|
To create a SQL Server login using the domain account Account_Two from the domain Contoso, use the Transact-SQL statement
|
CREATE LOGIN "CONTOSO\Account_Two" FROM WINDOWS
|
|
|
To create a SQL Server login using the domain security group Group_Two from the domain Contoso, use the Transact-SQL statement:
|
CREATE LOGIN "CONTOSO\Group_Two" FROM WINDOWS;
|
|
|
to create a SQL Server–authenticated login named sql_user_a with the password Pa$$w0rd, execute the following statement
|
CREATE LOGIN sql_user_a WITH PASSWORD = 'Pa$$w0rd';
CREATE LOGIN statement and the WITH PASSWORD option |
|
|
Enables you to configure SQL Server–authenticated logins so that the password expiration policy that applies to the host operating system applies to the login. This option can be set to ON or OFF with the default value of OFF.
|
CHECK_EXPIRATION option
of CREATE LOGIN statement |
|
|
Enables you to configure SQL Server–authenticated logins so that the password complexity policy that applies to the host operating system applies to the
login. |
CHECK_POLICY option
of CREATE LOGIN statement |
|
|
Which security principals can you use with a SQL Server login that uses Windows authentication?
|
You can use a local user account, a local security group, a domain user account, or a domain security group as the basis for a Windows-authenticated
SQL Server login. |
|
|
to create a certificate to be associated with the login that you want to use with a SQL Server login for Dan Bacon and which expires on the first of January 2018, issue the following Transact-SQL statement
|
CREATE CERTIFICATE Dan_Bacon
WITH SUBJECT = 'Dan Bacon certificate in master database', EXPIRY_DATE = '01/01/2018'; |
|
|
to create a login for Dan_Bacon using the Dan_Bacon
certificate created in the earlier example, issue the following Transact-SQL statement |
CREATE LOGIN Dan_Bacon FROM CERTIFICATE Dan_Bacon;
|
|
|
to create an asymmetric key named sql_user_5 by using the RSA_2048 algorithm on a database that has an existing master key, issue the following Transact-SQL statement
|
CREATE ASYMMETRIC KEY sql_user_e WITH ALGORITHM = RSA_2048;
|
|
|
example, to create a SQL Server login that uses the
asymmetric key created in the previous example, execute the following statement |
CREATE LOGIN sql_user_e FROM ASYMMETRIC KEY sql_user_e;
|
|
|
to disable the sql_user_a login, use this statement
|
ALTER LOGIN sql_user_a DISABLE;
|
|
|
You can generate a list of SQL logins either by querying what view
|
sys.server_principals
|
|
|
generate a list of SQL Server–authenticated logins by querying the
|
sys.sql_logins
|
|
|
T-SQL cmd to remove login
T-SQL cmd to deny connect |
DROP LOGIN
DENY CONNECY USE [master] GO DENY CONNECT SQL "contoso\domain_user_b"; GO |
|
|
Sysadmin server role
|
sysadmin
members can do all possible activities |
|
|
Serveradmin server role
|
Serveradmin
Role members can perform instance-wide configuration tasks. Members of this role can shut down the instance |
|
|
securityadmin server role
|
securityadmin
Assign this role to logins that must be able to manage instance-level permissions. Because this role can configure permissions at the instance level, membership in this role allows the elevation of privileges on logins and user-defined server roles to the equivalent of those assigned to the sysadmin fixed server role. |
|
|
processadmin server role
|
processadmin
members can terminate processes running on a Database Engine instance |
|
|
setupadim server role
|
setupadim
members can add linked servers to and remove linked servers from the Database Engine instance |
|
|
bulkadmin server role
|
bulkadmin
members can use the BULK INSERT statement on DBs hosted on the instance |
|
|
diskadmin server role
|
diskadmin
members can manage instance related files |
|
|
dbcreator server role
|
dbcreator
members are able to create, alter, drop, and restore DBs hosted on the DB engine instance |
|
|
public server role
|
public
All SQL Server logins are members of this role. |
|
|
to add the contoso\domain _group_b login to the serveradmin fixed server role, execute this statement
|
ALTER SERVER ROLE serveradmin ADD MEMBER "contoso\domain_group_b";
|
|
|
cmd provides list of fixed server roles
--------------------------------------------------- cmd provides list of fixed server role membership |
fixed server roles - sp_helpsrvrole
--------------------------------------------- fixed server role membership - sp_helpsrvrolemember |
|
|
cmd provides fixed server role permissions
------ cmd to check whether a SQL Server login is member of a specific role ------- cmd displays information about role memebers |
fixed server permissions - sp_srvrolepermission
------------- check whether login is a memeber - IS_SRVROLEMEMBER ------------- info about role members - sys.server_role_members |
|
|
to create a new user-defined server role named Modify_Databases, execute this statement:
|
CREATE SERVER ROLE Modify_Databases;
|
|
|
AUTHORIZATION option for CREATE SERVER ROLE cmd
|
this grant ownership of the server role to the principal specified in the AUTHORIZE clause
|
|
|
to grant the ALTER ANY DATABASE permission to the Modify_Databases role, execute the following Transact-SQL statement
|
GRANT ALTER ANY DATABASE TO Modify_Databases;
|
|
|
You can use the _______ statement to apply a deny permission to a user-defined server role.
You can use the ______ statement to remove a GRANT or DENY permission from a user defined server role. |
You can use the DENY statement to apply a deny permission to a user-defined server role.
You can use the REVOKE statement to remove a GRANT or DENY permission from a user defined server role. |
|
|
Credentials def
|
Credentials store the authentication information that facilitates a connection to a resource external to the Database Engine instance. You can map a single credential to multiple SQL logins, but it is only possible to map a single SQL login to one credential.
|
|
|
to create a credential named RemoteFTP that uses the FTP_Login identity and the password Pa$$w0rd, execute the following statement
|
CREATE CREDENTIAL RemoteFTP with IDENTITY = 'FTP_Login', SECRET = 'Pa$$w0rd';
|
|
|
SQL Server logins allows
|
SQL Server logins allow access at the instance level
They can be mapped to local users/groups domain users/groups certs asymmetric keys |
|
|
Which permission must a security principal have at the instance level to be able to create
SQL logins? A. ALTER ANY LOGIN B. ALTER ANY CREDENTIAL C. ALTER ANY ENDPOINT D. ALTER SETTINGS |
a
|
|
|
You want to create a SQL Server login that is mapped to the Research security group in the CONTOSO domain. Which of the following statements would you use to accomplish this goal?
A. CREATE LOGIN [Research] WITH PASSWORD = ‘Pa$$w0rd’ B. CREATE USER [Research] WITH PASSWORD = ‘Pa$$word’ C. CREATE LOGIN “CONTOSO\Research” FROM WINDOWS D. CREATE USER “CONTOSO\Research” |
C
|
|
|
You want to create an instance-level security principal named Development on server SQL-A. This security principal should use the password Pa$$w0rd and should be authenticated by the SQL Server 2012 instance.
Which of the following Transact-SQL statements would you use to accomplish this goal? A. CREATE LOGIN “SQL-A\Development” FROM WINDOWS B. CREATE USER “SQL-A\Development” C. CREATE LOGIN [Development] WITH PASSWORD = ‘Pa$$w0rd’ D. CREATE USER [Development] WITH PASSWORD = ‘Pa$$word’ |
C
|
|
|
Following the principle of least privilege, to which of the following fixed server-level roles would you add a SQL Server login if you wanted the user to be able to drop and restore databases?
A. sysadmin B. securityadmin C. setupadmin D. dbcreator |
d
|
|
|
You want to configure SQL Server so that the Database Engine instance can automatically authenticate against a remote FTP server. Which of the following should you create to accomplish this goal?
A. Database User B. SQL Login C. Credential D. User-defined server role |
C
|
|
|
Database Users def
|
Database users represent the identity of a SQL Server login when connected to a database.
Each database user maps to a SQL Server login except in the special case of contained databases. |
|
|
Security principal requires what permission on the database in order to be able to create database users
|
ALTER ANY USER permission
|
|
|
What SQL statement will you use in order to create a database user for an existing SQL login?
|
What SQL statement will you use in order to create a database user for an existing SQL login?
CREATE USER with the FOR LOGIN option USE [AdventureWorks2012] GO CREATE USER "contoso\domain_group_a" FOR LOGIN "contoso\domain_group_a"; GO |
|
|
orphaned user def
|
orphaned user is a database user whose corresponding SQL logins has been dropped or the database is restored or attached to a different instance of SQL Server
|
|
|
How can you find orphaned user
|
sp_change_users_login with @Action='Report'
check for orphaned users in the AdventureWorks2012 database, execute the following Transact-SQL statement: USE [AdventureWorks2012] GO; sp_change_users_login @Action='Report'; GO; |
|
|
What is the maximum number of database users that can exist in a database for a specific SQL Server login?
|
You can have only one database user mapped to a SQL Server login in any specific database.
|
|
|
db_owner fixed database-level role
|
assign this role to principals who need to perform all database configuration and management tasks.
Role member are able to drop the database |
|
|
db_securityadmin fixed database-level role
|
members of this role are able to manage the membership of fixed and flexible database-level roles
Principals who are members of this role can elevate their privileges to those functionally equivalent to the db_owner role |
|
|
db_accessadmin fixed database-level role
|
db_accessadmin fixed database-level role
Assign this role to security principals who need to manage database access for logins |
|
|
db_backupoperator fixed database-level role
|
db_backupoperator fixed database-level role
members of this role can back up the database |
|
|
db_ddladmin fixed database-level role
|
db_ddladmin fixed database-level role
adding a principal to this role enables him to run DDL cmd in the database |
|
|
db_datawriter fixed database-level role
|
db_datawriter fixed database-level role
assign this role when you want to enable the principal to insert, delete, or modify data in a DB table |
|
|
db_datareader fixed database-level role
|
db_datareader fixed database-level role
members of this role can read all data from all user tables in a database |
|
|
db_denydatawriter/db_denydatareader fixed database-level role
|
block/allow inserting, altering, or deleting data from a database's user table
|
|
|
Flexible Database-Level Roles def
|
Flexible database-level roles enable you to create roles with custom database-level permissions.
To create a role, a principal either needs the CREATE ROLE permission on the database or must be a member of the db_securityadmin fixed database role. |
|
|
Remember that the _______________ statement is used to create a flexible database-level role, and the _____________________________ statement is used to create a user-defined server role.
|
Remember that the CREATE ROLE statement is used to create a flexible database-level role,
and the CREATE SERVER ROLE statement is used to create a user-defined server role. |
|
|
You can use what statement with what option if you want to grant ownership of the role to the principal specified in the clause
|
You use CREATE ROLE with the option AUTHORIZATION
if you do not specify AUTHORIZATION clause the role will be owned by the security principal who executed the CREATE ROLE |
|
|
to create a flexible database role named TableCreator in the AdventureWorks2012 database that is owned by the contoso\kim_akers database user, execute the following Transact-SQL statement
|
USE [AdventureWorks2012];
GO CREATE ROLE TableCreator AUTHORIZATION "contoso\kim_akers"; GO |
|
|
to grant the CREATE TABLE permission to the TableCreator role in the AdventureWorks2012 database, execute the following Transact-SQL statement
|
USE [AdventureWorks2012];
GO GRANT CREATE TABLE TO TableCreator; GO |
|
|
What stored procedure will you use to add a principal to a flexible database role
|
sp_addrolemember
|
|
|
to add the contoso\domain_user_b database login to the TableCreator role in the AdventureWorks2012 database, execute the following Transact-SQL statement
|
USE [AdventureWorks2012];
GO EXEC sp_addrolemember 'TableCreator', "contoso\domain_user_b"; GO |
|
|
The ALTER ROLE statement enables you to alter only the name of a flexible database role.
You cannot use the ALTER ROLE statement to ... ? |
The ALTER ROLE statement enables you to alter only the name of a flexible database role.
You cannot use the ALTER ROLE statement to add role members. |
|
|
msdb Roles
|
msdb system database has a set of special database roles in addition to the nine fixed database-level roles. These roles enable you to assign permissions associated with Integration Services, Data Collector, Server Groups, database mirroring, and Policy-Based Management
|
|
|
msdb Roles
db_ssiadmin db_ssisoperator db_ssislthduser are used to |
db_ssiadmin
db_ssisoperator db_ssislthduser enable you to assign permissions to principals for Integration services |
|
|
msdb roles
dc_admin dc_operator dc_proxy are used to |
dc_admin
dc_operator dc_proxy this roles enable you to assign permissions to principals for the Data Collector |
|
|
What
ServerGoupAdministratorRole and ServerGroupReaderRole msdb database roles enable you |
ServerGoupAdministratorRole and
ServerGroupReaderRole enables you to assign permission to principal for creating Server Goups |
|
|
What
dbm_monitor msdb database role will allow you to do |
dbm_monitor msdb role is used when working whit Database Mirroring Monitor
This role does not exist until a database is registered with Database Mirroring Monitor |
|
|
PolicyAdministratorRole msdb is for
|
PolicyAdministratorRole msdb role enables you to assign permissions to principals for administering Policy-Based Management policies and conditions
|
|
|
SQLAgentOperatorRole
SQLAgentREaderRole SQLAgentUser msdb database role enable you |
SQLAgentOperatorRole
SQLAgentREaderRole SQLAgentUser enables you to assign permission to principals for the SQL Server Agent |
|
|
this catalog view provides you with information about database principals, including database roles, application roles, SQL Users, and Windows Users
|
sys.database_principals
|
|
|
this catalog view provides you with information about roles and the security principals two are members of those roles
|
sys.database_role_members
|
|
|
this catalog view provides you with information about permissions assigned to security principals at the database levels
|
sys.database_permissions
|
|
|
Contained Users def
|
Contained databases have no external dependencies
Easy to move to another instance Contained databases do not use SQL logins but instead use contained users Contained user is a DB user who does not have a SQL Server login Contained users can connect to the contained database by specifying credentials in the connection string. |
|
|
You can create contained users only after you enable contained database authentication at the instance level. You can do this by executing the following Transact-SQL statement:
|
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE; GO sp_configure 'contained database authentication', 1; RECONFIGURE WITH OVERRIDE; GO |
|
|
You can create a partially contained database by using the ____________ statement with the ______________ option
|
CREATE DATABASE statement with the CONTAINMENT = PARTIAL option
|
|
|
to create a partially contained database named partially_contained_db, execute the following Transact-SQL statement:
|
CREATE DATABASE partially_contained_db
CONTAINMENT = PARTIAL; |
|
|
After you have created the partially contained database, you can use the CREATE USER Transact-SQL statement without having to specify an existing SQL Server login. For example, to create a partially contained database user named contained_user who uses SQL Server for authentication, from the partially contained database, execute the statement
|
CREATE USER contained_user WITH PASSWORD = 'Pa$$w0rd';
You can also create partially contained users who use Windows authentication. For example, to create a partially contained database user mapped to the contoso\contained_user_a domain account, execute the statement: CREATE USER [contoso\contained_user_a]; |
|
|
Application Roles def
|
Application roles enable you to grant permissions to a specific application. For example, you might have a web application that needs to interact with a database hosted on a back-end SQL Server 2012 instance. Rather than have the user access the database with a database user permission, the user accesses the data by using the permissions assigned to the application role.
|
|
|
to create an application role named app_role_alpha in the AdventureWorks2012 database with the password Pa$$w0rd, execute the statement
|
USE [AdventureWorks2012]
GO CREATE APPLICATION ROLE app_role_alpha WITH PASSWORD = 'Pa$$w0rd'; GO You can use the ALTER APPLICATION ROLE Transact-SQL statement to change the name, password, or default schema applied to an application role. You can use the DROP APPLICATION ROLE statement to remove an existing application role. |
|
|
You have created a database instance security principal that maps to a domain-based user account. Which of the following should you create at the database level so that you can grant the database instance security principal the appropriate privileges to create and drop tables in the database?
A. Login B. Server role C. Credential D. User |
D Correct: You can create database users that are mapped to SQL logins when you want to assign permissions at the database level. You can also create flexible database roles, add the database user to the role, and assign the appropriate permissions to the role.
|
|
|
You want to create a security principal at the database level, add existing database users to this security principal, and assign permissions to it. Which of the following Transact-SQL statements would you use to accomplish this goal?
A. CREATE ROLE B. CREATE SERVER ROLE C. ALTER ROLE D. ALTER SERVER ROLE |
a
|
|
|
Which of the following statements or stored procedures do you use to add database users to a fixed database role?
A. ALTER SERVER ROLE B. ALTER ROLE C. sp_addrolemember D. CREATE ROLE |
c
|
|
|
To which of the following fixed database roles should you add a database user if you want to grant the ability to manage database access for logins without assigning unnecessary privileges?
A. db_owner B. db_accessadmin C. db_securityadmin D. db_ddladmin |
b
|
|
|
Which of the following steps must you take before you can create a database that allows database logins that do not map to a SQL Server login?
A. Enable contained database authentication at the instance level. B. Disable contained database authentication at the instance level. Create a userdefined server role. C. Create a flexible database role. D. Create a user-defined server role. |
a
|
|
|
database permission def
|
database permission enable you to control which actions can and cannot be performed by security principals at the DB level
|
|
|
Securable def
|
an items for which we can assign permissions
one securable can contain another they have scope |
|
|
ALTER permissions allow you to
|
ALTER permissions allow you the ability to modify the properties of a securable
Cannot change ownership Cannot grant ownership |
|
|
BACKUP/DUMP permissions
|
BACKUP/DUMP permissions allow you to backup or dump securables
|
|
|
CONTROL permissions
|
CONTROL permissions gives all defined permission on a securable
|
|
|
CREATE permissions
DELETE permissions EXECUTE permission |
CREATE permissions allow you to create securable
DELETE permissions allow you to delete securable EXECUTE permissions allow you to execute a securable |
|
|
IMPERSONATE permissions
INSERT permissions RECEIVE permissions |
IMPERSONATE permissions allows us to impersonate a securable
INSERT permissions allow insertion of data into a securable RECEIVE permissions allows you to receive changes from Service Broker |
|
|
PREFERENCE permission
RESTORE/LOAD permission SELECTE permission |
PREFERENCE permission to reference the securable
RESTORE/LOAD permission to restore the securable SELECT permission allows us to view data in the securable |
|
|
TAKE OWNERSHIP permission
UPDATE permission VIEW DEFINITION permission |
TAKE OWNERSHIP permission - take ownership of a securable
UPDATE permission - allows to modify data in the securable VIEW DEFINITION permission - view the securable definition |
|
|
What statements can you use to mange permissions on a object
|
GRANT
DENY REVOKE to remove permission |
|
|
Cmd to grant INSERT permissions to the Alpha role on the address table in AD2012 database
|
Cmd to grant INSERT permissions to the Alpha role on the address table in AD2012 database
USE [AD2012]; GO GRANT INSERT ON [Address] to [Alpha] GO To deny USE [AD2012]; GO GRANT INSERT ON [Address] to [Alpha] GO |
|
|
Which T-SQL statement do you sue to remove a DENY permission from a securable?
|
REVOKE permission to remove DENY
|
|
|
schemas def
|
schemas simplify the application of permissions by enabling you to collect securable into containers
Obj belong to schemas, schemas belong to security principals |
|
|
What statement will you use to move securable between schemas in the same DB
|
ALTER SCHEMA Lockdown TRANSFER dbo.Engines
|
|
|
What function can you use to determine a specific principal's effective permissions on a certain securable
|
HAS_PERMS_BY_NAME
|
|
|
What view can you query in order to see if a login is disabled
|
sys.server_principals
|
|
|
What T-SQL statement can you use and with what clause in order to force a password change the next time a SQL Server-authenticated login is used to connect to the Database Engine instance
|
ALTER LOGIN with MUST_CHANGE
UNLOCK clause can be used to unlock a SQL Server-authenticated account |
|
|
What view will you use to get info about each asymmetric key, including
how is key encrypted how is the private key encrypted key length algorithm used |
sys.asymmetric_keys
|
|
|
What view will you use in order to see each stored in the DB, including
cert name how is private key encrypted cert serial number cert SID cert expiration last time when it was backed up |
sys.certificates
|
|
|
What view will you use to get info about each symmetric encryption specified when using the CREATE SYMMETRIC KEY statement with the ENCRYPTION BY parm
|
sys.key_encryption
|
|
|
What view will provide info about every symmetric key that has been created by using the CREATE SYMMETRIC KEY statement
|
sys.symmetric_keys
|
|
|
What view will you use to provide info about endpoints used for DB mirroring and AlwaysOn Availability Groups
Connection authentication type Certs used to secure endpoints |
sys.database_mirroring_endpoints
|
|
|
What view will you usein order to get info about each endpoint create on the instance, including
endpoint protocol, payload type, endpoint state |
sys.endpoints
|
|
|
What view will you use in order to get information about each endpoint that uses the HTTP protocol. You can get
if SSL is used Authentication method configured for the endpoint |
sys.http_endpoints
|
|
|
What view will you used in order to get info about service broker endpoints, including
auto config encryption algorithm certs used (if any) |
sys.service_broker_endpoints
|
|
|
What view provides info about the TCP endpoints in the system, including
port number IP address if port is dynamic or not |
sys.tcp_endpoints
|
|
|
How can you start a stopped endpoint using T-SQL
|
ALTER ENDPOINT statement with STATE parameter
|
|
|
How can you change the endpoint authentication by using what T-SQL cmd
|
ALTER ENDPOINT statement with the AUTHENTICATION parameter
|
|
|
How you enable, disable, or require encryption on an endpoint by using T-SQL statement
|
ALTER ENDPOINT with ENCRYPTION
|
|
|
What view provides info about permission configured at Database Engine instance level
|
sysserver_permissions
|
|
|
What view provides info on server-level principals, including
principal type whether is disabled creation date modification date |
sys.server_principals
|
|
|
What view provides information on role membership
|
sys.server_role_members
|
|
|
What view provides information on whether a login is subject to password policy and is configured with an expiration date
|
sys.sql_logins
|
|
|
What view provides info about each executable system obj that you can enable or disable by using surface-area configuration
|
sys.system_components_surface_area_configuration
|
|
|
What view provides info about permissions at the db level
|
sys.database_permissions
|
|
|
What view provides info about principal type, including
authentication type creation date |
sys.database_principals
|
|
|
What view provides info about role membership at the db level
|
sys.database_role_members
|
|
|
What view provides info about database master key passwords, including the credential with which the master key password is associated
|
sys.master_key_passowrds
|
|
|
Which permission must you assign on a table if you want to allow a database user to view the information in that table?
A. REFERENCE B. INSERT C. UPDATE D. SELECT |
d
|
|
|
You want to configure permissions on a table so that members of a database role are allowed to insert data into a table but cannot change existing data in that table. Which of the following permissions should you assign?
A. REFERENCE B. INSERT C. RECEIVE D. UPDATE |
b
|
|
|
3. Which of the following Transact-SQL statements removes the INSERT permission from the Person.Address table for the Sydney_Users role?
A. GRANT INSERT ON [Person].[Address] to [Sydney_Users] B. GRANT SELECT ON [Person].[Address] to [Sydney_Users] C. DENY INSERT ON [Person].[Address] to [Sydney_Users] D. REVOKE INSERT ON [Person].[Address] to [Sydney_Users] |
d
|
|
|
To which fixed database role would you add a security principal to ensure that he or she cannot add, modify, or delete any data stored in user tables in the database?
A. db_datawriter B. db_denydatawriter C. db_denydatareader D. db_owner |
b
|
|
|
You have configured the default, ALPHA, BETA, GAMMA, and DELTA instances on a single host server so that users can connect by using their Active Directory domain credentials. This morning, users can connect to the default instance but report that they cannot connect to the ALPHA, BETA, GAMMA, and DELTA instances. Which of the following should you do when attempting to resolve this problem?
A. Configure the default instance to use mixed authentication. B. Start the SQL Server Agent service on the default instance. C. Start the SQL Server Browser service. D. Restart the SQL Server service on the default instance. |
c
|
|
|
A colleague in the Finance department cannot connect to the Database Engine, although other workers in the finance department can connect. You suspect that the user’s login is disabled. Which of the following catalog views can you query to determine
whether this is the case? A. sys.sql_logins B. sys.server_permissions C. sys.server_principals D. sys.server_role_members |
C. Correct. You can query the sys.server_principals catalog view to determine whether a login is disabled.
|
|
|
You are determining which SQL Server–authenticated logins on an instance are configured without a password expiration date. Which of the following SQL catalog views could you query to make this determination?
A. sys.server_permissions B. sys.sql_logins C. sys.server_role_members D. sql.server_principals |
B. Correct. You can use the sys.sql_logins catalog view to determine whether a SQL Server–authenticated login is configured with an expiration date.
|
|
|
You are resolving a problem a database user is having. You must determine whether instance, database, or Windows authentication is being used. Which of the following security catalog views can you query to determine this information?
A. sys.database_principals B. sys.database_permissions C. sys.server_principals D. sys.sql_logins |
A. Correct. You can query the sys.database_principals catalog view to determine which authentication type a database principal uses.
|
|
|
SQL Server Audit def
|
SQL Server Audit enables you to config sophisticated auditing at the instance and database level
Audit events are forwarded to a target either a flat file, logs. Only on Enterprise edition |
|
|
What cmd you use to create a server audit
|
CREATE SERVER AUDIT
CREATE SERVER AUDIT [BETA-AUDIT] TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = SHUTDOWN ) |
|
|
When you create a serve audit specification or a database audit specification, you choose ______________________ and ______________, which determine the type of actions SQL Server Audit records
|
Action groups and
Actions |
|
|
server audit, which involves specifying the following items:
|
Audit Name - A name for the server audit.
Queue Delay - The delay in milliseconds before audit actions must be processed. Default is 1,000 milliseconds. On Audit Log Failure - What to do when there is an audit log failure Audit Destination - Enables you to specify the Application log, Security log, or a file destination |
|
|
After the instance is shut down during a Server Audit you might need to use what option from the cmd line
|
-f
this option enables you to override the audit-trigger shutdown |
|
|
create a server specification named BETA-SPECIFICATION that uses the BETA-AUDIT server audit and audits database creation, alteration, and deletion actions, execute the following statement
|
CREATE SERVER AUDIT SPECIFICATION [BETA-SPECIFICATION]
FOR SERVER AUDIT [BETA-AUDIT] ADD (DATABASE_CHANGE_GROUP) |
|
|
add the DATABASE_LOGOUT_GROUP serverlevel
audit action group to the BETA-SPECIFICATION group created in the previous example, execute the statement |
ALTER SERVER AUDIT SPECIFICATION [BETA-SPECIFICATION]
ADD (DATABASE_LOGOUT_GROUP) |
|
|
What must you create before creating a server audit specification?
|
You must create a server audit before creating a server audit specification.
|
|
|
Database audit specifications
|
Database audit specifications enables you to perform targeted auditing on specific database and objects rather than more general auditing of a specific type of objects across all DB on the instance
|
|
|
to create a database audit specification named Audit_Spec_1 that uses existing server audit Srv_Audit_1 to audit INSERT operations by members of the Exemplar role on the Paradigm table, execute the statement:
|
CREATE DATABASE AUDIT SPECIFICATION [Audit_Spec_1]
FOR SERVER AUDIT [Srv_Audit_1] ADD (INSERT ON OBJECT::[dbo].[Paradigm] BY [Exemplar]) |
|
|
View that stores info on each audit action and each audit action group that you can use with SQL Server Audit
|
sys.dm_audit_actions
|
|
|
View that enables you to see the information about each SQL Server audit configured on an instance
|
sys.server_audits
|
|
|
View that enables you to see information about currently configured audits
|
sys.dm_server_audit_status
|
|
|
View that enable you to see info about the server audits
|
sys.server_audit_specifications
|
|
|
View that enables you to see info about actions that are audited at the server level
|
sys.server_audit_specification_details
|
|
|
View that enables you to see info about currently configured db audit specification
|
sys.database_audit_specifications
|
|
|
View enables you to see info about actions that are update at the DB level
|
sys.database_audit_specifications_details
|
|
|
Function that enables to query a file-based target for audit information
|
fn_get_audit_file
|
|
|
the following Transact-SQL statement to enable c2
audit mode: |
sp_configure "show advanced options", 1;
GO RECONFIGURE; GO sp_configure "c2 audit mode", 1; GO RECONFIGURE; GO |
|
|
enable common criteria compliance by executing the following Transact-SQL statement:
|
sp_configure "show advanced options", 1;
GO RECONFIGURE; GO sp_configure "common criteria compliance enabled", 1; GO RECONFIGURE; GO |
|
|
Policy-Based Management def
|
Policy-based management provides you with a method of managing more than one instance across multiple servers. You can use policies to enforce configuration
standards such as blocking anyone from enabling Auto Shrink and Auto Close on databases |
|
|
policy-based management
facets def |
policy-based management facets are a collection of properties that cna be configured
|
|
|
policy-based management
condition |
policy-based management condition the settings that can be configured for the property
available conditions depend on the facet |
|
|
policy-based management
policy |
policy-based management policy
a condition that should be enforced |
|
|
policy-based management
category |
policy-based management category
a collection of categories that should be enforced together |
|
|
policy-based management
target |
policy-based management target
defines the scope of policy |
|
|
View that provides one row for each policy-based management condition
|
syspolicy_conditions
|
|
|
View that provides one row for each policy-based management policy on the instance
|
syspolicy_policies
|
|
|
View that provides lists times when policies were executed and
the results of those actions |
syspolicy_policy_execution_history
|
|
|
View that provides detailed info on the
execution of policies, targets and results |
syspolicy_policy_execution_history_details
|
|
|
View that provides one row for each policy-based management subscription on the instance
|
syspolicy_policy_group_subscriptions
|
|
|
View that provides one row for each policy-based management policy on the instance
|
syspolicy_policy_categories
|
|
|
View that provides one row for each combination of policy-based management policy and target query expression
|
syspolicy_system_helath_state
|
|
|
1. You are configuring the Windows Security log as an audit target on the default instance of server SYD-SQL-A. The SQL Server service uses the contoso\SYD-SQL-SRV account as its service account. The SQL Server Agent service uses the contoso \SYD-SQL-AGT account as its service account. Which of the following policies must you configure to accomplish this goal? (Each correct answer presents part of a complete
solution. Choose all that apply.) A. Add the contoso\SYD-SQL-SRV account to the Generate Security Audits policy. B. Add the contoso\SYD-SQL-AGT account to the Generate Security Audits policy. C. Enable success and failure audits on the Audit Object Access policy. D. Enable success and failure audits on the Audit Privilege Use policy. |
AC
A. Correct. If you want to use the local Security log as a target, you must add the SQL Server service account to the Generate Security Audits policy. C. Correct. You must enable success and failure audits on the audit object access policy when using the local Security log as a target. |
|
|
2. You must perform database-level audits in a production environment. Which of the following SQL Server editions supports this functionality?
A. Enterprise B. Business Intelligence C. Standard D. Web |
A
|
|
|
3. An audit failure has caused the Database Engine to shut down. What steps can you take to restart the Database Engine so that you can modify the server audit and resolve the issue? (Each correct answer presents a complete solution. Choose all that
apply.) A. Restart the SQL Server Agent service. B. Start SQL Server by using the -f option. C. Start SQL Server in single-user mode. D. Restart the SQL Server Browser service. |
B
C |
|
|
4. SQL Server Audit has not been configured on a Database Engine instance. You will use SQL Server audit to track insert activity on specific tables in the Hovercraft database. You do not want to track insert activity on any other user databases hosted on the instance. Which of the following Transact-SQL statements would you use to configure SQL Server Audit to accomplish this goal? (Each correct answer presents a complete solution. Choose all that apply.)
A. CREATE SERVER AUDIT B. CREATE SERVER AUDIT SPECIFICATION C. CREATE DATABASE AUDIT SPECIFICATION D. ALTER DATABASE AUDIT SPECIFICATION |
ac
A. Correct. You must create a SQL Server Audit before you can create a database audit specification. C. Correct. After you have created a SQL Server Audit, you can create a database audit specification to track insert activities on a specific database without tracking the same activity on other databases hosted on the instance. |
|
|
Database mirroring is the process of
|
Database mirroring is the process of creating and maintaining an always up-to-date copy of a database on another SQL Server instance
Transactions applied to the database on the principal instance are also applied to the database on the mirrored instance. |
|
|
Database mirrors def
|
Database mirrors are paired copies of a single database that are hosted on separate instances of SQL Server
|
|
|
Depending on how you configure mirroring, the mirror instance functions in either ...
|
high-safety
or high-performance mode. |
|
|
high-safety mode in db mirroring
|
high-safety mode enables fail-over to occur without data loss from committed transactions
hot standby is possible Transactions are committed on both partners and after this they are synchronized Drawback: latency IF witness present fail-over can be automatic |
|
|
high-performance mode in db mirroring
|
data loss is possible in failover
primary instance does not wait for the mirror instance to confirm that it has received the log record |
|
|
Can you mirror system databases?
Can you rename mirrored databases? Can you config DB mirroring against a table that has FILESTREAM? Are cross-database and distributed transaction supported for mirrored databases? |
Can you mirror system databases? - No
Can you rename mirrored databases? - No Can you config DB mirroring against a table that has FILESTREAM? - No Are cross-database and distributed transaction supported for mirrored databases? - No |
|
|
What recovery mode needs to be configured for a database that you want to mirror?
|
Full
|
|
|
to configure the EXEMPLAR database to use the FULL
recovery model, execute the following Transact-SQL statement |
USE MASTER;
ALTER DATABASE Exemplar SET RECOVERY FULL; |
|
|
restore the AdventureMirror database from the c:\backup\adventuremirror.bak backup file by using the WITH NORECOVERY option, execute the statement:
|
RESTORE DATABASE AdventureMirror
FROM DISK = 'C:\backup\adventuremirror.bak' WITH NORECOVERY GO |
|
|
to perform a transaction log backup of the AdventureMirror database to the c:\backup\adventuremirror.trn location, execute this
statement: |
BACKUP LOG AdventureMirror
TO DISK = 'C:\backup\adventuremirror.trn' GO |
|
|
to restore the c:\backup\adventuremirror.trn transaction log backup file to the AdventureMirror database by using the WITH NORECOVERY option,
execute the statement |
RESTORE LOG AdventureMirror
FROM DISK = 'C:\backup\adventuremirror.trn' WITH FILE=1, NORECOVERY GO |
|
|
Steps to implement database mirroring
|
Backup database
Restore in new location using WITH NORECOVERY Backup transaction log on src Restore transaction log backup on target using WITH NORECOVERY |
|
|
statement, when
executed on instance SQL-A, creates an endpoint named Endpoint_Mirroring that uses port 7024 and certificate SQL_A_cert for authentication: |
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED AS TCP ( LISTENER_PORT=7024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SQL_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO |
|
|
You can configure the operating mode for DB mirriong in Transact-SQL by using the ALTER DATABASE with
the SAFETY option: |
Setting the SAFETY option to FULL will configure the database for high-safety mode. When you configure database mirroring, the session automatically starts in high-safety mode.
Setting the SAFETY option to OFF will configure the database for high-performance mode. |
|
|
to configure server SQL-CORE as the witness server for the AdventureMirror database when an endpoint
has been configured that uses TCP port 7024, execute the statement: |
ALTER DATABASE AdventureMirror
SET WITNESS = 'TCP://SQL-CORE:7024' |
|
|
Depending on the mode that the mirrored session is configured to use, three types of role switching are available:
|
Manual failover
Automatic failover Forced service (with possible data loss) |
|
|
Manual failover in db mirroring
|
when we use high-safety mode
we can start manually failover |
|
|
Automatic failover in db mirroring
|
if a witness is present and mirroring session is config for high-safety mode, automatic failover can occur if witness and mirror are still connected
|
|
|
Froced services (wiht possible data loss) in db mirroring
|
used in high-safety mode when no witness is present or in high performance mode
|
|
|
to trigger manual failover of the AdventureMirror database, execute the following statement on the principal instance:
|
USE master;
ALTER DATABASE AdventureMirror SET PARTNER FAILOVER; |
|
|
Automatic failover can occur under the following conditions:
|
■■ The mirroring session is configured for high-safety mode and has a witness.
■■ The mirror is in a synchronized state. ■■ The principal instance is unavailable while the mirror and witness instances retain quorum. If all instances lose communication, automatic failover will not occur even if the witness and the mirror reestablish communication. |
|
|
to configure forced service switchover on the AdventureMirror database, connect to the mirror instance and execute the following statement:
|
USE master;
ALTER DATABASE AdventureMirror SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS; MORE INFO |
|
|
You can determine the operating mode of a mirroring session by querying the ________________ view
on either the principal or mirror instance. |
sys.database_mirroring
|
|
|
Database Mirroring Monitor def
|
Database Mirroring Monitor enables you to monitor how data is being transmitted between the primary and mirrored instances in a mirroring session
|
|
|
What stored procedure enables you to create a job that update status info of all DB mirrored on the instnace
|
sp_dbmmonitoraddmonitoring
|
|
|
What stored procedure enables you delete the mirror monitoring job for all databases mirrored on an instance
|
sp_dbmmonitordropmonitoring
|
|
|
What stored procedure enables you to modify the value of a database mirroring parameter
|
sp_dbmmonitorchangemonitoring
|
|
|
What stored procedure enables you to view the status of monitored databases
|
sp_dbmmonitorresults
|
|
|
You are preparing an instance that will function as a mirror server. You must restore a backup of the database that will be mirrored to this instance. Which of the following restoration options must you use when performing this operation?
A. WITH RECOVERY B. WITH NORECOVERY C. WITH STANDBY D. WITH KEEP_REPLICATION |
B. Correct: You must restore the database by using the WITH NORECOVERY option when preparing a mirror instance.
|
|
|
In which of the following situations must you use certificate-based authentication when configuring database mirroring?
A. SQL Server Agent service accounts are members of an Active Directory domain. B. You are configuring a witness server on a Server Core–host operating system. C. SQL Server Agent service accounts are managed service accounts. D. SQL Server Agent service accounts are virtual accounts. |
D. Correct: Virtual accounts are local accounts that can be used with service accounts. You must use certificate authentication when the SQL Server Agent accounts are not members of an Active Directory domain.
|
|
|
Which of the following editions of SQL Server 2012 support high-performance mode for database mirroring?
A. Web edition B. Standard edition C. Enterprise edition D. Business Intelligence edition |
C. Correct: SQL Server Enterprise edition supports high-safety, high-performance, and witness modes.
|
|
|
The AdventureWorks database is participating in a high-safety mirroring session. The principal instance is hosted on server SQL-A. The mirror instance is hosted on SQL-B. You must configure an instance hosted on server SQL-CORE as a witness for this mirror. The endpoint that supports mirroring on each instance uses TCP port 7024. You have configured the appropriate permissions on each endpoint. Which of the following steps should you take to configure the SQL-CORE instance as the witness?
A. Execute the following statement on SQL-A: ALTER DATABASE AdventureWorks2012 SET WITNESS = ‘TCP://SQL-CORE:7024’; B. Execute the following statement on SQL-B: ALTER DATABASE AdventureWorks2012 SET WITNESS = ‘TCP://SQL-CORE:7024’; C. Execute the following statement on SQL-CORE: ALTER DATABASE AdventureWorks2012 SET WITNESS = ‘TCP://SQL-A:7024’; D. Execute the following statement on SQL-CORE: ALTER DATABASE AdventureWorks2012 SET WITNESS = ‘TCP://SQL-B:7024’; |
a
|
|
|
Publisher def
Article def |
Publisher is an instance that makes data available through publication.
Article is a published object. Can be table, stored procedure, view, indexed view, user-defined function. |
|
|
publication def
distributor def |
publication is a collection of articles
distributor is an instance that manages the transmission from publisher to subscriber |
|
|
subscriber def
agent def |
subscriber is an instance that receives publication
agent is a service that enables the publisher, distributor, or subscriber to perform replication related tasks |
|
|
Snapshot replication def
|
snapshot replication enables complete refreshes of data rather than updating the database on an incremental basis
|
|
|
transactional replication def
|
transactional replication is suited for instance-to-instance situation that require subscriber databases to stay up to date with changes on the publishing database
|
|
|
peer-to-peer replication def
|
peer-to-peer replication enables peer nodes to read and write changes and have those changes propagate top other nodes in the replication topology
|
|
|
merge replication def
|
merge replication is suited for mobile and distributed server application when data conflict is possible and any node may issue data change that will eventually be consistent across all nodes
|
|
|
When to use snapshot replication?
|
Data changes infrequently
Most of the changes to the database occur over a short period Only a small volume of data needs to be replicated |
|
|
When to use transactional replication?
|
you want change to the publisher to be affected on the subscriber as
Occurs in one direction |
|
|
peer-to-peer transactional replication how it works
|
peer-to-peer transactional replication enables transactions performed by subscribers to be synchronized with other nodes in replication topology
When you implement it you must partition the data in a such a way that the same row will not be update at separate locations |
|
|
When to use peer-to-peer transactional replication?
|
the data is partitioned
conflicts are likely to be rare all participant should be 2012 no filtering by column or row |
|
|
Merge Replication def
|
Merge replication enables databases hosted on instances at separate locations to be updated
and those changes replicated to all other databases participating in the replication topology. When you configure merge replication, the publisher and subscribers track changes made by using triggers. When a subscriber synchronizes with the publisher, they exchange all rows that have changed between the publisher and subscriber since the last synchronization |
|
|
when to use merge replication?
|
db is updated at multiple location and changes must be shared among all of them
subscribers can make offline changes to data. you can detect and resolve conflicts non-sql server nodes can be part |
|
|
Replication Monitor def
|
Replication Monitor is a tool available in SQL Server management studio that enables you to monitor replication
must be member of sysadmin role |
|
|
NOT FOR REPLICATION option
|
NOT FOR REPLICATION option enables you to configure foreign key constraints, check
constraints, identity columns, and triggers to behave differently when an insert, update, or delete operation is performed by a replication agent than they behave in normal operation. |
|
|
heterogeneous replication has the following constraints:
|
Merge replication is not supported.
Peer-to-peer replication is not supported. SQL Server 2012 cannot subscribe to an IBM DB2 publication. |
|
|
You have a database that is updated only between 1:00 A.M. and 1:30 A.M. each day. Updates occur only at your organization’s head office site. This database must be available on local SQL Server 2012 instances at five branch office sites. Which type of replication would you use to support this configuration?
A. Snapshot publication B. Transactional publication C. Peer-to-Peer publication D. Merge publication |
a
|
|
|
You are responsible for designing the replication topology of a database that will have hosted instances in six cities. Updates occur constantly and are processed only on the database hosted at the central site, but the databases in the other cities must be
kept as up to date as possible. Which form of replication should you use to support this configuration? A. Peer-to-peer publication B. Snapshot publication C. Transactional publication D. Merge publication |
c
|
|
|
Which publication type would you choose if you needed to allow updates to occur at 10 SQL Server 2012 database instances spread across Australia and New Zealand, to allow updates to any row in published tables from any site, and to enable conflict
resolution to occur with a minimum of administrator intervention? A. Transactional publication B. Snapshot replication C. Peer-to-peer publication D. Merge publication |
d
|
|
|
Which forms of heterogeneous replication does SQL Server 2012 support?
A. Snapshot replication from an IBM DB2 publisher B. Merge replication from an Oracle publisher C. Transactional replication from an Oracle publisher D. Peer-to-peer replication from an IBM DB2 publisher |
Correct: SQL Server 2012 supports functioning as a subscriber to transactional replication from an Oracle publisher.
|
|
|
An Oracle database can serve as a ______________ for data replicated to databases hosted on SQL Server 2012.
|
An Oracle database can serve as a publisher for data replicated to databases hosted on SQL Server 2012.
|
|
|
SQL Server 2012 can serve as a ____________ or ___________ for data replicated to Oracle and IBM DB2 databases.
|
SQL Server 2012 can serve as a publisher or distributor for data replicated to Oracle and IBM DB2 databases.
|
|
|
When SQL Server 2012 functions as a ________ to an Oracle database, that data can in turn be published to other databases running Oracle, IBM DB2, or SQL Server.
|
When SQL Server 2012 functions as a subscriber to an Oracle database, that data can in turn be published to other databases running Oracle, IBM DB2, or SQL Server.
|
|
|
SQL Server 2012 Ent supports up to ____ cluster node
SQL Server 2012 Business up to ___ SQL Server 2012 Std ___ |
SQL Server 2012 Ent - 16
SQL Server 2012 Business - 2 SQL Server 2012 Std - 2 |
|
|
What is required in order to deploy SQL Server as a failover cluster?
|
You must have a functional Windows Server failover cluster
|
|
|
What two wizard you need to run in order to install SQL Server failover cluster?
|
Advanced Cluster Preparation Wizard
Advanced Cluster Completion Wizard |
|
|
Multi-subnet failover clustering def
|
Multi-subnet failover clustering is a special configuration where each node in the failover cluster is located on a different TCP/IP subnet.
A multi-subnet failover cluster does not use shared storage. When configuring a multi-subnet failover cluster, you must use another solution to replicate data between the instances on separate subnets Only on SQL Server 2012 Ent & Win Server 2008 Ent/Datacenter |
|
|
Stretch cluster def
|
Stretch cluster is a term for a geographically dispersed cluster.
|
|
|
Quorum failure def
|
Quorum failure is a more complicated situation that is generally caused by persistent communications failure or by the problematic configuration of cluster nodes.
Quorum failure must be resolved manually |
|
|
Which of the following operating systems can you use as the host operating system for a SQL Server 2012 multi-subnet failover cluster instance?
A. Windows 7 Enterprise edition B. Windows Server 2008 Enterprise edition C. Windows Server 2008 R2 Enterprise edition D. Windows Vista Ultimate edition |
c
|
|
|
Which of the following Windows PowerShell commands can you use to perform failover of a SQL Server failover cluster instance from one node to another?
A. Move-ClusterGroup B. Move-ClusterResource C. Move-ClusteredSharedVolume D. Move-ClusterVirtualMachineRole |
a Correct:
The Move-ClusterGroup cmdlet enables you to move a clustered service or application from one node to another in a failover cluster. You can use this cmdlet to perform manual failover of a SQL Server clustered instance from one node to another. |
|
|
The primary node of a four-node SQL Server failover cluster instance fails due to a hardware failure. Replacement hardware will not arrive for 48 hours. Which of the following steps should you take first to remedy this situation?
A. Evict the failed node. B. Evict the new primary node. C. Reinstall SQL Server on the failed node. D. Join the failed node to the cluster. |
a
|
|
|
You have configured servers SYD-A and SYD-B to be members of a Windows Server failover cluster. Server SYD-B has control of the shared disk resources. You will deploy SQL Server 2012 as a failover cluster instance on these servers. Which of the following steps must you take to accomplish this goal? (Each correct answer forms part of a complete solution. Choose all that apply.)
A. Run Advanced Cluster Preparation on SYD-A. B. Run Advanced Cluster Preparation on SYD-B. C. Run Advanced Cluster Completion on SYD-A. D. Run Advanced Cluster Completion on SYD-B. |
D. Correct: You run advanced cluster completion only on the node that has control of the shared disk resource.
B. Correct: You must run advanced cluster preparation on all nodes that will participate in the failover cluster instance. A. Correct: You must run advanced cluster preparation on all nodes that will participate in the failover cluster instance. |
|
|
What Are AlwaysOn Availability Groups?
|
an alternative to DB mirroring
supports up to four sets of secondary tables the secondary DBs are accessible for read-only operations Only on SQL Server 2012 Ent |
|
|
availability group (AlwaysOn)
|
a collection of user DBs called availability databases that can fail over together
|
|
|
Requirements for hosts in AlwaysOn Availability Groups
|
host must
not be DC must be part of a win failover cluster |
|
|
SPN
VNN |
SPN Service Principal Name
VNN Virtual Network Name |
|
|
AlwaysOn Availability Groups support the following availability modes:
|
Asynchronous-commit mode
Synchronous-commit mode |
|
|
Asynchronous-commit mode (AlwaysOn Availability Groups)
|
use when replicas must be in different geographical locations
When you configure all secondary replicas to use asynchronous-commit mode in the primary will not wait for secondaries to harden the log and will run with minimum transaction latency If you configure the primary to use asynchronous-commit mode, the transactions for all replicas will be committed asynchronously independently of which mode you’ve configured on each secondary replica. |
|
|
Synchronous-commit mode (AlwaysOn Availability Groups)
|
mode increases transaction latency but minimizes the chance of data loss in the event of automatic failover.
When you use this mode, each transaction is applied to the secondary replica before being written to the local log file. The primary verifies that the transaction has been applied to the secondary before entering a SYNCHRONIZED state. |
|
|
to change the availability mode of the SQL-\AlwaysOn replica to synchronous commit for the AG-Alpha availability group, execute the statement:
|
ALTER AVAILABILITY GROUP AG-ALPHA MODIFY REPLICA ON 'SQL-C\AlwaysOn' WITH
( AVAILABILITY_MODE = SYNCHRONOUS_COMMIT); |
|
|
AlwaysOn Availability Groups support three forms of failover
|
Automatic failover
Planned manual failover Forced manual failover |
|
|
Automatic failover (AlwaysOn Availability Groups)
|
Automatic failover
No admin intervention Current primary and at least one secondary replica are configured with a failover mode set to AUTOMATIC, and at least one of the secondary replicas set to AUTOMATIC is also synchronized. |
|
|
(AlwaysOn Availability Groups)
|
Planned manual failover
Triggered by admin No data loss occurs Planned manual failover can occur only if at least one of the secondary replicas is in a SYNCHRONIZED state. You can perform planned manual failover only if the primary and replica instances are in synchronous-commit mode. |
|
|
(AlwaysOn Availability Groups)
|
involves the possibility of data loss
Use forced manual failover when no secondary replica is in the SYNCHRONIZED state or when the primary replica is unavailable. This failover is only supported if asynchronous-commit mode is used on the primary, or if the only available replica uses asynchronous-commit mode. |
|
|
to perform manual failover of the AG-Alpha availability group, execute the following statement
|
ALTER AVAILABILITY GROUP AG-Alpha FAILOVER;
|
|
|
to perform manual failover of availability group AG-Alpha to the SQL-D\AlwaysOn instance, execute what PowerShell command
|
Switch-SqlAvailabilityGroup -Path SQLSERVER:\SQL\SQL-D\AlwaysOn\AvailabilityGroups\AGAlpha
|
|
|
to force failover of the AG-Alpha availability group, execute the following statement:
|
ALTER AVAILABILITY GROUP AG-Alpha FORCE_FAILOVER_ALLOW_DATA_LOSS;
|
|
|
to force failover of availability group AG-Alpha to the
SQL-D\AlwaysOn instance, execute what PowerShell command: |
Switch-SqlAvailabilityGroup -Path SQLSERVER:\SQL\SQL-D\AlwaysOn\AvailabilityGroups\AGAlpha
-AllowDataLoss |
|
|
Readable secondary replicas def
|
Readable secondary replicas can service read-only requests for database access, which enables you to offload read-only workloads from the primary replica.
|
|
|
You can configure readable secondary properties for a replica by using the __________ Transact-SQL statement with the ____________ option
|
You can configure readable secondary properties for a replica by using the ALTER AVAILABILITY GROUP Transact-SQL statement with the SECONDARY_ROLE option
|
|
|
deploying AlwaysOn Availability Groups involves performing several tasks in order
|
■■ Creating a mirroring endpoint
■■ Enabling AlwaysOn ■■ Creating an availability group ■■ Creating an availability group listener ■■ Adding a secondary replica |
|
|
You can create an endpoint from the SQL Server PowerShell module by using the _______________ cmdlet
|
You can create an endpoint from the SQL Server PowerShell module by using the New-SqlHadrEndpoint cmdlet
|
|
|
to create an endpoint named AlwaysOnEndpoint that
uses TCP port 7028 on instance SQL-A\ALTERNATE, issue the command |
$endpoint = New-SqlHadrEndpoint AlwaysOnEndpoint -Port 7028 -Path SQLSERVER:\SQL\SQL-A\
ALTERNATE |
|
|
to start the endpoint what PowerShel cmdlet will you use
|
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"
|
|
|
to enable AlwaysOn on the ALTERNATE instance on server SQL-B, issue the following PowerShell command
|
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\SQL-B\ALTERNATE
Disable-SqlAlwaysOn PowerShell to disable |
|
|
availability group listener def
|
availability group listener is a network connectivity endpoint for an availability group
Clients connect to the listener, which in turn connects them to the availability group’s primary instance. |
|
|
to add a listener named Beta-Listener to the
AG-Alpha availability group that uses IP address 10.0.0.222, subnet mask 255.0.0.0, and port 7028, execute the statement |
ALTER AVAILABILITY GROUP [AG-Alpha]
ADD LISTENER 'Beta-Listener' (with IP (('10.0.0.222','255.0.0.0')), PORT=7028); GO |
|
|
to create a new availability group listener on instance SQL-C\AlwaysOn
named Gamma-Listener to the AG-Gamma availability group that uses IP address 10.0.0.224, subnet mask 255.0.0.0, and port 7030, use the PowerShell command |
New-SqlAvailabilityGroupListener -Name Gamma-Listener -StaticIP '10.0.0.224/255.0.0.0'
-Port 7030 -Path SQLSERVER:\SQL\SQL-C\ALWAYSON\AvailabilityGroups\AG-Gamma |
|
|
to join the AG-Delta availability group, execute
the following Transact-SQL statement on the Database Engine instance that hosts the secondary replica: |
ALTER AVAILABILITY GROUP AG-Delta JOIN;
|
|
|
to join the SQL-E\AlwaysOn instance to the AG-Delta availability group, execute the PoweShell command:
|
Join-SqlAvailabilityGroup -Path SERVER:\SQL\SQL-E\AlwaysOn -Name 'AG-Delta'
|
|
|
An AlwaysOn availability group can have ___________ primary and __________ secondary replicas.
|
An AlwaysOn availability group can have one primary and four secondary replicas.
|
|
|
Which tool can you use to enable AlwaysOn Availability Groups on a SQL Server 2012
instance? A. SQL Server Management Studio B. SQL Server Installation Center C. SQL Server Configuration Manager D. SQL Server Data Tools |
C. Correct: You can enable AlwaysOn Availability Groups by using either SQL Server Configuration Manager or PowerShell.
|
|
|
Which Windows PowerShell cmdlet can you use to perform manual availability group failover?
A. Switch-SqlAvailabilityGroup B. New-SqlHadrEndpoint C. New-SqlAvailabilityGroupListener D. Enable-SqlAlwaysOn |
a
|
|
|
Which Windows PowerShell cmdlet can you use to create a mirroring endpoint on an instance when preparing it for the deployment of AlwaysOn Availability Groups?
A. New-SqlAvailabilityGroupListener B. Switch-SqlAvailabilityGroup C. Enable-SqlAlwaysOn D. New-SqlHadrEndpoint |
D
|
|
|
You will configure an AlwaysOn Availability Group to support automatic failover from the primary replica to any available secondary replica. Which of the following availability modes should you configure for the replicas in this availability group? (Each correct
answer forms part of a complete solution. Choose all that apply.) A. Configure the primary replica to use the asynchronous-commit availability mode. B. Configure the primary replica to use the synchronous-commit availability mode. C. Configure the secondary replica to use the asynchronous-commit availability mode. D. Configure the secondary replica to use the synchronous-commit availability mode |
bd
B. Correct: To support automatic failover to any available secondary replica, all replicas must use synchronous-commit mode. Automatic failover cannot occur if the primary replica uses asynchronous-commit mode. D. Correct: To support automatic failover to any available secondary replica, all replicas must use synchronous-commit mode. Automatic failover cannot occur if the primary replica uses asynchronous-commit mode. |
|
|
Performance Monitor def
|
Windows utility that captures statistical information about the hardware environment, operating system, and any applications that expose properties and counters
|
|
|
data collector set def
|
data collector set provides the ability to group data collectors into reusable elements for different performance-monitoring scenarios.
|
|
|
basic terminology for Windows Performance Monitor
object counter |
An object is a resource that can be monitored.
An object exposes one or more counters. A counter might have several instances if more than one resource of that type exists |
|
|
Performance Monitor counters are organized into a three-level hierarchy
|
object,
counter, instance |
|
|
A counter object must have at least ______________.
A counter can have __________________________. |
A counter object must have at least one counter.
A counter can have zero or more instances |
|
|
What does the System Processor Queue Length counter measure?
A. The number of system requests waiting for a processor B. The number of SQL Server requests waiting for a processor C. The number of processors actively performing work D. The amount of time that a processor is in use |
a
|
|
|
What does the Buffer Manager performance object counter Page Life Expectancy counter measure?
A. The total number of pages on all free lists B. The number of requests to find a page in the buffer pool C. The number of physical database page reads that are issued per second D. The number of seconds a page will stay in the buffer pool without references |
d
|
|
|
What does the Physical Disk:Avg.Disk Queue Length counter measure?
A. The number of system requests, on average, that are waiting for disk access B. The time that the disk is busy with read/write activity C. The number of physical database page reads that are issued per second D. The number of physical database page writes that are issued per second |
a
|
|
|
In SQL Server Profiler, a category is a ...
|
group of related event classes.
An event class contains all the data columns that can be reported by an event. An event is defined as the occurrence of an action within an instance of the SQL Server Database Engine and is further defined by its attributes, which are listed in data columns |
|
|
The most commonly used event groups are
|
Locks,
Performance, Security Audit, StoredProcedures, and TSQL |
|
|
Trace files can also be imported into a SQL Server table by using the _______________ function
|
fn_trace_gettable
|
|
|
You are troubleshooting a performance issue with a SQL Server instance. Query performance declines every Monday morning from 9 A.M. to 10 A.M. Which tools can you use to diagnose the cause of the performance problems? (Choose all that apply.)
A. Extended Events Profiler B. Database Engine Tuning Advisor C. Resource Governor D. SQL Server Profiler |
ad
|
|
|
Which trace event would you use to determine when a T-SQL statement has completed?
A. SQL:StmtCompleted B. SQL:StmtStarting C. SQL:BatchComplete D. RPC:Completed |
cd
|
|
|
Which SQL Server Profiler events can identify the users involved in a deadlock? (Each answer presents a complete solution. Choose two.)
A. Lock:DeadLock B. Lock:DeadLock Chain C. Process:DeadLock D. Chain:DeadLock List |
a
b |
|
|
There are two types of dynamic management views and functions
|
Server-scoped dynamic management views and functions
Database-scoped dynamic management views and functions |
|
|
These objects provide information about connections, sessions, requests, and query execution.
|
sys.dm_exec_*
|
|
|
These objects provide access to SQL Server operating system–related information.
|
sys.dm_os_*
|
|
|
These objects provide access to transaction management.
|
sys.dm_tran_*
|
|
|
These objects provide information on I/O processes.
|
sys.dm_io_*
|
|
|
These objects provide database-scoped information.
|
sys.db_db_*
|
|
|
DMV returns one row for each current user session.
|
sys.dm_exec_sessions
|
|
|
DMV returns a row for each task that is currently waiting for a resource.
|
sys.dm_os_waiting_tasks
|
|
|
DMV returns information about how often and how long any task had to wait for a specific wait_type since the SQL Server instance started
|
sys.dmos_wait_stats
|
|
|
DMV objects can be queried to identify missing indexes
|
sys.dm_db_missing_index_*
|
|
|
Which of the following are used to locate blocked processes?
A. sys.dm_os_wait_stats view B. sys.dm_exec_sessions view C. sys.dm_exec_requests view D. sys.dm_os_waiting_tasks view |
c
|
|
|
Which dynamic management object can be used to identify the names of any tables where the index is missing?
A. sys.dm_db_missing_index_columns B. sys.dm_db_missing_index_groups C. sys.dm_db_missing_index_group_stats D. sys.dm_db_missing_index_details |
d
|
|
|
data collector def
|
data collector is a core component of the data collection platform for SQL Server 2012 and the tools that are provided in SQL Server.
The data collector provides one central point for data collection across your database servers and applications. can obtain data from variety of sources |
|
|
management data warehouse
|
The data collector stores the collected data in a relational database known as the
management data warehouse (MDW). |
|
|
The data collector can collect information from several locations
|
DMVs and DMFs to retrieve detailed information about the operation of the system.
retrieve performance counters that provide metrics about the performance of both SQL Server and the entire server It can capture SQL Trace events user defined functions |
|
|
Three components make up the data collection system in SQL Server
|
the data collector - set of jobs that run on the local server that collect data from dynamic management objects, performance counter, and SQL Trace events and then upload that data to the management data warehouse
the management data warehouse - consolidates data from multiple SQL Server instances Standard reports |
|
|
three goals for creating a centralized management data warehouse
|
access reports that combine info for all server instances
offload the need to collect data and to report on it from the production servers persist data that is stored in dynamic management objects |
|
|
Roles for the Management Data Warehouse
|
mdw_admin - Full access to the management data warehouse
mdw_writer - Write and read access; required by data collectors mdw_reader - Read access; required by users accessing reports |
|
|
Roles for Configuring the Data Collector
|
dc_admin - Full administrator access to the configuration
dc_operator - Read and update access to the configuration dc_proxy - Read access to the configuration |
|
|
Data Collector Reports
|
Server Activity History - CPU, memory, disk, and network I/O SQL Server waits and SQL Server activity
Disk Usage Summary - Trends and details of disk and file usage Query Statistics History - Most expensive queries ranked by CPU, duration, reads, and writes |
|
|
The data collector can query _____ and ____ to retrieve detailed information about the operation of the system
|
The data collector can query DMVs and DMFs to retrieve detailed information about the operation of the system
|
|
|
You must centralize your SQL Server capacity management tasks and evaluate the performance against a known baseline by implementing a solution that incurs minimal cost and requires the least effort to configure and maintain. Which solution should you implement?
A. Install a SQL Server 2012 instance and collect data from the dynamic management objects. B. Install a SQL Server 2012 instance and implement a management data warehouse. C. Configure Windows Performance Monitor. D. Implement Microsoft System Center Operations Manager. |
Correct: The Performance Data Warehouse in SQL Server 2012 enables you to configure data collection quickly against SQL Server instances to consolidate all
the capacity management and performance baseline analysis. |
|
|
You need to grant a user access to the management data warehouse reports. To which role should you add the user?
A. mdw_admin B. mdw_reader C. dc_operator D. dc_proxy |
B. Correct: The mdw_reader role provides read access, which is required by users accessing reports.
|
|
|
What does the Server Activity report include? (Each answer presents a complete solution. Choose two.)
A. CPU, memory, disk, and network I/O B. Trends and details of disk and file usagedc_proxy C. Most expensive queries ranked by CPU, duration, reads, and writes D. SQL Server waits and SQL Server activity |
a
d |
|
|
Server Activity Report def
|
Server Activity Report is based on the
Server Activity System Data Collection Set and is made up of the DMV snapshot and performance counter collector types. The collector runs every 60 seconds, and the data is uploaded every 15 minutes and retained for 14 days by default. |
|
|
Disk Usage Report def
|
Disk Usage Report is based on the Disk Usage System Data Collection Set. By default, the collection set gathers disk usage data every six hours and keeps the data for two years.
|
|
|
Query Statistics Report def
|
Query Statistic Report is based on the Query Statics System Data Collection Set. This collection set runs every 10 seconds, and the data is uploaded every 15 minutes and retained for two weeks by default. This collection set is the most intensive. To avoid the constant overhead of uploading the data collected by this collection set, the data is cached on the local file system and uploaded by SSIS every 15 minutes
|
|
|
Two counters that can be monitored to determine disk activity are:
|
PhysicalDisk: % Disk Time - percentage of time
that the disk is busy with read and write activity PhysicalDisk: Avg. Disk Queue Length - |
|
|
Use PhysicalDisk: Current Disk Queue Length counter
|
PhysicalDisk: Current Disk Queue Length counter
to see how many system requests are waiting for disk access. |
|
|
If Current Disk Queue Length and % Disk Time counter values are consistently high, consider doing one of the following:
|
Using faster disk drives
Moving some files to an additional disk or server Adding disks to a RAID array if one is being used |
|
|
What counter can you monitor in order to make sure that disk activity is not caused by paging
|
Page Fault/sec
|
|
|
When to use logical disk counters instead of physical disk counters
|
if you have more than one logical partition on the same hard disk
|
|
|
two counters that can determine the amount of I/O generated by SQL Server components are:
|
SQL Server:Buffer Manager: Page reads/sec.
SQL Server:Buffer Manager: Page writes/sec. |
|
|
To monitor for a low-memory condition, use the following object counters
|
Memory: Available Bytes - indicates how many bytes are currently available
Memory: Pages/sec - number of pages that were either retrieved from disk die to had page faults or written to disk to free space in working set due to page faults |
|
|
To monitor the amount of memory that SQL Server uses, examine the following performance
counters: |
Process: Working - amount of memory a process uses
SQL Server: Buffer Manager: Buffer Cache Hit Ratio SQL Server: Buffer Manager: Total Pages SQL Server: Memory Manager: Total Server Memory (KB) |
|
|
This counter monitors the amount of time the CPU spends executing a thread that is not idle
To determine the average for all processors, use the _____________ counter instead |
% Processor Time counter
System: % Total Processor Time |
|
|
Corresponds to the percentage of time the processor
spends on execution of Microsoft Windows kernel commands such as processing SQL Server I/O requests |
Processor: % Privileged Time
|
|
|
Corresponds to the percentage of time the processor
spends on executing user processes such as SQL Server. |
Processor: % User Time
|
|
|
Corresponds to the number of threads waiting
for processor time. |
System: Processor Queue Length
|
|
|
1. Which counters would you use to determine whether CPU usage rates for SQL Server are within normal ranges? (Each correct answer presents a complete solution. Choose all that apply.)
A. Processor: % Privileged Time B. Processor: % User Time C. SQL Server: Buffer Manager: Page reads/sec D. SQL Server: Buffer Manager: Page writes/sec |
ab
C. Incorrect: The Buffer Manager object provides counters to monitor how SQL Server uses the buffer pool. Page Reads/sec measures the number of physical database page reads that are issued per second. D. Incorrect: The Buffer Manager object provides counters to monitor how SQL Server uses the buffer pool. Page Writes/sec measures the number of physical database page writes that are issued per second. |
|
|
What does PhysicalDisk: % Disk Time measure?
A. The percentage of time that the disk is busy with read/write activity B. The number of physical database page writes issued C. The number of pages used to store compiled queries D. The percentage of time that the disk system was not processing requests and no work was queued |
a
|
|
|
By using Windows Performance Monitor, you have identified a large number of threads waiting for processor time. What should you do to resolve the performance issue?
A. Add RAM. B. Add spindles to the storage subsystem. C. Disable Hyper-Threading. D. Install a faster processor. |
d
|
|
|
CIK def
|
clustered index key (CIK) is the row identifier for the table data
also added to any non-clustered indexes created on the table |
|
|
There are three cases in which a clustered index is the most efficient index for retrieving data:
|
■■ Queries that return a large percentage of the columns in the table
■■ Queries that return a single row based on the clustered index key ■■ Queries that return range-based data |
|
|
There are two cases in which a non-clustered index is the most efficient index for retrieving data:
|
■ Queries that return few rows
■ Queries that can be covered by the index |
|
|
There are two cases in which a filtered index is the most efficient index for retrieving data:
|
■ When combined with sparse columns to locate specific non-null rows
■ When queries that are a small subset of the rows are selected often |
|
|
Columnstore Indexes def
|
a non-B-Tree type index
based on the VertiPaq engine technology that is used by PowerPivot provide performance improvements for the typical data warehouse–type queries that perform aggregations over large data sets. |
|
|
syntax to create a column store index
|
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_CS_FactProductInventory]
ON dbo.FactProductInventory (ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance) GO |
|
|
The following data types are not permitted in a columnstore index
|
text, ntext, image, varchar(max), nvarchar(max), rowversion,
binary, varbinary, sql_variant, uniqueidentifier, spatial, xml, timestamp, hierarchyid, datetimeoffset (scale > 2), decimal (precision > 18), numeric (precision > 18) |
|
|
Cardinality def
|
Cardinality refers to the number of rows that exist for a given value
|
|
|
Moving Row-Overflow Data def
|
Variable-length data has a maximum limit of 8,060 bytes.
If this limit is surpassed, some data must be moved off row. The data engine identifies the column with the largest width, moves that column in a ROW_OVERFLOW_DATA allocation unit, and then places a 24-byte pointer on the original page from which the data was moved. This is a dynamic process, and data can move off row and back on row as data is updated. |
|
|
Syntax to rebuild an index with a new FILLFACTOR
|
T-SQL
USE [AdventureWorks2012] GO ALTER INDEX ALL ON [Production].[Product] REBUILD WITH (FILLFACTOR = 80); GO |
|
|
DMV to determine which indexes have fragmentation
|
sys.dm_db_index_physical_stats
|
|
|
lists the columns that would comprise the missing index, which can be used to build the CREATE INDEX
statement. |
sys.dm_db_missing_index_columns
|
|
|
Lists details on the columns used for equality,
inequality, and included columns. |
sys.dm_db_missing_index_details
|
|
|
If an index contains a column with either a text or ntext data type, can you perform an online index rebuild?
|
Yes. In SQL Server 2012, only XML and spatial data types are excluded from online index rebuild operations.
|
|
|
Which index type organizes the data in the table in the logical order of the key?
A. XML index B. Spatial index C. Clustered index D. Non-clustered index |
c
|
|
|
What are the limitations for non-clustered indexes?
A. One index key and 900 bytes B. 249 index keys and 8,060 bytes C. 16 index keys and 900 bytes D. No maximum on number of key columns and 900 bytes |
c
|
|
|
What are two situations in which you should consider adding filtered indexes to a table?
A. When you want to support sparse columns B. When a column contains many duplicate values C. When you query often for a small subset of rows based on the value in a column D. When you want to support XML queries |
a c
|
|
|
4. Which DMV should you examine to determine whether there is index fragmentation?
A. sys.dm_db_index_physical_stats B. sys.dm_db_index_operational_stats C. sys.dm_db_missing_index_details D. sys.dm_db_index_usage_stats |
a
|
|
|
When you examine a query execution plan, you notice that for a given operator the actual number of rows and estimated number of rows returned are very different. What should you do?
A. Execute the sp_updatestats system stored procedure, specifying 20 for the value of the @resample parameter. B. Execute the UPDATE STATISTICS command on the table in question, specifying the WITH SAMPLE 20 PERCENT option. C. Execute the UPDATE STATISTICS command on the table in question, specifying the WITH FULLSCAN option. D. Execute the UDPATE STATISTICS command on the table in question, specifying the WITH NORECOMPUTE option |
C. Correct: Executing the UPDATE STATISTICS command on the table specifying the WITH FULLSCAN option will completely recalculate all statistics.
|
|
|
ACID
|
atomicity
consistency isolation duration |
|
|
Objects That SQL Server Can Lock
|
RID - row identifier within a heap
KEY - key or range of keys in an index PAGE - full 8-KB page EXTENT - block eight pages HoBT - heap or balanced tree TABLE - entire table FILE - database file APPLICATION - defined by the app METADATA - any system metadata ALLOCATION UNI - internal unit used for storage data DATABASE - the entire DB |
|
|
shared (s) lock mode
|
used for read operation that do not change or update data
|
|
|
update (U) lock mode
|
used on resources that might be updated
|
|
|
Exclusive (X) lock mode
|
Used for data-modification operations such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
|
|
|
Intent lock mode
|
Used to establish a lock hierarchy. The types of intent locks are intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
|
|
|
Schema lock mode
|
Used when an operation dependent on the schema of a table is executing. The types of
schema locks are schema modification (Sch-M) and schema stability (Sch-S). |
|
|
Bulk Update (BU) lock mode
|
Used when bulk copying data into a table and the TABLOCK hint is specified.
|
|
|
Key-range lock mode
|
Protects the range of rows read by a query when using the serializable transaction isolation
level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again. |
|
|
Dirty read def
|
A dirty read occurs when a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed
|
|
|
Non-repeatable read
|
A non-repeatable read occurs when, during the course of a transaction, a row is retrieved twice and the values within the row differ between reads
|
|
|
Phantom read
|
A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second
query is different from that returned by the first. |
|
|
What cmd will you use to determine the isolation level for the current connection
|
DBCC USEROPTIONS
USE [AdventureWorks2012]; GO DBCC USEROPTIONS; GO |
|
|
RCSI abbr & def
|
Read Committed Snapshot Isolation
similar to snapshot isolation level in that it enables readers to read data without being blocked by locks that have acquired by writers but it does not require the user to ask for it explicitly by declaring special isolation level |
|
|
DMV can display a view of the locks currently being held by open transactions
|
sys.dm_tran_locks
|
|
|
DMV displays the open transactions at the database level
and this can be used to obtain correlation information for associated sessions and transactions |
sys.dm_tran_database_transactions
sys.dm_tran_session_transactions |
|
|
compiling or recompiling query execution plans can cause blocking. You can identify this type of blocking by examining the wait_resource column of
|
sys.dm_exec_requests
DMV for a value of ‘COMPILE’ or by examining the resource _subtype column of the sys.dm_tran_locks DMV for a value of ‘COMPILE’. |
|
|
The most common types of deadlocks are
|
conversion, writer-writer, reader-writer, and
cascading. |
|
|
Conversion deadlock def
|
Conversion deadlock occurs when two connections both own shared locks on a resource and they both try to convert their locks to exclusive locks.
|
|
|
Writer-writer deadlocks def
|
Writer-writer deadlocks are caused by resource ordering, so ensuring that all code accesses resources in the same order will remove the specific type of deadlock
|
|
|
Reader-writer deadlock
|
Reader-writer deadlocks are the most common.
Occur when both connections hold exclusive locks on different resources, and each tries to request a shared lock on the other connection's resource. |
|
|
Cascading deadlocks def
|
Cascading deadlocks occur when there are more than two connections invloded in the deadlock. Even after SQL Server ch=chooses a deadlock victim, another deadlock still exists.
|
|
|
Trace flag 1204
|
Trace flag 1204 exists for backward compatibility and writes deadlock information into the SQL Server Activity log in a text-based format.
|
|
|
Trace flag 1222
|
Trace flag 1222 is the newer version of the information capture; it is an XML-based output and is often easier to read
|
|
|
If you need more detailed information about deadlocks that are occurring, you can configure
a SQL trace to capture the |
Lock:Deadlock Chain, the Deadlock Graph Event, and the Lock:Deadlock Event
|
|
|
KILL user process requires what permissions
KILL stats job requires |
Kill user process - ALTER ANY CONNECTION perm
KILL stat jobs - VIEW SERVER STATE |
|
|
killing the process with the SPID of 57 is demonstrated in the following T-SQL code:
|
/* KILL { session ID | UOW } [ WITH STATUSONLY ] */
KILL 57; -- Determine the progress status of the KILL operation KILL 57 WITH STATUSONLY; --This is the progress report. spid 57: Transaction rollback in progress. Estimated rollback completion: 70% Estimated time left: 20 seconds. |
|
|
What is the default transaction isolation level in SQL Server 2012?
A. Serializable B. Read committed C. Repeatable read D. Snapshot |
b
|
|
|
What is the dynamic management view that records the total time that tasks are waiting for resource types?
A. sys.dm_tran_session_transactions B. sys.dm_tran_locks C. sys.dm_os_waiting_tasks D. sys.dm_os_wait_stats |
d
|
|
|
You suspect that blocking is occurring. What can you examine to determine where the blocking is occurring?
A. Examine the blocking_session_id of sys.dm_os_waiting_tasks. B. Examine the blocking_session_id of sys.dm_exec_requests. C. Examine the Procesess pane of Activity Monitor. D. Examine the blocked_process column of the system_health Extended Event Session. |
Correct Answers: A, B, C, and D
A. Correct: The blocking_session_id column of sys.dm_os_waiting_tasks enables you to determine where blocking is occurring. B. Correct: The blocking_session_id column of .sys.dm_exec_requests enables you to determine where blocking is occurring. C. Correct: The Processess Pane of Activity Monitor enables you to determine where blocking is occurring. D. Correct: The blocked_process column of the system_health Extended Events Session enables you to determine where blocking is occurring. |
|
|
You have determined that blocking is occurring due to an index rebuild operation. The server process id is SPID 57. You need to remove the blocking and must be able to give an estimate of the time remaining until the blocking is resolved. Which command
should you execute? A. KILL 57 WITH STATUSONLY; B. KILL 57; C. ROLLBACK TRANSACTON D. KILL ‘D5499C66-E398-45CA-BF7B-CD9CD104B48FD’ |
a
|
|
|
SQLAgentUserRole
|
least privileged
permission only on the local jobs and job schedules they own does not allow use of multi-server jobs |
|
|
SQLAgentReaderRole
|
all permissions assigned to SQLAgentUSerRole
view history and properties of jobs and schedules including multi-server jobs |
|
|
SQLAgentOperatorRole
|
all the permissions assigned to the SQLAgentReaderRol
executre,stop, or start all local jobs delete history disable schedule on all local jobs |
|
|
Of which fixed server role must the SQL Server Agent service account be a member?
|
The SQL Server Agent service account must be a member of the sysadmin fixed server role.
|
|
|
Alerts can be triggered by
|
event log events,
WMI events, or performance conditions |
|
|
You can also create alerts by executing
|
sp_add_alert stored procedure from the msdb database
|
|
|
sp to create a job
sp to add a job step to existing job |
sp_add_job - Enables you to create jobs
sp_add_jobstep - Adds a job step to an existing job |
|
|
sp to create a shceduke to be used with a job
sp attaches a job to existing schedule sp to add a job to a server |
sp_add_schedule - Creates a schedule that can be used by any job
sp_attach_schedule - Attaches an existing schedule to an existing job sp_add_jobserver - Adds a job to a server |
|
|
Operators def
|
Operators represent groups or individuals who can receive notifications from SQL Server Agent.
|
|
|
stored procedure on a target server to enlist the target server in a master server
|
sp_msx_enlist
|
|
|
Scheduled jobs owned by users who are not members of the local administrators group on server SYD-SQL-A fail. These users can create jobs. Jobs that are owned by administrators who are members of this group run normally. The SQL Server service on SQL-A uses a domain account named syd-sql-a-agnt. To which of the following domain groups should you add the syd-sql-a-agnt account to ensure that jobs owned
by users who are not members of the local administrators group on server SYD-SQL-A do not fail? A. Backup Operators B. Cryptographic Operators C. Performance Monitor Users D. Pre-Windows 2000 Compatible Access |
d
|
|
|
Rooslan needs to be able to create SQL Server Agent jobs and to manage the schedules of jobs that he has created. Rooslan should not have any permissions on the jobs of other users and should not be able to create or use multi-server jobs. To which of
the following roles should you add Rooslan’s account? A. SQLAgentUserRole B. SQLAgentReaderRole C. SQLAgentOperatorRole D. sysadmin |
a
|
|
|
You want a specific job to run when the number of transactions per second on a particular database exceeds 20. You have created the job. Which of the following must you also create to accomplish this goal?
A. Alert B. Operator C. Maintenance plan D. Proxy |
a
|
|
|
You must automate the process of running a Windows PowerShell script followed by an Integration Services package and then a system stored procedure. These items should run every day at 2 A.M. Which of the following would you configure to accomplish
this goal? A. Operator B. Alert C. Job D. Proxy |
c
|
|
|
full database backup def
|
full database backup includes all database objects, system tables, and data.
Transactions that occur during the backup are also recorded. Full database backups allow you to perform a complete restoration of the database as it existed at the time the backup operation is performed. |
|
|
differential backup def
|
backs up data that has altered since the last full backup.
less time than full backups transactions that occur during the backup process are recorded |
|
|
transactional log backups def
|
transaction log backup records the changes that have
occurred since the previous transaction log backup and then truncates the transaction log. A truncation removes transactions from the log that have been committed to the database or cancelled. |
|
|
File and filegroup backups def
|
File and filegroup backups back up individual database files and filegroups rather than performing a full database backup.
This method backs up very large databases. |
|
|
Copy-only backups def
|
Copy-only backups are functionally the same as full database or transaction log backups but do not affect the backup sequence.
|
|
|
You can use the following Transact-SQL statement to enable backup compression on an instance:
|
EXEC sys.sp_configure [backup compression default], 1
GO RECONFIGURE WITH OVERRIDE GO |
|
|
Which recovery models enable you to take transaction log backups?
|
The full and bulk-logged recovery models enable you to take transaction log backups.
|
|
|
When you have a mirroring session, you can back up only the
|
When you have a mirroring session, you can back up only the principal database.
You cannot back up the mirrored database in a mirroring session. |
|
|
How you back up replicas in an AlwaysOn availability group depends on the automated backup preference setting applied at the availability group level
|
Prefer Secondary
Secondary Only Primary Any Replica |
|
|
Prefer Secondary preference for backing up AlwaysOn replica
|
Prefer Secondary - This is the default. Backups should occur only on a secondary replica, which allows backups to be taken on the primary replica if that is the only replica online.
|
|
|
Secondary Only preference for backing up AlwaysOn replica
|
Secondary - Only All automatic backups occur on secondary replicas. Backups should not be taken if the primary replica is the only replica online.
|
|
|
Primary preference for backing up AlwaysOn replica
|
Primary - Backups should always be taken on the primary replica. This allows differential backups to be taken.
|
|
|
Any Replica Prefer Secondary
Secondary Only |
Any Replica - Backups can occur on any replica in the availability group. Automated backups use the backup priority setting applied in the preference when choosing a replica as the target for a backup.
|
|
|
database checkpoint def
|
database checkpoint writes all modified pages held in memory to disk and then records that information in the transaction log.
The Database Engine manages checkpoints automatically; usually, a checkpoint occurs every minute. |
|
|
You can alter the frequency of checkpoints at the database level by using the ________ cmd
|
ALTER DATABASE statement with the
SET TARGET_RECOVERY_TIME parameter. |
|
|
You can create a backup device from Transact-SQL by using the
|
sp_addumpdevice stored procedure
EXEC sp_addumpdevice 'disk', 'Alpha-Backup', 'c:\backup\alpha-backup.bak' |
|
|
media set def
|
media set can include a single file, multiple files, a tape, or multiple tapes.
A single media set can include up to 32 backup devices Cannot be a combination of tape and files |
|
|
To perform a differential backup of the AdventureWorks2012 database to the Alpha-Backup backup device, execute the following statement
|
BACKUP DATABASE [AdventureWorks2012] to [Alpha-Backup] WITH DIFFERENTIAL;
|
|
|
to get the backup name, backup finish time, and backup size, execute the following Transact-SQL statement
|
SELECT database_name, backup_finish_date, backup_size, type FROM msdb.dbo.backupset;
|
|
|
You perform a full backup daily at 1 A.M. and transaction log backups every half hour on a database. You want to perform a full backup of the database at 1 P.M. without affecting the existing restore sequence. Which of the following backup types should you perform to accomplish this goal?
A. Full backup B. Transaction log backup C. Differential backup D. Copy backup |
d
|
|
|
You perform a full backup daily at 1 A.M. You perform transaction log backups every 30 minutes. You want to back up all data that has changed since the last full backup. Which of the following backups should you perform?
A. Full backup B. Transaction log backup C. Differential backup D. Copy backup |
c
|
|
|
Which of the following system databases do you NOT need to back up because it is regenerated each time the Database Engine restarts?
A. master B. msdb C. tempdb D. model |
c
|
|
|
Which backup preference should you set for an AlwaysOn availability group if you want backups to occur on secondary replicas but which enables backups to occur on the primary replica if that replica is the only one online?
A. Prefer Secondary B. Secondary Only C. Primary D. Any Replica |
a
|
|
|
AlwaysOn Availability Groups and database mirrors support automatic page repair
|
if the error occurs on the primary database, the primary broadcasts a request to all secondaries and retrieves the page from the first to respond. You can
verify the status of automatic page repairs in the following manner: ■■ AlwaysOn Availability Groups Query the sys.dm_hadr_auto_page_repair dynamic management view. ■■ Database mirroring Query the sys.dm_db_mirroring_auto_page_repair dynamic management view. |
|
|
To rebuild the system databases, run the following command, preferably from the setup
|
setup /Q /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER
|
|
|
Whether you perform a restore operation depends on the current state of the database. You can check the status of a database by using the following statement
|
SELECT databasepropertyex ('databasename', 'Status');
|
|
|
You can check which users can access a database by using the following statement:
|
SELECT databasepropertyex ('databasename', 'UserAccess');
|
|
|
You can check whether the database is read/write or read-only by executing the following statement:
|
SELECT databasepropertyex ('databasename', 'Updateability');
|
|
|
RESTORE WITH NORECOVERY option
RESTORE WITH STANDBY option |
RESTORE WITH NORECOVERY option recovers the database, but the database cannot
be accessed. RESTORE WITH STANDBY option leaves the database in read-only mode. |
|
|
A database has become corrupted. You will restore the database, but you want to examine it prior to allowing client queries and updates. Which of the following restore options should you use?
A. RESTORE WITH RECOVERY B. RESTORE WITH NORECOVERY C. RESTORE WITH STANDBY D. RESTORE WITH KEEP_REPLICATION |
c
|
|
|
You are performing an online file restore. Which is the final step in this process?
A. Take a transaction log backup. B. Restore the most recent transaction log WITH STANDBY. C. Restore the most recent transaction log WITH RECOVERY. D. Restore the most recent transaction log WITH NORECOVERY. |
a
|
|
|
You take a full database backup every day at 6 A.M. Differential backups are taken at 10 A.M. and 3 P.M. Transaction log backups are taken on the half hour except when a full or differential backup is being taken. Database files and transaction log files are stored on the same volume. The volume hosting the database files fails at 10:15 A.M. You replace the disk. Which of the following backups will you use when performing a
restore operation? (Each correct answer presents part of a complete solution. Choose all that apply.) A. 6 A.M. full backup B. 10 A.M. differential backup C. 9.30 A.M. transaction log backup D. 10.30 A.M. transaction log backup |
a
b |
|
|
Which of the following system databases can you rebuild by using the setup.exe command?
(Each correct answer presents a complete solution. Choose all that apply.) A. msdb B. model C. tempdb D. master |
abd
|
|
|
You manage a server running SQL Server 2012. Your backup plan includes the following backups:
*weekly fully backup of the master, model, and msdb database *nightly full backup of all users *hourly transaction log backups of all user DBs Master, model, and msdb DBs are configured for the simple recovery model. The server fails and the SQL Server instance will not start. You need to recover SQL Server. What should you do? a)Rebuild the master DB b)Restore the master DB c)Backup the tail of the master DB transaction log d)Restore the msdb database |
b - You should rebuild the master DB. If SQL Server will not start, you cannot recover the master from backup
|
|
|
You create a DB named Inventory on a computer running MS SQL Server 2012. You create a name policy to require all table in the DB to begin with "Invtbl". You need to ensure that a table cannot be create that does not adhere to the naming policy, You need to set the evolution mode for the naming policy. What should you do?
a)Configure the policy for On schedule b)Configure the policy for change:log c)Configure the policy for On change:prevent d)Configure the policy for On demand |
c - On change:prevent. This is an automated evaluation mode that uses a DDL trigger to prevent naming a table with a name that does not adhere to the naming policy
On demand policy evaluates the policy only when specified by user. |
|
|
CLR function you created in the Inventory DB needs to access Win resouces when it runs. SQL Server should have access to those resources only in the context of the CLR function.
When you test the CLR function it fails because of insufficient permissions to access the necessary resource. You need to ensure that the CLR func has the necessary permissions to access the resources. The solution should have minimal impact on Windows and SQL Server security. What should you do? a)Use the SqlContext.WindowsIdentity in the CLR function manged code b)Create a Windows user account with permission to access the required resources c)Create a Windows user account and add the account to the local Administrator group d)Use EXECUTE AS when running the CLR func e)Add the SQL Server Windows service account to the local Administrator group |
ab
You need to create a Windows user account with permission to access the required resources and use the SQLCintext.WindowsIdentity in the CLR function managed code When you execute a CLR function, it runs under the security context of the SQL Server service account. |
|
|
You have configured a SQL Server Agent job to perform a set of routine maintenance tasks. You have configured each job step to log detailed information to a table. One of the job steps did not complete successfully. You need to view the detailed information logged by the job step.
What should you do? a)execute sp_help_jobsteplog b)execute sp_help_jobstep c)execute the sp_help_jobhistory d)execute so_help_jobacitvity |
a
The sp_help_jobsteplog stored procedure returns a result set that includes a column containg the inforamtion logged be eahc job steps |
|
|
You are preparing to install SQL Server 2012 on a DB server that is a member of the domain. The DB server must support the following requirements:
*exec stored procedures that access a linked servers *exec a job that performs routine maintenance tasks *execute a third-party application that runs under the Network Service account. You need to identitfy the sercvice account you will use. You must select the most secure option. What should you do? a)Use the Local Service account for SQL Server. Use the Network Service account for SQL Server Agent b)Use the same domain user account for SQL Server and SQL Server Agent service c)Use different domain user accounts for SQL Server and SQL Server Agent service. d)Use the Network Service account for SQL Server. Use the Local Service account for SQL Server Agent. |
c
You should use different domain user accounts for SQL Server and SQL Server Agent services. The SQL Server service needs to access a linked server. Therefore you must use a domain user account that has been granted the necessary permissions The SQL Server Agent service running under a domain account provides the best security. |
|
|
DMF policy has been created to audit security settings for the server, including ensuring that Database Mail is not enabled. The policy's evaluation mode is set to On demand.
You evaluate the policy and check the Event Viewer log. No violation entries appear. You check the status of Database Mail and find that is enabled. You need to ensure that events are logged to Event Viewer if a policy violation exists. What should you do? (two) a)Change the evaluation mode to On schedule b)Log on as a member to the Policy AdministratorRole role and evaluate the policy c)Log on as a member of the securityadmins role and evaluate the policy d)Log on as a member of sysadmin the evaluate the policy e)Change the evaluation mode to On change: log only. |
ad
Log on as a member of sysadmin and evaluate the policy. When a policy is evaluated manually, the user's security context is used Change the evaluation mode to On schedule. |
|
|
You install a default instance of MS SQL Server 2012 on a computer running Windows Server 2012. The computer is a member server in an AD domain. You specify a domain user account as the SQL Server service account. Every other Saturday, the DB server is shut down for periodic maintenance. After six weeks of operation, the SQL Server service is unable to restart. You review the error logs and suspect that the problem is related to the service account. You need to verify whether or not the problem is related to the service account. You need to correct the problem before start of business on Monday. What should you do first?
a)Run: sqlserver -m b)Use SQL Server Management Studio to check the server instance properties c)Use SQL Server Configuration Manager to configure Local System as the service account and restart SQL Server normally. d)Run: sqlserver -f |
c
use SQL Server Configuration Manager to configure Local System as the service account and restart SQL Server normally. There could be a problem with the service account that is keeping the account from being able to log on. IT might be that the account has been reconfigure or that the password has expired or been changed. By configuring Local System as the service account and restarting, you can be relatively sure that the problem is related to the service account. |
|
|
You want to configure a job. However, the SQL Server Agent node is not listed in SQL Server Management Studio. You verify that the SQL Server Agent service is started. You need to modify SQL Server so that you can manage jobs.
What should you do? a)Execute: sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'SMO and DMO XPs', 1; GO RECONFIGURE; GO b)Use SQL Server Config Manager to start the SQL Server Browser service c)Execute: sp_configure 'show advanced options' 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE; GO d)Use SQL Server Config Manager to config the SQL Server Agent service to run under the Local System account |
c
The SQL Server Agent extended stored procedures must be enabled for the SQL Server Agent node to appear ion SQL Server Management Studio. You can enable the extended stored procedures y running sp_configure 'Agent XPs', 1 |
|
|
You want to config support for an Inventory control application. The application requires three DB. The application has not yet gone live. The server hosts other user DB in addition to these 3. For the application to work properly, you need to establish ownership chains among the 3 tables. The DBs are already loaded with initial data. You need to keep the effort required to implement your solution to a minimum. The solution should have minimal impact on ongoing DB operation. Your solution must not impact the security of other DB hosted on the server. What to do?
a)Use sp_configure b)Use ALTER DATABASE c)Use DROP DATABASE and CREATE DATABASE d)Use GRANT |
b
You should use ALTER DATABASE to set the DB_CHAINING option for the 3 tables. This will enable each DB to be used as source or destination for cross-database ownership chaining, but does not make nay change to the remaining user databases/. This command will result in minimal interruption to normal operations, if any |
|
|
SAra owns a table named Employees and a stored procedure named sp_GetSalaries. The sp_GetSalaries stored procedure queries the Employee table. Sara create a view anmed v_DueReviews that calls sp_GetSalaries and filters the resutls based on employees who are approaching their annual reviews. Mike needs to be able to use the view.
You need to assign Mike the minimum permissions necessary to use the view. What should you do? a)Grant Mike the SELECT permission on v_DueReviews and hte EXECUTE permission on sp_GetSalaries. b)Grant Mike the SELECT permission on v_DueRevirws c)Grant Mike the SELECT permission on the Employees table d)Gran Mike hte SELECT permission on v_DueReviews and Employees and the EXECUTE permission on sp_GetSalaries. |
b
SELECT perm on v_DueReviews. Because Sara owns all the objects in the cahin, ownership chaining rules apply. Therefore you only need to grant Mike SELECT on the view -------------- Granting SELECT on Employees table will allow mike to view not only the data for the view but other also. Granting EXECUTE permission on sp_GetSalaries will allow Mike to see all salary information |
|
|
what kind of clustering key would you select for an OLTP environment
|
For an OLTP environment, a short, unique, and sequential clustering key might be the best choice
|
|
|
You enable C2 audit mode on the server. Several minutes after enabling C2 audit mode, the SQL Server service ***** itself down. You meed the SQL Server service running as quickly as possible. Your primary concern is to provide access to critical data via SQ: Server. Once you have accomplished this goal, you will try to determine what is wrong with C2 audting. What should you do?
a)run: slqserv -c b)run: slqserv -x c)run: slqserv -f d)run: slqserv -m |
c
-m - single user mode -x - disable several monitoring feature, but does not disable auditing -c - shortens the time needed to start SQL Server from the command line, but does not disable auditing |
|
|
You maintain a DB server running SQL Server 2012. The DB server has the default instance of SQL Server 2012 installed. You install an instance named InternetApp. The SQL Server (InternetApp) service account is configured to run under a domain user account. Cleint applications on different computers can connect to the defaul instance, but cannot to InternetApp. You vertify that SQL Server (InternetApp) is started. You need to resolve the problem. What should you do
a) Start the SQL Server Agent Service b)Configure the SQL Server (InternetApp) service to use the Local System account. c)Start the SQL Server Browser service d)Configure the SQL Server (InternetApp) service to use the Network Service account. |
C - The SQL Server Browser service is responsible for enumerating SQL Servers and allowing application to discover and connect to named instances without specifying a port number or pipe name.
You should not start the SQL Server Agent Service. The SQL Server Agent service is responsible for executing jobs. It is not required to connect to a named instance. |
|
|
Members of the HelpDesk department need to be able to reset password for SQL Server logins and terminate processes involved in a deadlock condition. You create a login associated with the HelpDesk group. You need to grant the minimum necessary permissions. What should you do?
a)Add the login as a member of the sysadmin fixed server role. b)Add the login as a memeber of the processadmin and securityadmin fixed server roles c)Add the login as a member of the serveradmin and securityadmin fixed server roles. d)Add the login as a member of the serveradmin fixed role. |
b
|
|
|
Counter min max
Process: working set 2 GB 3 GB SQL Server: Buffer Manager: Buffer Cache Hit Ratio 90 97 SQL Server: Mem.Manager: Total Server Memory (KB) 12 GB 16 GB You manage an application server that runs SQL Server 2012, as well as serveral other server applicaitons. The server has 15 GB RAM. SQL Server is configured with the following settings: Min server memory = 4GB Max server memory = 8GB Users report poor performance when accessing other applications. System monitor consistently shows the performance counter values as shown in the exhibit. You need to improve performance. You solution should require the last amount of hardware investment. What should you do? a)Add more RAM b)Decrease Max server memory to 4GB c)Decrease Min server memory to 3GB d)Increase Min server memory to 12GB |
c
You should decrease Min server memory to 3GB. SQL Server automatically utilizes the amount of memory specified by Min server memory even if it is not required. Therefore, the current config causes 1 GB of RAM to be wasted because SQL Server never actually uses more than 3 GB of RAM, as indicated by the Process: Workint Set Values. |
|
|
You manage a database. That is configured for the full recovery model. The following tasks are scheduled:
*Midnigh: full *4AM: DB snapshot *1PM: Differential *6PM: DB snapshot *Hourly: Transaction log backup The full and dif. backups use a different backup device than the transaction log backups. You deploy a new application at 2 PM. An error in the application causes a large table to be deleted at 2:30 PM. You attempt to restore from backup and find that the media containing the full and differential backups is damaged. You need to recover as much data as possible. What should you do? a)Backup the transaction log revert to the 4:00 am snapshot restore the transaction log backups b)Backup the transaction log revert to the 4:00 am snapshot restore the transaction log backups drop the 6:00 pm snapshot c)revert to the 4:00 am snapshot restore the transaction log backups d)Backup the transaction log drop the 6:00 pm snapshot revert to the 4:00 am snapshot |
d
When a problem occurs in a database, you can revert it to the state it was in when a DB snapshot was taken. you need to drop all other database snapshots before reverting. You should also backup the transaction log before reverting to the snapshot so that you can use the transaction log to manually recover data. |
|
|
A DB Product have productID between three and 10 characters. A number of products have the same measurement in one or more dimensions. Nearly 100 products have either a height or width of 10 inches. A clustered index is created on the producID column. The table has a very large number of rows. You want to reduce the amount of stored space required for the table. What will be the best compression option?
a)use row compression b)use page compression c)define all columns as sparse columns d)define productId as a sparse column |
b)
page compression first uses row compression to compress a row of data. It also uses dictionary compression for multiple instance of the same value. Because there are duplicate value in many of the fields, dictionary compression will help conserve space. |
|
|
The Products table data is imported monthly by using a bulk import. The table has a clustered index based on ProductName column. Several foreign key constraints reference the ProductName column. The bulk import operation tales a long time to complete. You plan to reduce the amount of time it takes to complete the bulk import operation by performing the operation from multiple client computers. You need to ensure that the import form one client computer does not block the import from another client computer. What should you do?
a)Drop the clustered index Perform the import by using a table lock Recreate the index b)Drop the clustered index Perform the import by using a page lock Recreate the index c)Drop all constraints Perform the import by using a table lock Recreate the index c)Drop all constraints Perform the import by using a page lock Recreate the index |
a
To ensure that the parallel import to not block each other you need to use a table lock. You can only perform a bulk import by using a table lock if ht table does not have an index. Therefore, you first need to drop the clustered index. |
|
|
SQL1 - publisher
SQL2 - distributor SQL3 - subscriber SQL4 - subscriber The four servers above are configured for merge replication. You are planning to upgrade all four servers to SQL Server 2012 over a period of four months. You will upgrade one server each month. You need to identify the first step in the upgrade process. What should you do? a)Upgrade SQL1 b)Upgrade SQL2 c)Run the snapshot agent for each publication d)Run the merge agent for each subscription |
b)
You should upgrade SQL2 first. The distributor must be running a version that is later than or the same as the publisher for the replication to succeed |
|
|
Users report that queries often time out or hang during peak period. You need to create an XML report that shows any resources and processes involved in deadlocks. What should you do?
a)Create a filter on the Locks by Object tab of Activity Monitor b)Create a filter on the Locks by the Process tab of Activity Monitor. c)Create a trace by using SQL Server Profiler. Select the Lock" Deadlock Chain event. d)Create a trace by using SQL Server Profiler. Select the Deadlock graph event |
d)
The deadlock graph event logs detailed information about the process and resources involved in a deadlock. You can choose to generate a separate XML file for this data on the Event Extractions Settings tab of Trace Porperties |
|
|
You currently create a database snapshot of SalesDB once a month. Sales analysts use the snapshot for reporting. They are most interested in analyzing sales trends across previous months. The serve's hard disk is nearing the storage capacity. You need to minimize the amount of storage used by the snapshot. What should you do?
a)Execute DBCC SHRINKDATABASE on the database snapshot b)Change the schedule to create a weekly snapshot c)Move the snapshot to a different server d)Change the schedule to create a bimonthly snapshot |
b
You should change the schedule to create a weekly snapshot. DB snapshots use sparse files. When a database is first created, all records point ot the database. Before a change is made, the page is written to the snapshot. This operation is called copy-on-write. The snapshot contains the previous version of each page that was changed since the snapshot was taken. This means that the snapshot file grows over time. Therefore, to conserve disk space, you should take the snapshot more frequently You should not change the schedule to create a bimonthly snapshot. Changing the snapshot to bimonthly snapshot will cause the file to use more disk space. |
|
|
You manage a server running SQL Server 2012. The server is configured to reorganize indexes nightly at 10 PM. Users report poor performance when accessing the DB between 10 PM and midnight. You analyze server activity and find that traffic patterns are irregular. You need to modify the configuration so that index optimization affects users as little as possible. What should you do?
a)Change the job to ALTER INDEX REBUILD WITH ONLINE = ON b)Add a job step to set the recovery model to bulk-logged as the first job step c)Configure a CPU idle condition for the job d)Change the job to use DBCC INDEXDEFRAG |
c
The CPU idle condition allows you to define a threshold and a duration during which usage must be under the threshold before the job can be started, thus allowing the job to run during low traffic times. |
|
|
You are attempting to troubleshoot an intermittent server problem. You need to view the contents of the current SQL Server error log. What should you do?
a) Open \PRogram Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\log_1.trc b)Launch Windows Event Viewer c)Launch SQL Server Configuration manager d)Open Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\ERRORLOG |
d
This is the defaul location for the SQL error log. The most recent error log file will have no extension. The next most recent will be named ERRORLOG.1 and so on. Event Viewer lets you view the contents of the Windows event logs, but not the SQL Error log. |
|
|
A client application cannot connect to a named instance. You are able to connect to the instance by using SQL Server Management Studio and specifying port. You need to configure SQL Server to allow the client application to connect without specifying a port number. Your solutions should provide the best possible secuiry. What should you do?
a)Configure the SQL Server Browser service to start automatically using the Local System account. b)Configure each instance of the SQL Service to start automatically using the same domain user account. c)Configure the SQL Server Browser service to start automatically using the Local Service account, d)Configure each instance of the SQL Server service to start automatically using the Local System Account |
c
The SQL Server Browser service is used to allow connections to locate an instance of SQL Server that uses a non-default port number. The SQL Server Browser service can run under the local service account, whic h is an account with minimum permissions. |
|
|
You install SQL Server 2012 on a server. You install the DB engine and the client tools. After the installation completes, no client tools are available. You need to determine the name of the file were detailed info about the installation failure is logged. What should you do?
a)Review the errorlog.txt file b)review the systemconfigurationcheck_report.html file c)review the details.txt file d)review the sumarry.txt file |
d
Summary.txt file provides an overview of the installation process, including whether installation for each component succeeded or failed and the name of the log file generated by each component's msi file. |
|
|
MapDB database has a table named Maps that contains a column named MapInfo of type geography, a column named Description of type varchar(50), and a MapID column of type char(10). You need to optimize queries that use the STDistance function. What should you do?
a) Create a spatial index on the MapID and MapInfo columns. b) Create a clustered index on the MapInfo, Description, and MapID columns c) Create a clustered index on the MapID column d) Specify the MapID column as the primary key. create a spatial index on the MapInfo column. |
d
The geography data type is a spatial data type. The STDistance function is a geography function that can benefit from a spatial index. A spatial index an be based on only a single column of a spatial data type. The table must have a primary key before you can create a spatial index |
|
|
You need to ensure that you can balance the load among the database instances. You need to ensure that changes made to any instance are replicated to all instances. Latency must be kept to a minimum. Incremental traffic relating to replication should also be kept to a minimum. Each change made t oa database must be applied individually to other database in the configuration. What should you do?
a)Configure merge replication b)Configure peer-to-peer transnational application c)Configure snapshot replication d)Configure transactional replication |
b
Peer-to-peer transaction replication allows for a load balancing among the nodes.Changes made to any replication node are replicated |
|
|
You need to import data from a server running SQL Server 2008. You will import the data by using the OPENROWSET function. You need to prepare SQL Server 2012 to import the data. What should you do?
a)Install SSIS b)Set the Remote Access advanced configuration option On c)Add the SQL Server 2008 instance as a linked server d)Set the Ad Hoc Distributed Queries advanced configuration option on. |
d
You should set the Ad Hoc Distributed Queries advanced configuration option On. OPENROWSET is a function that is used to perform ad hoc queries based on a remote data source can only execute if the Ad Hoc Distributed Queries advanced options is set to on. |
|
|
Users report performance problems at specific times of the day. They report that some queries also time out. You need to view information about threads that have been waiting for resources since the last time SQL Server restarted. What should you do?
a)Query sp_locks b)Query sys.dm_os_threads c)Query sys.dm_os_waiting_tasks d)Query sys.dm_os_wait_stats |
d
You should query sys.dm_os_wait_stats. The sus.dm_os_wait_stats dmv returns historical information about threads that have waited since the last SQL Server restart. |
|
|
The server includes a DB named SalesData used to support a retail point-of-sale application. Users compilation of system response problems when sales activity is especially heavy. You need to collect information about transaction locks at various times during the day. You want to generate a snapshot of lock activity when the information is collected. You need to collect the information manually so that you can base the collocation times on current store activity. What should you do? (choose 2)
a)Use SQL Trace b)Usa sys.dm_tran_Active_snapshot_database_transactions c)Use sp_lock d)Use SQL profiler e)Use sys.dm_tran_locks f)Use sys.dm_os_waiting_tasks |
c
e |
|
|
You are planning to decommission the server where SalesDB is located. You move the SalesDB to a server running SQL Server 2012 using the Copy Database Wizard. You need to ensure that queries performed against the SalesDB execute optimally. What should you do?
a)execute sp_updatestats b)reorganize all indexes c)Executes DBCC CHECKDB d)Rebuild all indexes |
a
|
|
|
A newly deployed application is causing degraded performance. You suspect the problem is deadlocking during transaction processing. You need to be informed when deadlocks occur so that you can observer the problem more direct and want to be paged when there are more than four concurrent deadlocks. What should you do?
a)Create an Event Notification b)Create a Window System Monitor alert c)Create a SQL Server Agent performance condition alert d)Create a SQL Server Agent event alert |
c |
|
|
You manage several servers running SQL Server 212. ServerB is configured as a secondary log sipping server for ServerA. You plan to take ServerA down for maintenance. You need to failover to ServerB as quickly as possible. What should you do? A)Disable the log shipping backup job on ServerA B)Backup the transaction log on ServerA with the NORECOVER option C)Backup the DB on ServerA with the COPY ONLY option D)Copy the backups to serverB and restore them wITH RECOVERY |
B)
You should backup the transaction log on ServerA with the NORECOVERY option. Backing up with NORECOVERY backups the tai of the tranasaction log and allows |
|
|
You configure two instances of MS SQL Server 2012 in a failover config. Both instances are on computers running Win Server 2012 as member servers in the AD domain. Your use SQL Server Agent service is no longer available. What should you do? A) Use SQL Server Configuration Manager B) Use Windows Cluster Administrator C) Use SQL Server Management studio D) Use the Windows |
b) You should use Windows Cluster Administrator. Your are prompted to restart SQL Server after using SQL Server Configuration Manager to change the service account. After restarting fill0text search and SQL Server Agent are not brought back online automatically. You must use Windows Cluster Administrator to bring these services online |
|
|
You manage a MS SQL Server 2012. the The server includes a DB named SalesData used to support retail POS app. Users complain of system response problems when sales activity is especially heavy. You need to collect info about transaction locks at various times during the day. You want to generate a snapshot of lock activity when the information is collected. You need to collect the info manually so that you can base the collection times on current store activity. What should you do(2)? a) use sp_lock b) use sys.dm_tran_lock c) use sys.dm_tran _active_snapshot_database_Transaction d) use SQL Trace e) use sys.dm_os_waiting_tasks f) use sql profiler |
a & b You should use the sys.dm_tran_locks dynamic management view or sp_lock system stored procedure. Both provide lock activity, including a list of currently active locks when the data is retrieved. MS recommends using sys.dm_tran_locks --------------------------- Should not use SQL Trace or SQL Profilers. Both can provide info about lock activity, but are used to collect data over time, not to provide a snapshot activity Should not use sys.dm_os_waiting_tasks - this DMV returns info about tasks that are waiting for resources, but does not return lock info. Should not use sys.dm_tran_active_snapshot_database_transaction. This dmv view reports info about active transactions, but not about active lcoks |
|
|
You manage a server running SQL Server 2012. Your backup plan includes the following backups: 1)A weekly full backup of the master, model, and msdb DBs 2)A nightly full backup of all user DBs 3)Hourly transaction log backups of all user DBs Master, model, and msdb DBs are config for simple recovery model. The server fails and the SQL Server instance will not start. You need to recover SQL Server. What should you do? 1)Restored the master DB. 2)Rebuild the master DB. 3)Restore the msdb database. 4)Backup up the tail of the master DB transaction log |
b) you should rebuild the master DB. If SQL Server will not start, you cannot recover the master from backup. you should not restore the master/msdb DB. You must be able to start SQL Server to restore a DB, including the master DB |
|
|
You manage MS SQL Server 2012. You config two instances running on different computers for DB mirroring. The mirror is config for automatic failover. An error occurs in the principal DB in the mirror pair. Transaction safety is set to FULL. The mirror DB is SYNCHRONIZING over to the mirror as quickly as possible. Data loss, if unavoidable, is acceptable during failover. What should you do? A)Initiate a forced service failover. B)Replace the witness server. C)Set transaction safety to OFF. D)Initiate a manual failver. |
A) You need to initiate a manual failover. This is the only failover method available if the mirror is not fully synchronized with the principle. You know that is not synchronuzed because of the SYNCHRONIZING status. |
|
|
You manager a server running SQL Server 2012. SQL Server is configured to use mixed-mode authentication. Member of the HelpDesk department need to be able to reset passwords for SQL Server logins and terminate processes involved in a deadlock condition. You create a login associated with the HelpDesk group. What should you do? A)Add the login as a member of the sysadmin fixed server role. B)Add the login as a member of the serveradmin and securitadmin fixed server role. C)Add the login as a member of the serveradmin fixed server role. D)Add the login as a member of the processadmin and securityadmin fixed server roles. |
B |
|
|
You maintain a DB named POSdata on a def. instance of SQL Server 2012. The DB support an online retail application and in-house sales app. POSData includes a table named OrderDetails that is update and queried frequently through the day. Performance of any operations relating to the OrderDetails table has been degrading over time. You determine that the index is severely fragmented. You need to restructure the index to remove fragmentation. Users and online customers must have access to the table during the procedure. What should you do? A)Use ALTER INDEX with the REORGANIZE clause B)Use DBCC SQLPERF C)Use DBCC DBREINDEX D)Use DBCC CLEANTABLE E)Use DBCC INDEXDEFRAG |
ae ALTER INDEX with the REORGANIZE or DBCC INDEXDEFRAG. Either option will defragment the index. Both run as online operations. ALTER INDEX with REORGANIZE is the recommended method
DBCC DBREINDEX should not be used. This will rebuild the table indexes, defragmenting them in the process. This is also offline opertaion. |
|
|
You configure two instances of Microsoft SQL Server 2012 in a failover cluster configuration. Both instances are on computers running Windows Server 2012 configured as member servers in an Active Directory domain. You use SQL Server Configuration Manager to change the service account for both instances. After changing the service accounts and restarting the instances, the SQL Server Agent service is no longer available. What should you do? |
b)You should use Windows Cluster Administrator. You are prompted to restart SQL Server after using SQL Server Configuration Manager to change the service account. After restarting, full-text search and SQL Server Agent are not brought bads online automatically. You must use Windows Cluster Administrator to bring these services online. You should not use SQL Server Configuration Manager. Configuration Manager cannot be used to bring SQL Server Agent online in a failover cluster configuration. You should not use SQL Server Management Studio. Management Studio can be used to manage SQL Server Agent properties, but not to bring the agent back online in a cluster configuration. You should not use the Services utility. It cannot be used to bring SQL Server Agent back online after changing the SQL Server service account. You also should not use the Services utility to modify the services account, as this can cause the cluster to fail. You must use SQL Server Configuration Manager for that purpose. |
|
|
You manage two default instances of Microsoft SQL Server 2012 on computers named MainSery and AllServ. You configure log shipping between the two servers for the database Inventory. The primary database is on MainSery and the secondary is on AllServ. You need to take MainSery offline to perform extensive maintenance. You need to minimize the interruption to normal operations while this maintenance takes place. You need to fail over to the secondary copy of the database. The primary copy is still available. What should you do first? |
c) You should copy any uncopied transaction log backups to the destination folder. Before you can fail over to the secondary database, you must ensure that it is synchronized with the primary database. The first step in this is to copy any uncopied transaction log backups to the destination folder on the secondary server. You can then apply the transaction logs to the secondary server. You would then back up the primary database using the WITH NORECOVERY option, leaving it unavailable, and restore that backup to the secondary database. You should not apply any unapplied transaction log backups in the destination folder. Before you do this, you need to copy any uncopied backups from the shared backup folder to the secondary servers destination folder.
|
|
|
You maintain a database named OrderDB on a server running SQL Server 2012. |
d) You should execute the ALTER INDEX DC_Orders ON OrderDB.Orders REBUILD WITH ONLINE=ON command. The index is 40% fragmented, so it should be rebuilt. Because the table does not have any large object (LOB) data types, it can be rebuilt online to limit the impact on availability.
|
|
|
You have created multiple alerts designed to notify you by e-mail if various conditions occur. These include both event and performance condition alerts. While trying to correct an intermittent problem, you notice that you stop receiving event alert notification. You continue to receive performance condition alert notifications. You force an event to verify this. You need to correct the problem so that you can receive event alert notifications. What should you do? A) Clear the Windows Application Event Log B) Clear the Windows System Event Log. C) Stop and restart the SQL Server Agent service. D) Drop and recreate your Operator obj |
a) You should clear the Windows Application event log. SQL Server Agent reads from the Windwos Application Event Log to generate SQL Server Agent alerts. If the log is full Windows cannot write events to the log. If the events are not written to the Application Event log, the SQL Server Agent alert is never generated. By clearing the log contents, Windows can write events to the Application Event Log. |
|
|
you administer a reference database on a computer running MS Win Server 2008 R2 and SQL Server 2012. The computer has once hard disk configured with two volumes. ProgVol contains the operating system and server applications. The DataVol volume contains the reference database. The reference database is a read-only database. You need to ensure that you can recover the computer as quickly as possible after critical system failure, such as a hard disk failure. You need to minimize the administrative effort needed to protect and recover the computer. What should you do? A) Use Windows backup to run a full backup and periodic differential backups of both volumes. B) Use SQL Server to run a full backup of all databases any time changes are made. C) Use Windows backup to run a full backup of both volumes any time changes are made. D) Use SQL Server to run a full backup and periodic differential backups of all databases. |
c)
You should use Windows backup to run a full backup of both volumes any changes are made. This will enable you to quickly recover programs and data after a critical failure. Because the DB server hosts a read-only database, changes to the system will likely be rare and intermittent. By using a full backup, you only need to recover from one backup |
|
|
replications DMVs |
sys.dm_repl_articles - info about articles being published
sys.dm_repl_schemas - info about each table and column being publsihed
sys.dm_repl_tranhash - info about each transaction in a transactional repl
sys.dm_repl_traninfo - info about each transaction in a transactional repl |
|
|
there are five ways you can get the estimated execution plan |
SET SHOWPLAN_TEXT SET SHOWPLAT_ALL SET SHOWPLAN_XML Graph interface in SSMS SQL Trace |
|
|
A DMV for trackign resource pol affinity |
ssys.dm_resource_governor_resource_pool_affinity |
