Manage and Configure Databases Flashcards
What is Auto_Close?
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.
What is Auto_Shrink?
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.
How can you make sure that all databases start with the same options?
Go to the MODEL database and set up the options how you want. New databases will have those same options.
What file extension does the Primary Data File have?
.mdf
What file extension does the Transaction Log File have?
.ldf
What file extension do the Secondary Data Files have?
.ndf
What is the benefit to having multiple data files?
Your database can be searching two different drives at the same time and can increase performance.
What is required in order to move a table to a different file group?
A clustered index.
What is the t-sql syntax to move the storage to a different file group?
CREATE CLUSTERED INDEX [INDEXNAME] ON [TABLENAME] (
COLUMN1
)
ON [FILEGROUP]
How do you initially create a table on a different file group?
CREATE TABLE [tablename] ( col1 ) ON [FileGroup]
What is partitioning?
It is the ability to divide out a table and send data to different file groups.
What are the four steps to create a partition?
Create Filegroups/files
Create partition function
Create partition scheme
Create/Modify table using partition scheme
What is a partition function?
This is the filter for your data.
What is the t-sql needed to create a Partition?
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]
How can you see which partition data is going into?
SELECT $PARTITION.PARTITIONNAME(VALUE) AS PARTITIONNUMBER
What is a way that we can ease backing up and restoring huge amounts of data?
By using Files and FileGroups. You can backup strictly file groups and then restore those file groups without having to do the entire database.
What is the t-sql to backup a single filegroup?
BACKUP DATABASE [DB] FILEGROUP=N’FILEGROUP’ TO DISK = N’C:\PATH’ WITH NAME = N’NAME’
What is the t-sql to drop the size of a database file down?
dbcc shrinkfile(FileName, size)
How do you keep the transaction log size down?
Run a transaction log backup
What is the t-sql to drop the size of an entire database?
dbcc shrinkdatabase([db], PercentToStayOpen)
How can you truncate a database file?
dbcc shrinkfile(FileID, truncateonly)
What is a database container?
When you just have users that are connected to it that are not linked to any logins.
How do you set up database containment?
USE MASTER GO sp_configure 'contained database authentication', 1 GO RECONFIGURE GO ALTER DATABASE [DBNAME] SET CONTAINMENT = PARTIAL WITH NO_WAIT GO
