RE: How to connect test to production system

Answers inline.

changliw@xxxxxxxxxxxxxxxxxxxx (Charles Wang[MSFT]) wrote in
news:M55syrX3HHA.4100@xxxxxxxxxxxxxxxxxxxxxx:

Hi David,
Since your post has a good many of information, I would like to first
confirm with you if I understand your environment:
1. Your main computer connected to your home office network via
VPN; 2. Your local SQL Server computer connected to your main
computer via Ethernet (Local Area Network); It did not directly
connect to your home office network;
3. Your local SQL Server could connect to a remote SQL Server in
your home office network with a specific destination TCP port opened.

Also, I have two questions and appreciate your confirmation since I
have not totally understood your scenario:
1. What is the relationship between your main computer and your
local SQL Server computer?

They are both XP pro, connected with an Ethernet crossover cable, in the
same workgroup.

2. Which computer is your local copy of the main database located
on, the main computer or the local SQL Server computer?

The local copy of the main database is on the SQL server computer. My
"main" computer is only a client, not running a SQL server.



For your two questions,
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?

Did you mean databases in different server instances here? If the
databases at the home office were all in the remote SQL Server with
specific TCP port opened, your local SQL Server should be able to
connect to the databases in the remote SQL Server instance.

If you meant that you would like to access the databases in different
SQL Server instances (may be located on different servers) at your
home office via linked server via your local SQL Server, you may try
adding your main server as the gateway of your local SQL Server
computer to see if it helps. Anyway, you need to ensure that the
physical connections from your local SQL Server computer to your
remote servers could be established.

If the physical network connections could be established, you can add
the linked servers with SQL authentication (if Windows Authentication
could not be established).

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?

You may try adding some server aliases on your test systems. These
server aliases have the same names as the server names in production.
For example, your test SQL Server instance name is "Server1\SQL2K",
while your production SQL Server instance name is
"ProductServer\SQLServer". You can first run cliconfg.exe (SQL 2000
client) from command line or SQL Server Configuration Manager (SQL
2005 client) on your client computer to configure a server alias
"ProductServer\SQLServer" which represents your test SQL Server
instance "Server1\SQL2K" with a specific protocol (TCP/IP or Named
Pipes). After that, you can run:
Sp_addlinkedserver 'ProductServer\SQLSERVER'
Go
Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
Go
Then you can run the following statement to query the northwind
database in your local test SQL Server instance "Server1\SQL2K":
SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees

For configure server alias on a client computer, you may also refer
to: How to connect to a named instance of SQL Server 2005 or SQL
Server 2000 by using the client tools in the earlier version of SQL
Server http://support.microsoft.com/kb/265808/en-us

Hope this helps. If you have any other questions or concerns, please
feel free to let me know.

Thanks for the info. I will look into all of it.

I didn't know that I could make an alias which covers both the server
name AND the instance name. In fact, when I tried it, I got "SQL Server
does not exist or access denied." I'll make sure I have the parameters
right, and try again.

David


Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no
rights. ======================================================








.

Relevant Pages

  • SQLDataConnection
    ... right click on local SQL Server instance ... local ASPNET user has permissions to the database. ...
    (microsoft.public.cert.exam.mcsd)
  • Re: Post SP3 Enterprise Manage Problem
    ... When you say "database", do you really mean the SQL Server instance? ... you cannot connect to the local SQL Server instance running on ...
    (microsoft.public.sqlserver.security)
  • Re: Interesting Questions from 70-228(MS SQL Server 2000)
    ... > stored on the SQL Server computer. ... > Some of these partners have computers that do not use ... You need to configure the IIS and SQL Server ...
    (microsoft.public.cert.exam.mcse)
  • RE: How to connect test to production system
    ... main computer or the local SQL Server computer? ... should I be able to link the database on my local SQL server to the ... For example, your test SQL Server instance name is "Server1\SQL2K", while ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server 2000 SP4 client connection problem
    ... Have you checked for other errors in both the Windows event log and the SQL Server log particularly on the SQL Server computer? ... In Management Studio on the SQL Server computer, connect Object Explorer, right-click the server name, and then click Properties. ... This will help you determine if SQL Server is getting the login packet but rejecting it. ... Using Query Analyzer on ClientB, try connecting to: tcp:ipaddress,1433 ...
    (microsoft.public.sqlserver.connect)
Loading