Making data available across servers

Question

Post reply

Making data available across servers

David Cameron (not the PM)

SSC Enthusiast

Points: 131
More actions
May 15, 2011 at 5:07 am

#145281

Hi,
I'm wondering what options there are to make data available between servers/instances.
Two obvious options, linked servers and CLR procs to return data. I'm just wondering if there are other options are available, including pluses and minuses of the different options.
All suggestions welcome.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Alternatives to a Linked Server are OPENROWSET and OPENDATASOURCE.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

http://msdn.microsoft.com/en-us/library/ms179856.aspx

http://decipherinfosys.wordpress.com/2007/07/16/linked-servers-in-sql-server/

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 2

I think it really is going to depend on what you are trying to accomplish. Is this requirement for reporting, and does it need to be real-time?

If it doesn't have to be real-time, then you could use SSIS to extract copies of the data from other servers and host it in a single database.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

David Cameron (not the PM) SSC Enthusiast Points: 131 More actions · Answer 3

Hi guys,

thanks for the suggestions and sorry for the late-ish response.

To clarify, we are looking more at realtime, this isn't for just reporting, so SSIS isn't a great fit.

Thanks for the suggestions on OPENROWSET etc, that looks like a fairly good option.

In this case this is likely to be quite a number of tables being shared between servers, rather than just one or two tables being shared. So I'm thinking that a linked server might be a better option, possibly just easier to deal with.

Thanks again for the suggestions, I'm going to open a separate topic on linked servers.

thanks

David

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 4

You could also look at replication as an option - again, it really depends on what you need to accomplish.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs