DBA Tips Archive for SQL Server

  


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

If you are a system administrator or DBA, you know the importance of scripting. For many administrators, the choice is Perl. Given the fact that Perl is open source, available on almost all O/S platforms and extremely modular, many popular modules have been developed to extend the language. DBAs for example, have enjoyed the ease of scripting database functions using the "Database independent interface for Perl" or better known as DBI.

The DBI is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.

 

It is important to remember that the DBI is just an interface. The DBI is a layer of "glue" between an application (written in Perl) and one or more database driver modules (i.e. Oracle, MySQL, Sybase). It is the driver modules which do most of the real work. The DBI provides a standard interface and framework for the drivers to operate within.

Using the Perl DBI interface, you must first download and install the mandatory DBI Perl module. Downloading and installing all Perl modules mentioned in this article will be discussed in later sections.

At the time of this writing, the most current release of DBI available from CPAN.org is version DBI-1.614.

After installing DBI, you then would download and install any of the available DBD Perl modules (database dependent modules) that correspond to the database you would like to connect to and work with. Many DBD modules exist today for popular databases like Oracle, MySQL, Sybase as well as access other alternative data sources like CSV, Excel, and even Google! Like the Perl DBI module, you can find a comprehensive list of available DBD Perl modules at CPAN.org.

Looking at the list of available DBD modules on CPAN, you may have noticed one missing from the list - Microsoft SQL Server 2008!

 

Although it is possible to access SQL Server 2008 using the DBD::ODBC module, it would require a separate driver manager and suitable drivers. Using this method, however, is not the focus of this article.

This article will focus on how to access Microsoft SQL Server 2008 from a Linux server using the Perl DBD::Sybase driver module (this is the Sybase driver for the Perl DBI) combined with FreeTDS. FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server 2008 and Sybase databases.

Having this type of access presents more options to DBAs scripting in Perl as well as web developers using CGI scripts since they can now access MS SQL Server 2008 in the same manner they would access any other DBI support RDBMS server.

All testing done for this article was performed using the following software components:

This article applies to scripts running on the Linux/UNIX operating environment. If you have Perl scripts that are running on Microsoft Windows, you can simply use the DBD::ADO driver module for accessing SQL Server.

A Brief History of the Tabular Data Stream (TDS) Protocol

Designed and developed by Sybase Inc. in 1984, Tabular Data Stream (TDS) is a protocol used to support network communications for their SQL Server relational database product. Like many companies during this era, Sybase faced a problem with the fact that no commonly accepted application-level protocol existed to transfer data between the client and the database server. In hopes to encourage use of their product offerings, Sybase developed a pair of libraries named netlib and db-lib.

Note that a protocol is not an API. The two, however, are related. A protocol is when two computers have the ability to speak the same language. A server, for example, will be able to recognize and respond to a client request that has the right combination of bits in the correct order. This is all the responsibility of a software library. Today, many software libraries exist and they all have their own API. With regards to our current discussion, they are responsible for moving SQL data through a TDS pipe. Although ODBC, db-lib, ct-lib and JDBC have very dissimilar APIs, they are all considered the same to the server because on the wire, they speak TDS. For example, ODBC, db-lib, ct-lib and JDBC differ dramatically in their programming style and convention but they all use netlib to communicate to the server. The language they all use is TDS.

Netlib was responsible for the transport of data between two computers and can run on IPX/SPX, DECnet, NetBEUI and the ubiquitous TCP/IP. Db-lib, on the other hand, provided the API to the client program and did all of its communication with the server via netlib. Whatever db-lib sent to the server took the form of a stream of bytes (actually, a structured stream of bytes meant for tables) and was called a Tabular Data Stream.

Thanks to a technology sharing agreement with Sybase in 1990, Microsoft starting marketing its own SQL Server. All Microsoft SQL Server products then made use of the same network communications protocol used by Sybase - TDS.

 

Microsoft kept the db-lib API and also added ODBC plus several others. At around this same time, Sybase introduced a more robust successor to db-lib called ct-lib and renamed the pair "Open Client".

Within the technology sharing agreement with Sybase, Microsoft's SQL server products were being developed with the same network communications protocol that Sybase used, namely TDS. Through the release of SQL Server 7.0, Microsoft officially supported Sybase client software with a caveat that such support would come to an end. Until SQL Server 2000, is was then possible to use the Perl DBD::Sybase module (compiled with Sybase's freely downloadable client libraries) to access any Microsoft SQL server!

