New to SQL Server PDW? First, tinker with client tools

SQL Server Parallel Data Warehouse (PDW) uses multiple SQL Server database servers to store and process data. The PDW environment is built on a massively

    Requires Free Membership to View

    Login

parallel processing architecture that’s based on the DATAllegro warehouse appliance and SQL Server 2008 Enterprise edition. 

Because PDW and SQL Server are based on different architectures, some of the tools you use to access PDW are also different. To understand how these tools fit in with the overall product, it helps to have a rudimentary understanding of the SQL Server PDW environment.

Each PDW installation contains one control rack and one or more data racks. The control rack includes four nodes: management, control, landing zone and backup. Together these nodes provide for the necessary management and configuration of the PDW appliance.

For more on SQL Server PDW

SQL Server PDW reaches out to Hadoop for ‘big data’ processing

Learn the ins and outs of SQL Server PDW

The data rack includes multiple compute nodes that each run an instance of SQL Server and connect to dedicated storage. Although the data in a PDW database is distributed among the compute nodes, the database itself is presented as a single logical object into which you can load and query data.

All client tools communicate with SQL Server PDW via the nodes in the control rack. The tools use Data Direct’s SequeLink client drivers (open database connectivity, or ODBC; object linking and embedding database, or OLE DB; and ADO.NET) to access PDW. Figure 1 shows how the client tools connect to the control rack nodes.

Figure 1. Client tools access the PDW appliance nodes via the Data Direct SequeLink client drivers.

Notice that PDW supports numerous client tools that work with the appliance. Some of the tools you’re probably familiar with; others, perhaps not. Let’s take a closer look at each one.

Monitoring SQL Server PDW
PDW ships with a comprehensive Web-based monitoring tool called the Admin Console. The Admin Console interface provides the following tabs:

  • Sessions: Active user sessions.
  • Queries: Currently running queries, recently completed queries, and any related errors, as well as execution plans and node execution information.
  • Loads: Load plans, the current state of loads and any related errors.
  • Backups/Restores: Backup and restore operations.
  • Appliance state: Hardware and software state information for each node.
  • Alerts: Alerts and warnings for system nodes.
  • Dashboard: Real-time status details, including hardware utilization metrics.

The Admin Console is an Internet Information Services application that runs on the control node of the control rack. You access the tool through Windows Internet Explorer.

Managing SQL Server PDW
Currently, SQL Server Management Studio (SSMS) is not compatible with SQL Server PDW. However, to let you query data interactively, PDW ships with the Nexus Query Chameleon GUI tool -- a carry-over from the original DATAllegro product. The tool is similar to SSMS in that it lets you navigate through database objects and run Transact-SQL queries.

DDL statements in SQL Server PDW

Many of the data definition language (DDL) statements are slightly different for PDW. The changes were made to accommodate the nature of the PDW-distributed environment.

PDW also supports dwsql, a command-line utility you can use to issue Transact-SQL statements against your database. The utility, another holdover from the DATAllegro days, is similar to the sqlcmd utility in other editions of SQL Server.

Another command-line utility is dwloader. The utility lets you load large amounts of data from the loading zone node, where data is often staged, to the distributed environment of the compute nodes.

PDW also includes the PDW Configuration Manager. This tool lets you make appliance-level configuration changes and set options such as IP addresses or time zone. You can also use the tool to start and stop services.

Integrating SQL Server PDW in BI systems
SQL Server 2008 R2 business intelligence (BI) tools are fully integrated with PDW. For instance, PDW ships with adapters that you can install into SQL Server Integration Services to connect to and load data into a PDW database. In addition, SQL Server Reporting Services, SQL Server Analysis Services and Excel PowerPivot let you specify a PDW database as a data source so you can perform analyses and generate reports based on PDW data.

Moving forward with SQL Server PDW
With Parallel Data Warehouse, you can scale out your databases in ways you cannot with other editions of SQL Server. However, not all SQL Server tools, particularly SSMS, have caught up with this new architecture. No doubt in subsequent releases of PDW, we’ll see the rest of the SQL Server tools brought on board. Until then, PDW contains plenty of client applications to keep everyone happy -- and productive.

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles and training materials related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Find more information at http://rhsheldon.com.

 

This was first published in November 2011

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top