如何迁移32位 SQL Server 2005到64位SQL Server 2016

由于从SQL Server 2016开始不支持32位版本,很多用户将数据库实例升级到2016或者以上时,需要做数据库的迁移。本文以SQL Server 2005为例进行讲解。

升级前准备:

由于是做数据库迁移,我们需要新建一个2016数据库实例,然后将系统数据库,用户数据库和其他一些组件迁移到新的实例上。

硬件考虑主要有如下几点:

 https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-2017   

• .NET Framework 4.6, SQL Server 2016 (13.x) setup automatically installs .NET Framework.

• Windows 8.1, and Windows Server 2012 R2 require KB2919355 before installing .NET Framework 4.6.

• a minimum of 8 GB of available hard-disk space of SQL Server installation folder. C: driver by default

• SQL Server requires Super-VGA (800×600) or higher resolution monitor.

• Memory: minimum 1GB, recommend 4GB and should be increased as database size increases to ensure optimal performance.

• Processor Speed: Minimum: x64 Processor: 1.4 GHz. Recommended: 2.0 GHz or faster

• x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support

因此我们在做数据库升级时,最好先将操作系统升级到最新或者较新版本。通常需要留意的是内存和磁盘容量。另外如果在windows2012R2上安装SQL Server 2016时,需要确保已经安装更新KB2919355. 安装新的数据库实例时,最好检查一下老版本的数据库实例的字符集,安装时选择同样的字符集,否则只有重建系统数据库来修改实例字符集,非常麻烦。可以通过SSMS查看实例的字符集。

数据库迁移:

升级前告知业务部门暂停数据库的写入操作,以防止升级后数据库不一致。可以将用户和系统数据库设置为只读模式。不将MSDB设置为只读模式是因为备份数据库本身会对msdb进行写操作。

select ‘alter database [‘+name+’] SET READ_ONLY WITH ROLLBACK IMMEDIATE’ from sys.databases where name not in(‘master’,’tempdb’,’msdb’)

alter database [model] SET READ_ONLY WITH ROLLBACK IMMEDIATE

alter database [ReportServer] SET READ_ONLY WITH ROLLBACK IMMEDIATE alter database [ReportServerTempDB] SET READ_ONLY WITH ROLLBACK IMMEDIATE

alter database [test] SET READ_ONLY WITH ROLLBACK IMMEDIATE

备份用户数据库,并还原到新的实例。

select ‘backup database [‘+name+’] to disk=”C:\Program Files\Microsoft SQL Server\MSSQL13.ECHO\MSSQL\Backup\’+name+’_’+convert(varchar(30), getdate(),112) + replace(convert(varchar(30), getdate(),108),’:’,”)+’.bak”’ from sys.databases where name not in (‘tempdb’, ‘msdb’)

restore database [example] from disk=’C:\sqlbackup\86system\example_20190702100251.bak’ with file=1 ,replace, move N'{logical name for data file}’ to N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\DATA\model.mdf’, move N'{logical name for transaction log file}’ to N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\DATA\modellog.ldf’

或者用detach/attach的方法将用户数据库迁移到更高的版本。

select ‘EXEC sp_detach_db [‘+name+’] ,true’ from sys.databases where name not in (‘master’,’tempdb’,’msdb’,’model’)select ‘EXEC sp_detach_db [‘+name+’] ,true’ from sys.databases where name not in (‘master’,’tempdb’,’msdb’,’model’)

EXEC sp_attach_db @dbname = N’081db_1036′, @filename1 = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\DATA\081db_1036.mdf’, @filename2 = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\Data\081db_1036.ldf’;

由于系统数据库没法通过detach/attach或者backup/restore的方式来升级到新的实例,需要迁移以下内容:

迁移logins,可以在2005的实例上运行如下脚本批量导出Login并将生成的SQL脚本运行到新的实例上,从而实现Login的迁移,此脚本也包含权限的迁移。在新的服务器上创建login时,一定要注意SID要和原来的实例匹配,否则可能会导致登录失败,这是因为用户数据库里的user account匹配的是原来Login的SID。

