SQLServer2008升级2016 迁移方案

原创王也 2021-07-08

5731

SQLServer2008升级2016
迁移方案

目录
前期准备 4
一、 SQL SERVER 2016 新环境准备 4

SQLServer 2016针对 alwayson环境要求： 4
DC及DNS安装 4
SQL Server 2016 安装 4
Ip预留 4
Window故障转移群集安装 5
SQL启用alwayson 14
二、建立升级检查表 15
三、对要升级的数据库运行DBCC CHECKDB 16
四、原库用户登录导出 16
五、原库任务计划作业导出 20
迁移步骤 21
一、停库 21
二、原库数据库完整备份 21
三、新库还原完整备份 21
四、新库导入用户及作业 21
五、检查孤立用户 21
六、刷新统计信息 21
七、执行检查表 22
八、新库ALWAYSON配置 22
九、启用新库 33

前期准备
一、 SQL Server 2016 新环境准备

SQLServer 2016针对 alwayson环境要求：

要求链接
确保此系统不是域控制器。域控制器上不支持可用性组。
确保每台计算机正在运行 Windows Server 2012 或更高版本。安装 SQL Server 2016 的硬件和软件要求

确保每台计算机都是 WSFC 中的一个节点。 Windows Server 故障转移群集 (WSFC) 与 SQL Server

确保 WSFC 包含足够的节点来支持可用性组配置。群集节点可以为一个可用性组托管一个副本。同一个节点不能托管来自同一可用性组的两个副本。群集节点可以加入多个可用性组，每个组包含一个副本。

咨询数据库管理员，了解需要多少个群集节点才能支持计划的可用性组的可用性副本。

DC及DNS安装
另准备一台服务器安装windows DC及DNS，不详述。
SQL Server 2016 安装
所有SQL节点服务器都加入DC，创建DC用户账户，SQL server 2016安装时的服务账户都用这个统一的域账户。
Ip预留
除了各节点服务器自身的ip外，其他：
Windows需要预留一个群集ip
alwayson需要预留一个侦听ip
Window故障转移群集安装
首先是配置第一台-SQL2016-01的故障转移群集
安装完成
然后第二台也安装完成–SQL2016-02
打开群集管理器后，接下来我们就是配置集群了，在配置群集之前建议先验证集群，将两个SQL Server服务器增加到节点中
因为我们是测试环境，没有配置心跳线，所以网络会有警告
验证没有问题后，我们开始创建集群，定义集群名称及网络地址
SQLCLU 192.168.5.17
确认信息，开始创建
因为我们没有添加仲裁，所以会提示一下信息，当然，我们也可以在后面单独配置仲裁
群集创建完成。
我们查看节点信息
接下来我们需要配置仲裁—群集名称–右击—更多操作–配置仲裁设置
在此我们选择高级仲裁配置
选择所有节点
我们再次配置文件共享见证，可以根据自己的环境进行选择配置
我们使用文件仲裁见证，服务器放在了DC上，所以提前需要在DC上创建一个共享文件夹
指定文件共享路劲到DC服务器上的共享文件夹
确认信息
创建完成
SQL启用alwayson
打开所有节点的SQL SERVER配置管理器后，打开SQL Server服务
然后我们勾选启用AlwayOn可用性组
保存后，我们需要重启数据库服务

二、建立升级检查表
数据库升级兼容性，用数据迁移助手（DMA）准备一系列需要在数据库升级前，升级期间和升级后执行的检查表。
数据迁移助手（DMA）下载地址：
https://www.microsoft.com/en-us/download/details.aspx?id=53595

分析界面如下，测试数据库ttt为例：

操作手册文档地址：
https://docs.microsoft.com/zh-cn/sql/dma/dma-migrateonpremsql?view=sql-server-2016

三、对要升级的数据库运行DBCC CHECKDB
在数据库相对空闲的时间，执行DBCC CHECKDB，以确保它们在执行升级前处于一致的状态。执行时间比较久，执行前备份数据库确保安全。

