Manage and Configure Databases

Question 1

What is Auto_Close?

Answer 1

When all users disconnect from a database, then the database releases all of the resources for itself. Bad if you have connections come and go often. Good with a small database that is only accessed by a few users.

Question 2

What is Auto_Shrink?

Answer 2

Disk space does not shrink when you remove a lot of files. Better to start with as big as you think you need and give it as much space as you are able.

Question 3

How can you make sure that all databases start with the same options?

Answer 3

Go to the MODEL database and set up the options how you want. New databases will have those same options.

Question 4

What file extension does the Primary Data File have?

Answer 4

.mdf

Question 5

What file extension does the Transaction Log File have?

Answer 5

.ldf

Question 6

What file extension do the Secondary Data Files have?

Answer 6

.ndf

Question 7

What is the benefit to having multiple data files?

Answer 7

Your database can be searching two different drives at the same time and can increase performance.

Question 8

What is required in order to move a table to a different file group?

Answer 8

A clustered index.

Question 9

What is the t-sql syntax to move the storage to a different file group?

Answer 9

CREATE CLUSTERED INDEX [INDEXNAME] ON [TABLENAME] (
COLUMN1
)
ON [FILEGROUP]

Question 10

How do you initially create a table on a different file group?

Answer 10

CREATE TABLE [tablename] ( col1 ) ON [FileGroup]

Question 11

What is partitioning?

Answer 11

It is the ability to divide out a table and send data to different file groups.

Question 12

What are the four steps to create a partition?

Answer 12

Create Filegroups/files
Create partition function
Create partition scheme
Create/Modify table using partition scheme

Question 13

What is a partition function?

Answer 13

This is the filter for your data.

Question 14

What is the t-sql needed to create a Partition?

Answer 14

CREATE PARTITION FUNCTION NAME as RANGE [Right, Left] FOR VALUES (N’VALUE1’, N’VALUE2’)

CREATE PARTITION SCHEME [SCHEMENAME] AS PARTITION [NAME] TO ([FILEGROUP1],[FILEGROUP2],[FILEGROUP3])

CREATE CLUSTERED INDEX [INDEXNAME] ON [TABLENAME] ( COL1 ) ON [NAME] ([COL1])

DROP INDEX [INDEXNAME] ON [TABLENAME]

Question 15

How can you see which partition data is going into?

Answer 15

SELECT $PARTITION.PARTITIONNAME(VALUE) AS PARTITIONNUMBER

Question 16

What is a way that we can ease backing up and restoring huge amounts of data?

Answer 16

By using Files and FileGroups. You can backup strictly file groups and then restore those file groups without having to do the entire database.

Question 17

What is the t-sql to backup a single filegroup?

Answer 17

BACKUP DATABASE [DB] FILEGROUP=N’FILEGROUP’ TO DISK = N’C:\PATH’ WITH NAME = N’NAME’

Question 18

What is the t-sql to drop the size of a database file down?

Answer 18

dbcc shrinkfile(FileName, size)

Question 19

How do you keep the transaction log size down?

Answer 19

Run a transaction log backup

Question 20

What is the t-sql to drop the size of an entire database?

Answer 20

dbcc shrinkdatabase([db], PercentToStayOpen)

Question 21

How can you truncate a database file?

Answer 21

dbcc shrinkfile(FileID, truncateonly)

Question 22

What is a database container?

Answer 22

When you just have users that are connected to it that are not linked to any logins.

Question 23

How do you set up database containment?

Answer 23

USE MASTER
GO
sp_configure 'contained database authentication', 1
GO 
RECONFIGURE
GO
ALTER DATABASE [DBNAME] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO