1. What does SQL stand for
    Structured query language
  2. What would SQL be best described as?
    A relational database (RDBMS)
  3. What are SQL relations formed by?
  4. What is a SQL key?
    A unique set of information
  5. What language does SQL use?
    t-sql code
  6. What is a SQL instance?
    An installation of of SQL server
  7. What are the 2 types of SQL instances?
    • 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.
  8. What are the 4 included SQL system databases?
    • Master
    • Model
    • MSDB
    • TempDB
  9. 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.
  10. 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
  11. 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
  12. 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.
  13. What is a SQL server agent?
    Runs all tasks and commands that are done automatically.
  14. 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
  15. Describe the CRUD operations.
    Create, Read, Update, Delete.
  16. What is the -sql version of the CRUD operations.
    Insert, Select, Update, Delete.
  17. 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
  18. 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
  19. 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
  20. What is the initial file size?
    The size of the file when it was created
  21. What two aspects is file growth handled?
    % and MBs
  22. For best practice, what is the best file system type SQL data should be placed on?
    64k allocation unit size
  23. What is auto growth?
    Lets the file keep growing after it uses up its allocated space
  24. 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
  25. Describe the SA account.
    System Administrator Account for SQL server. Has all rights a permissions for SQL server.
  26. Describe a Maintenance plan.
    Set of tasks that are configured to execute when a set of conditions are reached.
  27. Describe a scheduled job.
    Single set of tasks that can be set to execute by the SQL server agent.
  28. Describe a Shrink File operation
    Shrinks a file to only what it is using
  29. Describe Auto Shrink
    Keeps the file true all of the time.  Does not allocate any space
  30. What 2 files do all Databases have?
    • .mdf
    • .ldf
  31. Describe "Recovery Models"
    Is a method of how the transaction log and main data log work together.
  32. What are the 3 types of recovery models?
    • Simple
    • Full
    • Bulk-Logging
  33. Describe the Simple Recovery Model
    At the check point all final changes in the .ldf file are sent to the .mdf file
  34. 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
  35. Describe a Bulk Logged Recovery Model
    Nothing happens at check points.  Allows multiple files to have one Logical Sequence number.
  36. Describe a Transaction Back up Log.
    Saves all information in the transaction log to a separate log.
  37. Describe a Logical Sequence Number (LSN)
    Every transaction that happens in the log gets its own LSN.
  38. 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
  39. 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.
  40. Describe Collation
    Data/Character set used by a data base
  41. 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"
  42. What is the "Query Plan Optimizer Cache"?
    Strategies for how to best execute queries against a data base.
  43. 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"
  44. Describe Roles.
    Roles are what give permissions to users.
  45. What is the 3 job operation that is accomplished by setting up a maintenance plan?
    Log Shipping
  46. 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
  47. Describe WITH_RECOVERY
    Rolls back any uncommitted transactions bringing database online for use
  48. Describe WITH_NORECOVERY
    Uncommitted transactions unaffected, data base left in "restoring" mode, allowing more transaction logs to be restored to the database
  49. 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
  50. Describe Mirroring
    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.
  51. 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.
  52. What does Asynchronous need to be capable of automatic fail over
    Must be accompanied by a "witness" located on a third instance.
  53. 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.
  54. What are the 3 different types of Replication and Disaster Recovery.
    • Log Shipping
    • Mirroring
    • Windows Failover Clustering
Card Set
tnt sql