Let us continue our question of the day series with day fourteen.

What is the msdb database (did it really start as the home address directory for Bill Gates) ?

The msdb database stores scheduling jobs and alerts for the SQL Server Agent as well as database mail and service broker.  However, there was no word on Bill Gates personal address.

Enjoy!

Let us continue our question of the day series with day thirteen.

What is the purpose of the master database (besides handling the puppets)?

The master database is the heart and soul of your SQL Server.  It stores all of the system-level information for your system such as system configuration and linked servers.   In previous versions SQL Server used to store system objects in the master database, but they are now stored in the resource database.

Check out my interview with the mysterious resource database.  Enjoy!

Let us continue our question of the day series with day twelve.

What is the purpose of the model database (and does it look great in a swimsuit)?

The model database is an aptly named template for all of the newly created databases on your instance.  If you wish for every database to be configured with a certain option, such as autogrowth of 64 MB, then you would set the option on the model database.  At that point, every new database would have the same autogrowth setting.  Keep in mind, if you change the model database at a later date, the previously created databases will not inherit the changes only databases created from that point forward.

Also, keep in mind that there are a few options that cannot be set on the model database such as enabling change data capture.  Enjoy!

Let us continue our question of the day series with day eleven.

What is a DMV (and will there be a long line there)?

DMVs are Dynamic Management Views and you will hear them tossed around alot.  DMVs are an amazing tool introduced in SQL 2005 to replace querying system tables in SQL Server 2000.  However, they give you a plethora of internal information for troubleshooting purposes.  Want more information?  Check out the MSDN reference page for a full listing and categorization of DMVs.

Enjoy!

Let us continue our question of the day series with day ten.

Where are the recovery models for SQL Server?

There are three different recovery models for SQL Server:  simple, full, and bulk-logged.  The simple recovery model allows you to recover back to your last database or differential backup.  If that is nightly, then all of the changes since then will be lost.  The full recovery model allows for no work to be lost unless there is damage to the log file.  Finally, bulk-logged recovery model allows for the most high performance and smaller log file storage.

Enjoy!

Let us continue our question of the day series with day nine.

Where does referential integrity mean?

Referential integrity means that when a table references another table the foreign key must always point to a real and valid row in the corresponding table.  In other words, if you have an order table and an order line item table and you delete the order table, there should not be an order line item records for that order.  Referential integrity ensures that those related records are update or deleted accordingly.  The tables are in essence synchronized.

Enjoy!

Let us continue our question of the day series with day eight.

Where are different types of triggers?

There are two types of triggers, Data Manipulation Language triggers (DML) and Data Definition Language triggers (DDL).  DDL triggers are fired whenever Drop Table, Alter Table, Create Table or Login events.  DML triggers are fired instead of or after the triggering event.

Enjoy!

Let us continue our question of the day series with day seven.

Where are SQL Server usernames and passwords stored?

SQL Server usernames and passwords are stored in the System Catalog Views: sys,server_principals and sys.sql_logins.  Keep in mind that the passwords are encrypted.

Enjoy!

Let us continue our question of the day series with day six.

What are the authentication modes available for SQL Server?

There are two authentication modes available:  Windows mode and mixed mode.  In other words, active directory authentication (Windows) and SQL Server (mixed).  There is no option to allow only SQL Server authentication, if you choose mixed mode then you are allowing both options.  Active directory authentication is the more secure of the two options.

Enjoy!

Let us continue our question of the day series with day five.

What is ACID?

ACID is an acronym that is used in relational databases to determine whether it meets architecture criteria.  The acronym stands for Atomicity, Consistency, Isolation, and Durability.  When we evaluate the architecture or design of the relational database all four of these attributes of the acronym must be met in order for the database to be considered reliable.

Atomicity is an all-or-none proposition in regards to transaction which are a unit of work to be committed or rolled back.

Consistency will guarantee that a transaction will never remain unfinished in your database.

Isolation keeps transactions separated from the database and from each other until they are committed or rolled back.

Durability guarantees that the database will track all changes so that if any sort of termination occurs the server can recover.

Enjoy!