How to migrate the schema of a SQL Server database from on-premise to SQL Server on AWS RDS with non-supported features

eoxn13cs 于 12个月前发布在 SQL Server

关注(0)|答案(1)|浏览(145)

I'm working on taking a on-premise server that works with SQL Server 2019 and migrating this to the cloud. The data right now is not the important thing, but rather the schema since this is a proof of concept. The main issue is that the on-premise server uses filestream to sometimes handle files. This will have to change in the future as refactoring and application updates take place.

The easiest way I thought would be to generate a schema .sql script from the old db and run that in the new environment, but this generated a TON of errors (25k).

Most of the errors include:

Failed permissions in database 'master'
Not finding certain objects in the new clean DB
Extended properties are not permitted on an object or it doesn't exist
Invalid data types
Database doesn't exist or permission not allowed
Filestream feature is disabled

So this probably won't work as a drop in solution to get the schema migrated to the new db. I've heard about AWS DMS (data migration service), but I don't know a lot about this. I'm asking, what tools could I look into to migrate over to RDS when RDS doesn't support features native to SQL Server?

sql-server

来源：https://stackoverflow.com/questions/73339479/how-to-migrate-the-schema-of-a-sql-server-database-from-on-premise-to-sql-server

1条答案

按热度按时间

mkh04yzy1#

One way to import schema is through the generated scripts wizard. You will have to manually tweak some things to make filestream and the local configuration of the sql server work nicely with aws RDS.

Generate and Publish Scripts Guide

Go to the source database
Right click the database in the menu on the left (Object Explorer) Tasks>Generate Scripts
Select All tables, procedures, etc.. except for filestream tables.
In the Scripts wizard pop up under Set Scripting Options, choose to make a .sql file, under advanced options, choose Schema Only. This will generate a script with only meta data for the tables and not the data in them
Generate the file.
Copy the .sql file over to the EC2 instance (probably the Bastion Host) that is connected to the RDS instance.
Open MS SQL Management Studio and right click on the top most object in the Object Explorer and open a new query.
Copy and paste the code inside the .sql file into the query window.
Change the file path location of the data and log file to be D:\rdsdbdata\DATA\TEST_AWS.mdf and D:\rdsdbdata\DATA\TEST_AWS_Log.ldf respectively. Any other file location will not be recognized by RDS and will fail to create the table.
Comment or remove the lines of code that include:

a. ALTER DATABASE [TEST_AWS] SET TRUSTWORTHY OFF

b. ALTER DATABASE [TEST_AWS] SET HONOR_BROKER_PRIORITY

c. ALTER DATABASE [TEST_AWS] SET DB_CHAINING OFF Creating global users

d. FileStream

Execute the Script
Consider adding towards the top of the script DROP DATABASE [TEST_AWS] before the creation of the new database just in case you need to run the script multiple times to find the errors. This will save you from overwriting errors or having a unfinished table in memory.

赞(0）回复(0）举报 12个月前

我来回答

How to migrate the schema of a SQL Server database from on-premise to SQL Server on AWS RDS with non-supported features

1条答案

相关问题

热门标签

最新问答