With the introduction of TDS 8.0 and now legacy support for TDS 7.0 in SQL Server 2000, compatibility with the Sybase client, using TDS 4.2, is broken. There is a work around however to accessing SQL Server 2000/2008 and this article describes how!

The crux of this article is how to access Microsoft SQL Server 2008 using the DBD::Sybase Perl module built with the TDS libraries (FreeTDS) from freetds.org which do support TDS version 7.0

Download Software Components

Let's now start by downloading the required software components. As I mentioned in the introduction to this article, I am utilizing the CentOS 5.4 Enterprise Linux operating system (a free and stable RHEL clone). During the Linux installation, I made sure to install Perl and the GNU C compiler. Both Perl and the GNU C compiler are available in RPM format from the Red Hat CDs. This article does not discuss installing the Linux O/S, Perl or the GNU C compiler.

The following is a list of software components / modules that should be downloaded. Instructions for unpacking, installing, and configuring these modules are discussed later on in this article.

Install DBI

The first component to install (if it is not already installed) is the Perl DBI module.

Use the following commands to unpack, build and install the Perl DBI module. Please keep in mind that the all of the following commands can be performed as any UNIX user, however, the make install must be performed as root.


[oracle@racnode1 ~]$ cd /tmp [oracle@racnode1 tmp]$ gunzip DBI-1.614.tar.gz [oracle@racnode1 tmp]$ tar xvf DBI-1.614.tar [oracle@racnode1 tmp]$ cd DBI-1.614 [oracle@racnode1 DBI-1.614]$ perl Makefile.PL *** Your LANG environment variable is set to 'en_US.UTF-8' *** This may cause problems for some perl installations. *** If you get test failures, please try again with LANG unset. *** If that then works, please email dbi-dev@perl.org with details *** including the output of 'perl -V' ... <snip>... Writing Makefile for DBI [oracle@racnode1 DBI-1.614]$ make /usr/bin/perl "-MExtUtils::Command" -e mkpath blib/lib/DBI rm -f blib/lib/DBI/Changes.pm cp Changes blib/lib/DBI/Changes.pm cp Driver_xst.h blib/arch/auto/DBI/Driver_xst.h cp lib/DBD/Proxy.pm blib/lib/DBD/Proxy.pm cp lib/DBI/Gofer/Response.pm blib/lib/DBI/Gofer/Response.pm cp lib/DBI/Gofer/Transport/Base.pm blib/lib/DBI/Gofer/Transport/Base.pm cp lib/DBI/Util/_accessor.pm blib/lib/DBI/Util/_accessor.pm cp lib/DBD/DBM.pm blib/lib/DBD/DBM.pm ... <snip>... [oracle@racnode1 DBI-1.614]$ su Password: xxxxxx [root@racnode1 DBI-1.614]# make install Manifying blib/man1/dbiprof.1 Manifying blib/man1/dbiproxy.1 Manifying blib/man1/dbilogstrip.1 Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/dbixs_rev.h ... <snip>... Installing /usr/bin/dbilogstrip Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/.packlist Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod [root@racnode1 DBI-1.614]# exit exit

Install FreeTDS

The next step is to unpack, configure, build and install FreeTDS.

Note that when running configure, we need to use the --with-tdsver=7.0 in order to create a Makefile suitable for compiling FreeTDS that specifies TDS 7.0 as the default protocol. Also note that I choose to install FreeTDS to /usr/local/freetds using the --prefix=(PATH) option.


[oracle@racnode1 ~]$ cd /tmp [oracle@racnode1 tmp]$ tar zxvf freetds-stable.tgz [oracle@racnode1 tmp]$ cd freetds-0.82 [oracle@racnode1 freetds-0.82]$ ./configure --with-tdsver=7.0 --prefix=/usr/local/freetds checking for a BSD-compatible install... /usr/bin/install -c checking whether build environment is sane... yes checking for gawk... gawk checking whether make sets $(MAKE)... yes checking build system type... x86_64-unknown-linux-gnu checking host system type... x86_64-unknown-linux-gnu checking for style of include used by make... GNU ... <snip>... config.status: creating win32/version.rc config.status: creating win32/freetds.nsh config.status: creating include/config.h config.status: executing depfiles commands [oracle@racnode1 freetds-0.82]$ make Making all in include make[1]: Entering directory `/tmp/freetds-0.82/include' make all-am make[2]: Entering directory `/tmp/freetds-0.82/include' echo '#define FREETDS_SYSCONFDIR "/usr/local/freetds/etc"' >freetds_sysconfdir.h ... <snip>... make[1]: Entering directory `/tmp/freetds-0.82' make[1]: Nothing to be done for `all-am'. make[1]: Leaving directory `/tmp/freetds-0.82' if test ! -f PWD; then cp ./PWD.in PWD; fi [oracle@racnode1 freetds-0.82]$ su Password: xxxxxx [root@racnode1 freetds-0.82]# make install Making install in include make[1]: Entering directory `/tmp/freetds-0.82/include' make[2]: Entering directory `/tmp/freetds-0.82/include' make[2]: Nothing to be done for `install-exec-am'. ... <snip>... make[2]: Leaving directory `/tmp/freetds-0.82' make[1]: Leaving directory `/tmp/freetds-0.82' [root@racnode1 freetds-0.82]# exit exit

Install DBD-Sybase

The final component to install is the Perl DBD::Sybase driver module. The commands used to install the DBD::Sybase driver module are exactly the same as building the Perl DBI module. There is on extremely important exception and that is that the environment variable SYBASE MUST be set to the path of the FreeTDS installation prior to installing DBD::Sybase.

Before configuring and installing the DBD::Sybase driver module, ensure to set the environment variable SYBASE to the path of the FreeTDS installation. In this article, we used /usr/local/freetds.

If your shell is BASH or KSH, use:


[oracle@racnode1 ~]$ export SYBASE=/usr/local/freetds

Although, not important for the installation phase, you should also set your LD_LIBRARY_PATH environment variable to the /lib directory as in:


[oracle@racnode1 ~]$ export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/local/freetds/lib

Both of these environment variables should be set your login profile (i.e. .bash_profile)

See http://www.freetds.org/userguide/envvar.htm for further information on setting environment variables for FreeTDS.

Use the following commands to unpack, build and install the Perl DBD::Sybase driver module. Please keep in mind that the all of the following commands can be performed as any UNIX user, however, the make install must be performed as root.

Also note that the Makefile.PL script asks you for information about your Sybase server. It uses this information to build and write a file called PWD which is used by the test utilities provided with the module. These tests were designed to run against a Sybase server - not Microsoft SQL Server 2008. Keep in mind that any error messages indicating No library found are due to compiling DBD::Sybase with the FreeTDS libraries instead of Sybase's and can be safely ignored.


[oracle@racnode1 ~]$ cd /tmp [oracle@racnode1 tmp]$ gunzip DBD-Sybase-1.10.tar.gz [oracle@racnode1 tmp]$ tar xvf DBD-Sybase-1.10.tar [oracle@racnode1 tmp]$ cd DBD-Sybase-1.10 [oracle@racnode1 DBD-Sybase-1.10]$ echo $SYBASE /usr/local/freetds [oracle@racnode1 DBD-Sybase-1.10]$ echo $LD_LIBRARY_PATH /u01/app/oracle/product/10.2.0/db_1/lib:/lib:/usr/lib:/usr/local/lib:/usr/local/freetds/lib [oracle@racnode1 DBD-Sybase-1.10]$ perl Makefile.PL Unknown Client Library version - assuming FreeTDS. By default DBD::Sybase 1.05 and later use the 'CHAINED' mode (where available) when 'AutoCommit' is turned off. Versions 1.04 and older instead managed the transactions explicitly with a 'BEGIN TRAN' before the first DML statement. Using the 'CHAINED' mode is preferable as it is the way that Sybase implements AutoCommit handling for both its ODBC and JDBC drivers. Use 'CHAINED' mode by default (Y/N) [Y]: Y Running in threaded mode - looking for _r libraries... ***NOTE*** There is an incompatibility between perl (5.8.x) built in threaded mode and Sybase's threaded libraries, which means that signals delivered to the perl process result in a segment violation. I suggest building DBD::Sybase with the normal libraries in this case to get reasonable behavior for signal handling. Use the threaded (lib..._r) libraries [N]: N OK - I'll use the normal libs Running in 64bit mode - looking for '64' libraries... BLK api NOT available. The DBD::Sybase module need access to a Sybase server to run the tests. To clear an entry please enter 'undef' Sybase server to use (default: SYBASE): VMWINDOWS1 User ID to log in to Sybase (default: sa): sa Password (default: undef): myPassword Sybase database to use on VMWINDOWS1 (default: undef): AlexDB * Writing login information, including password, to file PWD. Checking if your kit is complete... Looks good Note (probably harmless): No library found for -lcs Note (probably harmless): No library found for -lsybtcl Note (probably harmless): No library found for -lcomn Note (probably harmless): No library found for -lintl Note (probably harmless): No library found for -lblk ... <snip>... Writing Makefile for DBD::Sybase [oracle@racnode1 DBD-Sybase-1.10]$ make cp dbd-sybase.pod blib/lib/DBD/dbd-sybase.pod cp Sybase.pm blib/lib/DBD/Sybase.pm ... <snip>... chmod 755 blib/arch/auto/DBD/Sybase/Sybase.so cp Sybase.bs blib/arch/auto/DBD/Sybase/Sybase.bs chmod 644 blib/arch/auto/DBD/Sybase/Sybase.bs Manifying blib/man3/DBD::Sybase.3 [oracle@racnode1 DBD-Sybase-1.10]$ su Password: xxxxxx [root@racnode1 DBD-Sybase-1.10]# make install Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Sybase/Sybase.bs Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Sybase/Sybase.so Files found in blib/arch: installing files in blib/lib into architecture dependent library tree Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/dbd-sybase.pod Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm Installing /usr/share/man/man3/DBD::Sybase.3 Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Sybase/.packlist Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod [root@racnode1 DBD-Sybase-1.10]# exit exit

