Post reply

Question on moving content across databases

  • July 15, 2011 at 3:04 pm

    #240939

    Hi All,

    We have a scenario where data from various nodes are consolidated in multiple tables in a 'merge' database and then needs to be moved to a corresponding (mirror) tables in a (different) 'home' database. The move to the home database needs to be very quick.

    We have a mysql database and currently we are doing a table rename to achieve this - so we are doing something like rename table dbmerge.table1 to dbhome.table1 so that the content of the dbmerge.table1 is available in dbhome very quickly (we do not want to do a copy/move of the data).

    For SQL Server we cannot use the sp_rename procedure as it works on a single database - i.e. cannot rename across databases.

    We do not want to go the route of partitioning the table from the home database and switch partition since this will require significant change in our framework to support both MySQL and SQL Server.

    One option is to not to create the 'merge' database as a separate database but as a different schema (owner) say 'merge' in the same 'home' database and then do the table switch using sp_changeobjectowner dbo.table1, merge. But this also requires a fair amount of change in our framework to support both SQL server and MySQL.

    Is there any other option that can be used? The goal is to make the data of specific tables in the merge database be very quickly available in the home database without data copy/move since the data can be potentially large.

    Any pointers will be very helpful.

    Thanks

  • July 16, 2011 at 3:55 pm

    #1353891

    I would recommend you have a look into Synonyms and Views.

    Both can perform cross-database operations and can be dropped and recreated in the context of a transaction so you could swap the definitions out as needed without impacting users or the underlying storage.

    As an aside I agree with your assertion...MySQL databases are more closely related to SQL Server schemas in their logical as well as physical implementations. MySQL does not explicitly support schemas, and in my experience a database in MySQL is nothing more than a schema. This became clear when I started managing MySQL replication.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 2 (of 2 total)

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