AWS : SqlServer to Postgree continous replication

Question

AWS : SqlServer to Postgree continous replication

thbaig

Hall of Fame

Points: 3006
More actions
January 18, 2023 at 11:44 am

#4138984

Hi,
I have RDS mssql serer database on aws. for few tables I want to replicate them continuously (once a day) on postgres. I can write tsql code with linked server to populate data on postgres, but I read on AWS articles that it only allows sqlserver to sqlserver (min standard edition) linked server connections.
aws replication is expensive, so what are methods I can try to replicate my sql data to postgres ?

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

I think you're going to have to build a service on your own. I'm not aware of a cross-database service like this, let alone on AWS.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

thbaig Hall of Fame Points: 3006 More actions · Answer 2

thbaig

Hall of Fame

Points: 3006

January 30, 2023 at 4:26 pm

#4143368

what could be good and economical option on AWS ?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

thbaig wrote:

I want to replicate them continuously (once a day) on postgres.

Wholesale table change or only things that have changed?

Either way, I'd explore the possibility of a BCP OUT to a true CSV (hopefully, you're using SQL Server 2017 or above) and then have PostgresSQL do a CSV import to a working table.

If it's a "wholesale" change where all the data in a table is replaced, then just do a "Swap'n'Drop" once the working table has been successfully imported.

If it's an "changes only" type of "Upsert", then do that from the working table that you brought in with the understanding that you WILL need to do the occasional true-up of a full table replacement using the "wholesale" change method above.

S3 drive would be the common place for files between the two servers. I've seen where people actually can put a Drive Letter and volume name on it so it works like a typical local drive. I don't personally know how to set something like that up... I've only been the benefactor of such a thing and it works well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

thbaig wrote:

what could be good and economical option on AWS ?

If you're going to run on AWS, I'd start by experimenting with AWS RDS. See how that works for you. If it doesn't work well, you can look to AWS Aurora. If that's not working either, then you can look to getting a VM out on AWS and going more traditional. However, I'd start with AWS RDS.

However, you're still going to be building out everything on your own as Jeff has outlined.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning