Named instance - To log in you must have the host and instance name, and the SQL browser returns the port the named instance is listening on.
Default instance - Only one can be installed, to login with host name over port 1433.
What are the 4 included SQL system databases?
Describe the Master DB
Contains all settings and information specific to the SQL server instance on which it resides. The SQL server logs, log in information, and security information live here.
Describe the MSDB Database
Contains settings and information pertaining to the SQL server agent. Contains the log on credentials used by the agent, as well as all settings, scheduled jobs, maintenance plans, and SQL server agent log
Describe the Model DB database
Best described as the "template" database. Every new DB created (unless otherwise specified) is the exact copy of the model DB
Describe the TempDB database
Best described as the "scratch paper" for SQL server. All recently executed query results are stored here. When the SQL server service is stopped, the tempDB is dumped.
What is a SQL server agent?
Runs all tasks and commands that are done automatically.
What is the ACID model?
Atomicity - If one fails all fail
Consistancy - Data written must be vaild
Isolation - Transactions will execute seperatly
Durability - When a transaction is done, it stays that way
Describe the CRUD operations.
Create, Read, Update, Delete.
What is the -sql version of the CRUD operations.
Insert, Select, Update, Delete.
Name the 2 keys used in SQL
Primary Key - unique data set in a given table
Foreign Key - unique data set in a different table
Describe the SQL server browser
Listens for incoming requests for Microsoft SQL server resources and provides information about SQL server instances installed on the computer
What are the 3 File types used in SQL
.mdf - Main data files for SQL databases. Contains the actual tables. columns, and rows.
.ndf - Secondary data files. Created when the MDF becomes to large.
.ldf - Log data file. Contains log files for transactions
What is the initial file size?
The size of the file when it was created
What two aspects is file growth handled?
% and MBs
For best practice, what is the best file system type SQL data should be placed on?
64k allocation unit size
What is auto growth?
Lets the file keep growing after it uses up its allocated space
What is the difference between logins, and users?
Logins are mainly used for admin work where users are mainly used to access SQL databases and/or resources therein
Describe the SA account.
System Administrator Account for SQL server. Has all rights a permissions for SQL server.
Describe a Maintenance plan.
Set of tasks that are configured to execute when a set of conditions are reached.
Describe a scheduled job.
Single set of tasks that can be set to execute by the SQL server agent.
Describe a Shrink File operation
Shrinks a file to only what it is using
Describe Auto Shrink
Keeps the file true all of the time. Does not allocate any space
What 2 files do all Databases have?
Describe "Recovery Models"
Is a method of how the transaction log and main data log work together.
What are the 3 types of recovery models?
Describe the Simple Recovery Model
At the check point all final changes in the .ldf file are sent to the .mdf file
Describe the Full Recovery Model
At check point nothing happens. You will need a maintenance plan to execute a "transaction log backup".
All final changes are committed to the .mdf
transaction log gets truncated
Describe a Bulk Logged Recovery Model
Nothing happens at check points. Allows multiple files to have one Logical Sequence number.
Describe a Transaction Back up Log.
Saves all information in the transaction log to a separate log.
Describe a Logical Sequence Number (LSN)
Every transaction that happens in the log gets its own LSN.
What is the best practice for setting up your tempDB
Set the number of files in your tempDB to the number of CPU cores present on the machine, up to 8
In best practice, how should you set the RAM
Set the amount of RAM SQL can use to at least 2GB below the system max.
Data/Character set used by a data base
Describe Auto Close
When there are no open connections to the database, it is closed, deleting it from cache, and dumps the "Query Plan Optimizer Cache"
What is the "Query Plan Optimizer Cache"?
Strategies for how to best execute queries against a data base.
Describe what "sp_who2" will do
Sp = stored procedures, which are a group of stored commands that are executable in SQL
Command will execute the stored procedure "who2"
Roles are what give permissions to users.
What is the 3 job operation that is accomplished by setting up a maintenance plan?
Describe the 3 steps of Log Shipping
1. Transaction log backup of source database
2. Copy transaction log backup to destination server
3. Restore the transaction log backup to destination WITH_NORECOVERY
Rolls back any uncommitted transactions bringing database online for use
Uncommitted transactions unaffected, data base left in "restoring" mode, allowing more transaction logs to be restored to the database
6 steps of fixing log stripping
1. Take full backup of source database
2. Copy backup to destination server, and restore to destination instance WITH_NORECOVERY
3. Take t-log backup of source database
4. Copy to destination server
5. Restore to destination database WITH_NORECOVERY
6. Run your log shipping again
Involves 2 instances of SQL. A database is configured specifically for mirroring on the source server and is called the principle database. Another database is created on the destination instances called the mirror.
What are the 2 types of mirroring
Synchronous - Changes to the principle DB are coppied to the mirror site and confirmed before they are committed. Requires a low latency form of communication between the 2 instances. And they will always match exactly.
Asynchronous - Changes are made on the principal database and then transmitted to the mirror and committed. Disparity of data can occur.
What does Asynchronous need to be capable of automatic fail over
Must be accompanied by a "witness" located on a third instance.
Describe Windows Fail over Clustering.
SQL resources are presented to and installed on multiple servers. These servers have the ability to move the instance between then in the event that a hardware failure occurs.
What are the 3 different types of Replication and Disaster Recovery.