%META:TOPICPARENT{name="ConfigureSQLMultipleInstances"}%
==Single Driver Connectivity to Multiple Instances  ==
===Single-Tier Drivers on Unix Client to SQL Server 2000==


Use the following instructions to connect to SQL Server DBMSs that host multiple SQL Server instances:

1. Use the following steps to identify your instance names --

* [[HowDoIFindMySQLServerInstanceName|Microsoft SQL Server instance name]]

Microsoft SQL Server instance names should be immediately visible. Example: SQLSERV1, SQLSERV2.

2. Login to the machine that hosts your <nop>OpenLink Single-Tier driver.

3. cd into the root of the <nop>OpenLink installation.

4. Use the bash or sh command to open a bash or bourne shell.

5. Execute this command: 

{{{
. ./openlink.sh
}}}

6. cd into the bin sub-directory of <nop>OpenLink.

7. vi the openlink.ini file.

8. Locate the {{{[Server Types]}}} section.

9. Create a meaningful label for each of your SQL Server instances. For example:

{{{
[Server Types]
DB2             =
Informix 5      =
Informix 6      =
.
.
.
Server 1        =
Server 2        =
}}}

10. Locate this stanza in the file --

{{{
[Environment SQLServer 2000]
FREETDSCONF        = /usr/openlink/bin/freetds.conf
DSQUERY            = SQLSERVER  ; Load this section from freetds.conf
TDSHOST            = host.domain        ; Point this to your SQLServer machine
TDSPORT            = 1433
TDSVER             = 8.0
SQLSERVER_CATALOG  = Y
CURSOR_SENSITIVITY = LOW        ; Set to HIGH after loading oplrvc.sql
}}}

11. Copy and paste this stanza once for each new SQL Server instance that you want to add.

12. Make the following modifications to the new stanzas --

a) Modify {{{[Environment SQLServer 2000]}}} so that "SQL Server 2000" is replaced with one of the labels you created above;e.g., {{{[Environment Server 1]}}}.

b) Pass the desired instance name to DSQUERY. For example {{{DSQUERY=SQLSERV1}}}.

c) Pass the hostname or IP address of the SQL Server instance to TDSHOST.

d) Pass the tcp port associated with the instance to TDSPORT.

e) Set TDSVER to 8.0, if any other value appears.

f) Ensure you have an end result similar to this --

{{{
[Environment Server 1]
FREETDSCONF        = /usr/openlink/bin/freetds.conf
DSQUERY            = SQLSERV1  ; Load this section from freetds.conf
TDSHOST            = 192.158.12.121        
TDSPORT            = 1433
TDSVER             = 8.0
SQLSERVER_CATALOG  = Y
CURSOR_SENSITIVITY = LOW        ; Set to HIGH after loading oplrvc.sql

[Environment Server 2]
FREETDSCONF        = /usr/openlink/bin/freetds.conf
DSQUERY            = SQLSERV2  
TDSHOST            = 192.158.12.121        
TDSPORT            = 1434
TDSVER             = 8.0
SQLSERVER_CATALOG  = Y
CURSOR_SENSITIVITY = LOW        ; Set to HIGH after loading oplrvc.sql
}}}

13. Save your changes and exit the file.

14. vi freetds.conf. 

15. Make one new entry per each SQL Server instance using these conventions --

{{{
[<Instance Name>]
host                    = <ntmachine.domain.com>
port                    = <port>
tds version             = 8.0
try server logins       = yes
}}}

For example --

{{{
[SQLSERVER1]
host                    = 192.158.12.121    
port                    = 1433
tds version             = 8.0
try server logins       = yes


[SQLSERV2]
host                    = 192.158.12.121    
port                    = 1434
tds version             = 8.0
try server logins       = yes
}}}

16. Save your changes and exit the file.

17. vi odbc.ini.

18. Proceed to create your DSNs as per usual. However, pass the appropriate label from the {{{[Server Types]}}} section of openlink.ini. For example --

{{{
[Development DSN]
Driver          = /usr/openlink/lib/sql_mt_lt.so
ServerType      = Server 1
Username        = sa
Password        = sa
Database        = Northwind
Options         = -H 192.158.12.121 -P 1433 -V 8.0
FetchBufferSize = 99
ReadOnly        = no
DeferLongFetch  = no
JetFix          = no
Description     = Sample SQLServer 2000 Lite Connection

[Production DSN]
Driver          = /usr/openlink/lib/sql_mt_lt.so
ServerType      = Server 2
Username        = sa
Password        = sa
Database        = pubs
Options         = -H 192.158.12.121 -P 1434 -V 8.0
FetchBufferSize = 99
ReadOnly        = no
DeferLongFetch  = no
JetFix          = no
Description     = Sample SQLServer 2000 Lite Connection
}}}

19 Save your changes and exit the file.

20. Test.