Operating across multi SQL Server databases

[Br@dley]

We have a product that is being used by a client to outsource salary
packaging. They therefore run several instances of our database, one for
each of their clients.

There is now a requirement to run operations globally across all the
databases including a central database holding some global information. What
method(s) would people recommend that would work best?

(Access2000 ADP, SQL Server 2000)

--
regards,

Br@dley

[Larry Linson]

Re: Operating across multi SQL Server databases

I am not aware of the details of how to accomplish it, but you can link
tables in different databases in SQL Server, just as you can in Jet. If you
are going to do something "global" with disparate databases, that would seem
to be the first approach to consider.

I might suggest that you consider redesigning the database so that only one
instance would have to be run, but the tables have "user" identification for
the data. However, as that may not be a common requirement (e.g, just this
one "outsourcer " or a few), that might just be unused, extra data, for most
of your clients.

Other than this, I am not sure what recommendation you are seeking.

Larry Linson
Microsoft Access MVP


"Br@dley" <dontlookforme@ google.com> wrote in message
news:dvdc7n$k2j $1@news-02.connect.com. au...[color=blue]
> We have a product that is being used by a client to outsource salary
> packaging. They therefore run several instances of our database, one for
> each of their clients.
>
> There is now a requirement to run operations globally across all the
> databases including a central database holding some global information.
> What method(s) would people recommend that would work best?
>
> (Access2000 ADP, SQL Server 2000)
>
> --
> regards,
>
> Br@dley
>[/color]

[Graham Charles]

Re: Operating across multi SQL Server databases

I've done something similar in Access; I coded a routine to dyamically
link the tables I needed and pull in their data, using a local table to
keep a list of the database locations. I don't think an ADP would be a
good choice, since you can only connect to one database at a time.

If you're comfortable with SQL data warehousing, that's probably the
best bet, though... that's what it sounds like you're doing.

[Jerry Boone]

Re: Operating across multi SQL Server databases

What about finding a SQL Server hosting company?

If the SQL Server happened to be hosted in one place and the connection
strings all pointed there (IP over the web - or - VPN and connect) then your
in business. Multiple users anywhere could connect and operate from a
single datasource. Of course depending on the efficiency of the client
application and how much it utilizes the server itself for processing
(particularly on report generation), performance could be an issue. However
I would think it is certainly worth a shot before recoding or messing with
replication. Could be another profit center for you as well whether you
host the db yourself or outsource that. Especially if the client sites have
a good dedicated internet connection.

--
Jerry Boone


"Br@dley" <dontlookforme@ google.com> wrote in message
news:dvdc7n$k2j $1@news-02.connect.com. au...[color=blue]
> We have a product that is being used by a client to outsource salary
> packaging. They therefore run several instances of our database, one for
> each of their clients.
>
> There is now a requirement to run operations globally across all the
> databases including a central database holding some global information.[/color]
What[color=blue]
> method(s) would people recommend that would work best?
>
> (Access2000 ADP, SQL Server 2000)
>
> --
> regards,
>
> Br@dley
>
>[/color]

[Br@dley]

Re: Operating across multi SQL Server databases

Larry Linson wrote:[color=blue]
> I am not aware of the details of how to accomplish it, but you can
> link tables in different databases in SQL Server, just as you can in
> Jet. If you are going to do something "global" with disparate
> databases, that would seem to be the first approach to consider.[/color]

Thought of that but we're using an ADP...
[color=blue]
> I might suggest that you consider redesigning the database so that
> only one instance would have to be run, but the tables have "user"
> identification for the data.[/color]

Yes, this is the way I'd like to have done it but I didn't design it and
there is not the time/resources to redesign it at the moment. There is also
some benefit to having the data seperate.
[color=blue]
> However, as that may not be a common
> requirement (e.g, just this one "outsourcer " or a few), that might
> just be unused, extra data, for most of your clients.
>
> Other than this, I am not sure what recommendation you are seeking.
>
> Larry Linson
> Microsoft Access MVP
>
>
> "Br@dley" <dontlookforme@ google.com> wrote in message
> news:dvdc7n$k2j $1@news-02.connect.com. au...[color=green]
>> We have a product that is being used by a client to outsource salary
>> packaging. They therefore run several instances of our database, one
>> for each of their clients.
>>
>> There is now a requirement to run operations globally across all the
>> databases including a central database holding some global
>> information. What method(s) would people recommend that would work
>> best? (Access2000 ADP, SQL Server 2000)
>>
>> --
>> regards,
>>
>> Br@dley[/color][/color]

--
regards,

Br@dley

[Br@dley]

Re: Operating across multi SQL Server databases

Graham Charles wrote:[color=blue]
> I've done something similar in Access; I coded a routine to dyamically
> link the tables I needed and pull in their data, using a local table
> to keep a list of the database locations. I don't think an ADP would
> be a good choice, since you can only connect to one database at a
> time.[/color]

