January 29, 2012 at 6:31 pm
I need to move all tables in one database in one server to another server on a daily basis using ssis package what is the best way to do it ??
I am planning of getting the list of all tables and use for each loop container and loop it till it gets completed but i have around 1000 tables and moving forward the volume of data might get increased ..can you please suggest if that's the best way to do it...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
January 29, 2012 at 11:33 pm
Gosh... I wouldn't use SSMS for such a thing. If everything needs to be copied, just do a restore. If you want to avoid lengthy downtime, setup transactional replication or, if you have it, use SAN replication.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 30, 2012 at 5:36 am
I am using ssis not ssms ....and they want to do it thru ssis ..
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
January 30, 2012 at 6:15 am
I am using ssis not ssms ....and they want to do it thru ssis ..
so you mean everyday table will be dropped and re-created?
If yes, then
In your SSIS, select "Transfer SQL Server Object Task" and click edit to update the properties. You will see various option like:-
1) Drop Object First -- true/false
2) Copy Data -- True/False
3) Copy schema -- true/false
4) Include dependent objects -- true/false
5) copy all tables -- true/false
.
.
.
.
.
.
and so on. Select as per your preference and your package is ready to go as per your defined schedule.
----------
Ashish
January 30, 2012 at 6:19 am
Sri8143 (1/30/2012)
I am using ssis not ssms ....and they want to do it thru ssis ..
Use an Execute SQL Task to create a backup of the database.
Use another Execute SQL Task to restore the backup on the other server.
Voila, all done with SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 30, 2012 at 6:47 am
Koen Verbeeck (1/30/2012)
Sri8143 (1/30/2012)
I am using ssis not ssms ....and they want to do it thru ssis ..Use an Execute SQL Task to create a backup of the database.
Use another Execute SQL Task to restore the backup on the other server.
Voila, all done with SSIS.
First point here is that 'SSMS' in Jeff's response was a typo (correct me if I am wrong Jeff) - he meant SSIS.
Secondly, Koen's response is the way I would do it too - nice and simple.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 30, 2012 at 7:15 am
Which one would be better when compared to performance ....
Snapshot replication or ssis trsfr object task....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
January 30, 2012 at 10:04 am
Sri8143 (1/30/2012)
I am using ssis not ssms ....and they want to do it thru ssis ..
Apollogies... I meant SSIS. Koen's method will work just fine through SSIS. In fact, if you want to go that route, you could easily turn it into a scheduled maintenance plan. Might even be able to pull it off as an "online" restore but haven't actually tried that.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2012 at 6:49 pm
Thanks Guys transfer sql server seems working for me...
What can i do to tune the package as i have more than 1000 tables and huge data want to tune it ..
I am thinking whether dropping the indexes prior to loading and then re-creating after loading data do you think that would be a better idea when having huge volume of data??? Please suggest on it also please let me know if i need to check any other options for tuning the package ....as this would be a daily schedule job ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 1, 2012 at 11:28 pm
Sri8143 (2/1/2012)
Thanks Guys transfer sql server seems working for me...What can i do to tune the package as i have more than 1000 tables and huge data want to tune it ..
I am thinking whether dropping the indexes prior to loading and then re-creating after loading data do you think that would be a better idea when having huge volume of data??? Please suggest on it also please let me know if i need to check any other options for tuning the package ....as this would be a daily schedule job ...
First of all, what does your package look like? What does it do exactly and which steps uses it to get there?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2012 at 4:33 am
It has just transfer sql server objects whoch copies all tables data from one server to another .....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 2, 2012 at 4:37 am
So you are just ignoring almost all of the advice everyone has given you and still asking for help?
If you are not prepared to listen, or at least to justify why you have not done what has been suggested, why are you asking?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 2, 2012 at 5:41 am
Phil,
I am doing it thru transfer sql server objects tasks which was recomended initially in this conversation so going with it as i am unable to do backup and restore as i dont know how to do that ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 2, 2012 at 5:50 am
Sri8143 (2/2/2012)
Phil,I am doing it thru transfer sql server objects tasks which was recomended initially in this conversation so going with it as i am unable to do backup and restore as i dont know how to do that ...
Not everything that is suggested at a public forum is always the most optimal solution.
I'm pretty sure it will take you more time to develop and maintain an SSIS package with a 1000 objects than to read the MSDN pages about backup & restore.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2012 at 11:23 am
Koen Verbeeck (2/2/2012)
Sri8143 (2/2/2012)
Phil,I am doing it thru transfer sql server objects tasks which was recomended initially in this conversation so going with it as i am unable to do backup and restore as i dont know how to do that ...
Not everything that is suggested at a public forum is always the most optimal solution.
I'm pretty sure it will take you more time to develop and maintain an SSIS package with a 1000 objects than to read the MSDN pages about backup & restore.
+1. I'm relatively sure the Object Transfer component was basically built to facilitate the wizard. I've had nothing but headaches from it and it generally is a PITA for maintenance.
Transfers of whole databases like the one you're discussing are rarely done via SSIS, except when they're SMALL, because then the best practice and maintenance isn't as big a deal. Yours isn't small.
For real time upkeep (which you don't care about if your SSIS dependent) you'd use Transactional Replication, as already recommended. For static point upkeep of direct object transfers, you use backup/restore, mirror/snapshots (best bet), or snapshot replication.
SSIS for this task is using a wrench as a hammer. It can be done, it's just not the best tool for the job.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply