chn-02-数据库配置和维护

第2章

数据库创建、配置和维护

第2章：数据库创建、配置和维护文件和文件组创建数据库配置数据库选项维护数据库完整性数据库架构数据库快照文件和文件组数据文件数据存储事务日志文件事务扩展名文件组逻辑结构，一个或多个数据文件ON默认文件组文件和文件组主数据文件.mdfPRIMARY文件组辅助数据文件.ndf自定义文件组事务日志文件.ldf文件和文件组FILESTREAM数据为了能在数据库排序、管理、联合大量的非结构化数据和结构化数据FILESTREAM允许将文件和数据库联系在起来。文件存储在操作系统的文件夹中，直接和数据库链接，在数据库中，这些文件能够备份，恢复，全文索引和其他结构化数据联合。在数据库中存储FILESTREAM数据，需要定义文件的存储位置。通过指定数据库中的文件组的CONTAINSFILESTREAM属性在数据库中定义FILESTREAM数据的位置。为FILESTREAM文件组定义的FILENAME属性定义了文件夹的路径。文件和文件组FILESTREAM数据在FILESTREAM文件夹创建后，在文件夹中会创建一个名字为filestream.hdr的系统文件，该文件用来管理后续写到该文件夹中的文件。文件和文件组tempdb数据库临时对象分组，排序游标表变量溢出创建索引创建数据库CREATEDATABASEdatabase_name

[ON[PRIMARY][<filespec>[,...n][,<filegroup>[,...n]][LOGON{<filespec>[,...n]}]][COLLATEcollation_name][WITH<external_access_option>]][;]创建数据库<filespec>::={(NAME=logical_file_name,FILENAME={'os_file_name'|'filestream_path'}[,SIZE=size[KB|MB|GB|TB]][,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]])[,...n]}创建数据库<filegroup>::={FILEGROUPfilegroup_name[CONTAINSFILESTREAM][DEFAULT]<filespec>[,...n]}修改数据库ALTERDATABASEdatabase_name

{|MODIFYNAME=new_database_name

|COLLATEcollation_name|<file_and_filegroup_options>|<set_database_options>}[;]修改数据库<file_and_filegroup_options>::=<add_or_modify_files>::=<filespec>::=<add_or_modify_filegroups>::=<filegroup_updatability_option>::=配置数据库选项恢复自动改变跟踪访问参数化配置数据库选项恢复模式决定了备份类型包括完整恢复模式大容量日志恢复模式简单恢复模式修改恢复模式

ALTERDATABASEdatabase_name SETRECOVERY{FULL|BULK_LOGGED|SIMPLE}恢复模式配置数据库选项DML,DDL的所有修改都记录到事务日志中。当需要从灾难中恢复数据时，能最小化或消除某个给定时间点的数据丢失，改变将一直保留在事务日志中，只有当备份事务日志时才清空。恢复模式：完整恢复模式配置数据库选项通过使用最小方式记录大多数大容量操作，减少日志空间使用量。不记录每行的更改，减小开销、提高性能以最小日志方式所做的操作包括：BCPBULKINSERTSELECT…INTOCREATEINDEXALTERINDEX…REBULD不能进行时点还原恢复模式，大容量日志恢复模式配置数据库选项检查点进程执行之后，提交部分的事务日志截断。不能进行日志备份恢复模式：简单恢复模式配置数据库选项当数据写入磁盘的时候，由于电源或者磁盘问题倒是了被破坏的数据页写操作没有完成，不完全的数据页不能被读取当试图访问磁盘上的数据页时，才能够看到错误的结果PAGE_VERIFYCHECKSUM824错误suspect_pages

