1) How many types of files are there in
a SQL Server database?
SQL Server databases have three types of
files:
- 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 for primary data
files 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 for secondary data files 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.
- Disk I\O Performance
- Easy Management and Archiving of the data
- Benefit of doing File Group level Backups and restores
- 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
behaviors 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
}
‘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.
USE master;
GO
SELECT DatabaseProperty ('dbccpagetest', 'version');
GO
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;
GO
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?
State
|
Definition
|
ONLINE
|
Database is available for access. The
primary filegroup is online, although the undo phase of recovery may not have
been completed.
|
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.
|
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.
|
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.
|
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.
|
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.
|
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?
An extent is 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 MULTI 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.
References: Thanks to the all the SQL Server techies who wrote and shared the
valuable information in the below blogs which helped me a lot to prepare this
series of Questions. Also big thanks to Microsoft Documentation which contains
each and everything about their product.
SQL
Server Service Broker
Service Broker is a new technology in Microsoft SQL Server 2005 that
helps database developers build secure, reliable, and scalable applications. Because Service
Broker is part of the Database Engine, administration of these
applications is part of the routine administration of the database.
Service Broker provides queuing and reliable messaging for SQL Server.
Service Broker is used both for applications that use a single SQL Server
instance and applications that distribute work across multiple instances.
Within a single instance of SQL Server, Service Broker provides a robust
asynchronous programming model. Database applications typically use
asynchronous programming to shorten interactive response time and increase
overall application throughput.
· Service Broker also provides
reliable messaging between SQL Server instances.
· Service Broker helps developers
compose applications from independent, self-contained components called
services.
· Applications that require the
functionality exposed in these services use messages to interact with the services.
Service Broker uses TCP/IP to exchange messages between instances.
· Service Broker includes features to
help prevent unauthorized access from the network and to encrypt messages sent
over the network.
What
does Service Broker do?
Service
Broker helps developers build asynchronous, loosely coupled applications in
which independent components work together to accomplish a task. These
application components exchange messages that contain the information that is
required to complete the task. The fundamental aspects of Service Broker are:
•
Conversations
•
Message ordering and coordination
•
Transactional asynchronous programming
•
Support for loosely coupled applications
•
Service Broker components
Advantages
of Service Broker:
Service
Broker's features provide a number of significant benefits to database
applications. These features and benefits include:
· Database
integration enhances application performance and simplifies administration.
· Message
ordering and coordination for simplified application development
· Loose
application coupling provides workload flexibility.
· Related
message locking allows more than one instance of an application to process
messages from the same queue without explicit synchronization.
No comments:
Post a Comment