"Super SQL Server Systems" Book

As many of you may be aware, I've co-written a book called Super SQL Server Systems, "Turbocharge Database Performance with C++ External Procedures" with Joseph Gama, my co-author. I started on the project back in April 2004 when Joseph approached me to ask if I was interested in writing a book with him. At this stage he had a rough table of contents drawn up. After taking some time to mull over the idea, I agreed and started into the research. Two years later and the book is nearly upon us. The book is an in-depth look at Extended Stored Procedures which is the mechanism by which you can write plug-ins for SQL Server 2000 and 2005 using native mode code. The book has been in general availability from your favourite bookstores since the end of September 2006.

Details

Chapter Breakdown

The book is comprised of 29 chapters with the breakdown as follows: The initial chapters provide an introduction to XPs. Then we develop a class framework to make the development process simpler. The chapters in the middle of the book include loads of ready to run production quality XPs. Finally the remainder of the book provides a comprehensive primer on the various technologies in SQL Server which XP's build upon.

• 1 "Introduction to Extended Stored Procedures"
  This chapter describes how the Plug-in architecture is implemented in SQL Server and what goes into developing a so called "Extended Stored Procedure". It also details the low level API's involved. Some simple tutorials are also provided to get started creating your own XPs.
• 2 "Misc Compilers and Languages"
  This chapter provides a step by step list on how to create an XP in a range of development environments ranging from free open source compilers to commercial IDE's. Tools used include Microsoft Visual C++, Borland C++, Dev-C++, MingW, Intel C++ Compiler and Digital Mars.
• 3 "A C++ framework for XP's"
  In this chapter, we develop a comprehensive C++ class framework for developing Extended Stored Procedures. Like any class framework, the idea is to reduce the amount of boilerplate code which you the developer must write in order to develop your solution. The end result allows you to concentrate on coding your domain specific code as well as improve the overall robustness of the end solution.
• 4 "Network Messaging"
  This chapter provides an XP equivalent to the "NET SEND" command line tool.
• 5 "SMTP Mailing"
  This chapter provides a complete ready to run XP which provides Simple Mail Transfer Protocol support for SQL Server.
• 6 "NT Event Log"
  This chapter provides an XP which integrates with the event logging mechanism in Windows.
• 7 "Disk Logging"
  This chapter provides a completely customizable disk logging mechanism which you can integrate into your TSQL code
• 8 "Audio Notification"
  Continuing on the theme of notification, we provide a set of XPs which provide simple audio playback capabilities.
• 9 "Encoding HTML special characters"
  In a prelude to some of the security discussions which are covered in the book, we provide an XP to safely encode certain HTML / XML constructs which are considered dangerous.
• 10 "Cryptography"
  One of the areas in which XPs can play a significant part in SQL Server is cryptography. This key chapter provides a complete set of production quality XPs for Hashing, Private and Public key Encryption and Decryption, message signing and verification, and key management
• 11 "Random Data"
  This chapter provides tools to create random data in SQL Server. Areas where you might find this useful are in simulation, load testing and password generation.
• 12 "Ini file Manipulation"
  This chapter explores the area of Windows ini files and how they can be easily accessed with a custom XP.
• 13 "Retrieving free disk space"
  Even in the age of multi-terabyte disk arrays, disk space will always runs out. This chapters shows how SQL can be used to monitor the age old computer concern of free disk space.
• 14 "Retrieving CPU Usage"
  This chapter includes an XP which can be used to monitor the CPU utilization of the machine on which SQL Server is running.
• 15 "Raw Sockets sample"
  This chapter provides an innovative XP which can be used to replay IP packets, directly from SQL Server. This can be used for monitoring of security in Intrusion Detection Systems which integrate with SQL Server.
• 16 "Geographical Information Systems"
  With systems such as GPS, Google Earth and NASA World Wind, GIS and database systems are technologies which are being more commonly used together each year. This chapter provides a selection of functionality in the area of GIS.
• 17 "Regular Expressions"
  As most programmers will attest, Regular Expressions are a very powerful technology which can be used in the area of pattern matching. In this chapter we bring Regular Expressions to SQL Server.
• 18 "A Multiple Use XP"
  This chapter provides a tutorial on how you can expose a Win32 API, COM and an XP all from the one DLL.
• 19 "Computational Astronomy"
  Astronomy is one of my favourite hobbies and in this chapter we show how through the technology of XPs, we can unite the seemly disparate areas of Computational Mathematical Astronomy and SQL Server databases.
• 20 "IP Configuration"
  This chapter provides some simple tools to allow you to collect TCP/IP configuration information.
• 21 "Beyond C++"
  Having started the book, saying that XPs can not be developed in Visual Basic, this chapter sets out to break this limitation. The end result from this chapter is that you will be able to develop plug-ins for SQL Server 2000 in Windows VBScript, Windows JScript, Visual Basic 6 and even .NET.
• 22 "Protecting Intellectual Property with XP's"
  This chapter provides a primer on the advantages of developing an XP when protecting IP is an important concern.
• 23 "Managed Code in SQL Server 2005"
  SQL Server 2005 integrates the .NET Common Language Runtime and provides an alternative approach to developing a plug-in. In this chapter, we compare and contrast this new technology with XPs and the COM "sp_OA*" API.
• 24 "Security in SQL Server 2000"
  This chapter provides a breakdown on the various security concerns which arise when developing a SQL Server based solution. Specific issues covered include Port Security, SQL Injection, Cross-site scripting and Buffer overflows. Because an XP integrates with SQL Server at the lowest level, a comprehensive example is provided on how buffer overflows can occur and what problems can occur as a result.
• 25 "From TSQL to XPs"
  This chapter compares and contrasts solutions developed using TSQL directly and an XP.
• 26 "SQL Server Database Tutorial"
  This chapter provides a comprehensive primer on SQL Server, its shipped databases, data types, data integrity and its various object types. For the C++ programmer new to SQL Server, this should get you up to speed with the basics of databases.
• 27 "SQL Server Stored Procedure Tutorial"
  Because an XP appears to TSQL as a standard stored procedure, knowing how a standard stored procedure operates should be your first port of call once you've decided you need to write an XP.
• 28 "SQL Server User-Defined Functions"
  A UDF is a special type of construct in SQL Server which approximates a function in other programming language. You often need to write a UDF to encapsulate an XP so that it can be used in a SELECT statement. For example to successfully use the XP which was developed in Chapter 17 to filter table data you will need to create a UDF.
• 29 "System XP's"
  This final chapter documents the various XPs which ship out of the box with SQL Server and what they can be used for.

Errata

Click here to download the Errata list as a PDF document. You will need Adobe Reader installed to view it

Updates

1 April 2008

• Updated Code Depot zip file to include missing XP_SMTPSENDMAIL XP. Thanks to Kurt Kluth for reporting this issue.