Martin Reid
mwp.reid at qub.ac.uk
Thu Apr 1 13:27:44 CST 2004
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 > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >