mssql dba操作规范
良好的规范是减少故障的基础。
[TOC]
基本规范意识
所有操作应该遵循下述原则:
- <备份高于一切> 所有DML操作，必须事前做好备份，必要时可逆；
- <事前通知> 所有影响业务的操作，必须事先通知DBA主管和业务方；
- <大量操作分批做> 超过10W行的操作，必须分批处理；
- <工单审计> 所有上线需求必须走工单系统，口头通知视为无效；
服务器操作
- 禁用自动更新服务，原则上不允许在数据库服务器上安装补丁（重大安全更新除外）；
- 在数据库服务器上安装任何软件，需DBA经理确认，并经过安全同事检测；
- 对数据库服务器的维护操作，请走内部网络；
- 拷贝10G以上的文件必须限流，控制在50MB/s以下，如果无法限流，则只能在凌晨2点-8点期间拷贝；
数据库对象操作
数据库操作
创建数据库
- 数据库命名必须遵守《sqlserver建库建表规范》
- 调整自动增长设置，比如将默认 1MB 改为 100MB；
- 设置数据库所有者为 sa ，数据库恢复模型(RECOVERY MODE)使用完整模式 Full；
备份数据库
- 大于100G的生产库，只能在凌晨2点-8点期间做完整备份；
- 必须启用压缩备份 with compression；
工具库的使用
| 数据库名称 | 功能 | 备注 |
|---|---|---|
| bak | 用于存放dml操作之前的表备份 | 简单模式，不做备份，数据可接受丢失 |
| configdb | 用于存放dba提供的各类工具资源 | 简单模式，不做备份，数据可接受丢失 |
数据表操作
新建表
- 确认新表有主键、必要的索引；
- 确认表存储的数据的业务属性，如果不被业务依赖，且数据量1天超过100W条记录，不允许放在业务库中，只能放日志库；
- 确认是否可以做切表处理；
- 若数据有效性只有n天，确认是否可以转mongodb(利用ttl索引便于定期清理)；
备份表
- 请规范使用：
对象名_备份日期_备份人
--举例
select * into bak..orders_20170224_yuxiukai
from orders with(nolock) where orderid=1206228
删除表
- 已上线的表，原则上不允许改名或删除，除非和开发确认该表已经下线；
- 以表名为关键字，通过trace监控主库和从库1天以上，确认无读写操作；
- 改名（对象名del备份日期）后观察7天再删除，删除之前做归档；
字段操作
添加字段
- 对大于100W条记录的表，只能添加允许为空的字段；
- 要添加有默认值的NOT NULL字段，必须先允许NULL+默认值后分批更新值，再更改为NOT NULL；
删除字段
- 已上线的表不允许进行字段删除(除非明确告知已确认影响范围)；
更改字段类型
| 规范要求 | 允许 | 不允许 |
|---|---|---|
| 只能更改长度，不能更改类型 | char → char | int → bigint |
| 长度只能改大，不能改小 | varchar(10) → varchar(20) | varchar(20) → varchar(10) |
索引操作
添加或重建索引
- 当该实例为线上业务所依赖时，记录大于100W条的表只能在凌晨2点-6点进行，且使用在线创建模式；
--举例
CREATE NONCLUSTERED INDEX idx_city ON ip2location(city) WITH(ONLINE=ON)
删除索引
- 必须能够明确的证明该索引不被业务查询所使用；
- 删除索引的顺序：先禁用，再删除；
--举例
ALTER INDEX idx_id ON PayOrder DISABLE
DROP INDEX idx_id ON PayOrder
数据操作
插入数据
- 单条语句插入数据大于10W条或者超过100MB时，必须分批操作；
- 每批小于3000条，批次间主动停顿1秒以上；
更新数据
- 更新的记录需备份到bak库中；
- 单条语句更新数据大于10W条时，必须分批操作，每批小于3000条，批次间主动停顿1秒以上；
删除数据
- 需业务对应研发主管确认，才能删除数据；
- 删除的数据需备份到bak库中；
- 单条语句删除大于5W条时，必须分批操作，每批小于1000条，批次间主动停顿1秒以上；
其他
- DBA自己写的DML，影响行数超过10W行的，需找其他DBA二次确认，才能执行；
权限操作
- 不允许使用sa作为业务账号；
- 原则上业务账号只分配db_datareader、db_datawriter等dml权限，不给ddl(即db_owner) 权限；
- 原则上只读账号只分配db_datareader权限；
- 原则上禁止在生产环境申请开通个人帐号，也不允许在代码中使用个人账号连接数据库；
- 新增账号，DBA请务必及时登记账号及密码信息，保存密码的文件必须加密；
- 所有备份、监控、报表等作业建议使用专用账号，并严格控制相关权限；
- 数据库账户，一定要做到权限划分明确，读写帐号分离，并且有辨识度，能区分具体业务；
- 所有账户必须都在主库创建，只读查询只能从非候选上操作；
- 单个账号访问多个实例，则请通过指定 @sid 并使用sp_addlogin创建，以避免db整合后访问异常；
- 对DBA离职或转岗的同事要及时进行权限回收及账号注销；
备份操作
备份策略
| 数据库类型 | 备份策略 | 周天 | 频率 | 起止时间 | 说明 |
|---|---|---|---|---|---|
| SQL Server | 完整备份 | 周一三五七 | Daily | 03:00 | |
| 差异备份 | 周二四六 | Daily | 03:00 | ||
| 日志备份 | 每天 | 20min | 00:00 - 23:59 |
其他操作
- DBCC freeproccache , DBCC dropcleanbuffer ,CheckPoint等语句不能在生产环境中使用，会清空内存、编译好的执行计划，造成短时间内cpu、io 急剧上升；
- 线上数据库如果需要做SQL Server的failover、Mongo的stepdown、MySQL的主从切换，必须事先邮件通知相关业务方、开发、运维，操作时间为凌晨0点-8点，切换时间控制在1分钟内；
- 修改数据库相关的DNS，需由另一个DBA二次确认；
- 涉及数据库的影响范围较大的操作(如：拆分库分表、DB迁移等)，DBA需提前做好可行的操作方案及异常情况下的回滚方案，并给出详细步骤的演练过程报告(包括：影响范围、执行时间等)；最终该方案经过DBA团队、研发、运维等相关同事共同进行可行性评估及QA后方可通过；
- 涉及数据库的影响范围较大的操作(如：拆分库分表、DB迁移等)，必须提前与研发、运维同事沟通，并通知相关上下游业务方；对于需要停机维护的操作，需求方必须提前一周发起停机申请流程；