sys.dm_db_mirroring_auto_page_repair恢复模式，错误页配置数据库选项AUTO_CLOSEAUTO_SHRINKAUTO_CREATE_STATISTICSAUTO_UPDATE_STATISTICSAUTO_UPDATE_STATISTICS_ASYNCH自动选项配置数据库选项当最后一个连接断开后，SQLServer就关闭数据库，释放所有和数据库相关的资源。当有了新的连接，SQLServer启动数据库并且重新分配资源。默认关闭除非有大的内存压力，否则不要设置。经常访问的数据库不要设置该选项，会导致性能的严重降低。AUTO_CLOSE配置数据库选项设置了该选项后，SQLServer定期检查数据文件和事务日志文件的空间使用情况。如果空间检查算法发现数据文件有多于25%的未使用空间，文件就是自动收缩以回收磁盘空间。文件增长的代价高，推荐不要设置该选项。必要的时候手动收缩数据库。AUTO_SHRINK配置数据库选项统计使得查询优化器制定更有效的查询计划。AUTO_CREATE_STATISTICS配置数据库选项AUTO_UPDATE_STATISTICS：在查询优化过程中更新过时的统计AUTO_UPDATE_STATISTICS_ASYNCH：查询优化，更新统计是否异步自动选项配置数据库选项控制更改跟踪选项。可以启用更改跟踪、设置选项、更改选项以及禁用更改跟踪。

多用户系统的一个问题是一个用户的修改会意外覆盖另一个用户的修改，在读写中行是否改变跟踪机制通常包含

datetime

列或timestamp列，并且可能

包含整个的版本系统。CHANGE_TRACKING选项每次行改变，版本号就增加程序只要比较行版本来决定行读写的时候是否改变了，不需要设计系统避免多用户之间的影响。选择数据库中的哪些表捕获更新跟踪信息。更改跟踪配置数据库选项<change_tracking_option>::={CHANGE_TRACKING{=ON[<change_tracking_option_list>]|<change_tracking_option_list>|=OFF}}更改跟踪配置数据库选项<change_tracking_option_list>::={(<change_tracking_option>|<change_tracking_option_list>,<change_tracking_option>)}<change_tracking_option>::={AUTO_CLEANUP={ON|OFF}|CHANGE_RETENTION={retention_period{DAYS|HOURS|MINUTES}]}AUTO_CLEANUP更改跟踪配置数据库选项CHANGE_RETENTION久而久之，更改跟踪信息在数据库中积累起来，通过该选项可以定义跟踪信息保存多长时间AUTO_CLEANUP跟踪信息是否自动清除更改跟踪配置数据库选项ONLINE能完成所有可能的操作OFFLINE不可访问EMERGENCY只能由db_owner角色成员访问，并且只能执行select语句访问配置数据库选项READ_ONLY不能写SQLServer清除数据库的事物日志文件将数据库从READ_ONLY更改为READ_WRITE，SQLServer重新创建事务日志文件READ_WRITE访问ONLINE数据库配置数据库选项SINGLE_USERRESTRICTED_USERdb_owner,dbcreator,sysadmin角色成员访问MULTI_USER用户访问配置数据库选项如果把模式改变为SINGLE_USER时有多个用户在使用数据库，或者用户和设置为RESTRICTED_USER冲突，ALTERDATABASE命令直到这些用户断开连接才能执行如果不想等待用户完成操作以及断开和服务器的链接，可以定义ROLLBACK强行中断连接。ROLLCACKIMMEDIATE：强行回滚任何事务，断开连接ROLLBACKAFTER<numberofseconds>：在回滚事务和断开连接之前允许用户完成事务退出数据库多数数据库的正常运行模式是ONLINE,READ_WRITE,MULTI_USER用户访问配置数据库选项ALTERDATABASEdatabase_nameSET{SINGLE_USER|RESTRICTED_USER|MULTI_USER}[WITH{ROLLBACKAFTERinteger[SECONDS]|ROLLBACKIMMEDIATE|NO_WAIT}]NO_WAIT：如果不能立即完成将引起语句执行失败，为了可以执行成功，使用这个选项需要数据库中没有打开的事务。用户访问配置数据库选项在ALTERDATABASE中使用PARAMETERIZATION选项来控制数据库中是所有查询都参数化还是部分查询参数化。当查询提交到SQLServer时将引发参数化。SQLServer查看SELECT、INSERT、UPDATE和DELETE语句中的文本值，并且尝试对它们进行参数化（生成占位符），因此执行相似的查询时可以重用查询执行计划，而不是为每个查询都生成新的计划。在语句级别对参数化的查询创建执行计划，因此批处理中的每个语句都可以单独进行参数化。参数化配置数据库选项ALTERDATABASEdatabase_nameSETPARAMETERIZATION{SIMPLE|FORCED}参数化配置数据库选项在ALTERDATABASE中使用PARAMETERIZATION选项来控制数据库中是所有查询都参数化还是部分查询参数化。当数据库调用参数化，值可以作为变量传递，有利于应用程序参数化查询能更频繁的重用缓存的查询计划，避免重建查询计划所需要的时间PARAMETERIZATIONFORCED：强制SQLServer参数化每个查询数据库的默认设置是不强制参数化参数化配置数据库选项比较，排序，区分大小写，区分重音安装SQLServer时，可以为所有的数据库，表，列定义默认的排序规则。排序规则可以重新设置实例的排序规则可以在数据库级重新定义COLLATE子句CREATEDATABASEALTERDATABASE排序规则维护数据库完整性页验证选项残缺页页验证：该选项控制读和写页面到磁盘时使用的修改方法类型。有三个可选项：CHECKSUM（校验和）：校验和用来找出由磁盘I/O错误引起的不完整页面写入。当页面写入磁盘时，通过数据页的全部内容来计算出校验和，并存入页首。当以后从磁盘读出页面时，重新计算校验和并与页首存储的值进行比较。如果不相等，就会产生一个824错误消息。任何被操作系统检测出的I/O错误都将用823错误消息记入日志。数据库完整性检查维护数据库完整性页验证：该选项控制读和写页面到磁盘时使用的修改方法类型。有三个可选项：TORN_PAGE_DETECTION：在页面写入磁盘时，每512字节的扇区就有一位被反写。以后读该页面时，如果这一位是错误的，则页面被错误写入，残缺页面就检测出来了。提供向后兼容性，不推荐NULL：将来的数据页将不使用校验和或反写位，即使校验和和反写位存在，读取页面时也不做验证。当SQLServer遇到坏页，就抛出错误，视图访问坏页的命令中止，条目写入msdb数据库中的suspect_pages表数据库完整性检查维护数据库完整性可以强制SQLServer从磁盘读每页数据并且执行DBCCCHECKDB命令检查完整性DBCCCHECKDB命令检查数据库中对象的完整性

