November 2, 2009 at 12:44 pm
I need to access data that resides on an externally hosted server. I come from an application background and have limited SQL Server (especially 2005) experience (basically I know just enough to be dangerous). I've been reading up and taking DVD training, but I'd be a fool not to ask for assistance.
SRVR01EXT is outside our firewall and supports an externally hosted application (APP01). My group isn't involved with this project, but it is our understanding that our firm has no control at all (ie, we can't create Publications on the server).
SRVR01INT is inside our firewall and is the only server with access to the external server. My firm controls this one, but only one person has access and he is neither a SQL Server expert, nor a member of my group...although we can tell him how to set it up for us.
SRVR02 is inside our firewall and supports an internal application (APP02) which will consume data from SRVR01EXT. Our firm controls this one and my group has full admin rights.
Performance permitting, we would like SRVR02 to include realtime data from SRVR01EXT; otherwise, a scheduled snapshot could be used. The data, which consists of only 1K parent records and several related tables totaling less than 100K records, has been exposed for us by way of Linked Servers and Views on SRVR01INT. We tried using Linked Servers and Views on SRVR02 (daisy-chained to the Views on SRVR01INT), but performance isn't acceptable. We are considering Indexed Views, Replication and Packages. Any suggestions?
November 2, 2009 at 1:48 pm
You apparently have given a good description of what you have in place and what you intend to do, and also, you have pretty much figured out what are the chances you have to workout the solution.
I have a few questions and based on them I may suggest.
Does your SRVR01INT needs any Data from the Server SRVR01EXT, or it is acting as a channel between SRVR01EXT and SRVR02?
If it is just a link in the chain and it does not need any data, I would suggest to use BCP (since You said you cannot have replication setup) or Doing it with SSIS Packages.
If the SRVR01INT also needs the Data, then there would be a different plan to implement.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 2, 2009 at 2:57 pm
At the moment SRVR01INT is only a channel, but it wouldn't surprise me to see some additional reporting views there at some point. Originally, when I thought the user with the "keys" to that server was well versed with SQL Server 2005, I suggested scheduling packages to import the data...only later, after the Views were created (and I read about Replication) did I start to consider realtime data access.
OK, so it sounds like you recommend BCP (which I read about, also) or Packages. A couple more questions:
Does that mean it is not possible to set up replication from Views (eg from the Views on SRVR01INT to SRVR02)?
What I find out we can set up Publications on SRVR01EXT...does Replication to SRVR01INT merit consideration (obviously I would still need to get the data to SRVR02, but that should be easier once the data is physically inside our firewall).
FYI, regarding the data and how often it changes, etc: The external app is an HR system. The 1K records is our number of employees. The 100K child records is any related information (language, certification, etc). The internal app is a searchable firm directory on our intranet. At this time, this information is assumed to be read-only...any self-service updates would occur through the externally hosted HR app.
I appreciate your help. Thank again.
Erich
November 2, 2009 at 3:59 pm
First I want to compliment you on your well written summary of what you have and what you want, and the way it is setup. We rarely get anything that detailed and it IS appreciated.
I would go along with the previous poster. You can't really do transactional replication on a view pointing at a remote source, there is nothing indicating to that view that the data has changed which is the basis of transactional replication. You might be able to do snapshor replication by why bother when you can accomplish basically the same thing with SSIS packages. I'm not a fan of Buggy C Program (BCP) especially when SSIS is available.
If the Int01 server might ever need the data I would definitely write it there and then probably copy it to Int02. The way I would accomplish this is an SSIS package executed on Int01 that pulls the data from EXT and writes it to itself. I would use EXACTLY the same structure as the source on Int01, I would then copy the data, using the same SSIS package with different source and destination connection information to copy from Int01 to Int02. Now let me specify my reasons for each of these. SSIS is VERY good at copying data from SQL-to-SQL. The copy from EXT to Int01 will likely be the slowest part because it is not local to your network and we only want to pay for that copy once. The copy from Int01 to Int02 should be VERY fast because of two factors, SQL-to-SQL, and on the same local network.
I am not a fan of linked servers unless there is a strong well articulated need, I don't see that here as they would only be used to prevent that second copy. I have used them extensively and have spent the last several years pulling back from using them without long and hard consideration. When I do use them I use them sparingly and ALMOST never use the tables directly, I will often call a stored procedure on the remote server that returns heavily filtered data for me to use.
If you have additional questions I would be happy to answer..
CEWII
November 2, 2009 at 4:41 pm
You can solve the problem using SSIS, I agree with Elliott, about the efficiency and Performance of SSIS packages doing this.
The only consideration I gave to BCP was, if SSIS is not installed and not being used.
About Linked Servers, even I am confident, you will not want to pull Data across Servers when something like SSIS does it more efficiently.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 2, 2009 at 6:25 pm
Regarding your question on using replication from Views, You can replicate Views but the underlying tables must be present on the Subscriber.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply