that you are interested in learning how to create solutions with Microsoft SQL Server 2005, but
have no prior knowledge of SQL Server 2005. You may well have had exposure to other databases
such as MySQL, Oracle, or Microsoft Access, but SQL Server uses different interfaces and
has a different way of working compared to much of the competition. The aim of this book is to
bring you quickly up to a level at which you are developing competently with SQL Server 2005.
This book is specifically dedicated to beginners, and to those who at this stage wish to use only
SQL Server 2005. You may find this book useful for understanding the basics of other databases
in the marketplace, especially when working with T-SQL. Many databases use an ANSI-standard
SQL, and so moving from SQL Server to Oracle, Sybase, etc., after reading this book will be a
great deal easier.
This chapter covers the following topics:
• Why SQL Server 2005?
• How do I know if my hardware meets the requirements?
• Can I just confirm that I have the right operating system?
• What can I do with SQL Server 2005?
We will also then look at installing our chosen edition—this section of the chapter covers
the following:
• Installing SQL Server 2005 on a Windows XP platform
• Options not installed by default
• Where to install SQL Server physically
• Multiple installations on one computer
• How SQL Server runs on a machine
• How security is implemented
• Logon IDs for SQL Server, especially the sa (system administrator) logon
■ S QL SERVER 2005 OVERVIEW AND INSTALLATION
Why SQL Server 2005?
The following discussion is my point of view, and although it no doubt differs from that of
others, the basis of the discussion holds true. SQL Server faces competition from other databases,
not only from other Microsoft products such as Microsoft Access and Microsoft Visual
FoxPro, but also from competitors like Oracle, Sybase, DB2, and Informix, to name a few.
Microsoft Access is found on a very large number of PCs. The fact that it is packaged with
some editions of Office and has been around for a number of years in different versions of
Office has helped make this database ubiquitous; however, a great number of people actually
do use the software. Unfortunately, it does have its limitations when it comes to scalability,
speed, and flexibility, but for many small, in-house systems, these areas of concern are not an
issue as such systems do not require major database functionality.
Now we come to the serious competition: Oracle and Sybase. Oracle is seen as perhaps the
market leader in the database community, and has an extremely large user base. There is no
denying it is a great product to work with, if somewhat more complex to install and administer
than SQL Server; it fits well with large companies that require large solutions. There are many
parts to Oracle, which make it a powerful tool, including scalability and performance. It also
provides flexibility in that you can add on tools as you need them, making Oracle more accommodating
in that area than SQL Server. For example, SQL Server 2005 forces you to install the
.NET Framework on your server whether you use the new .NET functionality or not. However,
Oracle isn’t as user friendly from a developer’s point of view in areas like its ad hoc SQL Query
tool and its XML and web technology tools, as well as in how you build up a complete database
solution; other drawbacks include its cost and the complexity involved in installing and running it
effectively. However, you will find that it is used extensively by web search engines, although
SQL Server could work just as effectively. With the new functionality in SQL Server 2005, Oracle
will be under pressure to expand its existing functionality to meet this challenge. SQL Server
has always been a one-purchase solution, such that (providing you buy the correct version)
tools that allow you to analyze your data or to copy data from one data source such as Excel
into SQL Server will all be “in the box.” With Oracle, on the other hand, for every additional
feature you want, you have to purchase more options.
Then there is Sybase. Yes, it is very much like SQL Server with one major exception: it has
no GUI front end. Sybase Adaptive Server Anywhere, which is mainly used for small installations,
does have a front end, but the top-of-the-range Sybase does not. To purists, there is no need for
one, as GUI front ends are for those who don’t know how to code in the first place—well, that’s
their argument, of course, but why use 60+ keystrokes when a point, click, and drag is all that is
required?
Sybase is also mainly found on Unix, although there is a Windows 2000 version around.
You can get to Sybase on a Unix machine via a Windows 2000/XP machine using tools to connect
to it, but you still need to use code purely to build your database solution. It is very fast and very
robust, and it is only rebooted about once, maybe twice, a year. Another thing about Sybase is
that it isn’t as command-and-feature rich as SQL Server. SQL Server has a more powerful
programming language and functionality that is more powerful than Sybase.
Each database has its own SQL syntax, although they all will have the same basic SQL syntax,
known as the ANSI-92 standard. This means that the syntax for retrieving data, and so on, is the
same from one database to another. However, each database has its own special syntax to
maintain it, and trying to use a feature from this SQL syntax in one database may not work, or
work differently, in another.
■ SQL SERVER 2005 OVERVIEW AND INSTALLATION 3
So SQL Server seems to be the best choice in the database market place, and in many
scenarios it is. It can be small enough for a handful of users, or large enough for the largest
corporations. It doesn’t need to cost as much as Oracle or Sybase, but does have the ability to
scale up and deal with terabytes of data without many concerns. As you will see, it is easy to
install, as it comes as one complete package for most of its functionality, with a simple install
to be performed for the remaining areas if required.
Now that you know the reasons behind choosing SQL Server, you need to know which
versions of SQL Server are out there to purchase, what market each version is aimed at, and
which version will be best for you, including which version can run on your machine.
Hardware Requirements
Now that you know a bit about SQL Server, the next big question on your list may well be “Do I
have a powerful enough computer to run my chosen SQL Server edition on? Will this help me
refine my decision?”
Judging by today’s standards of minimum specification hardware that can be bought,
even the low-cost solutions, the answer will in most cases be “Yes” to most editions. However,
you may have older hardware (things move so fast that even hardware bought a couple of
months ago can quickly be deemed below minimum specification), so let’s take a look at what
the minimum recommendations are, and how you can check your own computer to ensure
that you have sufficient resources.
CPU
The minimum recommended CPU that SQL Server will run on is a 500 MHz processor, a
compatible processor, or similar processing power. However, as with most minimums listed
here, Microsoft wholly recommends a faster processor, 1 GHz in fact. The faster the processor,
the better your SQL Server will perform, and from this the fewer bottlenecks that could surface.
Many of today’s computers start at 2 GHz or above, and 500 MHz has not been the standard
installation for a couple of years now. If you have a lower-speed processor, try to invest in
upgrading it. You will find your development time reduced for it.
However, it is not processor alone that speeds up SQL Server. A large part is also down to
the amount of memory that your computer has.
Memory
Now that you know you have a fast enough processor, it is time to check whether you have
enough memory in the system. All the editions of SQL Server, with the exception of the
Windows Express and Mobile versions, require a minimum of 512MB of RAM onboard your
computer. Many of the editions that could be used will run with this, although you shouldn’t
have too many more applications open and running as they could easily not leave enough
memory for SQL Server to run fast enough. Microsoft recommends 1GB or above, and really
double that at least for when you start using your SQL Server in earnest.
The Windows Express and the Mobile versions have a minimum of 128MB of RAM.
Moving the other way, if you wanted to run the Enterprise Edition, then a minimum, and I
mean a bare minimum, of 1GB really should be installed, especially if you want to use any of
the more advanced features.
The more memory the better: I really would recommend a minimum of 1GB on any computer
that a developer is using, with 2GB ideal and sufficient to give good all-around performance.
If a process can be held in memory, rather than swapped out to hard drive or another area
while you are running another process, then you are not waiting on SQL Server being loaded
back into memory to start off where it left off. This is called swapping and the more memory,
the less swapping could, and should, take place.
Taking CPU speed and memory together as a whole, it is these two items that are crucial
to the speed that the computer will run, and having sufficient speed will let you develop as fast
as possible.
Hard Disk Space
You will need lots! But name a major application these days that doesn’t need lots! For SQL
Server alone, ignoring any data files that you are then going to add on top, you will need over
1GB of space. Certainly, the installation options that will be used later in the chapter will mean
you need this amount of space. You can reduce this by opting not to install certain options, for
example, Books Online; however, even most notebooks these days come with a minimum
40GB, and 80GB is not uncommon either. Hard disk space is cheap as well, and it is better to
buy one disk too large for your needs than have one hard drive that suits now, and then have
|to buy another later, with all the attendant problems of moving information to clear up space
on the original drive.
Again, you will need spare space on the drive for the expansion of SQL Server and the databases,
as well as room for temporary files that you will also need in your development process.
So think big—big is beautiful!
Operating System Requirements
You will find that SQL Server 2005 will run on Windows 2000 Professional Edition and above
with Service Pack 4, or all editions of Windows XP Service Pack 2 or above. It will also work on
the 64-bit operating systems for Windows XP, as well as the 64-bit editions of Windows Server
2003. So there is plenty of scope for running SQL Server on many operating systems.
■ S QL SERVER 2005 OVERVIEW AND INSTALLATION
The Example
In order to demonstrate SQL Server 2005 fully, together we will develop a system for a financial
company that will have features such as banking, share purchase, and regular buying, such as
a unit trust savings plan, etc. This is an application that could fit into a large organization, or
with very minor modifications could be used by a single person to record banking transactions.
The book builds on this idea and develops the example, demonstrating how to take an idea
and formulate it into a design with the correct architecture. It should be said, though, that the
example will be the bare minimum to make it run, as I don’t want to detract from SQL Server.
The book will give you the power and the knowledge to take this example, expand it to suit your
financial application needs, and give it the specifics and intricacies that are required to make it
fully useful for yourself.
But before we can get to this point, we need to install SQL Server.
Installation
It is now time to install SQL Server from the CD-ROM or DVD. The examples within this book
can run on any edition.
This chapter will guide you through the installation process of the Developer Edition,
although virtually all that you see will be in every edition. Some of the differences will be due to
the functionality of each edition. This book will cover many of the options and combinations
that can be completed within an installation. A number of different tools are supplied with SQL
Server to be included with the installation. We will look at these tools so that a basic understanding
of what they are will allow us to decide which to install.
Installation covers a great many different areas:
• Security issues
• Different types of installation—whether this is the first installation and instance of SQL
Server or a subsequent instance, for development, test, or production
• Custom installations
• Installing only some of the products available
Most of these areas will be covered so that by the end of the chapter you can feel confident
and knowledgeable to complete any subsequent installations that suit your needs.
A Standard Installation
Let’s now take the time to install SQL Server 2005 on our machines. Microsoft offers a 120-day
trial version at http://www.microsoft.com/sql/evaluation/trial/, which you can use to follow
along with the examples in this book if you don’t already have SQL Server 2005.
This book uses the Developer Edition because it is most suited to our needs, as developers,
for it doesn’t have all the operating system requirements of the Enterprise Edition. Insert the
CD for the Microsoft SQL Server 2005 edition of your choice in your CD-ROM drive and start
the installation. What the upcoming text covers is a standard installation.
■ SQL SERVER 2005 OVERVIEW AND INSTALLATION 7
Preparing to Install
First of all, ensure that you have logged on to your machine with administrative rights so that
you are allowed to create files and folders on your machine, which is obviously required for
installation to be successful.
If, when placing the CD-ROM into your computer, the installation process does not automatically
start, open up Windows Explorer and double-click autorun.exe, found at the root
level of the CD-ROM.
You are now presented with the installation screen for the Microsoft SQL Server 2005 edition
of your choice, as shown in Figure 1-1. After you accept the SQL Server End User Agreement,
SQL Server then installs some support files prior to set up. These files are part of SQL Server
that will be included in service packs and form part of the installation process. The main files
are setup files and the required .NET Framework version if it is not already installed.
.NET is a framework that Microsoft created that allows programs written in VB .NET, C#,
and other programming languages to have as a common compile set for computers. SQL
Server 2005 uses .NET for some of its own internal work, but also, as a developer, you can write
.NET code in any of Microsoft’s .NET languages, and include this within SQL Server.
■ S QL SERVER 2005 OVERVIEW AND INSTALLATION
The SQL Server 2005 Installation Wizard then starts with a welcome screen. Click Next.
We then come to the System Configuration check, as you see in Figure 1-2. Its main function
is to check that the PC meets the hardware and software requirements. There are certain
requirements for certain parts of the installation; for example, SQL Server Reporting Services,
a tool for producing reports from SQL Server that was an add-on with SQL Server 2000, requires
Internet Information Services (IIS). IIS is a process that runs on computers that provides the
ability to run a web server. SQL Server Reporting Services is web based.
