Shulou(Shulou.com)06/01 Report--

Database engine is the core service provided by SQL Server, which allows users to store, retrieve, process and protect data.

Database engine service enables users to build high-performance online transaction processing applications and support online analytical processing.

Storage engine is the "soul" of SQL Server database, which controls how data is stored on disk and how data is accessed by applications.

The database engine is a core component that does not interact directly with users, and it consists of some vital components that store and manage data.

The storage engine manages data storage based on table and column data type definitions.

For ease of query, use an index.

Using partitioning, you can split large tables and indexes into multiple storage structures.

Use locking and transaction management features to arbitrate multi-user access to data to ensure consistent data access.

In order to protect the data from catastrophic loss, we can make a full backup or incremental backup of the database in order to recover the corrupted data through backup.

SQL Server 2008 comes with several system databases for managing all aspects of the database engine: master, model, msdb, tempdb, and distribution.

The master database contains information about the core objects in an instance.

When you create a new database, the model database is used by SQL Server as the database template.

The role of the model database is to enable administrators to automatically create objects that need to be added to the new database when creating the database.

The core role of the msdb database is to store tasks and scheduling plans performed by the SQL Server agent.

The tempdb database is the SQL Server generic "erase" area. Never create any objects in tempdb that need to be permanently stored, because when the instance is restarted, all data stored in tempdb will be lost.

When the replication feature is enabled, the distribution database is created on the distributed device.

Objects created in a database usually refer to the Schema of the database.

The elements used to define databases and storage components are collectively referred to as database structures.

From a physical point of view, SQL Server databases are files on the operating system; from a logical point of view, they are used to group files in the database.

The most common file types that support SQL Server databases are data files and transaction log files. The extension of these files, the primary file .MDF, the secondary file .ndf, the transaction log file .ldf.

Several attributes of data files and transaction log files: initial file size, maximum size, physical file name, file growth factor.

SQL Server initializes the contents of each file with a 8KB-sized storage segment called "page". For each page, there is a specified structure for SQL Server to store and receive data.

Eight pages make up an extension. The block of 64KB, called an extension. The extension defines the maximum granularity of the SQL Server storage engine used to read / write data.

The filegroup (filegroup) has nothing but the name of an operating system fileset.

You can create three types of filegroups: Data (data), Full-text (full text), and FILESTREAM (file stream).

Unless you use the partitioning feature, the data saved by tables and indexes cannot be dispersed into filegroups.

When you create a file, you can set two optional properties: PRIMARY and DEFAULT. There can be only one PRIMARY or DEFAULT filegroup per database. The database filegroup specified as PRIMARY contains all system objects related to the database.

Full-text document group, also known as full-text catalogue.

The FILESTREAM filegroup is the filegroup specified to store FILESTREAM data.

FILESTREAM is not compatible with database mirroring and database snapshots.

The syntax for creating the database.

CREATE DATABASE database_name

[ON

[PRIMARY]

[[,... n] [, [,... n]] [LOG ON {[,... n]}]]

[COLLATE collation_name]

[WITH]

]

[;]

Examples of successful implementation:

Start SSMS, click New query on the toolbar, and enter the following code:

CREATE DATABASE SQL2012SBS ON PRIMARY (NAME = 5MB:\ SQL2012SBS.mdf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%), FILEGROUP FG1 DEFAULT (NAME = NAME:\ SQL2012SBS_1.ndf', SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 2MB), (NAME = nicked SQL2012SBSFG1encrypted Dat2, FILENAME = nicked:\ SQL2012SBS_2.ndf', SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 2MB) LOG ON (NAME = nicked SQL2012SBSFG1encrypted data) FILENAME = UNLIMITED:\ SQL2012SBS.1df', SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB) GO

Save, execute. Refresh object Explorer. The SQL2012SBS database appears under the databases directory.

Migrate database: detach (remove database items from the instance, close all files associated with the database and release all operating system locks), attach (create a new database in the instance and open all files associated with the database).

If you move files between different directories on the same server, you can use the ALTER DATABASE command.

When the database cannot be detached:

(1) the database is participating in replication

(2) the database is participating in database mirroring

(3) A database snapshot for the database has been created

(4) the database is in suspicious mode.

(5) Database is a system database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.