Francisco H Tapia
my.lists at verizon.net
Fri Apr 2 15:58:50 CST 2004
I recognize the benefit of cross-db-Joining... as w/ anything that gets locked down, security issues are always of concern. Copying over the tables introduces a series of data duplication that will ultimately create a new level of maintenance...One idea to keep the data in synch is to introduce transactional replication, so that all base data would be identical throughout the enterprise... but if this is the case, then it defeats the purpose of linked servers etc. Djabarov, Robert said the following on 4/2/2004 12:53 PM: >I would, and create an optimized path to those tables so that when they >are used the rest of the database is not affected. > >Robert Djabarov >SQL Server & UDB >Sr. SQL Server Administrator >Phone: (210) 913-3148 >Pager: (210) 753-3148 >9800 Fredericksburg Rd. San Antonio, TX 78288 >www.usaa.com > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Martin >Reid >Sent: Friday, April 02, 2004 2:11 PM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] Creating joins across multiple databases > >An example > > >A couple of systems keep student data but also some things very specific >to >the faculty. Only they owuld use this data so it si not held on any of >the >core systems. Unless of course we put it on them. > > >Martin > > >----- Original Message ----- >From: "Djabarov, Robert" <Robert.Djabarov at usaa.com> >To: <dba-sqlserver at databaseadvisors.com> >Sent: Friday, April 02, 2004 8:18 PM >Subject: RE: [dba-SQLServer] Creating joins across multiple databases > > > > >>Is there a way of getting rid of smaller databases while retaining >>"distinct functionality" by isolating those users via security >> >> >settings > > >>(designated database role) and specific set of data access paths - >>functions, stored procedures, etc.? >> >>Robert Djabarov >>SQL Server & UDB >>Sr. SQL Server Administrator >>Phone: (210) 913-3148 >>Pager: (210) 753-3148 >>9800 Fredericksburg Rd. San Antonio, TX 78288 >>www.usaa.com >> >>-----Original Message----- >>From: dba-sqlserver-bounces at databaseadvisors.com >>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of >> >> >Martin > > >>Reid >>Sent: Friday, April 02, 2004 12:49 PM >>To: dba-sqlserver at databaseadvisors.com >>Subject: Re: [dba-SQLServer] Creating joins across multiple databases >> >>Robert >> >>We have several large systems which will be migrated to SQL Server >>containing the core data of the University. Say we then have several >>smaller >>databases each performing a distinct function. They need access to the >>core >>information held within the large corporate systems. How would you >>handle >>this without cross db joins? >> >>Would you import data overnight using DTS or some other method?? >> >>Best Wishes >> >>Martin >> >> >> >>----- Original Message ----- >>From: "Djabarov, Robert" <Robert.Djabarov at usaa.com> >>To: <dba-sqlserver at databaseadvisors.com> >>Sent: Thursday, April 01, 2004 9:31 PM >>Subject: RE: [dba-SQLServer] Creating joins across multiple databases >> >> >> >> >>>Not sure if I understand "when joins where out" part, but I'd stay >>> >>> >out > > >>>of cross-database joins. If there is a need for something like >>> >>> >this, > > >>>it's most likely that the app architecture needs to be rethought. >>> >>> >Or, > > >>>instead of creating a new database along with a new app, maybe the >>>existing app and the underlying db need to be modified to >>> >>> >accommodate > > >>>newly requested features. >>> >>>Robert Djabarov >>>SQL Server & UDB >>>Sr. SQL Server Administrator >>>Phone: (210) 913-3148 >>>Pager: (210) 753-3148 >>>9800 Fredericksburg Rd. San Antonio, TX 78288 >>>www.usaa.com >>> >>> >>>-----Original Message----- >>>From: dba-sqlserver-bounces at databaseadvisors.com >>>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of >>> >>> >>Martin >> >> >>>Reid >>>Sent: Thursday, April 01, 2004 1:28 PM >>>To: dba-sqlserver at databaseadvisors.com >>>Subject: Re: [dba-SQLServer] Creating joins across multiple >>> >>> >databases > > >>>How would you handle the issue in that case when joins where out?? >>> >>>Martin >>> >>> >>> >>>----- Original Message ----- >>>From: "Djabarov, Robert" <Robert.Djabarov at usaa.com> >>>To: <dba-sqlserver at databaseadvisors.com> >>>Sent: Thursday, April 01, 2004 7:51 PM >>>Subject: RE: [dba-SQLServer] Creating joins across multiple >>> >>> >databases > > >>> >>> >>>>Based on what I've seen so far, it's not a good idea to do >>>> >>>> >cross-db > > >>>>joins, and not only because of security issues as Francisco >>>> >>>> >>mentions, >> >> >>>>but also because of questions like "Who the heck is this guy and >>>> >>>> >>what >> >> >>>>is he doing in my swamp?" which immediately start popping up, >>>> >>>> >which > > >>>>leads to finger-pointing when there is a slow-down or outage. It >>>> >>>> >>also >> >> >>>>violates database designation (DSS, OLTP) when it's OLTP for a >>>> >>>> >>native >> >> >>>>user while becoming a DSS for the new-comer. >>>> >>>>BTW, Francisco, did you copy the article or retyped it? If you >>>> >>>> >>copied >> >> >>>>it, I see a minor problem with Author's syntax... >>>> >>>> >>>>Robert Djabarov >>>>SQL Server & UDB >>>>Sr. SQL Server Administrator >>>>Phone: (210) 913-3148 >>>>Pager: (210) 753-3148 >>>>9800 Fredericksburg Rd. San Antonio, TX 78288 >>>>www.usaa.com >>>> >>>>-----Original Message----- >>>>From: dba-sqlserver-bounces at databaseadvisors.com >>>>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of >>>>Francisco H Tapia >>>>Sent: Thursday, April 01, 2004 12:41 PM >>>>To: dba-SQLServer at databaseadvisors.com >>>>Subject: [dba-SQLServer] Creating joins across multiple databases >>>> >>>>I'm including the latest article from Arthur Fuller on the >>>> >>>> >>Builder.com >> >> >>>>SQL Newsletter because I hope it can bring forward some positive >>>>discussion. Plus I'm sure many of us have dealt with this >>>> >>>> >>particular >> >> >>>>situation or will deal with it in the future. >>>> >>>>/<BUILDER.COM SQL Newsletter>/* >>>>Creating joins across multiple databases* >>>> >>>>Enterprise databases often comprise several databases--one from >>>>production, another from marketing, and so on. All of the >>>> >>>> >databases > > >>>rely >>> >>> >>>>on a certain amount of common data, which is frequently copied to >>>> >>>> >>each >> >> >>>>database that needs it. >>>> >>>>However, this copying routine creates a new problem: skew. Where >>>> >>>> >>there >> >> >>>>is duplication, there is at least the opportunity for skew. The >>>> >>>> >>>solution >>> >>> >>>>to this problem is often replication, or scheduled jobs that >>>> >>>> >update > > >>>>each >>>> >>>>copy of the common data from one "master" database. This approach >>>>works, >>>> >>>>but sometimes a better method is to move all the common data into >>>> >>>> >>one >> >> >>>>database, and then access it as needed from the other databases. >>>> >>>> >>This >> >> >>>>eliminates both the skew and the replication jobs. >>>> >>>>This approach typically requires joining some local tables to the >>>>common >>>> >>>>tables. For example, suppose you have two databases: Common and >>>> >>>> >>Sales. >> >> >>>>The Customers table resides in the Common database, while the >>>> >>>> >Orders > > >>>>table resides in the Sales database. You want to create a query >>>> >>>> >that > > >>>>lists the Customer information along with the Order information. >>>> >>>> >How > > >>>do >>> >>> >>>>you join these two tables? The answer lies in prefixes. >>>> >>>>SQL Server uses a dot-delimited nomenclature that extends outward >>>> >>>> >to > > >>>>the >>>> >>>>database and the server. To specify a column in a table in another >>>>database, name the other database: >>>> >>>>SELECT CustomerID FROM Common.Customers >>>> >>>>To join a table from the Common database to a table in the current >>>>(Sales) database, use this same nomenclature: >>>> >>>>SELECT * FROM Common.Customers >>>>INNER JOIN Orders >>>>ON Common.Customers.CustomerID = Orders.CustomerID >>>> >>>>Not every organization's application domain requires multiple >>>>databases. >>>> >>>>If your organization does require multiple databases, it's easy to >>>>create joins across databases. >>>> >>>>/Arthur Fuller has been developing database applications for more >>>> >>>> >>than >> >> >>>>20 years. He frequently works with Access ADPs, Microsoft SQL >>>> >>>> >2000, > > >>>>MySQL, and .NET./ >>>>/<BUILDER.COM SQL Newsletter>/ >>>> >>>>Yes it is easy enough to join accross multiple databases and even >>>>accross several Sql Server linked servers :) (very cool)... >>>> >>>> >>however >> >> >>>my >>> >>> >>>>concern and one that was not addressed in this article is the >>>> >>>> >>security >> >> >>>>aspect of it... say you just have a user who calls the report... >>>> >>>> >>does >> >> >>>>the user need to use an application who's using an application >>>> >>>> >role > > >>>and >>> >>> >>>>thus gets better than normal priviladges on the server? I have >>>> >>>> >not > > >>>been >>> >>> >>>>successful in adding new users to another database w/o also having >>>> >>>> >>to >> >> >>>>takes some hard steps; my solution was to ... Create VIEWS on the >>>> >>>> >>>target >>> >>> >>>>database... add the user to that 2nd database, then give explicit >>>>selet >>>>crights on the views and then join accross from db2 to db1 via the >>>> >>>> >>>sproc >>> >>> >>>>using the views instead of the tables... so while the data is not >>>>duplicated, the user is, and now has rights to base views that >>>> >>>> >>>represent >>> >>> >>>>tables :( , so because I think I might be doing this wrong.. I'm >>>>hoping >>>>Arthur or someone else will chime in with a few clues :) >>>> >>>>Thanks, >>>> >>>>-- >>>>-Francisco >>>> >>>> >>>> >>>> > > > -- -Francisco