Master DB
Records all the
system-level information for an instance of SQL Server.
This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.
Therefore, SQL Server cannot start if the master database is unavailable.
This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.
Therefore, SQL Server cannot start if the master database is unavailable.
msdb DB
It is used by SQL Server
Agent for scheduling alerts and jobs and by other features such as Service
Broker and Database Mail.
model DB
Is used as the template
for all databases created on the instance of SQL Server. Modifications made to
the model database, such as database size, collation, recovery model, and other
database options, are applied to any databases created afterward.
ResourceDB
Is a read-only database
that contains system objects that are included with SQL Server. SQL Server
system objects, such as sys.objects are physically persisted in the Resource
database, but they logically appear in the sys schema of every database.
Tempdb DB
It is a global resource
that is available to all users connected to the instance of SQL Server and is
used to hold the following:
§ Temporary user objects
that are explicitly created, such as: global or local temporary tables,
temporary stored procedures, table variables, or cursors.
§ Internal objects that
are created by the SQL Server Database Engine, for example, work tables to
store intermediate results for spools or sorting.
§ Row versions that are
generated by data modification transactions in a database that uses
read-committed using row versioning isolation or snapshot isolation
transactions.
§ Row versions that are
generated by data modification transactions for features, such as: online index
operations, Multiple Active Result Sets (MARS), and AFTER triggers.
Operations within tempdb
are minimally logged. This enables transactions to be rolled back. tempdb is
re-created every time SQL Server is started so that the system always starts
with a clean copy of the database. Temporary tables and stored procedures are
dropped automatically on disconnect, and no connections are active when the
system is shut down. Therefore, there is never anything in tempdb to be saved
from one session of SQL Server to another. Backup and restore operations are
not allowed on tempdb.
Distribution
Database responsible for
the replication metadata between the publisher and subscriber(s)
ReportServer
Primary database for
Reporting Services to store the metadata and object definitions
§ Reports security
§ Job schedules and
running jobs
§ Report notifications
§ Report execution history
ReportServer TempDB
Temporary storage for
Reporting Services
§ Session information
§ Cache
push subscription::
With a
push subscription, the Publisher propagates changes to a Subscriber without a
request from the Subscriber.
Changes
can be pushed to Subscribers on demand, continuously, or on a scheduled basis.
The
Distribution Agent or Merge Agent runs at the Distributor.
pull subscription::
With a
pull subscription, the Subscriber requests changes made at the Publisher.
Pull
subscriptions allow the user at the Subscriber to determine when the data
changes are synchronized.
The
Distribution Agent or the Merge Agent runs at the Subscriber.
Snapshot publication
The
Publisher sends a snapshot of the published data to Subscribers at scheduled
intervals.
Transactional publication
The
Publisher streams transactions to the Subscribers after they receive an initial
snapshot of the published data.
Transactional publication with updatable subscriptions
The
Publisher streams transactions to SQL Server Subscribers after they receive an
initial snapshot of the published data. Transactions originating at the
Subscriber are applied at the Publisher.
Merge publication
The
Publisher and Subscribers can update the published data independently after the
Subscribers receive an initial snapshot of the published data. Changes are
merged periodically. Microsoft SQL Server Compact Edition can only subscribe to
merge publications.
High safety with a witness
operating mode
The
Transaction safety setting is full
The Records are transferred synchronously. The clients does not get a transaction committed response until the mirror server has acknowledged the principal server.
Both the quorum and the witness server are required and present.
The failover is automatic, and manual failover is also possible.
No committed transactions are lost during failover.
The Records are transferred synchronously. The clients does not get a transaction committed response until the mirror server has acknowledged the principal server.
Both the quorum and the witness server are required and present.
The failover is automatic, and manual failover is also possible.
No committed transactions are lost during failover.
High Safety without a
witness operating mode
The
Transaction safety setting is full.
The Records are transferred synchronously. The clients does not get a transaction committed response until the mirror server has acknowledged the principal server.
The quorum is not required. The witness server is not present.
Manual failover.
No committed transactions are lost during failover.
The Records are transferred synchronously. The clients does not get a transaction committed response until the mirror server has acknowledged the principal server.
The quorum is not required. The witness server is not present.
Manual failover.
No committed transactions are lost during failover.
High-performance operating
mode
The
Transaction safety setting is off.
The Records are transferred asynchronously. The clients gets a transaction committed response as soon as the principal server has written the transaction to the log. The principal server does not wait for acknowledgement from the mirror server.
Both the quorum and witness server are not present.
Manual failover.
Some transactions are lost during failover.
The Records are transferred asynchronously. The clients gets a transaction committed response as soon as the principal server has written the transaction to the log. The principal server does not wait for acknowledgement from the mirror server.
Both the quorum and witness server are not present.
Manual failover.
Some transactions are lost during failover.
No comments:
Post a Comment