How to connect test to production system

I am looking for guidance. I do SQL and other development on 2
computers, and I work remotely from my company's home office. (We use
SQL 2000 and 2005 both.)
My "main" computer has a VPN to the home office. Outside of that, and
before the VPN was set up, I had a TCP connection to ONE of the SQL
server instances at the home office... that specific destination port is
filtered so that only my TCP source IP address can connect to it.
(Now I use the VPN to connect to the home office Exchange server and
shared folders.)
My own "SQL server" computer is connected by Ehternet, ONLY to my
"main" computer. The main computer is obviously multi-homed, connecting
to the SQL computer and also to the outside world through a DSL router
(which is what the VPN connection now uses).
The SQL client on my main computer can see all of the SQL servers at the
home office. When I run the SQL client running on the SQL server
computer, it can only see the server that we had the TCP port set up
for.
The guidance is needed for the following... most of the views and stored
procs that I develop, get the data from the database that I keep a local
copy of, and there is a copy at the home office.
A few of the view and procs need to access data on some of the other SQL
servers at the home office. While my main computer can connect to those
servers, I have trouble setting them up as linked servers from my local
SQL server.
I also would like for the views and procs in my copy of the main
database here to look *exactly* like the views and procs in the database
at the home office... but there are instances of this:
Select * from [server\instance].dbname.dbo.table where...
And server\instance need to be different between the local developement
database and the production database at the home office.
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
2) How do people develop views and stored procs on test systems, when
the call-out to the linked server might need a different server name in
production?
Sorry for such a long question, but I wanted to list all of the info
that might be pertinent.
Thanks.
David Walker
(I asked a specific but related question in the General group, and got
an answer that I couldn't quite do what I thought I could do. This
question is the general case, and I'm loking for advice.)
I would post this to microsoft.public.sqlserver.programming
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"DWalker" <none@.none.com> wrote in message
news:O8XxVY62HHA.1164@.TK2MSFTNGP02.phx.gbl...
>I am looking for guidance. I do SQL and other development on 2
> computers, and I work remotely from my company's home office. (We use
> SQL 2000 and 2005 both.)
> My "main" computer has a VPN to the home office. Outside of that, and
> before the VPN was set up, I had a TCP connection to ONE of the SQL
> server instances at the home office... that specific destination port is
> filtered so that only my TCP source IP address can connect to it.
> (Now I use the VPN to connect to the home office Exchange server and
> shared folders.)
> My own "SQL server" computer is connected by Ehternet, ONLY to my
> "main" computer. The main computer is obviously multi-homed, connecting
> to the SQL computer and also to the outside world through a DSL router
> (which is what the VPN connection now uses).
> The SQL client on my main computer can see all of the SQL servers at the
> home office. When I run the SQL client running on the SQL server
> computer, it can only see the server that we had the TCP port set up
> for.
> The guidance is needed for the following... most of the views and stored
> procs that I develop, get the data from the database that I keep a local
> copy of, and there is a copy at the home office.
> A few of the view and procs need to access data on some of the other SQL
> servers at the home office. While my main computer can connect to those
> servers, I have trouble setting them up as linked servers from my local
> SQL server.
> I also would like for the views and procs in my copy of the main
> database here to look *exactly* like the views and procs in the database
> at the home office... but there are instances of this:
> Select * from [server\instance].dbname.dbo.table where...
> And server\instance need to be different between the local developement
> database and the production database at the home office.
> 1) Given that I now have a VPN, and the "main" computer is multihomed,
> should I be able to link the database on my local SQL server to the
> databases at the home office?
> 2) How do people develop views and stored procs on test systems, when
> the call-out to the linked server might need a different server name in
> production?
> Sorry for such a long question, but I wanted to list all of the info
> that might be pertinent.
> Thanks.
> David Walker
>
> (I asked a specific but related question in the General group, and got
> an answer that I couldn't quite do what I thought I could do. This
> question is the general case, and I'm loking for advice.)
|||"William Vaughn" <billvaNoSPAM@.betav.com> wrote in news:O#W35g72HHA.536
@.TK2MSFTNGP06.phx.gbl:

> I would post this to microsoft.public.sqlserver.programming
>
Thanks. If I can't get things connected the way Charles Wang suggested,
I'll do that.
David