I need to migrate .SQB files to Snowflake.
I have a data relay where MSSQL Server database files are saved in .SQB format (Redgate) and available via sSTP with full backups every week and hourly backups in between.
Our data warehouse is Snowflake and the rest of our data from other sources. I'm looking for the simplest, most cost effective solution to get my data to Snowflake.
My current ETL process is as follows.
- AWS EC2 instance (Windows) that downloads the files, applies Redgate's SQL Backup Converter (https://documentation.red-gate.com/sbu7/tools-and-utilities/sql-backup-file-converter) to convert the files to .BAK. This tool requires a license
- Restore MS SQL database on the same AWS EC2 Instance
- Migrate MS SQL database to Snowflake via Fivetran
Is there a simpler / better solution? I'd love to eliminate the need for the intermediate EC2 if possible.
The .SQB files come from an external vendor and there is no way to have them change the file format or delivery method.
CodePudding user response：
This isn't a full solution to your problem, but it might help to know that you're okay to use the SQL Backup file converter wherever you need to, free of any licensing restrictions. This is true for all of SQL Backup's desktop and command-line tools. Licensing only gets involved when dealing with the Server Components, but once a .SQB file has been created you're free to use SQBConverter.exe to convert it to a .BAK file wherever you need to.
My advice would be to either install SQL Backup on whichever machine you want to use the tooling on, or just copy all the files from an existing installation. Both should work fine, so pick whichever is easiest for you.
(FYI: I'm a current Redgate software engineer and I used to work on SQL Backup until fairly recently.)
CodePudding user response：
You can Step 1: Export Data from SQL Server Using SQL Server Management Studio. Step 2: Upload the CSV File to an Amazon S3 Bucket. Step 3: Upload Data to Snowflake From S3 using COPY INTO command.
You can use your own AWS S3 bucket for this and then create a External Stage pointing to the S3 bucket or You can upload the files into internal Snowflake Stage.
Copy Into from External Stage - https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#loading-files-from-a-named-external-stage
Copy Into from an Internal Stage - https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#loading-files-from-an-internal-stage
Creating External Stage- https://docs.snowflake.com/en/sql-reference/sql/create-stage.html