SQLserver运维必备：权限管理、备份、复制

   小生博客：http://xsboke.blog.51cto.com

                                -------谢谢您的参考，如有疑问，欢迎交流

一、权限简介

二、备份简介

三、复制简介

四、案例

。。。。。。。。。。。。。

一、 权限

  1. SQL Server 的安全机制

    1）客户机的安全机制

    2）服务器的安全机制

       登录实例的账号和密码

       身份验证模式

       权限角色

    3）数据库的安全机制

       访问数据库的“数据库用户”

       权限角色

    4）数据对象的安全机制

       查询、更新、插入、删除    

  2. 登录权限设置

    1）SQL Server的身份验证模式

      A）Windows身份验证模式

        使用Windows用户和密码

        适用于局域网内部使用，如Windows域环境

      B）SQL Server 和Windows 身份验证模式（混合验证模式）

        用户名通过SQL Server创建并存储

    2）登录权限设置

      A）设置登录账户

        Windows用户或SQL Server用户。

      B）SA账户的使用

        设置复杂密码，

        建议禁用SA，并创建一个拥有与SA权限相同的超级用户。

  3. 访问许可权限设置

    1）服务器级别设置：只对服务器有效，对数据库和表无效

n  作用于整个实例

n  访问实例

n  在实例中创建数据库、管理和审核登录账户等管理权限

    2） 数据库级别设置：只对单个数据库有效

n  特点：

作用于单个数据库，

对数据库进行查询、更新、备份和还原等操作。

n  数据库用户：

登录账户的一个映射。

n  内置数据库用户

dbo：数据库所有者，

guest：来宾用户。

    3）对象级别权限设置

n  更加细化的权限，作用于表、视图、存储过程等

n  为表授权

n  为数据库授权

n  为存储过程授权

二、 备份

  1. 备份类型

1）完整备份

备份整个数据库、部分事务日志、数据库结构和文件结构，

是任何其他备份类型的基础。

2）差异备份

备份上一次完整备份之后所有更改的数据

3）事务日志备份

主要是T-SQL语句，记录了数据库的所有改变，相当于增量备份，支持时间点还原。

 2. 恢复操作

1）每次恢复数据库必须选择完整备份

2）如果在完整备份后使用的都是差异备份，那么选择完整备份后，只需要选择最后一次差异备份即可

3）如果在完整备份后使用的都是事务日志备份，那么需要选择每次的备份去恢复

  3. 备份设备

1）SQL Server支持的媒体：磁盘、磁带等

2）备份设备

物理备份设备：是操作系统用来标识备份设备的名称

如C:\Backups\Accounting\Full.bak

逻辑备份设备：是用户定义的别名，用来标识物理备份设备

3）使用备份设备的优点：可以查询之前的备份的信息

三、 复制

  1. 复制简介

1）作用

实现数据的同步，将主数据库上的数据更新到其他数据库

2）优点

节约成本、降低工作量

3）组成：发布服务器、分发服务器、订阅服务器

注释：

a.发布服务器：主数据库。

b.分发服务器：发布服务器通过分发服务器将数据分发到订阅服务器上（分发服务器一般不独立，与发布服务器在一台机器上）

c.订阅服务器：接收同步数据的服务器。

 2. 配置复制时需要注意：

l  做复制的表必须要有一个主键

l  登录模式必须为混合模式（做异地复制时）

l  代理服务必须开启，代理服务与自动化相关（自动同步数据）

l  sql server的端口1433必须开启

l  sql server配置管理器中的网络配置中TCP/IP协议要

  1）发布方式

l快照发布：

只能将发布服务器上的数据同步到订阅服务器上，但是不能更新数据。

l事务发布：

可以把数据发布到订阅服务器上，而且在发布服务器上更改表时，订阅服务器也会更新，但是订阅服务器更新数据时，发布服务器不会同步，属于单向的

l合并发布：

资源占用多，但是可以实现发布服务器更新数据，订阅服务器也会更新，订阅服务器更新数据，发布服务器也会更新

l具有可更新订阅的事务发布：

05版增加的新功能

解释：一般后两种发布方式使用比较少，因为一般不会让双方都可以更新数据，一般都使用事务发布，做事务发布的时候必须先做一次快照发布（根据向导设置即可）

   2）订阅方式：有两种

  a.在分发服务器上进行推送订阅，也叫强制订阅（在分发服务器上）。

  b.请求订阅（在订阅服务器上使用）。

   3）实例名修改后需要执行的语句：

sp_dropserver原数据库的实例名：数据库的实例名默认使用计算机的名字，所以当计算机名改变时，需要删除之前的实例否则无法识别新的实例名（如果实例名有特殊字符则需要加中括号‘[]’）

sp_addserver新的实例名,local：应用新的实例名，如果是本地计算机，必须在实例名后面加‘,local’，然后重启实例

  3. 具体实施流程

    1） 在发布服务器上新建复制

    2） 选择订阅方式

四、按列

本例概括

例：发布服务器和分发服务器为：WIN-B8RVQT412MQ

订阅服务器为：hangzhou

要求：1. 使用复制功能，使WIN-B8RVQT412MQ上的shop表可以同步到hangzhou上

      2. 使用事务快照（只有发布服务器更新数据会同步到订阅服务器上，相反则不行）

      3. 使用推送订阅（为了保证统一性）

注释：经过实验，发现，使用事务快照虽然订阅服务器更新数据，发布服务器不会同步，但是订阅服务器可以更新同步过来的表的数据，只是不同步而已。

1. 修改计算机名后需要做的操作（因为修改计算机名后数据库可能无法识别）

2. 复制第一步，在订阅和分发服务器上开启1433端口，用于保证数据库的连通性（我们这里分发和发布服务器安装在一块）。

3. 分别开启发布服务器和订阅服务器的代理服务，因为代理服务会涉及到自动化。

4. 查看SQL SERVER的配置工具中TCP/IP协议是否开启，否则会影响分发服务器与订阅服务器的连通性。

5. 登录模式必须为混合模式，模式修改后最好重启实例。

6. 做复制的表必须要有一个主键，下面举例。

7. 开始发布此表（此例为事务发布，具体涵义请查看详细文档）

8. 开始新建订阅（此例为推送订阅，所以在分发服务器上创建即可，这里只说明一些需要注意的地方）

9. 验证复制是否成功。

10. 验证发布服务器更新数据，订阅服务器是否同步。