SQL Server 2008 Basics

One, Configuration Manager

1. Management Services

Use Configuration Manager to start, stop, restart, resume, or pause services.

Server and Client network protocols

2.SQLSMS

Introduction:Sqlsms is an integrated environment for accessing, configuring, managing, and developing all of the components of SQL Server. To register a server:determine the machine, and server, where a SQL Server database resides for the client. 4.Sqlcmd Tools

SQLCMD communicates with the server through OLE DB, using the sqlcmd tool to enter T-SQL statements in a command Prompt window, invoking system procedures and script files.

A T-SQL script file is a text file that can contain a combination of T-SQL statements, sqlcmd commands, and script variables.

5. Other management tools

Business Intelligence Development Studio

Database Engine Tuning Advisor

SQL Server Profiler

Reporting Services

Second, the system database

1. master databaseThe master database is the most important database in SQL Server 2008. All system-level information for an instance of SQL Server is logged. such as: SQL Server initialization information, logon account and system configuration settings, location of all database files. If the master database is unavailable, SQL Server cannot start. The master database always has an up-to-date master database backup available. 2. tempdb database The tempdb database is a staging database that holds temporary objects or intermediate result sets that meet temporary storage requirements. the tempdb database is recreated every time that SQL Server is started. Temporary tables and temporary stored procedures are automatically dropped when the connection is broken, and no connection is active when the system shuts down. each time the database engine is started, the tempdb database is reset to its initial size, and the library automatically grows as needed when SQL Server runs. 3. Model DatabaseThe model database is the template that creates all the databases. When the database is created, the first part of the new database is created by copying the contents of the model database, and the remainder is populated with blank pages. If you modify the model database, all of the databases created later will inherit these modifications, i.e. all user-defined objects in the model database will be copied to all newly created databases. You can add any object, such as tables, views, stored procedures, and so on, to the model database to include them in all newly created databases. The model database is always stored in the SQL Server system. 4. msdb databaseThe msdb database is used by SQL Server Agent to dispatch alerts and jobs, and to record operators.

Third, the composition of the database

1. Database ObjectsTableViewIndexstored procedures and triggersUsers and Roles2. Database Files

There are 3 types of files in a SQL Server 2005 database:

Master Data File　secondary data Fileslog File① Master Data file

The master data file is used to hold the data, and it contains pointers to other database files, which are the starting point of the database. It contains system tables that record the location information of database objects and other files.

A database must have only one master data file, and the main data file has an extension of. MDF.

In SQL Server 2008, the location of all the database files is recorded in the primary file of the database and in the master database.

② secondary data files

Secondary data files are also used to hold data. A secondary data file is required if the master data file can include all of the data in the database, if the primary data file is too large or to be extended to more than one disk.

A database can have multiple or no secondary data files, and a secondary data file has an extension of. Ndf

③ transaction log Files

A transaction is a user-defined sequence of database operations. These operations are either done entirely or not.

Transaction log files are used to hold transaction logs. That is, the storage of all transactions and changes in the database caused by these transactions is used to recover the database.

One database has one or more transaction log files, and the log file has an extension of. LDF

3. File Groups

There are two types of file groups for file allocation and administration:

Primary file group:

The primary filegroup contains the master data file and any other files that are not explicitly assigned to other filegroups. All pages of the system table are assigned to the primary filegroup.

user-defined file groups:

A user-defined filegroup is any filegroup that is specified by using the FILEGROUP keyword in the CREATE DATABASE or ALTER DATABASE statement.

the principles that database files should follow: A single database has a filegroup that is designated as the default filegroup. If not specified, the primary filegroup is the default filegroup. If a filegroup is not specified when the table or index is created, all pages are assumed to be assigned from the default filegroup. You can have only one text group at a time as the default filegroup. A data file can belong to only one filegroup. log files cannot belong to filegroups.

Iv. Creating and deleting databases

1. Create a database:Use the Sqlsms tool to createcreating with T-SQL2. Delete the database:Use the Sqlsms tool to removeDelete using T-SQL

V. Separating and attaching databases

1. Detaching a database

The database is removed from the instance, but its database files and transaction log files remain intact.

2. Attaching a database

Add the detached database to the instance of SQL Server.

Vi. SQL scripts

SQL scripts allow you to create a database structure, rebuild a database, or use it as a tool for moving a database.

SQL Server 2008 Basics