January 26, 2011 at 2:23 pm
I would if anyone uses other way of connectivity other than linked server.
January 26, 2011 at 2:46 pm
SSIS seems to be as good an alternative to linked servers as anything, since you can have multiple datasets from differing sources.
Openrowset if you have to keep it within T-SQL.
I definitely prefer SSIS out of these two and in terms of Openrowset vs Linked server, to me they are about the same and have similar issues from a performance perspective.
January 26, 2011 at 3:47 pm
I've used SQLCLR..
Why don't you tell us a little but about what you want to do..
CEWII
January 26, 2011 at 3:59 pm
SSIS would be a good alternative.
But Linked Servers always have their place no matter what.
Thank You,
Best Regards,
SQLBuddy
January 26, 2011 at 4:29 pm
Mike - CI (1/26/2011)
SSIS seems to be as good an alternative to linked servers as anything, since you can have multiple datasets from differing sources.Openrowset if you have to keep it within T-SQL.
I definitely prefer SSIS out of these two and in terms of Openrowset vs Linked server, to me they are about the same and have similar issues from a performance perspective.
If you using SSIS dont you still need a linked server?
January 26, 2011 at 4:34 pm
No, it would just be two data sources joined together via a lookup.
January 26, 2011 at 4:42 pm
No, not required. But needs to do more work as its an alternative.
Abt OpenQueries Vs Linked Servers...
OPENROWSET and OPENDATASOURCE should be used only if you are accessing the remote OLEDB resources infrequently.
If you are frequently accessing remote OLEDB resources they it is better to use a linked server.
With OPENROWSET and OPENDATASOURCE the login info including the passwords need to be provided everytime we use them.
You can avoid this by creating a linked server and using it for the remote queries. Also with linked servers you can have better security mgt and also you can query the catalog information.
Thank You,
Best Regards,
SQLBuddy
January 27, 2011 at 10:54 am
I do not have much knowledge of using SQLCLR or OPENQUERY other than linked servers. Mentioned below is one of my requirement can someone please provide me the actual query based on my requirement.
Database Db_A is on server S1
Database Db_b is on server S2
There is synonym on Db_b pointing to tableA on Db_A on serverS1 something like below.
Test_Synonym = [LinkedServerS1].[Db_A].[dbo].[tableA].
Test_Synonym is on Db_b and are used to insert/read data through web application/jobs.Unfortunately we will not change synonyms( unless there is no OTHER WAY to avoid linked server]
Based on above scenario can you provide your sql? Thanks
January 27, 2011 at 12:35 pm
Using SQLCLR would be a situation where you would have some .Net code that created a connection that you could dynamically pass into a function or proc that would make the connection for you. I do do not have a quick and easy example of that.
However I do of OPENROWSET (which I reiterate that I do not feel is a great deal better than Linked Servers).
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=S1;Trusted_Connection=yes;',
'SELECT * from Db_A..tablename') AS a;
or
INSERT INTO OPENROWSET('SQLNCLI', 'Server=S1;Trusted_Connection=yes;',
'SELECT FIELD1 from Db_A..tablename')
SELECT 'some value here matching FIELD1'
or
delete A
FROM OPENROWSET('SQLNCLI', 'Server=S1;Trusted_Connection=yes;',
'SELECT * from Db_A..tablename') A
January 27, 2011 at 8:19 pm
Mike - CI (1/27/2011)
Using SQLCLR would be a situation where you would have some .Net code that created a connection that you could dynamically pass into a function or proc that would make the connection for you. I do do not have a quick and easy example of that.However I do of OPENROWSET (which I reiterate that I do not feel is a great deal better than Linked Servers).
SELECT a.*FROM OPENROWSET('SQLNCLI', 'Server=S1;Trusted_Connection=yes;',
'SELECT * from Db_A..tablename') AS a;
or
INSERT INTO OPENROWSET('SQLNCLI', 'Server=S1;Trusted_Connection=yes;',
'SELECT FIELD1 from Db_A..tablename')
SELECT 'some value here matching FIELD1'
or
delete A
FROM OPENROWSET('SQLNCLI', 'Server=S1;Trusted_Connection=yes;',
'SELECT * from Db_A..tablename') A
for open row set where are you passing the credentials?
January 28, 2011 at 6:39 am
In this case I am not passing in the credentials (note that I am using a trusted connection). But you can, here is the help file information that shows you what all you can pass through
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
January 28, 2011 at 7:39 am
sqldba_icon (1/27/2011)
I do not have much knowledge of using SQLCLR or OPENQUERY other than linked servers. Mentioned below is one of my requirement can someone please provide me the actual query based on my requirement.Database Db_A is on server S1
Database Db_b is on server S2
There is synonym on Db_b pointing to tableA on Db_A on serverS1 something like below.
Test_Synonym = [LinkedServerS1].[Db_A].[dbo].[tableA].
Test_Synonym is on Db_b and are used to insert/read data through web application/jobs.Unfortunately we will not change synonyms( unless there is no OTHER WAY to avoid linked server]
Based on above scenario can you provide your sql? Thanks
If you want to reference an external SQL Server by name and have the flexibility to change the DSN, then a linked server fits the bill.
If performance is an issue, then consider a pass-through style query will generally perform better than a query using 4 part naming convention, because 4 part named queries can result in inefficient execution plans, table scans, and cross-server joins. However, a pass-though query is executed entirely on the remote server, and all that gets passed back across the wire is the final resultset.
You can perform an a pass-through style remote query on a linked server using OPENROWSET or EXEC <SQL> AT <LINKEDSERVERNAME>. I prefer EXEC AT because it allows passing paramters; unlike OPENROWSET which accepts only a static SQL statement.
For example:
EXEC (
'select c1, c2, c3 from TableA
join TableB on TableB.c1 = TableA.c1
where c3 = ?
', @C ) AT XSERVER;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 28, 2011 at 10:10 am
The BEST way to manage LINKED SERVERS is to never use them at all! They do LOTS of unfortunate things. The way to avoid them is to put databases on same server or use replication to keep data on local server. I have helped clients get orders of magnitude better throughput on their applications using replicated copies of remote data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 28, 2011 at 1:18 pm
TheSQLGuru (1/28/2011)
The BEST way to manage LINKED SERVERS is to never use them at all! They do LOTS of unfortunate things. The way to avoid them is to put databases on same server or use replication to keep data on local server. I have helped clients get orders of magnitude better throughput on their applications using replicated copies of remote data.
If a database is making heavy use of data from a remote server, then it would be best to replicate over the required tables. However, depending on what edition of SQL Server (or RDBMS platform) you're working with remotely, replication may not be possible or practical. In one specific case where I'm leveraging a linked server connection, I have a reporting datamart in SQL Server 2005 which queries reference data and summary totals from a 3rd party supported Oracle database, once per month, and then inserts the resultsets into local tables. I have no problems using a linked server connection, so long as I stick to pass-through queries. However, joining a million row table on Server A with another remote million row table on Server B; that's what presents a problem, especially when joining across to a non-SQL Server database.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 31, 2011 at 8:39 am
Hello,
You told not to use Link Servers! Because I have performance issues on my server because of having many databases, then I want to seprate them, since they need to transact to each other, I shal relate them . Found no way except link server. But wat about performance issues comming with link servers?!!!!Replication cannot help me this way!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply