# Moving data from SQL Server 2012 to ElasticSearch

**URL:** <https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683>\
**Category:** Elasticsearch\
**Created:** [January 5, 2017, 12:52pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683 "2017-01-05T12:52:53Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![rrmave](https://avatars.discourse-cdn.com/v4/letter/r/d2c977/32.png) [@rrmave](https://discuss.elastic.co/u/rrmave)\
**Post date:** [January 5, 2017, 12:52pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/1 "2017-01-05T12:52:53Z")

</div>

Hi,

I am trying to move data from SQL Server to ElasticSearch. I am using the JDBC importer ([http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.4.1/](http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.4.1/)) and have written a batch script to pull the data.

My initial steps were to install java and elasticsearch and confirm it was running

![](https://us1.discourse-cdn.com/elastic/original/2X/a/adf5327183f7aaf62520bb2af7a82372b2dd0a62.png)

I then extracted the JDBC importer to the elasticsearch\bin folder

 ![](https://us1.discourse-cdn.com/elastic/original/2X/3/3ece8d2917e2432a0cd78960b0175c919784c7c7.png)

I then downloaded the SQL Server drivers and placed them in the Importer Plugin Lib folder

 ![](https://us1.discourse-cdn.com/elastic/original/2X/5/5cd61b0fdb0cce82351fe22cb9453fac5e7ea2ff.png)

I then created the following script

@echo off

set DIR=%~dp0  
set LIB=%DIR%..\lib\*  
set BIN=%DIR%..\bin

REM ???  
echo '  
{  
"type" : "jdbc",  
"jdbc" : {  
"url" : "jdbc:sqlserver://localhost:;instanceName=\<instance\_name\>;databaseName=\<db\_name\>",  
"user" : "",  
"password" : "",  
"sql" : "\<select\_statement\>",  
"treat\_binary\_as\_string" : true,  
"elasticsearch" : {  
"cluster" : "elasticsearch",  
"host" : "localhost",  
"port" : 9200  
},  
"index" :"record",  
"type" :"record"  
}  
}  
' | "%JAVA\_HOME%\bin\java" -cp "%LIB%" -Dlog4j.configurationFile="%BIN%\log4j2.xml" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter"

placed it in the Importer Plugin bin folder and ran the script. But I am getting the following error

C:\elasticsearch-2.4.1\bin\elasticsearch-jdbc-2.3.4.1\bin\>mssql-simple-example.bat  
'  
'{' is not recognized as an internal or external command,  
operable program or batch file.  
'"type"' is not recognized as an internal or external command,  
operable program or batch file.  
'"jdbc"' is not recognized as an internal or external command,  
operable program or batch file.  
'"url"' is not recognized as an internal or external command,  
operable program or batch file.  
'"user"' is not recognized as an internal or external command,  
operable program or batch file.  
'"password"' is not recognized as an internal or external command,  
operable program or batch file.  
'"sql"' is not recognized as an internal or external command,  
operable program or batch file.  
'"treat\_binary\_as\_string"' is not recognized as an internal or external command,

operable program or batch file.  
'"elasticsearch"' is not recognized as an internal or external command,  
operable program or batch file.  
'"cluster"' is not recognized as an internal or external command,  
operable program or batch file.  
'"host"' is not recognized as an internal or external command,  
operable program or batch file.  
'"port"' is not recognized as an internal or external command,  
operable program or batch file.  
'}' is not recognized as an internal or external command,  
operable program or batch file.  
'"index"' is not recognized as an internal or external command,  
operable program or batch file.  
'"type"' is not recognized as an internal or external command,  
operable program or batch file.  
'}' is not recognized as an internal or external command,  
operable program or batch file.  
'}' is not recognized as an internal or external command,  
operable program or batch file.  
''' is not recognized as an internal or external command,  
operable program or batch file.

I am new to ES so advise would be greatly appreciated.

Thanks,

---

<div class="post-metadata">

**Author:** ![danielmitterdorfer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/danielmitterdorfer/32/110510_2.png) [@danielmitterdorfer](https://discuss.elastic.co/u/danielmitterdorfer)\
**Post date:** [January 5, 2017, 2:25pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/2 "2017-01-05T14:25:28Z")

</div>

Hi @rrmave,

your problem is unrelated to Elasticsearch. You use Unix features in a Windows batch script, that's why it fails.

The [docs of the importer](https://github.com/jprante/elasticsearch-jdbc) say, that you can also provide the parameters in a file and this seems to be the better (only?) option in your case:

So I guess you need to do something along these lines:

```auto
"%JAVA_HOME%\bin\java" -cp "%LIB%" -Dlog4j.configurationFile="%BIN%\log4j2.xml" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter" "statefile.json"

```

where `statefile.json` contains your parameters:

```auto
{
  "type": "jdbc",
  "jdbc": {
    "url": "jdbc:sqlserver://localhost:;instanceName=;databaseName=",
    "user": "",
    "password": "",
    "sql": "",
    "treat_binary_as_string": true,
    "elasticsearch": {
      "cluster": "elasticsearch",
      "host": "localhost",
      "port": 9200
    },
    "index": "record",
    "type": "record"
  }
}

```

Daniel

---

<div class="post-metadata">

**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\
**Post date:** [January 5, 2017, 2:30pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/3 "2017-01-05T14:30:27Z")

</div>

@danielmitterdorfer Thanks for the quick response here, even though JDBC importer is not supported by Elastic company 🙂

---

<div class="post-metadata">

**Author:** ![danielmitterdorfer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/danielmitterdorfer/32/110510_2.png) [@danielmitterdorfer](https://discuss.elastic.co/u/danielmitterdorfer)\
**Post date:** [January 5, 2017, 2:36pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/4 "2017-01-05T14:36:42Z")

</div>

Hi @jprante,

you're welcome. It was quite easy to find in the docs so I figured I could write a quick response. 🙂

Daniel

---

<div class="post-metadata">

**Author:** ![rrmave](https://avatars.discourse-cdn.com/v4/letter/r/d2c977/32.png) [@rrmave](https://discuss.elastic.co/u/rrmave)\
**Post date:** [January 5, 2017, 3:14pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/5 "2017-01-05T15:14:49Z")

</div>

Hi Daniel,

Thanks a lot for the update. I knew I was doing something silly.

---

<div class="post-metadata">

**Author:** ![rrmave](https://avatars.discourse-cdn.com/v4/letter/r/d2c977/32.png) [@rrmave](https://discuss.elastic.co/u/rrmave)\
**Post date:** [January 5, 2017, 3:15pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/6 "2017-01-05T15:15:48Z")

</div>

I was actually following the steps in [http://hintdesk.com/how-to-connect-elasticsearch-to-ms-sql-server/](http://hintdesk.com/how-to-connect-elasticsearch-to-ms-sql-server/)

---

<div class="post-metadata">

**Author:** ![rrmave](https://avatars.discourse-cdn.com/v4/letter/r/d2c977/32.png) [@rrmave](https://discuss.elastic.co/u/rrmave)\
**Post date:** [January 5, 2017, 3:20pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/7 "2017-01-05T15:20:27Z")

</div>

Maybe connecting to the SQL Server db via ES and searching is not ideal from a performance perspective. Would you recommend copying the data to ES rather than connecting to the db?

Thanks

---

<div class="post-metadata">

**Author:** ![danielmitterdorfer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/danielmitterdorfer/32/110510_2.png) [@danielmitterdorfer](https://discuss.elastic.co/u/danielmitterdorfer)\
**Post date:** [January 5, 2017, 4:06pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/8 "2017-01-05T16:06:33Z")

</div>

Hi @rrmave,

well, there is no "right" answer to your question, that depends on your application and how you intend to use Elasticsearch. For starters you can read [https://www.elastic.co/blog/found-keeping-elasticsearch-in-sync](https://www.elastic.co/blog/found-keeping-elasticsearch-in-sync).

Daniel

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 2, 2017, 4:06pm UTC](https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/9 "2017-02-02T16:06:40Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.