- What's New in Microsoft Office Access 2010: An Overview
- Changes to the Office 2007 Ribbon User Interface
- Security, Trusted Locations, Packages, and Certificates
- Access Web Databases and SharePoint Lists
- Application Development by Templates
- Access Macros Redux
- SQL Server 2008 [R2] Express Edition Setup
This chapter is from the book
This chapter is from the book
This chapter is from the book
SQL Server 2008 [R2] Express Edition Setup
SQL Server 2005 Express replaced Access 2000's Microsoft Data Engine (MSDE) 1.0 (based on SQL Server 7.0) and Access 2002 and 2003's Microsoft SQL Server Desktop Engine (MSDE) 2000 (based on SQL Server 2000). SQL Server 2005 Express was a major upgrade to MSDE 2000 and took Microsoft almost five years to finish. SQL Server 2005 Express was the first free SQL Server version to include a management tool—SQL Server Management Studio Express (SSMS). SQL Server 2005 Express removed MSDE's performance throttle, which limited query execution to five simultaneously running queries.
Microsoft released SQL Server 2008 [Express] on August 6, 2008. SQL Server 2008 added spatial (GEOMETRY and GEOGRAPHY), HIERARCHYID and specialized date/time data types, as well as Transparent Data Encryption (TDE) and improved data compression features. SQL Server 2008 R2 (called SSX in this book) released to manufacturing on April 21, 2010, six days after Office 2010 RTMed. The R2 upgrade added new features, including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, ReportBuilder 3.0, and Reporting Services Add-in for SharePoint.
Office 2000–2003 included installation code for MSDE 1.0 and 2000; the Office 2010 CD-ROM images don't include SQL Server 2008 R2 Express Edition and Management Tools, which is the recommended version for Access users who don't have a network connection to SQL Server 2005 Workgroup Edition or later.
Downloading and Installing SSX
If you intend to develop or just explore Access 2010 data projects (ADP) or Access front-ends linked to SQL Server 2008 R2 or SQL Azure tables, do the following to install SSX on your client PC or a network server:
- Download and save the SQL Server 2008 R2 Express Edition and Management Tools installer (SQLEXPRWT_x86.exe, 32-bit: 235MB or SQLEXPRWT_x64.exe, 64-bit: 247MB) from http://www.microsoft.com/express/database/ to a temporary folder, usually \Users\UserName\Downloads.
- Run SQLEXPRWT_x??.exe with an administrative account, accept the End User License Agreement, click Next, and then click Install to extract the setup files and open the SQL Server Installation Center dialog (see Figure 1.20).
Figure 1.20 The SQL Server Installation Center dialog opens a few seconds after the installer extracts the setup files.
- Click the New Installation or Add Features to an Existing Installation link to open the License Terms dialog.
- Mark the I Accept the License terms check box, optionally mark the Send Feature Usage Data to Microsoft check box, and click Next to open the Setup Support Files dialog.
- After the Setup Support Files install and the system configuration check completes, the Feature Selection dialog opens (see Figure 1.21).
Figure 1.21 The Feature Selection dialog opens with all available features selected by default.
- Accept the default features and click Next to perform another System Configuration Check, and click Next to open the Instance Configuration dialog.
- Accept the default Named Instance option and, optionally, change the Instance ID value from SQLExpress to SQLEXPRESS (see Figure 1.22).
Figure 1.22 The default instance name for SQL Server Express databases is SQLEXPRESS. Other SQL Server instances, for example an SQL Server 2005 SP3 Express instance named SQLXSP3, appear in the Installed Instances list.
- Click Next to open the Instance Configuration dialog (see Figure 1.23).
Figure 1.23 The Instance Configuration dialog lets you specify an Account Name and Password for the SQL Server Database Engine and SQL Server Browser. The default values shown here usually are satisfactory.
- Accept the default Service Accounts and Collation and click Next to open the Database Engine Configuration dialog. Mark the Mixed Mode check box to enable logon with SQL Server's System Administrator (sa) account. Type and confirm a complex (upper- and lower-case letters, numeral(s) and symbol) password, and click Add Current User if you aren't logged in as the local machine administrator (see Figure 1.24).
Figure 1.24 The Account Provisioning dialog lets you add an sa login with SQL Server security in addition to the required integrated Windows security login for the local machine Administrator account.
- Review the Data Directories and FILESTREAM pages, and click Next to open the Error Reporting dialog.
- If you want to share error information with the SQL Server team, mark the Send Windows and SQL Server Error Reports to Microsoft check box. Click Next to open the Installation Progress dialog and start installation.
- After a few minutes, the Complete dialog opens with a message that the installation completed successfully and added details in the Supplemental Information text box (see Figure 1.25).
Figure 1.25 The Complete dialog's supplemental information text box contains details for downloading the SQL Server 2008 R2 Books Online (help) file and sample databases.
- After you've read the Supplemental Information, click Close to close the Complete dialog.
)
)
)
)
)
)
Setup installs a Programs, Microsoft SQL Server 2008 R2 submenu with Import and Export Data (32-bit), Import, Export Data (64-bit), and SQL Server Management Studio choices, plus Configuration Tools and Integration Services folders. Installing Books Online adds its choice to the Documentation and Tutorials submenu.
To learn more about SQL Server Management Studio Express (SSMSX), see "Adding SQL Server User Logins with SQL," p. 900 and "Moving from MSDE to the SQL Server 2005 or Later Express Edition," p. 1415.
Managing SSX
If you run SSX on the same machine as your Access front end, you don't need to do anything after installation. SSX will start as a service automatically when you boot your computer. If you want to make the instance of SSX you install accessible to remote networked users, you must perform some minimal management tasks. You must set the SQL Server Browser Service to start automatically and enable at least the TCP/IP protocol for SSX. The Browser Service enables clients to locate SQL Server 2005 instances on remote computers. If you're running the Windows Firewall and Install doesn't establish exceptions for SQL Server and SQL Server Browser, you must create an exception for SQL Server and Browser connections also.
Making SSX Accessible to Remote Users
To make a local SSX instance accessible to other networked computers, do the following:
- Choose Programs, Microsoft SQL Server 2008 R2, Configuration Tools, SQL Server Configuration Manager to open the dialog of the same name.
- Double-click the SQL Server Configuration Manager (Local) node to display the nodes SQL Server 2008 Services, SQL Server 2008 Network Configuration, and SQL Native Client Configuration.
- Double-click the SQL Server 2008 Services node to display the SQL Server Browser and SQL Server (SQLEXPRESS) service items in the right pane (see Figure 1.26). If you installed other services, they will appear also.
Figure 1.26 Installing the SSX database engine installs but doesn't start the SQL Server Browser service automatically.
- Right-click the SQL Server Browser item and choose Properties to open the SQL Server Browser Properties dialog. Click the Service tab, open the Start Mode list box, and choose Automatic (see Figure 1.27). Click OK to close the Properties dialog.
Figure 1.27 Change the SQL Server Browser's Start Mode setting to Automatic, if it's currently Disabled or Manual.
- If you changed the setting to Automatic, right-click the SQL Server Browser item and choose Start to start the service for the first time.
- Double-click to expand the SQL Server 2008 Network Configuration item, select the Protocols for SQL Express node, right-click the TCP/IP item in the right pane's Protocol Name list, and choose Enabled (see Figure 1.28). Acknowledge the message that states you must stop and restart SSX for the change to become effective.
Figure 1.28 Enable the TCP/IP protocol for SSX to communicate with remote clients.
- Repeat step 6 for the SQL Server Network Configuration (32-bit) node, if you installed the 64-bit version.
- If you want to communicate with remote SSX or SQL Server instances, expand the SQL Native Client 10.0 Configuration node, select Client Protocols, and enable the TCP/IP protocol.
- Repeat step 8 for the SQL Server Native Client Configuration (32-bit) node, if you installed the 64-bit version.
- Select SQL Services, right-click SQL Server (SQLEXPRESS), choose Stop, wait for the service to stop, and then choose Start.
- Close SQL Server Configuration Manager.
)
)
)
Alternatively, you can access SQL Server Configuration manager from the Computer Management dialog's Services and Applications node.