Operating across multi SQL Server databases

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Br@dley - 17 Mar 2006 04:59 GMT

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)

Signature

regards,

Br@dley

Reply to this Message

Larry Linson - 17 Mar 2006 07:36 GMT

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

> 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
[quoted text clipped - 5 lines]
>
> (Access2000 ADP, SQL Server 2000)

Reply to this Message

Br@dley - 19 Mar 2006 01:13 GMT

> 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.

Thought of that but we're using an ADP...

> 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.

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.

> However, as that may not be a common
> requirement (e.g, just this one "outsourcer" or a few), that might
[quoted text clipped - 18 lines]
>>
>> Br@dley

Signature

regards,

Br@dley

Reply to this Message

Graham Charles - 17 Mar 2006 08:14 GMT

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.

Reply to this Message

Br@dley - 19 Mar 2006 01:14 GMT

> 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.

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...

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

Signature

regards,

Br@dley

Reply to this Message

Jerry Boone - 17 Mar 2006 19:19 GMT

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.

Signature

Jerry Boone

Reply to this Message

Br@dley - 19 Mar 2006 01:15 GMT

> What about finding a SQL Server hosting company?
>
[quoted text clipped - 9 lines]
> yourself or outsource that. Especially if the client sites have a
> good dedicated internet connection.

All the databases are on the same local server so none of this is required:)

>> We have a product that is being used by a client to outsource salary
>> packaging. They therefore run several instances of our database, one
[quoted text clipped - 11 lines]
>>
>> Br@dley

Signature

regards,

Br@dley

Reply to this Message

Jerry Boone - 20 Mar 2006 16:37 GMT

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.

Signature

Jerry Boone

> > What about finding a SQL Server hosting company?
> >
[quoted text clipped - 27 lines]
> >>
> >> Br@dley

Reply to this Message

Br@dley - 21 Mar 2006 22:57 GMT

> Ahh...
>
[quoted text clipped - 29 lines]
> changes you have a "shim" layer where to can adjust such changes to
> keep from having to re-code your application.

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.

>>> What about finding a SQL Server hosting company?
>>>
[quoted text clipped - 33 lines]
>>
>> Br@dley

Signature

regards,

Br@dley

Reply to this Message

Lyle Fairfield - 19 Mar 2006 02:49 GMT

> We have a product that is being used by a client to outsource salary
> packaging. They therefore run several instances of our database, one
[quoted text clipped - 6 lines]
>
> (Access2000 ADP, SQL Server 2000)

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.dbname.owner.object?
eg SELECT * FROM Server2.FFDBA_ESO_Remote.dbo.tblSchools

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.

Signature

Lyle Fairfield

Reply to this Message

Br@dley - 19 Mar 2006 21:43 GMT

>> We have a product that is being used by a client to outsource salary
>> packaging. They therefore run several instances of our database, one
[quoted text clipped - 8 lines]
>
> Are the dbs on different servers? Do they have different names?

Same server.

> 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).

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).

<>

Signature

regards,

Br@dley

Reply to this Message