February 2, 2015 at 9:30 am
I am assisting on a project and we are in need of some SQL advice / direction.
Background:
This is a website designed to gather intelligence information and allow people to search on it. 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.
February 2, 2015 at 12:21 pm
If the agencies are going to be doing regular searches across the entire data set, I would consider putting it all into a single database (it does depend on the size of the data we're talking about, the frequency of these searches, the volumes being searched, that sort of thing). Cross database queries, let alone cross instance or cross server, can be problematic for performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2015 at 12:35 pm
I appreciate the reply.
Unfortunately, I think most of the agencies are going to want to control their own data and have their own server & SQL instance.
That being said, I'm trying to give my guys some info on how to approach this. I have used/installed SQL many times but I don't have the comfort level or expertise to design a wide-scale implementation of it.
The searching is the big issue.
Let's say there will be 3 sites and each with their own instance of SQL. In a very brief outline, what is the best way to approach this web app as to how it will perform a search across all 3?
February 2, 2015 at 12:59 pm
That's tough to say. It really depends on the type of search we're talking about. Is it across a single data set that could be encapsulated in a partitioned view and the filter will always be by partition value first? That's one query, not that hard to set up, and can perform well enough. Or, are we talking, Search All The Things, across all the servers, roughly at will. That's easy enough to set up, linked servers, but is going to be real performance hog, difficult to code against, generally a pain in the bottom. Or, are there sub-sets of shared data that we could set up some ETL processes through SSIS to ensure are available everywhere, or maybe set up a central hub to dump the shared resources to. Or... there are more options after that. You'd really have to get into the details to make really solid suggestions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2015 at 1:20 pm
10-4. I believe each agency will want to be able to search across all data. The data is mostly names, addresses, vehicle info and then associated comments on these things. Since they are sharing intel they want to be able to get at each other's info but still "own" and maintain their own info. I know that a shared server would be best but this is probably not going to be an option in this instance....... thus making this much harder than it otherwise would be.
So, everyone shares the same database schema. They all enter & maintain their own data. Then when they run the web app it in-turn searches across all the databases for a specific name, vehicle, address, etc. and then returns the results and in the results there will also be an identifier as to which agency the hit came back from.
How does one have the app search across all these SQL instances? This is where my knowledge gets foggy.
Is it best to implement some sort of index to search first and then it in-turn figures out which server(s) has the data and then routes to that particular server?
I'm thinking we are going to need to get a SQL expert in the mix locally to assist. The complexity comes in with all these agencies.
February 2, 2015 at 1:38 pm
That is the hard part. Let's say there's a reasonably defined set of possible searches, you could have a view do a UNION ALL query across each of the linked servers. Still... makes me twitchy as a solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2015 at 7:02 pm
Andre,
You could give each agency their own database on one SQL Instance. I believe that your web app could use an Application Role inside each database to sign on. The Application Role assigns permissions to the app and not to individual users. So, when people use the app, then they get whatever permissions are assigned to the Application Role.
If groups of people for each agency then want some sort of direct access to their data in the database, then you can create Active Directory Groups, assign those groups only to the databases that control/access the desired databases. If you keep all the databases on the same SQL instance and server, then you don't have to worry with the performance hit of Linked Servers, which Grant has already pointed out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply