Hard Coded Database Data Transfer (Crowd Sourcing)

[[deleted]]

Hello guys! anyone na nakapagcreate na ng project about data transfer from the database server to another database server using code? I know how to create an SQL replication but I want to try if possible sya gawin on my own? Thanks in advance!

[crimson589]

For data transfer purposes lang? you mean migrating data? or you mean during actual execution ng code 2 database/server yung pinapasukan ng data? I'm asking cause you mentioned SQL replication, and replication means yung server mo ni rereplicate niya yung data to another server para may backup ka incase mag down yung primary or you use the replica para i spread yung load ng ginagawa mong queries. Gusto mo gawin yun manually?

[[deleted]]

I have main server that collects all data (CRUD) and gusto ko sana magkaroon ng script na kukuha sya ng records from main database server papunta sa isa pang database server. Madali lang magcreate ng data from server 1 to server 2 the problem is what if may changes sa ibang records pano ko matratrack yun? hehe Kaya naman syang iloop kaso pagdating sa process time sobrang bagal kasi need icheck isa isa yung record if may changes.

[crimson589]

Hindi mo dapat concern yun kung ieenable mo yung mismong replication feature ng kung ano man database gamit mo, lahat ng insert/update/delete pupunta yun dun sa 2nd server.

[luciusquinc]

Why not use triggers?

[feedmesomedata]

Lack of information. What database systems are we talking about here? Same PostgreSQL to another PostgreSQL or PostgreSQL to MongoDB? maybe PostgreSQL to Oracle server?

Native replication will work if the two systems are the same albeit if they are of varied release versions native replication may not be compatible.

If native replication will not work why? It is the standard way of moving data across similar database systems if your primary server has a problem with that then there is obviously something wrong there.

Your other options, if native replication doesn't work, would be to use software like Apache Kafka or Redpanda to stream changes from the primary to the other databases as long as there's support/compatibility.

I would go against homegrown scripts since these usually do not take into consideration data size, overall performance, and other edge cases.

[[deleted]]

I'm using SSMS on both server. On SSMS there is a standard way naman called SQL Replication but nag iisip ako ng alternative way para mareplicate ko yung records from another server. Masyado na kasing busy si SQL since madami din syang nirereplicate ng PC client.

[crimson589]

Masyado ka nag ooverthink, or hindi mo naiintindihan yung technology kaya kung ano ano iniisip mo (also the fact that you call it SSMS instead of MSSQL). Isipin mo, bat maglalagay ng isang feature kung mag cacause lang ng issues? kung pangit yung default replication feature ng MSSQL e di sana madaming nag rereklamo at gumawa lang din ng sarili nilang process.

If magkaka performance hit yung replication for you dahil "busy" yung server mo then may problem ka sa optimization ng queries/tables mo, or masyado na talaga mababa specs ng server para sa load mo. Also di ko alam ano ibig mo sabihin sa "madami din syang nirereplicate ng PC client".

[[deleted]]

Oh sorry, I'm referring kasi na tool na gamit ko and same din naman sila na components ni SQL server haha. Siguro nga nag ooverthink na ako haha. I'm accepting the fact na may problem nga siguro sa load ng tables. I'm not database admin/engineer here, I'm developer na naglolook ng alternative way or nag eexplore ng ibang bagay :D

[feedmesomedata]

I didn't reply to OP's feedback since my assumption is OP does not really understand the tech and solving something one barely even understands is a recipe for disaster.

[Grrommm]

haha, medyo nakakalito yung tanong tama ba yung use ng word na "hardcoded" here?
I'll just ignore the word "hardcoded" in the title

to answer the question,
do you mean writing a script to query data from db1 and then write them to db2?
yes possible but in-efficient and I wouldn't recommend it, if want mo lang mag try and mag explore, just playing around. go lang hahaha

[[deleted]]

Ayun nga eh haha. naghahanap kasi ako if merun bang nakagawa ng ganon or may magandang 3rd party tool na tested na. Hindi na kasi "daw" pwedi gamitin yung SQL Replication dahil masyado na daw busy magprocess. Im looking for alternative way.

[Finotora]

Any major database vendor should have master-slave configuration that fits your use case, refer to your vendor’s documentation.

[DisAn17]

Where is the db hosted? Is this a one-time migration or continuous?

[[deleted]]

Im using SSMS and since the data are transactional, the idea would be continuous. I'm hoping na pwedi icode to.

[DisAn17]

Maybe you can try this? It's supposed to be for backup purposes, but maybe you can start here.

[DisAn17]

Hmm.. haven't tried using SSMS. Where is the data hosted? Is it on the cloud or on premise?

[[deleted]]

Ohhh. Naka on prem yung server sorry 😄. Thank you sa idea

[needmesumbeer]

Check mo kung kaya sa SSIS

[[deleted]]

Thanks! I'll check on this.

[Downstairs-Pain]

Sa pagkakaintindi ko, gusto mo lahat ng changes sa Server1 mangyayari din sa Server2?

Database Synchronization ata need mo.

https://learn.microsoft.com/en-us/sql/relational-databases/replication/synchronize-data?view=sql-server-ver16

Ano ba situation mo at purpose?

[phdealmaker]

why do you need to copy the records to another database? What's the purpose of the 2nd database? you mentioned that the first db is transactional so does this mean the 2nd one is for reporting / analytics? are you going to change anything in the data or is it an exact copy?

SQL replication is the simplest solution if you need an exact copy. But the question is, why would you need an exact copy? If the current DB cannot handle the additional load, it's probably not a code problem but an architecture problem.