Course 10361 Incomplete!!!

Question 1

Who creates and manages databases?

Answer 1

Both database administrators and developers.

Question 2

How are databases moved from the development environment to the production environment?

Answer 2

Organizations create SQL Server 2008 R2 databases in a development environment and then generate a script that will be passed on to a database administrator to run on a production computer running SQL Server 2008 R2. Understanding the different methods of creating a database by using Transact-SQL and SQL Server Management Studio is important. This course will explore the planning issues that are involved prior to creating the database.

Question 3

When is the best time to optimize performance of a database?

Answer 3

Although there are many ways to improve the performance of SQL Server 2008 R2, the database creation stage is the best opportunity to implement the logical and physical design for optimal performance.

Question 4

What types of databases are created when you install SQL Server 2008 R2?

Answer 4

The first type is a system database, which is created automatically. Each system database performs different functions and stores information about SQL Server 2008 R2 or one of its specific components. System databases also store metadata related to SQL Server 2008 R2.
The second type is the user-defined database. These databases are created manually by a database developer. User-defined databases typically hold business data that can be queried and modified.

Question 5

What are the types of databases?

Answer 5

master
msdb
model
tempdb
distribution
Mssqlsystemresource

Question 6

What is the name of the type of database that is the template for all new user-defined databases?

Answer 6

model

Question 7

What is the name of the database that stores temporary objects?

Answer 7

tempdb

Question 8

What type of database stores information about the jobs, alerts and operators that are defined in the SQL Server Agent?

Answer 8

msdb

Question 9

What kind of database is created when replication is enabled and configured?

Answer 9

distribution

Question 10

What is the name of the hidden, read-only system database? What is another name for it?

Answer 10

Mssqlsystemresource, aka Resource database

Question 11

Which database to SQL Server is like Windows registry is to Windows?

Answer 11

master

Question 12

Where do features such as SQL Server Integration Services, Service Broker, and DAtabase mail store metadata?

Answer 12

msdb

Question 13

Where is information about all the databases in an instance of SQL Server 2008 stored?

Answer 13

master. This data is known as metadata.

Question 14

What is used as the basis of a new user-defined database and how is it produced?

Answer 14

A copy of the model database is used as the template for the new database. It is then renamed as the database name defined in the CREATE DATABASE statement. Metadata about the new database is stored in the master database before the new database is created.

Question 15

Who is tempdb available to?

Answer 15

All users connected to SQL Server.

Question 16

What does the distribution database store?

Answer 16

Metadata and history data for all types of replication and transaction for transactional replication.

Question 17

What contains all system objects that are included n SQL Server 2008?

Answer 17

Mssqlsystemresource

Question 18

What happens if the master database becomes corrupt and what is a recommended safety measure?

Answer 18

If the master database becomes corrupted, it prevents SQL Server 2008 from running. It is recommended that a backup of the master database be taken every time a new database is added or removed from the instance of SQL Server 2008.

Question 19

When are temporary objects removed from tempdb?

Answer 19

When a connection that creates a temporary object disconnects or if the server restarts. If the tempdb database becomes corrupt, it is recreated with SQL Server restarts.

Question 20

What is the SQL Server Agent responsible for?

Answer 20

Scheduled automation of jobs and the firing of alerts.

Question 21

What two files are created when you create a user-defined database?

Answer 21

MDF, LDF

Question 22

What does the MDF store and how many can you have?

Answer 22

MDF is the primary data file that stores the data. You can define only one MDF.

Question 23

What does the LDF store?

Answer 23

The LDF file is the transaction log file that stores a record of the transactions that have been issued against the SQL Server.

Question 24

Where is it recommended you put the MDF and LDF files?

Answer 24

As a minimum requirement, it is recommended that the LDF file is stored on a faster, separate disk than the MDF file.
Ideally, each disk is on a separate controller. This improves data throughput and disk I/O for information that is read and written to the database.

Question 25

How does the database verify user permission?

Answer 25

While the primary data file, the MDF file, is used to store data, it must be considered that on its own that MDF stores both user data and database metadata in the same file. Consider that in accessing data in a table, SQL Server 2008 must read the database metadata such as the sys.syspermissions view to verify if the connection is granted and the user has permission to read the data in a table before the data is read.

Question 26

Where can you store user data separate from the MDF?

Answer 26

You can consider introducing secondary data files, known as NDF files, to store the user data in a file separate from the database metadata that is stored in the MDF file.

Question 27

Where are NDF files and how many can you have?

Answer 27

NDF files can be defined on a separate disk. Also, you are not limited to the number of NDF files that you can define. This provides the potential to spread the data across different NDF files to take advantage of the disk configuration of the physical server to improve the access to the data.

Question 28

If the ability to recover data to a point in time is important how should you configure the database?

Answer 28

If the ability to recover data to a point in time is important, it is recommended that you store the LDF file on a RAID 1 volume. This provides resilience if a disk fails. This also provides the ability to back up the log file if one of the disks fails and the other remains operational. Storing MDF and NDF files on a single disk can represent a single point of failure. By utilizing RAID 5 volumes, the data files benefit from improved read and write performance as the data is spread across multiple disks. There is also a resilience if a disk in the RAID 5 array goes down.

Question 29

How should you configure the database if high performance is critical and recoverability is not?

Answer 29

However, if the data requirements of your business specify high performance is critical and recoverability is not, using RAID 1 volumes is a useful solution as long as it is understood that data can only be recovered from a backup once a disk goes down.

Question 30

Can you change the structure that SQL Server uses to store data?

Answer 30

No.

Question 31

What is the smallest unit that SQL Server uses to store data? What is its maximum size?

Answer 31

At its smallest unit, SQL Server 2008 R2 stores data on a page. A page has a maximum size of 8192 bytes.

Question 32

How are rows stored and what are the limitations?

Answer 32

A page has a maximum size of 8192 bytes of which 8060 bytes (8 KB) is reserved for storing data in a row within a page. A row that is defined in a table cannot span a page. Hence, the maximum size of a row is 8060 bytes.

Question 33

How can you optimize how rows are stored?

Answer 33

You can fit many rows in a single page (assuming they are much smaller than the limitation of 8060 bytes (8KB). Therefore, it is important to minimize the amount of space that is defined in the row of a table. If more rows are fitted in the page, then more data is stored and lesser I/O is required to retrieve a large amount of data—thus improving the speed at which data is retrieved.

Question 34

What form does SQL Server retrieve data and what is the size?

Answer 34

SQL Server 2008 retrieves data in the form of extents. Eight pages are grouped together to form an extent, which is 64 KB in size.

Question 35

What are the different types of extents and how are they defined?

Answer 35

If all the pages in an extent contain data that belong to the same SQL Server 2008 object, such as data in a single table, the extent is known as a uniform extent. If an extent contains data of different objects, then it is known as a mixed extent.

Question 36

What are file groups used for?

Answer 36

You can use filegroups to distribute the table data across multiple disks and facilitate backup at a filegroup level.

Question 37

What is the default for filegroups when a database is created?

Answer 37

By default, when a database is created, the primary data file maps to a filegroup named Primary.

Question 38

What do filegroups store and not store?

Answer 38

A filegroup stores one or more data files in it. Transaction log files cannot be associated with a filegroup.

Question 39

What is VLDB?

Answer 39

very large databases

Question 40

What is the benefit of filegroups for VLDB?

Answer 40

From an administrative perspective, databases can be backed up at a filegroup level, reducing the backup times of very large databases (VLDB).

Question 41

What other reasons would you create User-defined filegroups?

Answer 41

data allocation and placement. From a data allocation and placement perspective, filegroups are used to define the location of the table or index data. This provides the ability to spread the table or index data across multiple disks to improve performance.

Question 42

What do you call a special filegroup that contains file system directories instead of files?

Answer 42

A FILESTREAM filegroup.

Question 43

What is the benefit of FILESTREAM data?

Answer 43

FILESTREAM data enables the storage of large binary data objects within a SQL Server database within the Microsoft Windows NT file system. This provides the benefit of storing large binary objects that are greater than 2GB in size. In order to use FILESTREAM, the FILESTREAM data must be stored in FILESTREAM filegroups.

Question 44

What are the file system directories in a FILESTREAM called and what do they do?

Answer 44

These file system directories are called data containers. Data containers are the interface between the Database Engine storage and the file system storage.

Question 45

How can you limit modification of a file group?

Answer 45

Any existing filegroup, except the primary filegroup, can be marked as read-only. A filegroup marked read-only cannot be modified in any way. Read-only filegroups can also be compressed.

Question 46

What is BLOB?

Answer 46

A Binary Large OBject (BLOB) is a collection of binary data ((example: word documents, image files, music and videos etc) stored as a single entity in a database management system. Blobs are typically images, audio or other multimedia objects, though sometimes binary executable code is stored as a blob. (per Wikipedia)

Question 47

When was FILESTREAM introduced?

Answer 47

SQL Server 2008. It is initially disabled at installation by default but can be enabled then or later. Note: filegroups were available previously. This is FILESTREAM only.

Question 48

What are the three levels of access to FILESTREAM?

Answer 48

1. Enable FILESTREAM for Transact-SQL access 2. Enable FILESTREAM for file I/O streaming access 3. Allow remote clients to have streaming access to FILESTREAM data https: //www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/

Question 49

If a file group is spread across Data1.ndf on disk 1 with 50 GB and Data2.ndf on disk 2 with 100 GB, how will SQL store 60 mb of data?

Answer 49

SQL Server uses a proportional fill strategy across all disks that are part of the same filegroup. It will place 20mb on Data1.ndf/disk 1 and 40mb on Data2.ndf/Disk 2. This ensures that each disk fills up proportionally.

Question 50

How can file groups help if the time taken to perform a full or differential backup is too prohibitive?

Answer 50

You can create an effective backup strategy by using filegroup backups. For example, a database consists of three filegroups—PRIMARY, DYNAMIC, and STATIC. A backup of the PRIMARY filegroup takes place every Monday, while the STATIC filegroup is backed up every Wednesday, and the DYNAMIC filegroup is backed up every Friday. This backup is conducted on a weekly basis. This is useful for very large databases or databases that have a very small maintenance window.

Question 51

How can filegroups be used to improve performance of read and write operations against the table?

Answer 51

Filegroups can be used to horizontally partition the data of a single table into different data files. To create a table partitioning strategy, perform the following steps. 1. Create a partition function 2. Create a partition scheme 3. Map the partition scheme within a CREATE TABLE statement.

Question 52

Collation is an option when creating your database. What is collation?

Answer 52

Collation is a set of rules for comparing strings (or characters in a character set). It seems obvious that A is before B. But you can have variants like case-insensitive (A and a are equal and both before B and b), accent-insensitive or more specific collations like Spanish in which words starting with Ch come after all other words starting with C. For example: In latin9, there are letters as e é è ê f, if sorted by their binary representation, it will go e f é ê è but if the collation a different collation could put them in the order you thought they would be, which is all of e é è ê are equal, and then f.