asked
SQL Advice
I am assisting on a law enforcement project but need some advice for our group on SQL. Background: This is a website designed to gather intelligence information. It will initially reside on one Windows server with an installation of SQL on the server. Eventually this system may need to be expanded to other agencies in the area and this may happen in one of two ways. #1, each new agency coming on board will have their own server & SQL install or #2, an agency may want to come on board and put their data on this original server. Sencario #1 is more likely to happen. That being said, we are looking for info on the following: 1) Once there are multiple instances of SQL involved, what is the best approach to do searches across the multiple SQL databases? The goal is to have the single interface and when you search it will bring back results from all the servers. Does this require some sort of index? 2) What generally needs to happen (or be considered) on the initial install to prepare for expanding to multiple SQL databases that may come online later? What needs to happen on the SQL side and on the website applicaiton side? These are the general questions. It is all about how to manage the SQL databases and how the searching will work. Thank you for your time. Prefer response to
iacis-listserv@live.com Regards,
Having data in different databases on different instances will make fast, efficient queries very difficult. There is no concept of an 'index' as you mention that spans databases even on the same instance. If your data is split between databases based on certain logic (ie geographical location , etc) then your application layer could direct the query to the correct server but if you want to search them all then it will become for complex. It is possible to create Linked Servers on each SQL Server that will allow cross-instance queries but they are prone to slow performance and other issues. I think I would certainly look to quantify how many queries of this broader scope are likely to take place every hour (or minute, or day) to see if a different method would be more appropriate. Scenario 2 will not have these complications but you will see greater stress on your hardware as the number of agencies increases and the load on your database becomes more intense. The option here would be to then increase your hardware with possibly a cluster etc.
18 People are following this question.
