70-462 Flashcards
What are the versions of SQL Server
Premium - Parallel Data Warehouse and Enterprise
Core -
Other - Developer, Express
Tablular Data Stream TDS protocol
?
SQL Native Access Client (SNAC)
?
Security user options
Window Logins, Window Groups, SQL Server Logins
How do you access things on the master database?
Views and other db tools
What are Named pipes?
?
Parallelism
?
MAXDOP
? Max degree of parallelism
32 bit v. 64 bit
32 bit - 4GB of memory
64 bit - 8TB
You can be on a 64 bit machine with a 32 bit license…it runs like 64 bit.
Buffer Pool
The main membry object of SQL Server
- It holds data cache
- Provides memory for other SQL Server components
- Is divided into 8K pages
Physical I/O
?
Logical I/O
?
What does Maximum worker threads = 0 mean?
It means there are unlimited thread, you can change it to an actual number to limit the threads.
SQLIOSIM
-Unsupported but provided by Microsoft
-Can be run through command line or GUI
???
SQLIO
-Unsupported but provided by Microsoft
??
What are primary and secondary data files?
?
Transactions logs
?
tempdb
?
Windows Collation
based on the rules for the associated windows locale
SQL Server Collations
?
Default Collation and Sort Rules
?
ServerProperty(‘Collation’)
?
DatabasePropertyEX(‘tempdb’,’Collation’)
?
Layout the 4 parts of Collate
Latin1_General_CI_AS
?
Changing Collation means you cannot actually compare two columns with = you will get an error
?
What is the order to install SQL Server by command line?
?
What is the order to Upgrade SQL Server by command line?
?
What is ndf file?
These are secondary data files, they should be stored on a second hard drive.
Extent
8 contiguous 8kb pages
How big is a page?
8kb
What are master system databases?
Stores all system-level configuration
What is msdb?
Holds SQL Server Agent configuration including job, backup and restore history.
What is model system database?
Is the template for new databases
What is tempdb system database?
Holds temp data like temp tables, temp variables, table variagbles, hash tables, row version sotre
Microsoft recommends putting this on it’s own hard drive.
When you restart the SQL server it clears out the tempdb.
What is resource system database?
Hidden read-only database that contains system objects that are mapped to the sys schema of databases.
Why could a memory bottleneck lead to higher tempdb usage?
?
Create db statement
?
Database option - Auto Options
?
Database option - Page Verify
?
Database option - Recovery Model
?
Database option - State Options
?
sys.database_files
?
Alter database
?
Turstworthy
?
Broker_Enabled
?
Recovery Model - Simple
?
Recovery Model - Full
?
Recovery Model - Bulk Logged
?
Backup types - Full
?
Backup types - Differential
?
Backup types - Partial
?
Backup types - Transaction Log
?
Backup types - ??? there are more
?
Taillog backup?
Continue_After_Error
With Init v NoInit
?
restore labelonly
?
restore headeronly
?
restore filelistonly
?
restore headeronly from disk = ‘[path]’
?
restore filelistonly from disk = ‘[path]’
?
restore verifyonly from disk = ‘[path]’
?
Do not use this as a substitute for testing the actual backup.
restore labelonly from disk = ‘[path]’
?
Stripped set
Familycount
familysequencenumber
Striped set
?
SpaceSavings=(Uncompressed size–Compressed size)*100/Uncompressed size
?
Restore phases
data copy
redo
undo
StopAtMark
?
STOPAT
?
ETL
Data Transfer steps - Extract, Transform, Load
Check_Policy
?
SQLAgentUserRole
Control permission for jobs and schedules that they own.
SQLAgentReaderRole
All permissions of the SQLAgentUserRole plus permission to view the list of all the available jobs and job schedules.
SQLAgentOperatorRole
Permission to manage all local jobs, view properties for operators and proxies and enumerate available proxies and alerts (they cannot manage them only view)
sysadmin
?
DBCC CheckDB
?
What are the CheckDB options?
Physical_Only NoIndex Extended_Logical_Checks Tablock All_ErrorMsgs No_InfoMsgs EstimateOnly
What the difference between rebuild versus reorganize an index?
?
