"Super SQL Server Systems"
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.
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
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
- 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
- 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.
Click here to download the
Errata list as a PDF document. You will need
Adobe Reader installed to view it
1 April 2008
- Updated Code Depot zip file to include missing XP_SMTPSENDMAIL XP.
Thanks to Kurt Kluth for reporting this issue.