Chapter 1: Getting to Know SQL Server 2005

SQL Server 2005 is Microsoft’s relational database management system (RDBMS). It builds on a legacy of accomplishments spanning more than a decade of SQL Server development and critical success, from SQL Server 6.0, 6.5, 7.0, and 2000. But it is more than that. It is the most widely used and most scalable data management system in the world, currently deployed in hundreds of thousands of companies where it is in service day in and day out, storing the records of the digital universe that now supports our very existence.

Despite the lofty capabilities of this product, and its reputation for being highly complex, one of the key-and ambitious-objectives of this book is to get all database administrators and developers, young and old, using SQL Server 2005. If within a few chapters you don’t see your way to becoming permanently hitched to this product, then my mission has failed. This chapter has thus been designed to be a warm-up for the benefit of novice users, traditional desktop RDBMS users, and gurus working on the dependable SQL Server 2000, other platforms, or older versions.

Note 

Throughout this book I will often use “SQL Server” and “SQL Server 2005” interchangeably, When referring to an earlier version of SQL Server prior to SQL Server 2005, I will include the version being referenced.

A DBMS for Everyone…and Everything

SQL Server 2005 is for old and new companies, small and large. It is equally at home with huge teams of developers and database administrators (DBAs) as it is with lone rangers or the DBA++ who does it all. It does not matter whether you work for a small company with no more than a handful of workers or for a giant multinational with tens of thousands of employees; SQL Server 2005 is for everyone.

Most database developers and administrators are introduced to databases through the likes of Microsoft’s Access, Visual FoxPro, the infamous Borland Database Engine (BDE), or the legendary dBase. (I, for one, came up through the ranks-I still have my first dBase application that I created back in 1987. The last time I looked at it, it had more GOTO clauses than off-ramps on the interstate between Miami and Nova Scotia.)

You have probably always connected SQL Server with big companies, a complex programming environment, geeks like me and my friends who talk in tongues few understand-and very expensive computer equipment. Those days are over, gone forever. Not only do you have a choice of several Windows platforms for SQL Server (from Windows XP to Windows Server 2003 Release 2 and beyond), but you can carry SQL Server around with you in your back pocket on any Windows Mobile appliance. Imagine SQL Server on a chip the size of quarter. Now imagine it in a data center that can house the USS Ronald Reagan.

But why would you want to move to SQL Server? There are still many situations or applications today that can be satisfactorily serviced with a relational database product like Access or FoxPro. For starters, SQL Server is still expensive and complex to use…or is it?

How Critical Is Your Data?

There are important distinctions you need to make between SQL Server and a product like Access or FoxPro, and these revolve around the following three concepts, seen or felt mainly from the data consumer’s perspective:

• Concurrent access to data

• Integrity of data

• Availability of data

Each of these factors spawns collateral factors that either support or violate modern trends in computing and technology, business rules and needs, and database modeling. Let’s explore these three terms and see how they justify your consideration of a product like SQL Server 2005.

Concurrent Access to Data

It is becoming rare these days for businesses to create databases for one pair of eyes only, or a single process. Concurrent access to data, the sharing of databases among many users and processes, is thus an important consideration, if not the most important, when you are designing and creating data-driven software or relational database applications.

If you start with a “tiny” concern of 1–50 people, the more people that need access to your data and the faster they need it, the sooner will concurrency support break down. Yes, products like Access and FoxPro can support dozens, even hundreds, of users, all accessing the database concurrently. However, the database engines that underpin desktop relational database products begin to fall apart when the number and frequency of “hits” on the database files escalates to levels beyond which the desktop engines can cope.

A traditional desktop database product, such as the Joint Engine Technology (Jet) database engine in Access, uses file/server functionality to share the database files. This means that every time a client requires access to data, it needs to connect to the local database engine, which opens the database files on the network server to haul data across the network. The client application must thus be capable of making calls to a database engine installed in the local machine’s processing space, because all that is installed on the server is the database files (such as data.mdb).

Opening and working with a database in this fashion requires every client to have access to a local database engine on the local machine. Each database engine makes its connection to the database, locks tables, and excludes everyone else until the data is either abandoned at the client or updated to the database table. The lock may not be noticeable to anyone requiring casual access, but it may cause a disaster when a write becomes critical and every process hitting the database files needs service.

Having complex database engine code on every desktop or client is a cumbersome and expensive exercise. No matter the cost of the RDBMS software, there will be considerable costs at the client to continue to support the traditional file/server database solution. You will often read about total cost of ownership (TCO) in this book, directly and indirectly, but it behooves you to know that the cost of deploying file/server data engines to fat clients is greater than that of deploying client/server database engines using thin clients. Even with the initial cost of the SQL Server product (free if you just need the Express Edition), client/server solutions are far cheaper. You also need to factor in the cost of hardware obsolescence and the cost of developing for and supporting fat-client solutions.

When selling managed data services or managed network services to a company, I draw attention to cost of ownership by showing how going cheap on software and development invariably costs more in the long term. It can even threaten the business. Support costs add up when you have consultants trying to fix corrupt flat-file databases, or your users sit around idle with nothing to do because Access has been corrupted for the umpteenth time, or the database files were destroyed.

And when dozens of clients all access such data independently of one another, you end up with what I call “the highway hysteria” factor. You can have hundreds of drivers who all use the turnpike all day long, and there are no accidents, and everyone stays clear of each other, and everything is just “cool.” But come rush hour, everyone hits the road as units of independent insanity. Not one driver can know or predict what the other is doing, or react in time to avert disaster. At every rush hour, you can be sure someone is going to get hurt.

Client/server database systems are used everyday by all of us on the Internet. While an increasing number of these servers are SQL Server solutions, your client software need never know what technology is being implemented on the back end. The local computing device or client software can be “thin,” such as a Web browser-and the user interface or client process can run on any operating system, on any platform. If every machine on the Internet required a local database engine to access data on the Internet…the Internet would have been a bad idea.

In client/server databases, the database server’s engine alone controls access to the data, performs the queries, manages the database and the requests of every client, and keeps the database server operating in the best possible way. There is no highway hysteria, because the server is in control of every read or write request for data and can take the necessary steps to avoid accidents. If the highway itself were in control of all the cars, we would not have deaths on the road.

Obviously many situations, and many users, are dependent on traditional file/server database solutions. You can even argue that you are doing fine on your old dBase files. But as soon as you start sharing your data with more people and processes more often, and as soon as access to that data becomes mission critical, you have arrived at the bridge you will need to cross to come over to a client/server database solution like SQL Server. Many of the chapters ahead will help you make the transition, easily and painlessly, without having to spend a lot of money

Note 

Not all client/server RDBMS products are cheap. Some are priced out of the range of nearly all small businesses, and buying development for them can cause you to auction off one of your kidneys. SQL Server is one of the cost-effective products on the market and one of the cheapest to buy applications for. In the past it would have been a mistake not to admit that the other products were more expensive because they were so much better. That is definitely history. SQL Server 2005 is the killer package of databases, and since it still has the best price in the industry, it’s like buying an oceangoing yacht for the price of an average speed boat.

Integrity of Data

Data integrity constraints have never been high on the list of priorities on desktop databases, probably because it is not very difficult to violate them. Sure you have your validation rules. Access even supports declarative referential integrity, which means the constraints are defined in the database schema. But as long as your solutions rely on the client applications for validation, you expose the database to unreliable data.

It is too easy to modify a front-end application, like Access, and bypass validation rules and logic. You can secure the databases, but have you ever needed to find the password for an Access MDB file? If not, you should try it sometime. Dozens of tools on the Internet will hack the password for you in under a minute. And you thought the database was safe.

A client/server database is much more committed to ensuring the validity of data, along with ensuring that what goes into the database is as plausible as possible. The bigger the databases become, and the more complex the data model, the more vulnerable the data. It is thus vital to use technology that has been specifically designed to absolutely enforce integrity rules where needed, especially where your life and the lives of others depends on it. Only a dedicated service component like the SQL Server database engine can be trusted to enforce the constraints. In addition to several well-established SQL Server integrity features inherited from the earlier versions, SQL Server 2005 supports the concept of cascading integrity discussed in Chapter 12.

Cascading integrity works like a dream and saves many hours of programming because you use it everywhere to ensure that no orphaned records exist in your precious “normalized” tables and that related values are all concurrently updated when they need to be. Here’s a simple example in a delete scenario: Let’s say you modularize a little more by splitting ship-to and bill-to into two separate tables, leaving yourself with three tables, the main one being the customer’s details, such as the name of the company and the primary contact information.

Now what do you do when you need to delete the customer from the database? Well, to ensure referential integrity, you have to delete the customer’s record-usually linked to related tables via foreign key elements-in each table individually. If you don’t, you’ll have either records that can no longer be referenced or data that cannot be deleted (or updated) because of referential integrity constraints on the data. This might require you, as it has up to now, to build a manual cascade using triggers, a process that is time-consuming to code and that will degrade performance.

The cascade feature supports the ANSI definition for supporting cascaded delete and update operations. In other words, it means that a delete or update can automatically be cascaded from the record in the referenced table to a record in a referred table without the need for special trigger code. In the past, your triggers would typically have included code to switch between taking no action on an UPDATE or cascading a delete operation on DELETE. I will cover cascading integrity constraints in detail in Chapter 11, which is devoted to the subject of data integrity in SQL Server 2005.

Availability of Data

Not a second goes by in our lives in which data is not critical to us. I once saw a bumper sticker that read “stuff happens-know about it.” It is becoming more difficult to get along in our wired world without constant access to databases for accurate data or analytical information.

A person is denied access to drugs because a pharmacy’s database is offline; a telecommunications system drops a call because a table is corrupt; an order is placed with our competitors because a price cannot be met or a quantity cannot be fulfilled; the list could go on forever. Businesses, people, processes, life itself, all depend on the availability of data every day. While both data access and data integrity might themselves be judged components of availability, it has mostly to do with just being online, being there when you need it, responding in a reasonable time frame.

Availability is a very large and complex subject, fully tackled in Chapters 7–9. When you consider what would ensure that a system or a database remains available, you look at many factors. These include load balancing, distribution and replication, access to large memory resources, mirroring, capability to work with very large databases, symmetric multiprocessing (SMP), 64-bit computing, clustering, and so on. Let’s now look at the key applications for SQL Server 2005.