Configuring Data Ship for Microsoft SQL Server
The bulk import and bulk export functionality available for data ship for Microsoft SQL Server requires the configuration of the Microsoft bcp utility. When the bulk import and bulk export functionality is used instead of the JDBC functionality, performance of operations is greatly improved. The BCP utility (bcp.exe) is a command-line tool that uses the Bulk Copy Program API. The Microsoft bcp utility performs the following tasks:
• Bulk exports data from a SQL Server table into a data file.
• Bulk exports data from a query.
• Bulk imports data from a data file into a SQL Server table.
• Generates format files.
If you choose to implement the JDBC functionality, you do not need to configure the bcp utility, or change any TDV configuration settings.
When configuring data ship or a cache for Microsoft SQL Server, you can configure the bcp utility and then set up access permissions. Both of these processes are described below.
The configuration steps are very similar for caching and data ship, for more information see
Configuring Native Caching Option for Vertica.
Note: Note: In the bcp utility, NULL values are interpreted as EMPTY and EMPTY values are interpreted as NULL. TDV passes the ‘\0’ value (which represents EMPTY) through the bcp utility to insert am EMPTY value into the cache or data ship target. Similarly, TDV passes an EMPTY string (“”) to the bcp utility to insert a NULL value into the cache or data ship target.
To configure the Microsoft bcp utility to work with TDV for data ship
1. Verify that bcp.exe has been installed in an accessible directory.
Note the full path to the bcp.exe file.
2. Open Studio.
3. Select Administration > Configuration.
4. Locate and select the Microsoft BCP utility parameter.
5. For Value, type the full directory path to the bcp.exe. For example:
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe
6. Optionally to use Windows Authentication, locate and select the DataShip BCP Threshold parameter.
The default value is 50000. After bcp utility is enabled, if the data shipping cost estimate is 50000 or above, data ship uses the bulk import and export implementation; otherwise JDBC, is used to move the data.
7. Optionally to use Windows Authentication, adjust the value of the DataShip BCP Threshold parameter to values that fit your requirements.
8. Locate and select the DataShip BCP Threshold parameter.
The default value is 50000. After bcp utility is enabled, if the data shipping cost estimate is 50000 or above, data ship uses the bulk import and export implementation; otherwise JDBC, is used to move the data.
9. Adjust the value of the DataShip BCP Threshold parameter to values that fit your requirements.
10. Click Apply.
11. Click OK.
12. Stop the TDV Server.
13. From the command line where your TDV Server runs, log in as the domain user.
14. Start the TDV Server.
15. Click OK.
16. Optionally, when running with Windows Authentication, on windows, open Services > TDV Server <version> and select Properties.
17. Select the Login On tab, set the This account fields to a domain user who has access to MSSQL instance.
18. Click OK.
To set up access permissions for SQL Server for data ship
1. Consult your SQL Server documentation for how to set the SHOWPLAN permissions for the database.
2. For every SQL Server database that will participate in the data ship optimization, grant the SHOWPLAN permissions. For example:
GRANT SHOWPLAN
TO <database> [ ,...n ]