Solved

SQL Server 2005: Import and Export (Synchronizing database)

Posted on 2011-09-11
11
342 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
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server 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

Myth Busting: MongoDB Scalability (it scales!)

I was talking with one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. He mentioned to me that several customers have been telling him that “MongoDB doesn’t scale!” MongoDB’s scalability was in question?

My response was, “Is that a joke?"

Question has a verified solution.

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

This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

623 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