四、原库用户登录导出
导出代码如下：

USE master
GO
IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR

  SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
DECLARE login_curs CURSOR FOR

  SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ’
PRINT @tmpstr
SET @tmpstr = ‘** Generated ’ + CONVERT (varchar, GETDATE()) + ’ on ’ + @@SERVERNAME + ’ */’
PRINT @tmpstr
PRINT ‘’
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ‘’
SET @tmpstr = '-- Login: ’ + @name
PRINT @tmpstr
IF (@type IN ( ‘G’, ‘U’))
BEGIN – NT authenticated account/group

  SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
    -- obtain password and sid
        SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    -- obtain password policy state
    SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
    SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

        SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

    IF ( @is_policy_checked IS NOT NULL )
    BEGIN
      SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
    END
    IF ( @is_expiration_checked IS NOT NULL )
    BEGIN
      SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
    END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
  SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
  SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
  SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

EXEC sp_help_revlogin
Go

在原库执行，执行结果中的登录用户保存成文件，传输到新库服务器。

五、原库任务计划作业导出
打开作业栏目右键编写作业脚本到新窗口
保存脚本，到新服务器执行

执行结果保存成 .sql 执行文件，每个作业保存一个文件，传输到新数据库服务器上。

迁移步骤
一、停库
停止应用，确保数据库里的数据不再有变动。
二、原库数据库完整备份
原数据库做完整备份。
对于数据量大的库，可以采用完整备份+日志备份的方式，减少停库时间，即在停库前先完整备份数据库，到新库还原，再停库，做日志备份，到新库还原日志。

三、新库还原完整备份
在新库一个节点上还原数据库，更改兼容性级别。

四、新库导入用户及作业
在新库中执行之前保存的的数据库用户创建脚本及作业创建脚本。

五、检查孤立用户
执行以下代码检查用户和登录账户的关联情况

Use dbname
go
sp_change_users_login @Action=‘Report’;

六、刷新统计信息
执行以下命令刷新统计信息

USE dbname
GO
EXEC sys.sp_updatestats
GO

新版本数据库和老版本执行优化方式会有不同，在新库刷新统计信息有利于提高执行效率。
或者
如果时间允许，可以执行索引重建。

DECLARE @name varchar(100)
DECLARE authors_cursor CURSOR FOR Select [name] from sysobjects where xtype=‘u’ order by id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
　DBCC DBREINDEX (@name, ‘’, 90)
FETCH NEXT FROM authors_cursor INTO @name
END
deallocate authors_cursor

刷新统计信息或索引重建，执行一个就可以。

七、执行检查表
按检查表内容执行升级后检查。

八、新库Alwayson配置
Alwayson配置步骤：

新建可用性组
定义高可用组名称
创建高可用组前，我们需要对数据库进行完整备份。
我们先对数据库进行备份。
备份完成
接下来回到创建高可用性组的想到就可以满足条件了
默认只有一个副本，因为我们有两台服务器，所以我们就可以添加副本
连接第二个节点
我们按照自己的生产环境进行配置，即可。
我们提前在DC上创建了一个共享目录
我们先不创建侦听器
选择同步目录，需要注意的是，该同步目录只是在一开始创建高可用性组的时候才可以用到，所以路劲无所谓
确认信息
创建完成
创建完成后，我们可以看见相关的配置信息
接下来就是创建侦听器侦听器
一个侦听器包括虚拟IP地址、虚拟网络名称、端口号三个元素，一旦创建成功，用户就可以使用此名称来连接到可用性组中。
为侦听器定义一个名称和IP地址
创建完成
我们可以看见集群角色会自动配置好角色----是监听器的地址
最近后我们alwayon地址可以登录
192.168.5.16

九、启用新库
数据库迁移操作完成，更改应用链接，开启访问。

sql server

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

SQLServer2008升级2016 迁移方案

评论