Long-Term Learning
Learn efficiently and remember over time.
Start Long-Term Learning
Add this set to a folder
File system, filegroups
In the ____, in files, and often group into ____ is how databases are stored
SQL Server Configuration Manager
Used to manage the SQL server instances and set up the clients/client protocols.
Event class, trace
____ instances that you can monitor in the ____ process are what captured events in the Database Engine become.
SQL Server Profiler
Used to utilize captured traces, and capture and save data to analyze later. Can also create templates to define event classes and data columns
Standard Template
Captures all the stored procedures and T-SQL batches that are run. Used to monitor the general database server activities
TSQL Template
Captures all T-SQL statements submitted by clients and the time issued. Used for debugging client applications.
TSQL_Duration Template
Capture statements submitted and their execution time. Used to identify the slower queries.
Standard, TSQL, TSQL_Duration, TSQL_Grouped, TSQL_SPs
____ are the predefined templates for SQL Server Profiler (5)
TSQL_Grouped Template
Captures all statements submitted with information grouped by the client that submitted it. Use it to investigate queries from specific clients.
TSQL_SPs Template
Captures detailed information about the executing stored procedures. Used to get information about stored procedures that are executed.
Right-click, Properties
____ on the object and select ____ to get information about an objects properties in Management Studio.
sysadmin,
serveradmin
securityadmin
processadmin
setupadmin
bulkadmin
diskadmin
dbcreator
public
____ are the fixed Server Level Roles provided by SQL Server. (9)
serveradmin role
Granted the permission to change server-wide configuration options and shut down the server
securityadmin role
Granted the permission to manage logins and their properties, as well as GRANT, DENY, and REVOKE permissions at server-level, and if they have access to database-level permissions. They can reset passwords
dbcreator role
Granted the permission to create, alter, drop and restore any database. They cannot alter others databases.
Management Studio, CREATE LOGIN
The ____ or ____ statement are two ways to configure logins and their passwords.
migrating, server
The HASHED argument should be used only when ____ databases from one ____ to another.
MUST_CHANGE option
Has the server prompt the user for a new password on their first login with that password
CHECK_POLICY option
Says the Windows password policies of the local server computer should be enforced on this particular login.
User must change password at next login
Enforce password expiration
Enforce password policy
____ are the optional password policies available. (3)
Older applications, third-party, mixed operating systems
If you need to provide support for ____, ____ applications, or if you have ____ in place is when you would use SQL Server Authentication.
Authentication, user session
After ____ is finished and before the ____ is established is when logon triggers fire.
One, Browser
You need ____ SQL Server ____ for all SQL Server instances installed on the same server.
Configuration values, -f
When there a is particular ____ preventing the server from starting is when you most often use the ____ option.
-g option
Used to specify the size of memory that SQL Server can leave available for allocations within the process outside of the Server memory pool
Autocommit mode
A mode that means every T-SQL statement will be committed or rolled back when it is completed.
BEGIN TRANSACTION, SET IMPLICIT_TRANSACTIONS
____ and ____ are the two statements that can end Autocommit mode.
COMMIT TRANSACTION
The ____ statement, practically, marks the end of a successful implicit or explicit transaction.
ROLLBACK TRANSACTION statement
Removes all data modifications made and rolls back to the beginning of the transaction
Does not, implicit
Another statement ____ need to be written to mark the start of a new ____ transaction.
Resource Governor
A feature you can use to specify limits on resource consumption by incoming requests
ALTER RESOURCE GOVERNOR RECONFIGURE, Management node
Using the ____ statement or Right-clicking on it in the ____ of the Management Studio and selecting Enable are the two ways can you enable the Resource Governor.
Workload Groups
Are containers for session requests with similar or identical classification criteria.
CREATE WORKLOAD GROUP, ALTER RESOURCE GOVERNOR RECONFIGURE
____ and ____ are the two T-SQL statements that are needed to make a new workload group,
100
The sums of the minimum CPU and minimum memory percentages for all available resource polls cannot go over ____ percent.
CREATE RESOURCE POOL, ALTER RESOURCE GOVERNOR RECONFIGURE
____ then ____ are the two T-SQL statements that are needed for creating resource pools.
View, Resource Governor
You can reconfigure Resource Governor in Management Studio Under ____ - Template Explorer - ____ - Configure Resource Governor.
Transaction Isolation Levels
Definitions of the types of locks that can be acquired on read operations.
READ UNCOMMITTED level
Will not issue shared locks. They are not blocked by exclusive locks, and it means dirty reads.
REPEATABLE READ
Statements cannot read data modified but not yet committed and no other transactions can modify data that has been read until current transaction is completed.
SNAPSHOT
A transaction can only recognize data modifications committed prior to the start of the transaction. Modifications made after the start will be invisible.
SQL Server Agent, automation
You would use ____ for database ____. Designed for processing alerts, and running scheduled jobs. Can also send out relevant alerts and notifications through email
Database, SMTP
To set up ____ Mail you would configure one or more databases as mail hosts and then define client settings so SQL Server Agent can send message via ____ mail server
RECONFIGURE statement
The ____ is how you update current configured values that have been changed with sp_configure.
sp_configure, all, GUI, most commonly
The difference between the Management Studio GUI and sp_configure is while ____ allows access to ____ options the ____ contains the ____ used server configuration options.
RECONFIGURE or RECONFIGURE WITH OVERRIDE statements
The affinity mask can be configured without a restart with the ____.
ALTER SERVER CONFIGURATION, PROCESS AFFINITY
You can configure processor affinity options in SQL Server 2012 by using the ____ statement and then the ____ argument.