SQL Server Base Information
SQL Server 2005
OS: Windows
Version
1. Enterprise --> Cluster / Mirror / Snapshot
2. Standard
3. Workgroup
4. Express
Installation Components
1. Database Service --> Database Driver; Duplicataion, Full Index
2. Integration Services --> Data Migration
3. Analysis Services --> Online analysis and data mining
4. Notification Services --> Application Notification
5. Client components, Files, Tools
Management Tools
Default Databases
System Databases
1. Master: login information; System configuration
2. Model: Create database template
3. Tempdb: Save temporary objects
4. Msdb: Auto jobs, Alerts
5. Resource: Read only database, system objects for sqlserver 2005
User Databases
1. AdventureWorks: Sample database
2. User1
3. User2
Database Storage Structure
Database files
1. Data file
2. Log file
Database file type
1. Main database file .mdf (created when database create)
2. Associate database file .ndf (when the storage is not enough, add another file)
3. Event log file .ldf (created when database create)
Database file structure
page: minimize save unit. 8K
district: base save unit. 64K
Check installation log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt
Extend database
1. Automatic
a. Set the automatic extend
2. Manually
a. Add new data file
b. Change the max size of the file
Decrease database:
Manually: menu --> Decrease
Automatic: set database property(not recommend)
Seperate database: remove the database from database manager
Append database: add the database into databae manager
Delete database:
1. delete database from database manager
2. delete database related files
Main Data Type
1.int -2^31 ~ 2^31-1
2. datatime 1/1/1753 ~ 31/12/9999
3. varchar 2^31-1 bytes
_: indicate one character
%: indicate any characters
Security Manage
SQL security policy
1. Customer PC security
2. Network security
3. SQL Server security
4. Database security
5. Database Objects security
Verification
1. Windows verification
2. SQL Server and windows verification
SQL Server role: cover all the databases in the SQL Server
sysadmin
--在 SQL Server 中进行任何活动。该角色的权限跨越所有其它固定服务器角色。
serveradmin
--配置服务器范围的设置。
setupadmin
--添加和删除链接服务器，并执行某些系统存储过程（如 sp_serveroption）。
securityadmin
--管理服务器登录。
processadmin
--管理在 SQL Server 实例中运行的进程。
dbcreator
--创建和改变数据库。
diskadmin
--管理磁盘文件。
bulkadmin
--执行 BULK INSERT 语句。
Add account for database: Database manager --> Security --> Add login
Database Role: control the rights for the database
public
--public 角色是一个特殊的数据库角色，每个数据库用户都属于它。public 角色：
--捕获数据库中用户的所有默认权限。
--无法将用户、组或角色指派给它，因为默认情况下它们即属于该角色。
--含在每个数据库中，包括 master、msdb、tempdb、model 和所有用户数据库。
--无法除去。
db_owner
--进行所有数据库角色的活动，以及数据库中的其它维护和配置活动。
--该角色的权限跨越所有其它固定数据库角色。
db_accessadmin
--在数据库中添加或删除 Windows NT 4.0 或 Windows 2000 组和用户以及 SQL Server 用户。
db_datareader
--查看来自数据库中所有用户表的全部数据。
db_datawriter
--添加、更改或删除来自数据库中所有用户表的数据
db_ddladmin
--添加、修改或除去数据库中的对象(运行所有 DDL)
db_securityadmin
--管理 SQL Server 2000 数据库角色的角色和成员，并管理数据库中的语句和对象权限
db_backupoperator
--有备份数据库的权限
db_denydatareader
--拒绝选择数据库数据的权限
db_denydatawriter
--拒绝更改数据库数据的权限
Database Object Role: Setting in the Table property
Database Backup and Restore
Backup Type
1. Full backup
2. Increment backup: Need full backup
3. Log backup: Need full backup
Log Type
1. Simple: work without log
2. Full: work with log
3. Large Volumn Log: Migration large data log
Backup and Restore Strategy
1. full backup restore without log small database
2. full + increment backup restore without log reduce backup time
3. full + log backup restore comprehensive backup
Export and Import
1. Seperat and Append use to move database between server
2. Export and Import: follow guide
3. SSIS: develop the complex export and import
4. Duplication: Copy data from source database to target --> Syncronization
Duplication Type
1. Snapshot: full
2. Log: modify data
3. Emergy
Performance Monitor--> Windows performance
SQL Server Profile --> Track the account access, transaction status...
SQL Server Agent --> Automatic jobs, Alerts
Comments
Post a Comment