?
Solved

How To Copy Specific Data from Main Server to Link Server Daily.

Posted on 2007-08-01
9
Medium Priority
?
194 Views
Last Modified: 2013-11-30
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!!!
0
Comment
Question by:CrazyVBr
  • 5
  • 3
9 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19609109
try push replication from main  sql server
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 19609970
Can you drop all indexes and constraints before importing and recreate them after import?
0
 

Author Comment

by:CrazyVBr
ID: 19616279
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.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 43

Accepted Solution

by:
Eugene Z earned 1500 total points
ID: 19616994
yes, you do not need to push all - just changes, etc

check
Setting Up Transactional Replication with SQL Server 2005
By Baya Pavliashvili.
http://www.awprofessional.com/articles/article.asp?p=599700&seqNum=3&rl=1

--
more

SQL Server Replication
http://msdn2.microsoft.com/en-us/library/ms151198.aspx
--
Transactional Replication Overview

http://msdn2.microsoft.com/en-us/library/ms151176.aspx
0
 

Author Comment

by:CrazyVBr
ID: 19631924
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!
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19632488
it is not mirror:
if you wish to have indexes: you can run pre-created script
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19634379
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
0
 

Author Comment

by:CrazyVBr
ID: 19635579
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!
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19637794
sounds good:
sometimes to make the data export faster you may pump data without indexes and after add them via script on destination.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question