Solved

SQL Server 2005: Import and Export (Synchronizing database)

Posted on 2011-09-11
11
341 Views
Last Modified: 2013-11-19
Hello,

I have a database which fed to web application, batches and reports too.
Due to huge processing in web application, batches; I would like to separate reports database in a separate server and web application and batches will be having one year data on that server.

So, below is my thinking

--> I will move all data on reports database and current database will be having one year data.
--> I need to transfer daily data on reports database i.e. from current database to reports database.

Now, the question is that, I can easily this task using SSIS (.dtsx) package but I need to dig all bases like I can achieve this by log shipping, replication, mirroring and failover clustering.
Can you please put your stamp for other alternatives regardless SSIS (.dtsx)?

Important Note: the reports database can be on separate network then through .dtsx; I can export data into flat files and place on FTP from source and on destination, I can schedule another .dtsx file which import data from flat files after get files from FTP location.
But, can I achieve log shipping, replication etc. on separate network?

Best Regards,
MohitPandit
0
Comment
Question by:MohitPandit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36522023
Hi Mohit,


Before you go down the path of having different DB's on different servers have you considered partitioning? It sounds like from your description that you could potentially resolve your issue using partitioned tables (on record date or similar)

What do you think?

http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx

Thanks

Dave
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 36522147
Hi David,

But we need to change in current SQL scripting for mention the partition name in DML queries.

what is your suggestion for other ways?

Best Regards,
MohitPandit
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36522170
Hi Mohit,

That's the beauty of partitioning, you don't need to change of your scripts, they all just continue to work and the engine takes care of which partitions to use


Thanks
Dave
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36522439
You can pass data to destination server which are new or updated using SSIS packages.

For that, you need to use lookup transformation, DAta flow task etc. It's best way to do and you can schedule it.
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 36527378
But we can do the same thing using log shipping and database replication. correct?
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 36558209
If there is a location accesseble for both db-servers to exchange the log files log shipping won't be a problem if it's on a separate network.
Disadvantage off that separate network is that your secondary db-server can't be used as a hot standby in case of problems on your primary server.
0
 
LVL 5

Author Closing Comment

by:MohitPandit
ID: 36581911
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

734 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