Chapter 38: Upsizing Access Databases to SQL Server ..............................................................
1225
SQL Server also provides significant security for its databases. The SQL Server security system
directly incorporates with Windows Active Directory, which means that (depending on configura-
tion) SQL Server may recognize a user by virtue of their Windows login and membership in Active
Directory groups, or may require each user to log in each time a SQL Server database is accessed
from an application. Because the physical SQL Server database resides only on an application
server, there is little chance an unauthorized user is able to access data stored in SQL Server or
abscond with an entire SQL Server database.
For all these reasons, SQL Server is the natural destination for Access databases that have outgrown
the practical limits of a file-based database, or databases that must be shared with more users than
is practical using the Access database engines.
Microsoft provides SQL Server Express as a practical server-based database engine for moderate-
size databases. Because SQL Server Express is binary-compatible with full SQL Server, no conver-
sion is necessary to move a SQL Server Express database to full SQL Server. All you have to do is
detach the database files from the Express edition and attach the file to the full edition.
SQL Server Express contains exactly the same core code as all SQL Server editions and works with
exactly the same format database files as SQL Server Enterprise. The data file format, Transact-SQL
syntax, security architecture, and other specifications are the same in SQL Server Express and SQL
Server Enterprise. The primary difference between these database engines is that SQL Server
Express does not include several of the more-advanced features (such as full text searches) that
SQL Server Enterprise includes. Also, SQL Server Express supports databases up to 4GB in size;
SQL Server Enterprise supports databases in excess of 500,000TB.
SQL Server Express includes SQL Server Management Studio and SQL Server Reporting Services.
Furthermore, unlike the Microsoft Database Engine (MSDE) that preceded SQL Server Express, the
Express edition does not contain the performance throttle that inhibited more than a few connec-
tions to MSDE. Consult the SQL Server Express pages (www.microsoft.com/Express/SQL)
on the Microsoft Web site for more details.
SQL Server Express is the ideal database engine for small workgroups and individuals wanting to
make the leap into client-server architecture. And the price is definitely right! SQL Server Express
is a free download from the Microsoft MSDN site (www.microsoft.com/express/sql/
default.aspx).
Installing SQL Server Express Edition is painless. Download either the 32- or 64-bit version for
your computer and install using all the default settings. The memory, hard disk, and other hard-
ware requirements for SQL Server Express Edition are somewhat more stringent than for Microsoft
Office, but they should be met by most desktop and laptop computers today.
Upsizing Access and the Upsizing Wizard
Today, many organizations are becoming more and more dependent on their database applications
to manage everyday business operations, and these applications are growing both in volume of
data and number of users. Applications that you might have developed using Microsoft Access