How to transfer data from ONE Sql server DB to another Sql Server DB

kk123
kk123 Member Posts: 13
in NAV Three Tier
Hi All,
We are using two Sql servers in different locations. Is it possible to transfer table data from one Sql server DB to another sql server DB without manual intervention. This sync has to be done on daily basis.

Please share your ideas.

Thanks & Regards,
Kishore

Answers

  • Resolus
    Resolus Member Posts: 40
    You mean you want to keep both databases exactly the same in terms of data?
    I believe MSSQL has a system for Replication already, take a look at the link below to see if thats what you need.

    https://learn.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication
  • kk123
    kk123 Member Posts: 13
    Is it possible to transfer data by using ODBC in Business central?
  • Resolus
    Resolus Member Posts: 40
    Hmmm, in Business Central.. perhaps if you're OnPrem? But then you'd have to program everything yourself. I can imagine it will really affect performance and be much more prone to problems and errors.

    Why do you want to use ODBC?
  • kk123
    kk123 Member Posts: 13
    Hi Resolus,
    Otherthan ODBC can you suggest any third party tools or suggest how to solve this. Thanks

    Regards,
    Kishore
  • Resolus
    Resolus Member Posts: 40
    Well, if you use SQL Server right now, which I assume you do, then you can just use the built-in functionality for SQL Server Replication.

    Unfortunately I can't help you with how that works or has to be set-up.
    Google might be able to help you, or someone else on here.
  • cgimec
    cgimec Member Posts: 3
    You have the following options, chose the one that matches your scenario and performance requirements:
    1. Availability Groups with a R/O replica (if you need to write into the other database this is not an option)
    2. TL Shipping
    3. Replication if you need just certain tables, fields - but if you need to many you want to look at 1 or 2 above
    4. backup and restore (bleah, but it works)
    5. Linked servers with stored procedures (can be done but you need to build a delta between tables and it's a more complex solution which shouldn't be used unless there's a very good reason for that)

    Of course, you can go for ODBC and build something from scratch, but it's going to be much more complex. I wouldn't chose that.

Categories