Post reply

Need help in refreshing some tables in a database from one server to another server

  • January 5, 2015 at 12:21 pm

    #313476

    Hi, I need some help in refreshing process some of the tables in a database from a sql server (running 2008) to another database in a destination sql server (running 2012) ...thing is this will be a recurring process ..so wanted to know if there's any easy way....can't use SSIS here as sql versions differ...any help is appreciated..Thanks!

  • January 5, 2015 at 12:47 pm

    #1767857

    How often does the refresh need to run?

    How big are the tables?

    What are the availability requirements during the "refresh"?

    What are the different versions of the two instances?

  • January 5, 2015 at 12:52 pm

    #1767858

    Hi, Refresh needs to happen everyday..all tables are about 10GB....source instance is sql 2008 R2 and Destination sql instance is SQL 2012

  • January 5, 2015 at 2:37 pm

    #1767884

    You could just use snapshot replication, scheduled once a day.

    Tables on target instance would not be usable during load.

    Another option would be change data capture and load modified data once a day.

    You could implement that in such a way as to have the tables on target instance be usable during load.

    Also you could just use good old BCP. Create new tables with a different name. BCP in the data and when done rename each pair (new and old) of tables and then drop the old tables.

    In each of these you need to look out for issues related to foreign key constraints.

  • January 5, 2015 at 2:38 pm

    #1767885

    Also there are many table diff tools out there. I know Redgate has one.

  • January 5, 2015 at 2:47 pm

    #1767887

    v4vaas 19815 (1/5/2015)

    Hi, I need some help in refreshing process some of the tables in a database from a sql server (running 2008) to another database in a destination sql server (running 2012) ...thing is this will be a recurring process ..so wanted to know if there's any easy way....can't use SSIS here as sql versions differ...any help is appreciated..Thanks!

    I just wanted to note that SSIS doesn't care about the version of SQL. Source and destination can be completely different and that's part of the idea of having an ETL tool. Arnipetursson already gave some ideas that I won't repeat but I support.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 5, 2015 at 2:54 pm

    #1767889

    Thanks for suggestions arnipetursson..Appreciate it.

    Luis, I already tried creating a SSIS package (back & forth) but it threw an error saying versions differ...you can test it if u want.

  • January 5, 2015 at 6:06 pm

    #1767935

    If it's only a few tables, you could try just using a MERGE query to make it so that the data is moved across based on what's new, deleted or modified.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 8 posts - 1 through 7 (of 7 total)

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