SQL Server 2005: Import and Export (Synchronizing database)

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
LVL 5
MohitPanditAsked:
Who is Participating?
 
jogosConnect With a Mentor Commented:
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
 
DavidMorrisonCommented:
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
 
MohitPanditAuthor Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
DavidMorrisonCommented:
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
 
Alpesh PatelAssistant ConsultantCommented:
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
 
MohitPanditAuthor Commented:
But we can do the same thing using log shipping and database replication. correct?
0
 
MohitPanditAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.