Query Relay

• animo3d

  Newbie

  Points: 7

  More actions

  October 13, 2009 at 4:21 pm

  #94019

  Hi, I have probably an unusual request, what I need to do I a server that relays queries to another server

  because of the network topology and security required we need to do this.

  I have hundreds of clients, and they perform queries regularly (a simple call to a stored procedure with a few parameters), but they can't reach the server required, my idea is to add another (or several ones) intermediate server that get the query, and they query the Actual server (performing exactly the same query), and then answer the client with the response received (just one record with 7 or 8 fields)...

  I kind of newbie programing server, have experience developing apps in the client side.

  questions:

  Can this intermediate server be done using a SQL Express?

  is it possible to program something in C# that emulates the server and just forward the query?

  I will appreciate any thoughts on this

  Thanks

  Sergio

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  October 14, 2009 at 5:18 pm

  #1066053

  Yes, this can be done, and somewhat easier than you describe, however there are some limitations and some very real security issues.

  What you need to do is to establish a Linked Server to the Target DB, then create a View for each Table and View in the target database.. This View in the Intermediate DB should have exactly the same name as the Table or View in the Target DB, and should look like this:

  CREATE VIEW [tableOrViewName]

  AS

  SELECT

  {list every field name here, ...}

  FROM [TargetServer].[TargetDB].[dbo].[tableOrViewName]

  Then copy all of the stored procedures from the Target DB and reproduce them in your Intermediate DB.

  And that's it. This will get you about 90-95% of the way there. And that last 5% is extremely difficult (or even impossible in some cases).

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]