Unfortinately it's not a choice... it's what the system has been written in.

I could maybe write an external interface MDB to do the processing...
[color=blue]
> If you're comfortable with SQL data warehousing, that's probably the
> best bet, though... that's what it sounds like you're doing.[/color]

--
regards,

Br@dley

[Br@dley]

Re: Operating across multi SQL Server databases

Jerry Boone wrote:[color=blue]
> What about finding a SQL Server hosting company?
>
> If the SQL Server happened to be hosted in one place and the
> connection strings all pointed there (IP over the web - or - VPN and
> connect) then your in business. Multiple users anywhere could
> connect and operate from a single datasource. Of course depending on
> the efficiency of the client application and how much it utilizes the
> server itself for processing (particularly on report generation),
> performance could be an issue. However I would think it is certainly
> worth a shot before recoding or messing with replication. Could be
> another profit center for you as well whether you host the db
> yourself or outsource that. Especially if the client sites have a
> good dedicated internet connection.[/color]

All the databases are on the same local server so none of this is required:)
[color=blue]
> "Br@dley" <dontlookforme@ google.com> wrote in message
> news:dvdc7n$k2j $1@news-02.connect.com. au...[color=green]
>> We have a product that is being used by a client to outsource salary
>> packaging. They therefore run several instances of our database, one
>> for each of their clients.
>>
>> There is now a requirement to run operations globally across all the
>> databases including a central database holding some global
>> information. What method(s) would people recommend that would work
>> best?
>>
>> (Access2000 ADP, SQL Server 2000)
>>
>> --
>> regards,
>>
>> Br@dley[/color][/color]

--
regards,

Br@dley

[Lyle Fairfield]

Re: Operating across multi SQL Server databases

"Br@dley" <dontlookforme@ google.com> wrote in
news:dvdc7n$k2j $1@news-02.connect.com. au:
[color=blue]
> We have a product that is being used by a client to outsource salary
> packaging. They therefore run several instances of our database, one
> for each of their clients.
>
> There is now a requirement to run operations globally across all the
> databases including a central database holding some global
> information. What method(s) would people recommend that would work
> best?
>
> (Access2000 ADP, SQL Server 2000)[/color]

Are the dbs on different servers? Do they have different names?

If they're not on different servers on can just access them using their
names? eg: SELECT * FROM FFDBA_ESO_LOCAL .dbo.tblSchools runs fine when my
ADP is linked to a db named Temp but which is on the same server as the db
FFDBA_ESO_LOCAL (assuming I have permissions of course).

And if they are on different servers the servers can be linked (BOL has
mucho info about linking servers; we can even link to an mdb if it resides
on the same machine as the SQL server), and the dbs can be accessed by
servername.dbna me.owner.object ?
eg SELECT * FROM Server2.FFDBA_E SO_Remote.dbo.t blSchools

But perhaps I am not understanding. Perhaps you need to access tblSchools
on several different dbs on several different servers. I don't know if a
Union will work in those circumstances or not, but I'd give it a shot.

--
Lyle Fairfield

[Br@dley]

Re: Operating across multi SQL Server databases

Lyle Fairfield wrote:[color=blue]
> "Br@dley" <dontlookforme@ google.com> wrote in
> news:dvdc7n$k2j $1@news-02.connect.com. au:
>[color=green]
>> We have a product that is being used by a client to outsource salary
>> packaging. They therefore run several instances of our database, one
>> for each of their clients.
>>
>> There is now a requirement to run operations globally across all the
>> databases including a central database holding some global
>> information. What method(s) would people recommend that would work
>> best?
>>
>> (Access2000 ADP, SQL Server 2000)[/color]
>
> Are the dbs on different servers? Do they have different names?[/color]

Same server.
[color=blue]
> If they're not on different servers on can just access them using
> their names? eg: SELECT * FROM FFDBA_ESO_LOCAL .dbo.tblSchools runs
> fine when my ADP is linked to a db named Temp but which is on the
> same server as the db FFDBA_ESO_LOCAL (assuming I have permissions of
> course).[/color]

Perhaps. Ideally I'd need to make it as dynamic as possible (ie. 'register'
the database into a table and insert the table names into the SQL
statement).

<>
--
regards,

Br@dley

[Jerry Boone]

Re: Operating across multi SQL Server databases

Ahh...

Interesting, so you need to combine all the databases together for a single
source solution.

You could make a database called "globaldb" or something and make views
named for each table object you have in the other databases...

use globaldb
create view 'table1'
as
select * from db1.dbo.table1
union
select * from db2.dbo.table1
union
select * from db3.dbo.table1

create view 'table2'
as
select * from db1.dbo.table2
union
select * from db2.dbo.table2
union
select * from db3.dbo.table2

and so on...

This gives you a single db to connect to that combines data from the other
db's. In just about any sql server scenario view objects are completely
interchangeable with table objects. I always use views to "pull" in data
from other databases. Then if the source database changes you have a "shim"
layer where to can adjust such changes to keep from having to re-code your
application.

--
Jerry Boone


"Br@dley" <dontlookforme@ google.com> wrote in message
news:dvi7s8$nna $1@news-02.connect.com. au...[color=blue]
> Jerry Boone wrote:[color=green]
> > What about finding a SQL Server hosting company?
> >
> > If the SQL Server happened to be hosted in one place and the
> > connection strings all pointed there (IP over the web - or - VPN and
> > connect) then your in business. Multiple users anywhere could
> > connect and operate from a single datasource. Of course depending on
> > the efficiency of the client application and how much it utilizes the
> > server itself for processing (particularly on report generation),
> > performance could be an issue. However I would think it is certainly
> > worth a shot before recoding or messing with replication. Could be
> > another profit center for you as well whether you host the db
> > yourself or outsource that. Especially if the client sites have a
> > good dedicated internet connection.[/color]
>
> All the databases are on the same local server so none of this is[/color]
required:)[color=blue]
>[color=green]
> > "Br@dley" <dontlookforme@ google.com> wrote in message
> > news:dvdc7n$k2j $1@news-02.connect.com. au...[color=darkred]
> >> We have a product that is being used by a client to outsource salary
> >> packaging. They therefore run several instances of our database, one
> >> for each of their clients.
> >>
> >> There is now a requirement to run operations globally across all the
> >> databases including a central database holding some global
> >> information. What method(s) would people recommend that would work
> >> best?
> >>
> >> (Access2000 ADP, SQL Server 2000)
> >>
> >> --
> >> regards,
> >>
> >> Br@dley[/color][/color]
>
> --
> regards,
>
> Br@dley
>
>[/color]

[Br@dley]

Re: Operating across multi SQL Server databases

Jerry Boone wrote:[color=blue]
> Ahh...
>
> Interesting, so you need to combine all the databases together for a
> single source solution.
>
> You could make a database called "globaldb" or something and make
> views named for each table object you have in the other databases...
>
> use globaldb
> create view 'table1'
> as
> select * from db1.dbo.table1
> union
> select * from db2.dbo.table1
> union
> select * from db3.dbo.table1
>
> create view 'table2'
> as
> select * from db1.dbo.table2
> union
> select * from db2.dbo.table2
> union
> select * from db3.dbo.table2
>
> and so on...
>
> This gives you a single db to connect to that combines data from the
> other db's. In just about any sql server scenario view objects are
> completely interchangeable with table objects. I always use views to
> "pull" in data from other databases. Then if the source database
> changes you have a "shim" layer where to can adjust such changes to
> keep from having to re-code your application.[/color]

Thanks.

The reason I need to work across the databases is because there is an import
transaction file that contains data for all the databases. So this needs to
be imported and reconciled.

[color=blue]
> "Br@dley" <dontlookforme@ google.com> wrote in message
> news:dvi7s8$nna $1@news-02.connect.com. au...[color=green]
>> Jerry Boone wrote:[color=darkred]
>>> What about finding a SQL Server hosting company?
>>>
>>> If the SQL Server happened to be hosted in one place and the
>>> connection strings all pointed there (IP over the web - or - VPN and
>>> connect) then your in business. Multiple users anywhere could
>>> connect and operate from a single datasource. Of course depending
>>> on the efficiency of the client application and how much it
>>> utilizes the server itself for processing (particularly on report
>>> generation), performance could be an issue. However I would think
>>> it is certainly worth a shot before recoding or messing with
>>> replication. Could be another profit center for you as well
>>> whether you host the db yourself or outsource that. Especially if
>>> the client sites have a good dedicated internet connection.[/color]
>>
>> All the databases are on the same local server so none of this is
>> required:)
>>[color=darkred]
>>> "Br@dley" <dontlookforme@ google.com> wrote in message
>>> news:dvdc7n$k2j $1@news-02.connect.com. au...
>>>> We have a product that is being used by a client to outsource
>>>> salary packaging. They therefore run several instances of our
>>>> database, one for each of their clients.
>>>>
>>>> There is now a requirement to run operations globally across all
>>>> the databases including a central database holding some global
>>>> information. What method(s) would people recommend that would work
>>>> best?
>>>>
>>>> (Access2000 ADP, SQL Server 2000)
>>>>
>>>> --
>>>> regards,
>>>>
>>>> Br@dley[/color]
>>
>> --
>> regards,
>>
>> Br@dley[/color][/color]

--
regards,

Br@dley