Networks

Ask something
Question Locked

Querying and merging disparate databases

0 helpful
7 replies

by mlieberman

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am relatively new to application development (at least in the professional world) and was wondering what the best way to go about querying several different databases and transferring that data to 1 central database.

As a bit of a background I'm working on developing an application that will merge all of the company I work for's network monitoring data in one place that can be accessed easily as opposed to needing to check multiple applications.

My first idea would be to develop a Java server application that has connections to all the databases I need and queries and transforms that data in regular intervals (once a minute, once every 20 seconds, etc.) and then inserts all that new data into the central SQL Server 2005 database. Is there any easier way? Would using a different programming language be easier? Would I be able to do it directly through SQL Server? Would I be able to somehow combine all of this into a JSP/ASP site?

Any suggestions?

7 replies

by tony hopkinson

is knowing that you have so you don't pull it again.

Personally I prefer to push the data up if I get the chance. That's bit more involved coding wise though, but as it's event based at each node it can chop a lot of traffic and empty requests out.

Depends on the databases involved and if you gave a full copy of SQL2005, but SSIS could do this for you, that's the new version of DTS.
In theory you could replicate as well from sql to sql.

There's no right answer to theis question picking tools you are comfortable with will save as much effort as picking the 'best' one.

Start small, is my best advice, and then if it turns out you idea sucks you haven't wasted a lot of resource on it. Some sort of dash board of the nodes on the system, whether they look to be alive or not, will be a valuable tool.

by mlieberman

Yeah we have a bunch of full licenses for SQL Server 2005, and I thought it had something like SSIS but wasn't sure what it was called or how it worked. Also the tools for SSIS weren't installed on this workstation. Thanks. I was getting ready to start querying everything from scratch in Java and now that I'm reading up on SSIS, this seems like it does everything I want it to much simpler than coding it by hand.

by tony hopkinson

Have a look at the impact of actually using the collected data. Locks and such. Be careful when / if you start building MIS type functions in there Aggrates and such.

Oh and make sure your transaction scopes and such are right, you don't want partial of broken data in the mix.

Remember the tortoise won the race.

by jgarcia102066

Without knowing the full extent of your data sources I offer this possibility.

Since you are using SQL Server 2005 you may be able to create linked servers to your disparate data sources. We have created linked servers to other SQL servers, Oracle Servers, Active Directory and other LDAP sources. If you can create the linked servers, you have a world of options to work with.

You can create a view that "unions" the information.

You can write stored procedures that query the disparate database separately and combine the information in an application.

You can create and schedule "Jobs" that periodically updates a single table with data from each of the separate data sources.

and so forth and so forth...

Each approach will have resource and data integrity risk implications which you will need to assess in order to architect the best solution for your company.

by mlieberman

I've been looking into this solution as well. This is my first time doing something this complex so I'm definitely examining the situation with a finetoothed comb.

by mlieberman

I don't think it will be a major issue. The data is very dynamic but isn't updated often. For the most part it should always list all the same IPs that each server has and only be updated if something goes down and its given a new IP. I'll have to look into it if it turns out we need to implement this system for some of our larger clients.

This discussion is closed to new replies.