Configure FreeTDS

At this point, all software components and modules are installed. The final step is to configure FreeTDS to talk to your SQL Server 2008 database!

FreeTDS uses a configuration file called freetds.conf. This file can be found in the FreeDS installation directory under /etc. In this article, the file would be located at /usr/local/freetds/etc/freetds.conf. This file contains several configuration examples (some commented out). It is possible to modify one of them to reflect your server's information.

Here are several example entries I made:


[AlexDB] host = vmwindows1.idevelopment.info port = 1433 tds version = 8.0 [AdventureWorks] host = vmwindows1.idevelopment.info port = 1433 tds version = 8.0 [AdventureWorks2008R2] host = vmwindows1.idevelopment.info port = 1433 tds version = 8.0 [AdventureWorksDW] host = vmwindows1.idevelopment.info port = 1433 tds version = 8.0 [AdventureWorksDW2008R2] host = vmwindows1.idevelopment.info port = 1433 tds version = 8.0 [AdventureWorksLT] host = vmwindows1.idevelopment.info port = 1433 tds version = 8.0 [AdventureWorksLT2008R2] host = vmwindows1.idevelopment.info port = 1433 tds version = 8.0

 

When configuring the entry, note that the dataserver name ([AlexDB] and the [AdventureWorks] sample databases in this example) must be a valid database name! Otherwise, the server will refuse your connection.

The name of my SQL Server in the above example is vmwindows1.idevelopment.info and is running its database service on the default port of 1433. Notice that I have also specified the tds version of 8.0.

Example Perl Scripts

Now let's take a look at our new configuration in action by running a couple of example Perl scripts.

Query the Server, Connection Properties and Available Databases


#!/usr/bin/perl use DBI; my $user = "ahunter"; my $passwd = "myPassword"; my $server = "AlexDB"; my $dbh = DBI->connect("DBI:Sybase:server=$server", $user, $passwd, {PrintError => 0}); my $sth; unless ($dbh) { die "ERROR: Failed to connect to server ($server).\nERROR MESSAGE: $DBI::errstr"; } else { print "\n"; print "Successful Connection."; print "\n\n"; } print "Current Connection Properties\n"; print "---------------------------------------------------------------------------------------\n"; my $sqlStatement = "select \@\@servername, \@\@version, db_name(), system_user, host_name()"; unless ($sth = $dbh->prepare($sqlStatement)) { $dbh->disconnect; die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr"; } unless ($sth->execute) { $dbh->disconnect; die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr"; } while (( $serverName , $sqlServerVersion , $currentDatabase , $currentUser , $clientMachine) = $sth->fetchrow) { ($sqlServerVersion, @dummy) = split(/\n/, $sqlServerVersion); print " SQL Server Instance : $serverName\n"; print " SQL Server Version : $sqlServerVersion\n"; print " Current Database : $currentDatabase\n"; print " Current User : $currentUser\n"; print " Client Machine : $clientMachine\n"; } print "\n"; $sth->finish; print "Available Databases\n"; print "-----------------------------------------------------------------\n"; my $sqlStatement = "select isnull(db_name(dbid), 'MSSQL SYSTEM RESOURCE') as Name , str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size from sys.sysaltfiles group by dbid order by 2 desc"; unless ($sth = $dbh->prepare($sqlStatement)) { $dbh->disconnect; die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr"; } unless ($sth->execute) { $dbh->disconnect; die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr"; } while (($databaseName, $databaseSize) = $sth->fetchrow) { printf(" %-40s %20s\n", $databaseName, $databaseSize); } print "\n"; $sth->finish; print "Disconnecting from SQL Server.\n\n"; $dbh->disconnect; exit(0);


