CODEWITHSUNDEEP
solrsql-server-2016ojdbcdataimporthandlersolr8
RyanWorks
RyanWorks

Reputation: 39

How do I get Solr 8.11.1 to connect to SQL Server 2016

I'm currently running Solr 8.11.1 on RHEL 8.8 (Ootpa). I have a collection that's successfully pulling data from an Oracle database into Solr. It does so by using Solr's DataImportHandler and ojdbc6.jar. I've tried creating a new collection to connect to a SQL Server, but haven't had any luck.

I followed a similar approach to how I got my collection that's currently connected to an Oracle Database, which was to do the following:

Steps Attempted to Connect Solr 8.11.1 to connect to SQL Server 2016:

  1. I added the DataImportHandler in the solrconfig.xml to include the necessary jar files (odbc6.jar, solr-dataimporthandler-8.11.1.jar, and solr-dataimporthandler-extras-8.11.1.jar)
<lib dir="/search/solr-8.11.1/server/lib" regex="ojdbc6\.jar"/>
    <lib dir="/search/solr-8.11.1/dist" regex="solr-dataimporthandler-.*\.jar"/>
    <requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler">
        <lst name="defaults">
            <str name="config">data-config.xml</str>
        </lst>
    </requestHandler>

  1. Made sure that the 3 jar files are in the correct locations as specified in the above step.

  2. Create the data-config.xml file. This is where I'm not sure. I've seen various "url" formats for SQL Server and tried them all, but still get the same outcome each time.

<dataConfig>
        <dataSource type="JdbcDataSource" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://myServer.com\myInstanceName;databaseName=myDB" user="myUser" password="myPassword">
        </dataSource>
        <document name="assets">
                <entity name="item" query="SELECT PHOTO_ID AS id FROM myDB.PHOTOS">
                        <field column="ID" name="id"></field>
                </entity>
        </document>
</dataConfig>

Outcome: After making all of the above changes, I'll restart Solr and then go to the Solr Admin screen to view the collection I made these changes to. I then attempt to run a dataimport on that collection, but it doesn't import anything. It says "Indexing completed. Added/Updated: 0 documents. Deleted 0 documents."

Result

Any ideas on what I need to correct? Or any tutorials on how to do this? I haven't found much online and any help is greatly appreciated!

Upvotes: 0

Views: 226

Answers (0)

Related Questions