4.Database Architecture and Database
Properties
1) How many types of files are there in a SQL Server
database? 3 Types
·
Primary data files
·
Secondary data files
·
Transaction Log files
2) Explain each type of database files?
Primary data files
The primary data file is the starting point of the database
and points to the other files in the database. Every database has one primary
data file. The recommended file name extension is .mdf.
Secondary data files
Secondary data files make up all the data files, other than
the primary data file. Some databases may not have any secondary data files,
while others have several secondary data files. The recommended file name
extension is .ndf.
Transaction Log file
This file holds all the log information that is used to
recover the database. There must be at least one log file for each database,
although there can be more than one. The recommended file name extension for
log files is .ldf.
3) What is the major difference between Primary data
files and secondary data files?
Primary data file contains system objects where as secondary
data files contains all user defined Database objects if these are not part of
the Primary File group.
One of the important difference between Primary and
Secondary data files is BOOT PAGE. Page Number 9 is the boot page Page type
13). Boot page is available as 9th page only in the primary data file.
4) How many maximum files can be added to a database? 32,767
5) What are file groups and Type of File Groups?
Database File groups: Database objects and files can
be grouped together in file groups for allocation and administration purposes.
There are two types of file groups:
Primary: The primary file group contains the primary data
file and any other files not specifically assigned to another file group. All
pages for the system tables are allocated in the primary file group.
User-defined: User-defined file groups are any file groups
that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER
DATABASE statement.
6) What is the default File Group in a database? Primary
File group
7) Is it possible to change the Default file group from
Primary to some other user defined file group? What is the benefit of this?
Yes it is possible to change the Default File group to user
defined File group. All the newly created objects will be created in User
defined File group be default.
8) How many Maximum File groups can be added in a
database? 32,767
9) Is it possible to add Transaction Log file in a file
group?
Log files are never part of a filegroup. Log space is
managed separately from data space.
10) What is the use of having multiple File Groups?
Below are the major benefits which can be achieved using
multiple data files and placing these files in separate file groups on separate
disk drives.
1.
Disk I\O Performance
2.
Easy Management and Archiving of the data
3.
Benefit of doing File Group level Backups and
restores
4.
Usage of File Groups in Portioning of the tables
11) Is there any benefit to add multiple log files?
Yes/No, Why?
No, there is no benefit of adding multiple log files in a
database as the write operations in a Transaction log files are always serial.
12) Will the below script work if yes, how?
Create database Test;
Yes, this script will work because rest of the parameters
will be taken from model database and Files will be located to the respective
folders which are set at the SQL Server instance level.
13) What is Database Growth and what settings are
available in a database?
Each database file that is associated with your database has
an auto-growth setting. There are three different settings you can use to
identify how your database files will grow. They can grow by a specific size, a
percentage of the current size, or not grow at all. Additionally you can set
your files to unrestricted growth, which means they will keep growing as they
need more space or you run out of disk space. Or you can restrict the growth of
a database file to grow no larger than a specified size. Each one of these
different auto-grow setting have defaults, or you can set them for each
database file.
14) What are the
recommended settings for transaction Log File for file growth?
If you are required to set the setting for Auto growth of
Transaction log file, it should always be in a specific size instead of
percentage.
15) What is a compatibility level of a database?
Compatibility level sets certain database behaviours to be
compatible with the specified version of SQL Server. The default compatibility
level is 110. Databases created in SQL Server 2012 are set to this level unless
the model database has a lower compatibility level.
16) How to change the Compatibility level of a database?
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 90
| 100 | 110 | 120|130 }
90 = SQL Server 2005
100 = SQL Server 2008 and SQL Server 2008 R2
110 = SQL Server 2012
17) What’s the difference between database version and
database compatibility level?
Database version
The database version is a number stamped in the boot page of
a database that indicates the SQL Server version of the most recent SQL Server
instance the database was attached to.
SELECT DatabaseProperty ('dbccpagetest', 'version');
Database compatibility level
The database compatibility level determines how certain
database behaviors work. For instance, in 90 compatibility, you need to use the
OUTER JOIN syntax to do an outer join, whereas in earlier compatibility levels,
you can use ‘*=’ and ‘=*’
SELECT name AS 'DB Name', compatibility_level AS
'Compatibility Level' FROM master.sys.databases;
18) What is a Page Verify option in a database?
When CHECKSUM is enabled for the PAGE_VERIFY database
option, the SQL Server Database Engine calculates a checksum over the contents
of the whole page, and stores the value in the page header when a page is
written to disk. When the page is read from disk, the checksum is recomputed
and compared to the checksum value that is stored in the page header. This
helps provide a high level of data-file integrity.
19) What are the different Database states in SQL server
instance?
|
No
|
State
|
Definition
|
|
1
|
ONLINE
|
Database is available for access. The primary filegroup is online,
although the undo phase of recovery may not have been completed.
|
|
2
|
OFFLINE
|
Database is unavailable. A database becomes offline by explicit user
action and remains offline until additional user action is taken. For
example, the database may be taken offline in order to move a file to a new
disk. The database is then brought back online after the move has been
completed.
|
|
3
|
RESTORING
|
One or more files of the primary filegroup are being restored, or one
or more secondary files are being restored offline. The database is
unavailable.
|
|
4
|
RECOVERING
|
Database is being recovered. The recovering process is a transient
state; the database will automatically become online if the recovery
succeeds. If the recovery fails, the database will become suspect. The
database is unavailable.
|
|
5
|
RECOVERY PENDING
|
SQL Server has encountered a resource-related error during recovery.
The database is not damaged, but files may be missing or system resource
limitations may be preventing it from starting. The database is unavailable.
Additional action by the user is required to resolve the error and let the
recovery process be completed.
|
|
6
|
SUSPECT
|
At least the primary filegroup is suspect and may be damaged. The
database cannot be recovered during startup of SQL Server. The database is
unavailable. Additional action by the user is required to resolve the
problem.
|
|
7
|
EMERGENCY
|
User has changed the database and set the status to EMERGENCY. The
database is in single-user mode and may be repaired or restored. The database
is marked READ_ONLY, logging is disabled, and access is limited to members of
the sysadmin fixed server role. EMERGENCY is primarily used
for troubleshooting purposes. For example, a database marked as suspect can
be set to the EMERGENCY state. This could permit the system administrator
read-only access to the database. Only members of the sysadmin fixed
server role can set a database to the EMERGENCY state.
|
20) How many
databases can be created in SQL server instance? 32,767
21) What is auto close option?
AUTO CLOSE option:
When set to ON, the database is shut down cleanly and its
resources are freed after the last user exits. The database automatically
reopens when a user tries to use the database again.
When set to OFF, the
database remains open after the last user exits.
22) What is auto shrink option?
AUTO SHRINK option:
When set to ON, the database files are candidates for
periodic shrinking. Both data file and log files can be shrunk automatically by
SQL Server. AUTO_SHRINK reduces the size of the transaction log only if the
database is set to SIMPLE recovery model or if the log is backed up.
When set to OFF, database files are not automatically shrunk
during periodic checks for unused space.
23) What is page?
Page is the smallest unit of storage in SQL Server database,
the page size is 8 KB. This means SQL Server databases have 128 pages per
megabyte. Each page begins with a 96-byte header that is used to store system
information about the page. This information includes the page number, page
type, the amount of free space on the page, and the allocation unit ID of the
object that owns the page
24) What is an extent? a collection of eight physically contiguous
pages.
25) Types of extent?
Uniform extents are owned by a single object; all eight
pages in the extent can only be used by the owning object.
Mixed extents are shared by up to eight objects. Each of the
eight pages in the extent can be owned by a different object.
26) What is the difference between single user and
restricted user and restricted user option?
This option controls who and how many users can connect to a
database.
When SINGLE_USER is specified, one user at a time is allowed
to connect to the database. All other user connections are broken.
When RESTRICTED_USER is specified, only members of the
db_owner fixed database role and dbcreator and sysadmin fixed server roles are
allowed to connect to the database, but it does not limit their number.
When MULTI_USER is specified, all users that have the
appropriate permissions to connect to the database are allowed.
27) What is a logical File and physical File name?
Each data and transaction log file in a SQL Server database
has two names:
logical_file_name
The logical_file_name is the name used to refer to the
physical file in all Transact-SQL statements. The logical file name must comply
with the rules for SQL Server identifiers and must be unique among logical file
names in the database.
os_file_name
The os_file_name is the name of the physical file including
the directory path. It must follow the rules for the operating system file
names.
28) What is ROW_OVERFLOW_DATA? How does it work?
ROW_OVERFLOW_DATA : Assume that a table is created with
record size 12000 bytes having 4 varchar data types of size 4000 bytes.
Whenever user inserts a record with size greater than 8000 (page size is 8K),
then the excess data is moved to ROW_OVERFLOW_DATA pages. In simple terms,
ROW_OVERFLOW_DATA pages will come in to picture only when the row size exceed
page maximum limit.
29) How can we check the allocation unit of objects?
DMV sys.system_internals_allocation_units
30) What is trustworthy property of a database?
Trustworthy property :When ON, database modules (for
example, user-defined functions or stored procedures) that use an impersonation
context can access resources outside the database.
When OFF is specified, in an impersonation context cannot
access resources outside the database.
TRUSTWORTHY is set to OFF whenever the database is attached.
31) What is Instant file initialization?
Data and log files are initialized to overwrite any existing
data left on the disk from previously deleted files. Data and log files are
first initialized by filling the files with zeros when you perform one of the
following operations:
·
Create a database.
·
Add files, log or data, to an existing database.
·
Increase the size of an existing file (including
autogrow operations).
·
Restore a database or file group.
·
File initialization causes these operations to
take longer. However, when data is written to the files for the first time, the
operating system does not have to fill the files with zeros.
Instant file initialization is only available if the SQL
Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME.
Members of the Windows Administrator group have this right and can grant it to
other users by adding them to the Perform Volume Maintenance Tasks security
policy.
No comments:
Post a Comment