说明:

由于临时接到做OA的主从环境,基于Windows 200R2 SQLServer2008 R2,由于搜索的资料都不完整,好多重要之处有遗漏,亲自动手做了好几次,填完了坑,整理以备忘记与分享,如有错误遗漏之处欢迎指正交流~

一、配置环境

主库: 10.8.11.214 数据库版本:SQL Server2008 R2 服务器名称:WIN-D4GRPQKED93
从库: 10.8.11.79 数据库版本: QL Server2008 R2 服务器名称:WIN-3ME2DJ8L9KT

注意点:
服务器名称(主机名)最好在确定好后再安装SQL Server 2008R2数据库这样能确保主机名,实例名保持一致
如果是在安装好sqlserver2008后修改了主机名,通常会导致主机名和实例名不一致问题;
可通过下面的命令进行检测和修复

查看服务名称(默认安装时的实例名也是和主机名一致)和主机名是否一致
    select @@servername
    select serverproperty('servername')

如果不一致请进行修改:
    if serverproperty('servername') <> @@servername
    begin
    declare @server sysname
    set @server = @@servername
    exec sp_dropserver @server = @server
    set @server = cast(serverproperty('servername') as sysname)
    exec sp_addserver @server = @server , @local = 'LOCAL'
    End

以下是我的运行结果:
SQLServer 2008R2主从部署实战

二、搭建主从准备

1、主从两台分别创建sqladmin用户加入administrators组删除默认的users组,并且设置"密码永不过期"和"用户不能更改密码" 为sqladmin创建一个密码
2、主从两台设置网络共享
打开控制面板-->网络和 Internet-->网络和共享中心-->高级共享设置 如图:
SQLServer 2008R2主从部署实战
3、在主从库上分别创建用于存放主从备份日志文件的共享文件夹DB_Backpup(sqladmin用户能读写administrators拥有者)
步聚如下:
SQLServer 2008R2主从部署实战

右击 ”DB_Bakcup“ -->共享-->特定用户 -->添加sqladmin用户读写权限
如图:
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战

在"高级共享"窗口中,勾选"共享此文件夹"选项,然后单机"权限"按钮对该共享文件夹的权限进行设置。需要让sqladmin用户具有完全控制该文件夹的权限,先将默认的“erverone”用户删除,然后添加sqladmin用户,并在“sqladmin”的权限中勾选“完全控制”,“更改”和“读取”项,然后单击两次“确定”按钮保存共享设置。 如下图:
SQLServer 2008R2主从部署实战
在NTFS文件系统中,还需要设置用户对该目录的安全权限,如果安全权限不够,系统在写入备份文件的时候会报没有权限的错误。
可以在“安全”选项卡,单机“编辑”按钮,在“DB_Backup的权限”界面,单击“按钮”,添加sqladmin用户,然后在“sqladmin的权限”中选择“完全控制”权限,单机“确定”按钮保存权限信息。
如下图:
SQLServer 2008R2主从部署实战

4、分别从主数据库服务器上和从数据库服务器上打开"SQLServer配置管理器",将SQLServer服务和SQLServer代理服务的“登录身为”sqladmin用户且启动模式为:自动
如图:
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战

至此重启主库服务器!

三、配置SQLServer日志传送

主库重启后以sqladmin用户登录系统并能sqladmin登录数据库
在主数据库上配置
在主数据库服务器上打开SQL Server Management Studio管理控制台,连接到本地的主数据库服务器上,此时应确保下面的“用户名”中的用户具有控制该SQL Server服务器的权限。单击“连接”按钮连接到本地SQL Server数据库示例中。
如图:
SQLServer 2008R2主从部署实战

右击数据库服务器实例,选择“属性”选项,在弹出的“服务器属性 - WIN-D4GRPQKED93”界面中,单机左侧的“安全性”,然后在右侧窗口中的“服务器身份验证”中选择“SQLServer和Windows身份验证模式”,并勾选“服务器代理账户”中的“启用服务器代理账户”选项。输入正确的“代理账号”和“密码”,单击“确定”按钮保存配置信息。
如图:
SQLServer 2008R2主从部署实战

在主数据库服务器WIN-D4GRPQKED93中配置要同步的数据库UFSystem属性
如图:
SQLServer 2008R2主从部署实战

设置完之后选择“事务日志传送”,勾选“将此数据库启用未日志传送配置中的主数据库”选项,单击“事务日志备份”中的“备份设置按钮”,打开“事务日志备份设置”界面。
SQLServer 2008R2主从部署实战

如图:
SQLServer 2008R2主从部署实战

主库配置先暂停,到从库上测试是否能访问主库的网络共享文件夹DB_Backup文件夹
备库上进行操作:
win+R,输入网络路径看能否访问\10.8.11.214\DB_Backup
如图:
SQLServer 2008R2主从部署实战

右击-UFSystem--属性 --事务日志传送--备份设置--在“作业计划属性”界面,确认“计划类型”为重复执行,为测试效果明显,设置为15秒执行一次作业计划。最后确认“持续时间”,根据自己4需要设置,如果一直备份的话,可以设置为“无结束日期”
如图:
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战
设置完成,保存之后再次打开“事务日志备份设置”界面,则备份作业的作业名称后面变成“编辑作业”按钮,单击进去,将“所有者”修改为sqladmin。
如图:
SQLServer 2008R2主从部署实战

单击数据库属性界面的“辅助数据库”中的“添加”按钮,打开“辅助数据库设置”窗口。
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战
如果浏览不到从库,需要从库启动SQL Server Browser服务

SQLServer 2008R2主从部署实战

SQLServer 2008R2主从部署实战

在第一次连接时可能无法连接到辅助数据库,会报错:
a、用户XXX登陆失败
解决方法:
在从库上使用sa登录,master上新建查询,输入
create LOGIN [WIN-3ME2DJ8L9KT\sqladmin] FROM WINDOWS
b、用户XXX不在sysadmin固定的服务器角色中
在从库上执行
EXEC sp_addsrvrolemember 'WIN-3ME2DJ8L9KT\sqladmin','sysadmin'
连接成功后对辅助数据库进行设置
如图:
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战
查看辅助数据库的数据目录
如图:
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战
设置完之后点击确定按钮,在数据库属性配置界面将配置好的脚本保存到本地,最后点击确定如图:
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战
到此数据库主从配置完成,可以在主库进行操作,在从库上查看对应的数据是否同步,如果没有同步成功,可以在从库上查看同步任务状态,查看失败原因。
以下是同步成功的截图,同时从库上已经同步了主库上的UFSystem数据库
如图:
SQLServer 2008R2主从部署实战
SQLServer 2008R2主从部署实战

四、验证主从同步
在主库UFSystem中创建一张表
SQLServer 2008R2主从部署实战
稍等15s以后到从库上查看
SQLServer 2008R2主从部署实战

此时可以发现已经完成了主从同步

参考文档:
https://yq.aliyun.com/articles/601067
https://www.cnblogs.com/tatsuya/p/5025583.html
https://www.cnblogs.com/brucewang/p/7825128.html