I am trying to connect to SQL Server instance hosted on GCP cloud from Apache beam Java code. The beam pipeline reads from source table to destination table. The source table is hosted in SQL Server while the destination table is hosted in MySQL server. The pipeline will be executed by Google Cloud Dataflow.
It is similar to the question asked [here][1], but I need for SQL Server based on Java SDK.
To connect to a cloud instance, I stumbled upon [Cloud SQL Socket Library][2] that allows a user with the appropriate permissions to connect to a Cloud SQL database. However, it seems that it's only for MySQL and Postgres.
I wrote the following code for connecting to MySQL instance, and it works.
```
private static JdbcIO.DataSourceConfiguration getMySQLDataSourceConfiguration() throws PropertyVetoException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
String password = System.getenv("MYSQL_USER_PASSWORD");
String userName = System.getenv("MYSQL_USER_NAME");
String databaseName = System.getenv("MYSQL_DATABASE_NAME");
String instanceConnectionName = System.getenv("MYSQL_INSTANCE_CONNECTION_NAME");
String dbUrl = String.format("jdbc:mysql://google/%s?cloudSqlInstance=%s" +
"&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false" +
"&user=%s&password=%s&rewriteBatchedStatements=true", databaseName, instanceConnectionName, userName, password);
dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
dataSource.setJdbcUrl(dbUrl);
dataSource.setMaxPoolSize(10);
dataSource.setInitialPoolSize(6);
return JdbcIO.DataSourceConfiguration.create(dataSource);
}
```
For MySQL, I know the structure of the connection string, however, I am not being able to find similar for SQL Server.
How do I connect to an SQL Server type instance from Apache Beam code?