CrazyVBr
asked on
How To Copy Specific Data from Main Server to Link Server Daily.
We have a "main" SQL Server 2005 with about 200 tables. We need to copy the data daily from 10 of these tables to a "2nd" 2005 SQL server outside our control. We've setup the (2nd) server as a linked server to the "main" server. For security reasons we can give the "main" server access to the "2nd" server but not the other way around.
The table structure on the "2nd" server is exact. Including PK, FK, Relations, Indexes; and must remain this way for reporting purposes.
I'm having problems with each of the methods I've tried so far:
SSIS: set to Export the data and delete existing files from "2nd" Server. I get an error that SSIS can't truncate the tables due to constraints on the tables.
Stored Procedure: I can't set identity_insert on the tables on a linked server.
Combo: I delete all records from 2nd server via stored procedure, then tried to setup an SSIS to export tables with append method to linked server. Problem is that SSIS is trying to export both orders and customer tables at the same time and throwing constraint errors. I can't find a way in the SSIS package to tell it to export all customers first then the orders.
Thanks for your assistance!!!
The table structure on the "2nd" server is exact. Including PK, FK, Relations, Indexes; and must remain this way for reporting purposes.
I'm having problems with each of the methods I've tried so far:
SSIS: set to Export the data and delete existing files from "2nd" Server. I get an error that SSIS can't truncate the tables due to constraints on the tables.
Stored Procedure: I can't set identity_insert on the tables on a linked server.
Combo: I delete all records from 2nd server via stored procedure, then tried to setup an SSIS to export tables with append method to linked server. Problem is that SSIS is trying to export both orders and customer tables at the same time and throwing constraint errors. I can't find a way in the SSIS package to tell it to export all customers first then the orders.
Thanks for your assistance!!!
EugeneZ
try push replication from main sql server
Can you drop all indexes and constraints before importing and recreate them after import?
ASKER
My exported tables are 5 gigs in size, I didn't realize that until after I seperated them out. It took 3 hours for the export package to run. Way too long in my book. I know very little about push replication. Will push replication allow me to just update the changes to the database nightly instead of recreating the whole database? What overhead does populating the replication snapshot from the main server cause? Thanks again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I just noticed that all my constraints and all but FK/PK indexes are missing. I thought these were pushed as part of the replication. How do I add them so anytime the snapshot is redone that the indexes are all rebuilt? Thank you!
it is not mirror:
if you wish to have indexes: you can run pre-created script
if you wish to have indexes: you can run pre-created script
check the solution:
Database Mirroring and Database Snapshots
/SQL Server 2005 Enterprise Edition and Developer Edition, you can create database snapshots to read the mirror database at a point in time/
http://msdn2.microsoft.com/en-us/library/ms175511.aspx
Database Mirroring and Database Snapshots
/SQL Server 2005 Enterprise Edition and Developer Edition, you can create database snapshots to read the mirror database at a point in time/
http://msdn2.microsoft.com/en-us/library/ms175511.aspx
ASKER
I found the properties of the table several options to turn off and on. By default, non-clustered indexes were not included in the snapshot. I've made the change and ran my snapshot again. Now all my indexes are there. My snapshot takes 9 minutes to complete and my initial subscription of my snapshot to my other server took 7 minutes. I'm very impressed with the speed of transfering 5 gigs of data, this seems extremly fast. Thank you for all your help!
sounds good:
sometimes to make the data export faster you may pump data without indexes and after add them via script on destination.
sometimes to make the data export faster you may pump data without indexes and after add them via script on destination.