SQL Server一种高可用性和灾备性解决方案,添加AlwaysOn 可用性组允许在多个数据库副本之间同步数据,用性并提供了故障转移和自动故障恢复功能,副本以确保数据库的实战持续可用性和数据保护。
DECLARE @Version NVARCHAR(128) SET @Version = CONVERT(NVARCHAR(128),副本SERVERPROPERTY ('ProductVersion'))SELECT CASE WHEN @Version like '11%' THEN 'SQL SERVER 2012' WHEN @Version like '12%' THEN 'SQL SERVER 2014' WHEN @Version like '13%' THEN 'SQL SERVER 2016' ELSE 'Unknown' END AS 'Sql Server Version Name', SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductLevel') AS ProductLevel
配置SQL SERVER2012服务器,用性配置静态IP地址。副本
关闭防火墙和自动更新功能
安装.NET Framework 3.5功能和故障转移集群功能。
将计算机加入域,将与用户添加进本地管理员组,并配置DNS地址。
打开故障转移集群管理器,连接到现有集群后,右击节点,选择添加节点。
此时进入添加节点向导,进入验证阶段,点击下一步。
点击浏览,进入选择计算机页面,此时位置为整个域。点击高级-立即查找,选择需要添加的计算机,点击确定,服务器添加成功。
一直点击下一步,进入验证阶段。
验证完成即进入添加节点向导阶段。
点击下一步。
添加集群节点成功后,此时可以打开配置管理器,启用ALWAYSON高可用性。启用后重启SQL SERVER 实例。
先测试在WIN-P4节点用域帐号能否登录到其他节点。测试成功后,可以进行完备-日志备-完整还原-日志还原。
DECLARE @name NVARCHAR(256) -- database name DECLARE @path NVARCHAR(512) -- path for backup files DECLARE @fileName NVARCHAR(512) -- filename for backup DECLARE @fileDate NVARCHAR(40) -- used for file name -- specify database backup directorySET @path = '\\172.18.248.98\sharebackup\' -- specify filename formatSELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databasesAND state = 0 -- database is onlineAND is_in_standby = 0 -- database is not read only for log shipping OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '.BAK' BACKUP DATABASE @name TO DISK = @fileName WITH STATS=10, COMPRESSION FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
DECLARE @name NVARCHAR(256) -- database name DECLARE @path NVARCHAR(512) -- path for backup files DECLARE @fileName NVARCHAR(512) -- filename for backup DECLARE @fileDate NVARCHAR(40) -- used for file name -- specify database backup directorySET @path = '\\172.18.248.98\sharebackup\' -- specify filename formatSELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databasesAND state = 0 -- database is onlineAND is_in_standby = 0 -- database is not read only for log shipping OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '.TRN' BACKUP LOG @name TO DISK = @fileName WITH STATS=10, COMPRESSION FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
RESTORE DATABASE xuejinnewpro FROM DISK = 'E:\AlwaysonBackup\xuejinnewpro.BAK'WITH NORECOVERY, STATS = 5GORESTORE DATABASE XUEJINPRO FROM DISK = 'E:\AlwaysonBackup\XUEJINPRO.BAK'WITH NORECOVERY, STATS = 5GO
RESTORE log xuejinnewpro FROM DISK = 'E:\logbackup\xuejinnewpro.TRN'WITH NORECOVERY, STATS = 5GORESTORE log XUEJINPRO FROM DISK = 'E:\logbackup\XUEJINPRO.TRN'WITH NORECOVERY, STATS = 5GO
在主副本上选择可用性组-DB01,选择添加副本,此时跳出添加副本向导界面。在辅助副本中是没有添加副本这个选项的。
点击下一步,连接原有的辅助副本。
点击下一步,添加副本.
点击下一步,选择仅连接.点击下一步进行创建。
点击下一步,直至可用性组创建成功。
注意:若只对一个数据库进行了还原操作,则在添加副本时会提示副本创建失败,原因是为还原的数据库无法连接。退出后会发现其实已经添加副本成功,做过还原操作的数据库也已同步成功。而失败的数据库虽然同步失败,但是会出现在可行性数据库列表中。此时只需要对该数据库进行完备-日志备-完整还原-日志还原后,在可用性数据库列表下选择该数据库,右键选择连接,该数据库即可成为可用性数据库中的一部分并正常同步。
SELECT DISTINCT ar.replica_server_name, drcs.database_name, drs.database_id, drs.synchronization_state_desc, drs.database_state_descFROM sys.availability_replicas ar JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id=drs.replica_id JOIN sys.dm_hadr_database_replica_cluster_states drcsON drs.group_database_id=drcs.group_database_id
主副本都创建只读用户的情况:step1: 在主副本创建用户ze_bi:通过TSQL查询SIDselect sid from sys.sql_logins where name = 'ze_bi'step2: 查询出主副本sid后,在辅助副本上创建ze_bi:create login ze_bi with password='passwd', sid=主副本查询的sid
安装相关补丁即可。
R包需要手动下载。
SQL运行在域用户下,服务器也在域中能通过IP连接,但是不能通过计算机名连接。
测试计算机名也能正确解析。
最后通过setspn解决。
setspn -D MSSQLSvc/<servername.domainname>:1433 <servername> setspn -D MSSQLSvc/<servername.domainname> <servername>
这里不需要重启机器,过了几分钟就能连接了。
可以通过以下方法解决该问题。1.新建一个test登录名,但是不要添加数据库映射。2.使用脚本,将孤立用户test关联到登录名test上:Use [数据库名]gosp_change_users_login 'update_one', 'test', 'test'
DECLARE @name NVARCHAR(256) -- database name DECLARE @path NVARCHAR(512) -- path for backup files DECLARE @fileName NVARCHAR(512) -- filename for backup DECLARE @fileDate NVARCHAR(40) -- used for file name -- specify database backup directorySET @path = '\\172.18.248.98\sharebackup\' -- specify filename formatSELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databasesAND state = 0 -- database is onlineAND is_in_standby = 0 -- database is not read only for log shipping OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '.BAK' BACKUP DATABASE @name TO DISK = @fileName WITH STATS=10, COMPRESSION FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
DECLARE @name NVARCHAR(256) -- database name DECLARE @path NVARCHAR(512) -- path for backup files DECLARE @fileName NVARCHAR(512) -- filename for backup DECLARE @fileDate NVARCHAR(40) -- used for file name -- specify database backup directorySET @path = '\\172.18.248.98\sharebackup\' -- specify filename formatSELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databasesAND state = 0 -- database is onlineAND is_in_standby = 0 -- database is not read only for log shipping OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '.TRN' BACKUP LOG @name TO DISK = @fileName WITH STATS=10, COMPRESSION FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
RESTORE DATABASE xuejinnewpro FROM DISK = 'E:\AlwaysonBackup\xuejinnewpro.BAK'WITH NORECOVERY, STATS = 5GORESTORE DATABASE XUEJINPRO FROM DISK = 'E:\AlwaysonBackup\XUEJINPRO.BAK'WITH NORECOVERY, STATS = 5GO
RESTORE log xuejinnewpro FROM DISK = 'E:\logbackup\xuejinnewpro.TRN'WITH NORECOVERY, STATS = 5GORESTORE log XUEJINPRO FROM DISK = 'E:\logbackup\XUEJINPRO.TRN'WITH NORECOVERY, STATS = 5GO
-- this turns on advanced options and is needed to configure xp_cmdshellEXEC sp_configure 'show advanced options', '1'RECONFIGURE-- this enables xp_cmdshellEXEC sp_configure 'xp_cmdshell', '1' RECONFIGUREEXEC XP_CMDSHELL 'net use Y: \\172.18.248.98\sharebackup /user:AP1AZRAP3051\bcc_byol_project 6GB!eZ!2m4KmfI1l'EXEC XP_CMDSHELL 'Dir Y:' RECONFIGURE;GO
https://blog.csdn.net/weixin_38357227/article/details/79115005。
责任编辑:姜华 来源: 今日头条 SQL ServerAlwaysOn(责任编辑:焦点)
【益路华彩·公益助农】让爱传递携手同行 BOSS商学院情系“最美逆行者”
广西专员办对南宁市海绵城市建设试点专项资金进行了重点绩效评价工作
正业国际(03363.HK)全年纯利下降33.35% 每股基本盈利人民币11分
【益路华彩·公益助农】蓝天茶业关爱基层工作者 积极践行社会责任
银行理财子公司《净资本管理办法》落地 2020年3月1日起施行
同济科技(600846.SH):终止2017年度配股公开发行证券方案 维护投资者利益