Let me start by describing the situation.
We have 2 companies within the same organisation that need to share the schema and data for a small set of a DB.
We have been trying to come up with a way of shaing this data using XML as the 2 SQL Servers cannot see each other and never will.
We have generated the Schema XML and Data XML but when we use the XMLBulkLoad in .Net the tables are not created and therefore no data inserted but it throws no errors!!
Here is the Schema XML
And here is the data XML
Any help would be greatly appreciated as it is driving us nuts now, or is there an easier/better way to do this, remember that the 2 SQL Servers cannot see each other and never will.
asked Feb 26, 2010 at 09:07 AM in Default
If a full backup/restore solution isnt applicable - you only want selected tables shipped to the other site - then I would recommend you review something like SQL Data Compare from Red Gate (14 day free trial here). It lets you script the database schema (or parts thereof) and the data in the objects selected into a folder on your system (network share/local HDD etc) and you could then zip + encrypt, email and then run SQL Data Compare at the other end to get the other location up to date.
Its not an ideal (read 'recommended') solution, transporting data by email has obvious risks and something managed by the SQL engine would be preferred - FTP replication etc but if they are not connected then it could be a struggle and bring its own risks. Check out http://www.replicationanswers.com/default.asp to see if you can get something sorted.
I would say bcp to import/export the data but you need to cope with schema changes too. Are they so frequent that the schema needs updating EVERY time?
answered Mar 01, 2010 at 01:25 PM
When you say they cannot see each other - how will the XML get from one to the other? Are you using a common network location to host the XML output? Are they on the same network/domain/subnet??
You could bcp, or use SSIS maybe?
answered Feb 26, 2010 at 10:49 AM
Another way to go about this is to use the command line versions of xSQL Schema Compare and xSQL Data Compare. If you download the desktop versions of both of these tools, you also get the command line versions. You can generate XML files which contain the configuration for the synchronization process and are used as arguments by the command line tools (to see how to do that check out this post). After this, you can create a batch file and schedule it to be executed periodically to keep the two databases synchronized.
DISCLAIMER: I'm affiliated to xSQL.