DBCCCHECKDB[('database_name'|database_id|0 [,NOINDEX|{REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST |REPAIR_REBUILD}])] [WITH{[ALL_ERRORMSGS][,[NO_INFOMSGS]][,[TABLOCK]] [,[ESTIMATEONLY]][,[PHYSICAL_ONLY]]|[,[DATA_PURITY]]}]数据库完整性检查维护数据库完整性当执行DBCCCHECKDB命令时，SQLServer完成以下操作：检查数据库中分配的页检查所有表和索引视图的结构完整性计算每个数据和索引页的校验和，同存储的校验和比较验证每个索引视图的内容检查数据库目录验证数据库中的ServiceBroker数据数据库完整性检查维护数据库完整性DBCCCHECKALLOC检查数据库中分配的页DBCCCHECKATALOG检查数据库目录DBCCCHECKTABLE检查数据库中每个表和视图的结构完整性任何错误都会输出以便修复问题索引完整性错误，则删掉重建索引表完整性错误，使用最近的备份修复被破坏的页数据库完整性检查维护数据库完整性DBCCCHECKFILEGROUP检查文件组中所有表的分配和结构完整性DBCCCHECKCONSTRAINTS检查表的完整性如果有数据库镜像，SQLServer尝试从镜像中检索页的拷贝。如果页能够从镜像中检索到并且页内容正确，页自动替换。当SQLServer从镜像中替换了坏的页，在sys.dm_db_mirroring_auto_page_repair视图中进行记录。数据库完整性检查查看数据库信息查看数据库属性和文件信息sp_helpdb显示数据库磁盘空间使用情况sp_spaceusedDBCCSQLPERF数据库架构架构只是一种组织数据库对象并为它所包含的对象指派权限的方法。架构本身可为任意数据库主体所有，同时包含多个由不同用户拥有的对象。架构只是对象容器。数据库架构创建架构CREATESCHEMAschema_name[AUTHORIZATIONowner_name]将对象转移到其它架构ALTERSCHEMAschema_1TRANSFERschema_2.object将object从schema_2结构转移到schema_1架构数据库快照

数据库快照是只读的、某一特定时间点的静态数据库副本。