2.1.3 Data Portability Methodology

Exporting data from SQL Server PDW is a four-step process, as follows:

1. Configure an external Windows system to receive parallel data exports. Or, configure an external SQL Server SMP database to receive parallel data exports.

2. Update host names in DNS.

3. Run the CREATE REMOTE TABLE statement.

4. Use BCP [MS-DPBCP] to export the data from the SQL Server SMP database to a file that can be imported into another vendor's database.

Step 1a: Configure an external Windows system to receive parallel data exports

The parallel data export feature exports data from the SQL Server PDW appliance to an external SQL Server SMP database that is running on a Windows system. Use of this feature requires a Windows system that will receive the exported data to be provided. To do this, follow these steps:

1. Purchase a Windows system and rack it close enough to the appliance that it can be connected to the appliance Infiniband network.

2. Purchase Infiniband cables and an Infiniband network adapter from your appliance hardware vendor. We recommend purchasing a network adapter with two ports for fault tolerance when receiving the exported data. A two-port network adapter is recommended but is not a requirement.

3. Install the Infiniband network adapter on the Windows system.

4. Connect the Infiniband network adapter to the main Infiniband switch in the Control rack by using Infiniband cables.

5. Install and configure the appropriate Windows driver for the Infiniband network adapter.

   Infiniband drivers for Windows are developed by the OpenFabrics Alliance, an industry consortium of Infiniband vendors. The correct driver might have been distributed with the Infiniband adapter. If not, the driver can be downloaded from www.openfabrics.org.

6. Configure the IP address for each port on the adapter. SMP systems are required to use static IP addresses from a range of addresses reserved for this purpose. Configure the first port according to the following parameters:

   • IP network address: 172.16.132.x

   • IP subnet mask: 255.255.128.0

   • IP host range: 1-254

   For Infiniband network adapters with two ports, configure the second port according to the following parameters:

   • IP network address: 172.16.132.x

   • IP subnet mask: 255.255.128.0

   • IP host range: 1-254

7. If a two-port adapter is used or multiple external Windows systems are connected to an appliance, assign each system a different host number within each IP subnet.

Step 1b: Configure an external SQL Server SMP database to receive parallel data exports

The parallel data export feature exports data from the SQL Server PDW appliance to an external SQL Server SMP database that is running on a Windows system. After the external Windows system is configured to receive parallel data exports, the next step is to install and configure Microsoft SQL Server Enterprise Edition on the Windows system.

To configure an external SQL Server SMP database to receive parallel data exports, follow these steps:

1. Install Microsoft SQL Server 2008 Enterprise Edition or Microsoft SQL Server 2008 R2 Enterprise Edition on the Windows system. SQL Server 2008 R2 is an SMP system.

2. Configure SQL Server to accept TCP/IP connections on a fixed TCP port. This configuration is disabled by default and is required to be enabled to allow SQL Server PDW to connect to the SQL Server SMP database.

3. Disable Windows Firewall. Or, configure the TCP port of the SQL Server SMP database so that it will work with Windows Firewall enabled.

4. Configure SQL Server to allow SQL Server Authentication mode. The parallel data export always uses SQL Server accounts for authentication.

5. Choose a SQL Server account on the SQL Server SMP database (or create a new account) to be used for authentication. Grant this account privileges to create, drop, and insert data into tables in the destination database for the parallel data export operation.

Step 2: Update host names in DNS

The CREATE REMOTE TABLE statement, used for parallel data exports, specifies the destination server by using either the IP address or the IP name of the Windows system that is running the SQL Server SMP database. To use the IP name, add entries for successful name resolution to the DNS server on the SQL Server PDW Management node. To do this, follow these steps:

1. Log on to the SQL Server PDW Management node.

2. Open the DNS Manager. This is located under Administrative Tools on the Start menu.

3. Use the DNS Manager to add the IP name.

Step 3: Run the CREATE REMOTE TABLE statement

The CREATE REMOTE TABLE statement selects data from a SQL Server PDW database and copies that data to a new table in a SQL Server 2008 or SQL Server 2008 R2 SMP database. The SMP database does not reside on a server in the SQL Server PDW appliance. This provides the flexibility to handle scenarios that require SQL Server functionality.

The following is an example of the syntax for the CREATE REMOTE TABLE statement.

 CREATE REMOTE TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name 
     AT ('<connection_string>')
     [ WITH ( BATCH_SIZE = batch_size ) ]
     AS SELECT select_criteria
 }
 [;]
 <connection_string> ::= 
     Data Source = [tcp:] { IP_address | hostname } [ \ instance_name ]     [, port ]; User ID = user_name ;Password = password;

The following table describes the arguments that are used in the CREATE REMOTE TABLE statement.

The following example creates a remote table named MyOrdersTable on the OrderReporting database and the Orders schema. The OrderReporting database is on a server named SQLA. Port 8080 is used for the connection. The connection to the server is made with the credentials of the user whose user name is David and whose password is e4n8@3.

 CREATE REMOTE TABLE OrderReporting.Orders.MyOrdersTable
 AT ( 'Data Source = SQLA, 8080; User ID = David; Password = e4n8@3;' )
 AS SELECT <select_criteria>;

Step 4: Use BCP to export the data from the SQL Server SMP database to a file that can be imported into another vendor's database

To do this, see [MS-DPBCP].