Solved

SQL Server 2005: Import and Export (Synchronizing database)

Posted on 2011-09-11
11
338 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
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…

831 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