Reporting on two remote location databases

We have couple of SQL databases on different SQL Servers that are at
different remote locations in two different network domains. The
requirement is to generate reports by pulling in information from both
these databases.
What is the best way to go about it?
We have been asked to consider the linked server option, but we are
wondering what kind of impact will it have on performance? Is this a
efficient way to go about it? As far as the report ideally we would
like the reports on live data however if by linking the servers over
the internet causes performance issue we would like to research other
avenues.
Any ideas, suggestions or links in this subject will be greatly
appreciated.
ThanksAs a lot of reports tend to present high-level (aggregated) overviews of
data, linked servers sounds like a definite non-starter.
Are you able to replicate the 'required subset' of tables from one or other
(or both if neither is local) to the site at which the reports will be
generated?
Integration services, and replication are probably your best bets, unless
the reports are extremely simple.
"shub" <shubtech@.gmail.com> wrote in message
news:1182974876.155463.320990@.q75g2000hsh.googlegroups.com...
> We have couple of SQL databases on different SQL Servers that are at
> different remote locations in two different network domains. The
> requirement is to generate reports by pulling in information from both
> these databases.
> What is the best way to go about it?
> We have been asked to consider the linked server option, but we are
> wondering what kind of impact will it have on performance? Is this a
> efficient way to go about it? As far as the report ideally we would
> like the reports on live data however if by linking the servers over
> the internet causes performance issue we would like to research other
> avenues.
> Any ideas, suggestions or links in this subject will be greatly
> appreciated.
> Thanks
>|||On Jun 27, 3:15 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> As a lot of reports tend to present high-level (aggregated) overviews of
> data, linked servers sounds like a definite non-starter.
> Are you able to replicate the 'required subset' of tables from one or othe
r
> (or both if neither is local) to the site at which the reports will be
> generated?
> Integration services, and replication are probably your best bets, unless
> the reports are extremely simple.
> "shub" <shubt...@.gmail.com> wrote in message
> news:1182974876.155463.320990@.q75g2000hsh.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
Thank you very much Will for reponding and you input.
I am assuming the reports are very simple, I mean it will probably
involve few simple joins. What kind of setup will be required to
connect to a SQL Server over the web or do we need a VPN connection to
make it secure and run faster?
Thanks|||1) Check out linked server. I would recommend you encapsulate the remote
reports into sprocs if possible. Also, look to reduce the cross-server
joins and the amount of data returned.
2) Consider some form of encryption (VPN perhaps?) to ensure data security
if this is not an already secure network setup.
TheSQLGuru
President
Indicium Resources, Inc.
"shub" <shubtech@.gmail.com> wrote in message
news:1182974876.155463.320990@.q75g2000hsh.googlegroups.com...
> We have couple of SQL databases on different SQL Servers that are at
> different remote locations in two different network domains. The
> requirement is to generate reports by pulling in information from both
> these databases.
> What is the best way to go about it?
> We have been asked to consider the linked server option, but we are
> wondering what kind of impact will it have on performance? Is this a
> efficient way to go about it? As far as the report ideally we would
> like the reports on live data however if by linking the servers over
> the internet causes performance issue we would like to research other
> avenues.
> Any ideas, suggestions or links in this subject will be greatly
> appreciated.
> Thanks
>