0

We have a requirement where we will have to move data between different database instance on regular basis. (For e.g. some customers willing to pay more for the better performance). So this is not going to be one off.

The database tables has referential integrity. Is there a way in which this can be done without rewriting sql script (or some other method) every time we migrate customers data?

I came across this How to move data between multiple database's table while maintaining foreign-key relationships/referential integrity?. However it appears that we have write script every time we migrate data (please correct me if I misunderstood the answer on this thread).

Thanks

Edit:

  • Both servers are using SQL Server 2012 (same version). Its an Azure SQL Server database.
  • They are not necessarily linked (no firewall between them)
  • We are only transferring some data, not the whole database. This is only for certain customers who opted pay more.
  • The schema are exactly same in both databases.
Community
  • 1
  • 1
activebiz
  • 6,000
  • 9
  • 41
  • 64
  • 2
    Could you please put some comments why it was voted down? So that I can correct it. Thanks – activebiz Jun 01 '15 at 09:00
  • There are several ways this can be solved, starting with SSIS and ending with backing up the database on one server and restore on the other. We need more details in order to help you. Are both servers using the same version (and what version it is)? Can the servers be linked (meaning there is no firewall between them)? Are we talking about transferring entire databases or just some data? if it's just data, does the table schema in both databases match? – Zohar Peled Jun 01 '15 at 09:14
  • Question is edited. with more details thanks for the input. – activebiz Jun 01 '15 at 09:47
  • Does "on regular basis" mean that will only happen in certain intervals or should e.g. the customer trigger the move and take effect immediately. It would be interesting to hear if you have already found a solution. – Marcel Jun 10 '15 at 08:24

2 Answers2

4

Preyash - please see the documentation on the Split-Merge tool. The Split-Merge tool enables you do move data between databases, as you have described, based on a sharding key (e.g., customer ID). One modification that you will need for your application is to add a shard map (i.e., a database that understand the global state of which customers resides in which databases).

Joseph Idziorek
  • 4,853
  • 6
  • 23
  • 37
  • Although this is a cleverer solution, it requires much more changes to our existing application then we would have liked. What I was hoping for is a simpler solution, as Zohar Peled said poss. a SSIS package? or a script which can be parameterised perhaps. – activebiz Jun 02 '15 at 07:25
1

Have a look into Azure Data Sync. It is much more aligned with your requirements. But you may end up in having another SQL Azure DB to maintain a Hub. Azure data Sync follows hub-spoke pattern and will let you do all flexible directional syncs with a few minutes of syncing gap. It is more simple and can set it up very fast without any scripts and all as you wanted.

Mahesh Jasti
  • 572
  • 3
  • 18
  • It takes care of referential integrity and other dependency issues. – Mahesh Jasti Jun 02 '15 at 09:39
  • Thanks, this is perfectly valid reply however this is another feature of Azure Database and it means changing existing infrastructure and throwing money at the problem. – activebiz Jun 02 '15 at 13:28