Book I
Chapter 2
SQL Server
Architecture and
Key Concepts
Business Intelligence
19
Business Intelligence
In the not-too-distant past, only the largest enterprises could take advantage
of the proven benefits from complex business intelligence analysis. The soft-
ware and hardware necessary to run these computations was simply out of
reach of most organizations. The past few years have seen the price of hard-
ware and software fall at a steady pace, bringing these kinds of solutions to a
new audience. Microsoft has done its part as well, delivering highly capable
business intelligence technology in conjunction with its database frame-
work. Known as SQL Server Analysis Services, these technologies, which
seamlessly integrate with the Microsoft Office suite, make it possible to
develop and deliver robust analytic solutions without the need for expensive
software and consulting services. Figure 2-3 highlights how, again, the Visual
Studio development environment is the foundation for developing a SQL
Server–related solution. In this case, designing and creating a multidimen-
sional cube.
Figure 2-2:
Creating a
new project
in Visual
Studio.
06_179543-bk01ch02.qxp 8/23/08 12:25 AM Page 19
Reporting
20
Reporting

Key Concepts
Integration
21
Figure 2-5:
Building an
SSIS
project.
Figure 2-4:
The
Microsoft
Report
Designer.
06_179543-bk01ch02.qxp 8/23/08 12:25 AM Page 21
Book I: Essential Concepts
22
06_179543-bk01ch02.qxp 8/23/08 12:25 AM Page 22
Chapter 3: Getting Started,
Getting Around
In This Chapter
ߜ Hardware and software requirements
ߜ Converting to SQL Server 2008
ߜ Tools at your disposal
I
f you’re ready to get started on the road to a fully functional SQL Server
environment, this chapter is for you. We get the ball rolling by telling you
about the hardware and software foundations that you need to install the
product. The next task is to examine what it takes to either upgrade from an
earlier version of SQL Server or convert from an entirely different database
platform. The chapter closes by taking you on a brief tour of the excellent
tools included with SQL Server, along with some examples of situations

will always use as much memory as it needs but not more.
✦ Disk: Given that relational databases use disk drives as their primary
storage mechanism, it’s always difficult to recommend a fixed value for
the right amount of available disk capacity — every site and application
is different. However, note that a full installation of SQL Server and
related tools eats more than 2GB before any of your data arrives.
SQL Server ships in several editions for both 32- and 64-bit platforms. This
can affect the exact hardware and software configuration that you need. In
general, “more and faster” is better.
✦ Operating system: Microsoft gives you a fairly wide choice of operating
systems (both 32-bit and 64-bit) that can run SQL Server. They include
• Windows Server 2008 (Standard, Data Center, Enterprise)
• Windows Server 2003 (Standard, Data Center, Enterprise)
• Windows XP Professional Edition
• Windows Vista (Ultimate, Home Premium, Home Basic, Enterprise,
Business)
Be prepared to apply the latest service pack for your operating system;
in many cases, SQL Server depends on these patches.
✦ Supporting software: Because it’s built on top of some of Microsoft’s
newest technologies, SQL Server requires that you install some addi-
tional software components. These can include
• .NET Framework 2.0
• SQL Server Native Client
• SQL Server Setup support files
• Windows Installer 3.1
• Microsoft Data Access Components (MDAC) 2.8 SP1 or newer
• Internet Explorer SP1 or newer
SQL Server’s installation logic is quite sophisticated; it generally obtains
these components automatically for you as part of the installation
process, assuming you’re connected to the Internet.

2. Launch the SQL Server setup application.
The Setup.exe file is under the \Servers folder on your installation
media.
3. Accept the license terms and click Next.
The installation program obtains any necessary supporting software.
4. Select the Upgrade from SQL Server 2000 or 2005 option in the SQL
Server 2008 Installation Center dialog box.
The System Configuration Checker analyzes your computer to see if it’s
capable of running SQL Server 2008. If any problems occur, you’re
alerted here.
5. Choose the instance you want to upgrade and click Next.
You can also instruct SQL Server on whether you want to upgrade the
entire instance or just its shared components. Figure 3-1 shows how this
dialog box appears:
07_179543-bk01ch03.qxp 8/23/08 12:26 AM Page 25
Converting to SQL Server 2008
26
6. Review the features that will be upgraded and click Next.
Figure 3-2 shows the list of features that are being upgraded.
7. Configure the accounts you want to run the SQL Server services and
click Next.
8. When prompted, fill in details about how you want errors handled,
and click Next.
Figure 3-2:
Selecting
features.
Figure 3-1:
Selecting an
instance to
upgrade.

Converting from a different database
Normally, the mere thought of converting between relational database
platforms is enough to send shivers up the spine of even the most hardened
database administrator. Fortunately, SQL Server 2008 offers several simple
yet powerful tools to make migrating data less of a burden. I’ll briefly
describe two of these tools, along with criteria you can use to pick one
of them.
SQL Server Import and Export Wizard
This utility (launched by right-clicking on the Management folder within the
SQL Server Management Studio and selecting the Import Data menu option)
allows you to import information easily into your new SQL Server instance.
It’s quite flexible and simple to use, and as shown in Figure 3-3, you can bring
in data from a broad range of information storage formats, including:
07_179543-bk01ch03.qxp 8/23/08 12:26 AM Page 27
Converting to SQL Server 2008
28
✦ ODBC
✦ Oracle
✦ SQL Server
✦ Flat files
✦ Microsoft Access
✦ Microsoft Excel
If your existing database is on this list, then it’s likely that this is the right
tool to use to import information into SQL Server. Book VIII, Chapter 2
explores this topic in more detail.
SQL Server Integration Services
These components are much more powerful, but significantly more complex
to employ. They make it possible for SQL Server administrators and integra-
tion specialists to connect to and manipulate just about any data format out
there. Figure 3-4 offers a brief glimpse into the kinds of sophisticated integra-