October 19, 2007 at 3:19 am
FYI, you can use server aliases to get around this issue
If I were to move this database to it's own server, which could happen if either of these databases or the load on the server grew, then any process that accessed this table would require recording
Create a server alias local to the database server, ie give the server a logical rather than netbios name. For example, OrdersServer. You can then repoint any DB code by changing the server this alias refers to.
This technique is also useful for use in support dev/test environments without having to change your code base when you change environments.
October 19, 2007 at 3:19 am
Can you make a synonym for database?
If not mistaking, you can use sysonyms for objects in database. So, you can choose between use of them(for most db objects) and views(for tables).
Well, this could simplify your code and change of database name/schema.
Phillip (10/19/2007)
Ah, sorry, new to forum. Others had explained synonyms. Had been looking at thread sorted in reverse chronologically and didn't see earlier posts on same topic.
Collation is server/database/table specific option and should be considered for specific query. You should consider it once for each table query. I do not think anybody would change collation while moving database from one server to other, but server name/database name could change.
October 19, 2007 at 3:35 am
This is OK for general reporting, but when you want to get online data..., you need to use link server.
Also, do not forget that using data from source you receive it actuall. Is there a guaranty that data in your database will be same as on source server?
I am not against DTS and think it should be used. I am saying there are different tasks and different solutions. Question here, I suppose is the most afficient use of link servers for quering other database.
Roseline (10/19/2007)
Using Linked Server is always gonna slow down the process when data grow...i prefer to have a DTS written that transfers data to a table in the local server either daily or periodically and this local table is to be replaced in place of view.
We also did not cover distributed transactions 🙂 as well.
You might get error, even doing following:
insert into #ttt
select * from [linkserver].[x].[x].[tb]
Because this is distributed transaction 🙂
I do not remember when it fires, but sometime you get it.
October 19, 2007 at 7:50 am
I agree that if you're using databases on the same server that a view definitely seems to be the way to go, but...
Even if you use a view to query Subscription from Orders, if either database is moved to another server, you're still going to have to in and edit the view to correct it. And, unless you decide to use OpenRowSet / OpenDataSource, you're still going to have to use Linked Servers.
So, if you're in a scenario where you have 2 different servers and 2 different dbs, one on each server, which option do you go for? Creating a view with a Linked Server 4 dot name in it or creating the actual query with the Linked Server 4 dot name or OpenRowSet / OpenDataSource?
I haven't actually played much with the OpenRowSet / OpenDataSource stuff yet. And I happen to be working on a major reporting product which does indeed require me to connect to multiple servers and their databases, so I'd like to see what thoughts everyone has on the issue.
October 19, 2007 at 8:03 am
The followoing solution is for SQL2005 only. It works for read, write and stored procedure calls:
1.
select *
from Subscription.dbo.subscriber
2.
create synonym syn_subscriber
for Subscription.dbo.subscriber
go
3.
select * from syn_subscriber
4.
--move Subscription db to another instanse DBSRV2
--add linked server DBSRV2
drop synonym syn_subscriber
go
create synonym syn_subscriber
for DBSRV2.Subscription.dbo.subscriber
go
5
select *
from syn_subscriber
Thanks,
Alex
October 19, 2007 at 8:15 am
Unfortunately, only one of my servers is 2005 (the destination). The other server (the source) is 2000 and we can't upgrade yet because of an app that currently doesn't work on 2005.
I hate expensive 3rd party apps that don't take portability into account. Bleargh.
October 19, 2007 at 8:42 am
Thanks, Steve. I've used views (in my case select only) to enable easier reporting, too. I'm mainly an analyst, so it's great to receive some confimation from someone with significantly more knowledge and experience that this method is an acceptable option.
Julie
October 19, 2007 at 9:01 am
For those of you that are still working with SQL Server 2000, I think you are missing the SYNONYM. I have used SYNONYMs a little, and have found them to be a nice way to query separate database or servers. The base code can query the SYNONYM, then you can drop and recreate your SYNONYM for any source. For SQL Server 2000, I was in the habit of using Views. I am not a fan of Linked Servers and wish that Microsoft would put some effort toward performance tuning the Linked Server process. This is a common issue that I have seen in the user groups and with my own professional experience.
I do like SYNONYMs for SQL 2K5.
Greg
October 19, 2007 at 9:45 am
What about using the linked server to reach a custom table function? Does that keep the performance optimizations on the local server?
Select * from
server.database.owner.fnRemoteTableView( filterCriteria )
Suppose there are 10 million rows in the base table, but the filtered results are only 5k records - the server that hosts the data can use appropriate indexes, materialized views, partitioning, etc. then return only the useful 5k rows.
If the remote data contains more history than you need (10 years life of business, but you're running a quarterly report) it might be useful to locally cache a subset (the last year) then make multiple selections from the cache. We even use this approach on in-database selections simply because the optimizer sometimes makes unwise decisions on complex joins with large tables.
October 19, 2007 at 10:06 am
Not related to the original topic, but here is a tip on implementing indexed views. I typically create / optimize the objects in our database but the other devs are always throwing in a new SP to access them. We run sql 2005 standard, so I need to make sure a WITH(NOEXPAND) gets tagged on to every reference to my indexed views.
I do this by naming every indexed view with a suffix of 'Base', and then make another view that calls the 'Base' view and specifies WITH(NOEXPAND). Everyone calls my container view and so we never have to remember to specify with(noexpand).
Our environment is asp.net in the front end so we do a ton of databinding in c# to the values returned from sql. It's annoying to have to use count_big() in the indexed view and receive a bigint because I get a cast error trying to bind this to a 32 bit int in .Net. So my wrapper view typically casts the count column to Int and provides a *_BIGINT column that passes the count through natively as bigint (so we can have better perf when doing like a TOP 10 ... ORDER BY *_BIGINT DESC and avoid the unneeded cast).
Finally the other hurdle for indexed views in sql 2005 standard was that you need Enterprise to support replicating indexed views. We need bi-directional transactional replication so I just ended up doing 'initialize from backup' with replication so that all of my defined indexed views get created on a new server in the topology. We do all of our database dev work on one DB, so I end up reinitializing replication every few weeks to bring all of the new DB objects into the topology anyhow (with a website we're always rolling out new DB features).
This has worked very well for us so far. Example of indexed view creation:
create table t1 (i int identity primary key, bucket int not null, points int not null);
insert t1 (bucket, points)
select 1, 10
union all
select 1, 15
union all
select 2, 10;
GO
create view t1TotalsBase
with schemabinding as
select
bucket,
count_big(*) as bucketCount,
sum(points) as pointsSum
from dbo.t1
group by bucket;
GO
create unique clustered index t1totalsbase_ucidx_bucket on t1TotalsBase(bucket);
GO
create view t1Totals
as
select
bucket,
cast(bucketCount as int) as bucketCount,
pointsSum,
cast(pointsSum as float) / bucketCount as pointsAvg,
bucketCount as bucketCount_BIGINT
from t1TotalsBase WITH (NOEXPAND);
GO
select * from t1Totals;
October 19, 2007 at 12:23 pm
I've had similar situations and I agree with your solution of using views, then referencing them in your sp's
However, I'd strongly recommend against granting your users in one DB the connect, select, update permissions in the other. Users from one DB should not be allowed into another without a good reason to be there. Also, in general, I use execute permissions only, and do not grant any select or update permissions.
So how do we combine these two things and make lemonade out of the Lemonade?
Simple!
If you create the SP's that reference the other DB with an "Execute As" statement, the SP itself will assume a set of permissions that could include access to the other DB. While access to the sp is still controlled by who has execute permission to the SP.
As a simplistic example:
DB1 has a user User1
DB2 has a user User2
User3 has access and permissions to both DBs, but no login uses it.
DB1 has a view that selects data from DB2 and a SP that references the view
User1 needs to be able to execute that SP.
By using Execute As to say that this SP executes as User3, User1 can execute it, retrieving their data without being given any permissions to DB2!
October 19, 2007 at 2:33 pm
I have a situation where the different lines of business have specific databases but there is a common database that is shared by all lines of business.
The common database is replicated to all database servers.
Cross database ownership chaining is deliberately enabled on the common database so that procs in the specific line of business databases can access the common data without me having to grant elevated privileges in that common database.
It works very well.
If performance is important avoid cross server queries like the plague. If you introduce server dependencies then you are introducing a point of failure. Even if you have the perfect hardware that never goes down you end up with performance issues.
I think the Service Broker is going to offer a lot of interesting solutions to cross server problems provided I can find a decent resource on how to get it set up correctly.
October 19, 2007 at 11:15 pm
Steve Jones - Editor (2/22/2005)
Comments posted to this topic are about the content posted at
at the top of the page (http://www.sqlservercentral.com/articles/Advanced/designingcrossdatabasequeries/1753/), you mention:
designingcrossdatabasequeries
and you give information about VIEWS,
THAT'S SO WIERD
October 20, 2007 at 12:37 am
That so wierd
Umm... using passthrough "synonym" views to perform the crosstalk between databases without using the full 3 part naming convention was the whole purpose of the article... what's wierd about that? :blink:
Also, we implimented these types of views about 5 years ago on SS 2k... they work great and have not been affected by our large increase in scale over time.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2007 at 3:19 am
Some posts there have raised concerns about collations. Whilst it is highly desirable to have everything nicely aligned (including collations), this isn't always the case. It is pretty straightforward to tell SQL Server which collation to use for string comparisons
e.g. Select * From table1 inner join table2 on table1.column = table2.anothercolumn collation Latin1_General_CI_AS
As for concerns about the use of linked server... using a synonym defintely does not change that. The same performance considerations exists when you use an synonym or a 4 part name - they both need to use a linked server if you are accessing a database on another server. You must understand the environment you are working in BEFORE you implement a solution otherwise you may well end up with a serious problem.
Certainly, there are other options. A number of the are discussed in earlier in this discussion. You should think about each of them when deciding how to access data in another database - regardless of whether that database is on the current server or not.
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply