Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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
0
MohitPandit
Asked:
MohitPandit
1 Solution
 
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
 
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
Industry Leaders: 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!

 
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
 
jogosCommented:
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
 
MohitPanditAuthor Commented:
thanks
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now