JDBC连接SQL Server命名实例访问错误实例 查询结果异常问题

问题现象 #

域内部署多台SQL Server实例，在SSMS中连接MSSQL\SQL2019（SQL Server 2019实例），执行查询：

SELECT serverproperty('InstanceDefaultDataPath') as dataFolder;

可正常返回预期路径：
D:\Program Files\Microsoft SQL Server\MSSQL15.*SQL2019*\MSSQL\DATA\

但通过JDBC执行同一查询时，返回的是其他SQL Server实例的对应路径，相关代码如下：

public static String getSqlServerDataFolder(Connection connection) {
    String query = "SELECT serverproperty('InstanceDefaultDataPath') as dataFolder;";
    List<String> results = getSqlResults(connection, query, "dataFolder");
    return results.get(0);
}

public static List<String> getSqlResults(Connection connection, String query, String key) {
    List<String> results = new ArrayList<String>();
    try {
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(60);
        ResultSet result = statement.executeQuery(query);
        while (result.next()) {
            results.add(result.getString(key));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return results;
}

public static Connection connectSql(String dataSource) {
    String connectionString = null;
    if (networkSqlServerNames.contains(dataSource)) {
        connectionString = "jdbc:sqlserver://" + dataSource + ":1433;integratedSecurity=true";
    }
    else {
        connectionString = "jdbc:sqlserver://" + dataSource + ";integratedSecurity=true";
    }
    Connection connection = null;
    try {
        connection = DriverManager.getConnection(connectionString);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return connection;
}

调用示例：

Connection connection = SqlExpressManager.connectSql("MSSQL\\SQL2019");
System.out.println(SqlExpressManager.getServerName(connection));
System.out.println(SqlExpressBackupManager.getSqlServerDataFolder(connection));
System.out.println(SqlExpressBackupManager.getSqlServerBackupFolder(connection));

运行输出结果：

MSSQL\SQL2019
d:\Program Files\Microsoft SQL Server\MSSQL13.*SQL2016*\MSSQL\DATA
d:\Program Files\Microsoft SQL Server\MSSQL13.*SQL2016*\MSSQL\Backup

除此以外还存在其他异常：通过JDBC连接A实例执行创建数据库语句，最终数据库会被创建在B实例上。

问题原因 #

JDBC连接字符串中如果指定了端口号，实例名会被忽略，导致程序实际访问的是端口对应的默认实例而非目标命名实例。原环境中有一台SQL Server占用了默认TCP/IP端口1433，其余实例使用动态TCP/IP端口，因此无论传入的实例名是什么，只要写死1433端口，都会连接到占用1433端口的SQL2016实例。

解决方案 #

1. 给每台SQL Server实例分配独立的固定TCP/IP端口
2. 通过静态Map<String, String>存储实例名与对应端口的映射关系
3. 构造连接字符串时读取目标实例对应的端口，替换硬编码的1433端口

修改后的连接代码如下：

public static Connection connectSql(String dataSource) {
    String connectionString = null;
    if (networkSqlServerNames.contains(dataSource)) {
        connectionString = "jdbc:sqlserver://" + dataSource + ":" + tcpipPortMap().get(dataSource) + ";integratedSecurity=true";
    }
    else {
        connectionString = "jdbc:sqlserver://" + dataSource + ";integratedSecurity=true";
    }
    Connection connection = null;
    try {
        connection = DriverManager.getConnection(connectionString);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return connection;
}

内容的提问来源于stack exchange，提问作者pburgr