迁移SQL JOB,如果使用了SQL Server Agent job, 需要手动迁移这些job。可以通过SSMS来快速进行job迁移。View–>Object Explorer Details,单击job,在右边弹出的窗口选中所有的job, 右击选择Scripts job as–>CREATE TO–>New Query Window,导出了所有job的定义,然后在新的实例上运行。如果里面涉及到密码,可能需要手动修改后运行。

迁移server的配置信息:

select * from sys.sysconfigures

迁移MSDB, modelDB, master里的user account和其它对象, 可以通过右键数据库名,Tasks–>Generate Scripts…来导出数据库对象,并在新的实例里运行。

迁移link servers定义,可以运用迁移agent job的方式来迁移Link servers定义。如果还安装并使用了integrition service和reporting  services. 也需要迁移这些服务。

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/choose-a-database-engine-upgrade-method?view=sql-server-2017#migrate-to-a-new-installation

升级后:

升级后可以rebuild index, update statistics. 可以每个数据库中运行如下脚本,然后执行命令 exec AzureSQLMaintenance ‘all’ 来在每个数据库重建索引并更新统计信息。

https://raw.githubusercontent.com/yochananrachamim/AzureSQL/master/AzureSQLMaintenance.txt

将数据库兼容模式修改为最新的版本,并让业务组测试性能。

此升级的不足在于新的数据库实例名和之前的不一样,应用需要修改连接字符串来重新连接到新的数据库。对于一些hardcode的应用来说是不能接受的,可以卸载老的实例,重建一个同样版本的实例,通过backcup restore再迁移一次数据库。但是过程比较麻烦。如果客户的实例运行在虚拟机上,可以找一台备机,将数据全部备份过去,在备机上装相同实例名的数据库实例,再根据以上方法做数据迁移,最后修改机器名,切换IP地址。

如果有SQL Server 2005 64位的安装介质,也可以安装64位的SQL Server,然后通过backup restore  user database, system database 来实现数据库迁移。

顺序如下:

CMD:

net stop MSSQLSERVER

net start MSSQLSERVER /T3608

restore msdb

restore model

net stop MSSQLSERVER

net start MSSQLSERVER /m /T3607

restore  master

net stop MSSQLSERVER

net start MSSQLSERVER /T3608 /T3609

alter database tempdb modify file(name=tempdev,filename=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\DATA\tempdb.mdf’)

go

alter database tempdb modify file(name=templog,filename=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\DATA\templog.ldf’)

go

alter database msdb modify file(name=MSDBData,filename=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\DATA\MSDBData.mdf’)

go

alter database msdb modify file(name=MSDBLog,filename=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\DATA\MSDBLog.ldf’)

go

alter database model modify file(name=modeldev,filename=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\DATA\model.mdf’)

go

alter database model modify file(name=modellog,filename=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.TESTUPGRADE\MSSQL\DATA\modellog.ldf’)

go

alter database master modify file(name=master,filename=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2016\MSSQL\DATA\master.mdf’) go

alter database master modify file(name=mastlog,filename=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2016\MSSQL\DATA\mastlog.ldf’) go

如果master db里还有user database的信息,需要删除user db. 重启实例。

retore userdb or  detach/attach user db

这种方式不需要一步步迁移数据库对象,对于复杂的环境是比较推荐的,毕竟backup/restore是最安全和完整的迁移方式。在还原系统数据库时,建议以msdb–>model–>master的方式进行还原,如果先还原master, 后面还原msdb和model可能会失败,另外tempdb是每次重启实例时基于model重建的一个系统数据库,如果model还原失败会导致tempdb起不来,这个时候可以用/T3609不启动tempdb的方式来启动数据库实例。在还原master之后,需要以/T3608 /T3609的方式启动数据库实例,并修改对应数据库的文件位置,否则可能因为找不到数据文件而启动失败。