@tom: once your code changes are done, pls have them code reviewed by Asif

The MS JDBC has already been checked-into our code base. svn#49348.

Sample code for connecting to SQL Server using MS JDBC: https://msdn.microsoft.com/en-us/library/mt720656(v=sql.110).aspx

Driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver

List of connection properties: https://msdn.microsoft.com/en-us/library/ms378988(v=sql.110).aspx

Hi Sachin, See attached..   Is this the layout that you want? Thanks, Tom

[SP] Yes, looks fine

svn #50715 - First pass 

svn #50737 - Additional logic changes. 

svn #50754 - Changed Microsoft driver selection

svn #50755 - Changed driver selection.

To accomodate the lack of variant support for the Microsoft JDBC, I had to change the following queries in connection properties in order to establish a connection. Notice the casting for the Microsoft driver.

Hi Asif, When you get a chance, would you have a look at my code? svn #50715  Thanks, Tom

@tom: pls also test out import batch mode to see if there are differences we need to address. Here is a note on bcp for SQL Server. Don't make any changes to use bcp. For now, test the code to see if batch & threshold import works or not. Even if batch mode does work, we need QA to do a performance test of v17 vs v18 to ensure there is no slowdown using the ms jdbc driver

https://msdn.microsoft.com/en-us/library/mt221490(v=sql.110).aspx

Hi Sachin, We will need to fix the variant problem in the extracts in order to test the import batch process. Neither excel nor csv import gets past the first couple screens before it hits the variant problem and closes the connection. Thanks, Tom

svn #50715

UI Feedback:

I was wondering whether we should move "Driver" option into Authentication section like how we did for "Apache Hive". In "Apache Hive" we added options like Connection Type and Distribution under Authentication section; This would enable us to follow the same UI and coding pattern.

Code review feedback:

-- CoreMSSQLServer2kDriver

The driver instance and class should initialized by overriding the standard api:

Driver getJDBCDriverInstance(ConnectionProperties connSettings)

This would avoid the need of special handling in

• ConnectionProperties > getCoreServerDriver() api
• MSSQLServerRegistrationUI > constructProperties()

Eg: refer CoreHiveDriver.
 

-- MSSQLServerAuthenticationPanel

We should not select Save Password in refreshJTDSCombo() and refreshMicrosoftCombo() api: _chkSavePassword.setSelected(true);

Save password is enabled based on the datastudio.properties config. Refer ConnectionUiUtil.setupSavePassword()

-- You may want to update the links to online documentation for

• MS SQL Server JDBC Driver
• MS SQL Server Registration

Hi Asif,  Thank you for the advice and review. I made the code changes. As far as the location of the driver drop down, this was the location that Sachin wanted it. Tom

Hi QA Team, Please do overall testing of ADS against an MSSQL database using the Microsoft driver. This includes but not limited to QB, ERM, VA, Tools, DBA tools, QA and Explain, Table Data Editor,  Creates, Alters and Scripting. Also, regression test the Jtds driver with the same paradigm. 

Additionally, please do some performance testing and show results comparing the Jtds driver to the Microsoft driver for batch import. 

Do not worry about testing with SSO. This will be done in a subsequent issue. Just test using the SQL Server Authentication. See Server Properties. 

Please also test and report how long a connection in QA takes to time out?

Thanks in advance, Tom

verified in ADS 18-rc-5