[oracle@racnode1 bin]$ perl queryServer.pl Successful Connection. Current Connection Properties --------------------------------------------------------------------------------------- SQL Server Instance : VMWINDOWS1 SQL Server Version : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Current Database : AlexDB Current User : ahunter Client Machine : racnode1 Available Databases ----------------------------------------------------------------- AdventureWorks2008R2 180.69 MB AdventureWorks 172.00 MB AdventureWorksDW2008R2 75.50 MB AdventureWorksDW 68.81 MB MSSQL SYSTEM RESOURCE 61.06 MB msdb 12.63 MB ReportServer 9.38 MB tempdb 8.50 MB AdventureWorksLT2008R2 7.19 MB AdventureWorksLT 7.19 MB master 5.00 MB ReportServerTempDB 3.00 MB AlexDB 3.00 MB model 1.75 MB Disconnecting from SQL Server.

Query AdventureWorks Database Customer Names


#!/usr/bin/perl use DBI; my $user = "ahunter"; my $passwd = "myPassword"; my $server = "AdventureWorks"; my $dbh = DBI->connect("DBI:Sybase:server=$server", $user, $passwd, {PrintError => 0}); my $sth; unless ($dbh) { die "ERROR: Failed to connect to server ($server).\nERROR MESSAGE: $DBI::errstr"; } else { print "\n"; print "Successful Connection."; print "\n\n"; } print "Individual Customers (Last Name = 'Beck')\n"; print "--------------------------------------------\n"; my $sqlStatement = "SELECT FirstName, LastName FROM [AdventureWorks].Person.Contact AS C JOIN [AdventureWorks].Sales.Individual AS I ON C.ContactID = I.ContactID JOIN [AdventureWorks].Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID WHERE Cu.CustomerType = 'I' AND LastName = 'Beck' ORDER BY LastName, FirstName"; unless ($sth = $dbh->prepare($sqlStatement)) { $dbh->disconnect; die "ERROR: Failed to prepare SQL statement.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr"; } unless ($sth->execute) { $dbh->disconnect; die "ERROR: Failed to execute query.\nSQL: $sqlStatement\nERROR MESSAGE: $DBI::errstr"; } while (($firstName, $lastName) = $sth->fetchrow) { printf(" %s, %s\n", $lastName, $firstName); } my $rows = $sth->rows; print "\n"; print "Rows returned: $rows\n"; print "\n"; $sth->finish; print "Disconnecting from SQL Server.\n\n"; $dbh->disconnect; exit(0);


[oracle@racnode1 bin]$ perl queryAdventureWorksIndividualCustomers.pl Successful Connection. Individual Customers (Last Name = 'Beck') -------------------------------------------- Beck, Alejandro Beck, Alicia Beck, Alisha Beck, Alison Beck, Alvin Beck, April Beck, Ashlee Beck, Barbara Beck, Bethany Beck, Bonnie Beck, Brad Beck, Bradley Beck, Brendan Beck, Calvin Beck, Carl Beck, Carly Beck, Cedric Beck, Christine Beck, Christy Beck, Clayton Beck, Colin Beck, Colleen Beck, Corey Beck, Cristina Beck, Dale Beck, Damien Beck, Darrell Beck, Dawn Beck, Derek Beck, Devon Beck, Donna Beck, Ralph Rows returned: 32 Disconnecting from SQL Server.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Wednesday, 28-Dec-2011 14:04:55 EST
Page Count: 25223