SQL Server 2005 Express Edition – Part 7 – Post Installation Configuration Tasks (Management)

In the most recent articles of our series dedicated to SQL Server 2005
Express Edition, we have been discussing a number of tasks that modify default
settings applied during standard installation, resulting in a variety of
functional improvements. Throughout this discussion, we have described
activities that enable remote connectivity and enhance security through
encryption of network traffic between the database engine and its clients. In
this installment, we will look into a few other options intended to simplify
the handling of administrative responsibilities.

One of the most common complaints among database administrators and
developers dealing with Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
was the lack of a management utility that would deliver an easy to use but
powerful graphical interface, comparable to SQL Server 2000 Enterprise Manager
bundled in with Standard and Enterprise editions of the product. Even though
its capabilities included remote management of MSDE 2000 instances, such a solution
required at least one existing license of a full-fledged product, which in many
cases was not economically feasible. This limitation has been addressed with
the release of SQL Server 2005 Express Edition by coupling it with a scaled
down rendition of SQL Server 2005 Management Studio, available as a separate
(43.1 MB) download from SQL
Server 2005 Express Edition section
of the MSDN web site. Its design (just
as the full version’s) is based on the multi-window layout of Visual Studio
.NET 2005, featuring the familiar Object Explorer and Query Editor, giving you
flexibility sufficient to control the majority of built-in functionality. As
expected, the interface reflects some of the product limitations, including, the
absence of the SQL Server Agent Service (hence the SQL Server Agent node is missing
in the Object Explorer interface) or limited replication support (the Replication
folder contains only the Local Subscriptions subfolder), which, interestingly,
apply also when connecting to instances of SQL Server 2005 Standard, Developer,
or Enterprise editions. Note that the SQL Server Management Studio Express is
incorporated into Microsoft SQL Server 2005 Express Edition with Advanced
Services (which additionally offers Reporting Services and Full-Text Search
components), posted alongside its stand-alone version. The same download
location also gives you access to Microsoft SQL Server 2005 Express Edition
Toolkit, further extending the variety of management options with a feature-limited
implementation of Business Intelligence Development Studio that can be used for
generating Reporting Services reports. Furthermore, you might want to consider
downloading and installing Books
Online
(which, starting with its April 2006 release, consolidates
information relevant to all editions of SQL Server 2005, but allows you to
retrieve topics that match your interests through filtering) as well as sample code and databases
(including a copy of the AdventureWorks Database, frequently referenced in the
product documentation and a variety of training manuals).

Once you have installed SQL Server Management Studio Express and launched it,
you will be prompted to connect to a target instance (this can be any edition
of the SQL Server 2005 database engine), provide its name (this typically takes
the form ServerName/InstanceName, unless you deal with the default
instance, in which case ServerName entry will suffice), as well as
authentication method (utilizing either Windows accounts or SQL Server logins).
Additional settings (displayed on the Connection Properties tab of the Connect
to Server dialog box, revealed once you click on the "Options
>>" command button) give you the ability to specify a number of
parameters (such as target database, network protocol and its packet size,
connection and execution timeouts, which might need to be adjusted in case you
expect significant latency) and to force encryption (for this particular
connection only). If you deal primarily with remote installations of SQL Server
and notice that it takes a while for the Management Studio to complete loading,
try using the setting "Open empty environment" at startup (located in
the EnvironmentGeneral section of Options dialog box accessible from the
Tools->Options… menu). When working consistently with the same set of
targets, simplify the process of establishing initial connections by adding
them to the Registered Servers window (activated from the View menu), allowing
you to assign custom properties to each. You can also arrange registered
entries into arbitrarily named groups. While the Registered Servers window
serves as the primary interface for designating a scope of target SQL Server
instances, Object Explorer provides the bulk of their individual management
features (as a matter of fact, the majority of options present in the former is
also available from the latter). However, as mentioned earlier, the functionality
implemented in the SQL Server Management Studio Express version of Object
Explorer is limited compared with its equivalent incorporated in SQL Server
2005 Standard, Enterprise, or Developer editions. Among the most prominent
examples of this disparity (in addition to the missing SQL Server Agent node) is
the absence of Replication Publishing and Full-Text Search, as well as the lack
of Log File Viewer (which complicates the task of monitoring SQL Server logs).

If you want to be able to generate reports or perform Full-Text searches,
(we will dedicate to these topics one of our future articles), you have an
option of installing SQL Server 2005 Express Edition with Advanced Services
(currently at SP2 level), available as a 234 MB download.
During its installation, you will be prompted to select components you are
interested in (which, besides Replication and Full-Text Search, also include Express
version of SQL Server Management Studio) and the target instance that they
should be added to. As with the standard setup, you will have a chance to
decide whether to enable User Instances and add the currently logged-on user to
the SQL Server System Admin role (on the Configuration Options page of the
wizard). You will also be able to configure Error and Usage Report Settings.
Your choice in this case will determine whether details regarding product
issues you encounter and your usage patterns will be forwarded to Microsoft
(subject to constraints protecting your personal information, as outlined by SQL Server 2005 Privacy
Statement
), delivering data that can be analyzed, potentially resulting in
stability or performance improvements. Your initial selection can be modified
at any point following the setup with the SQL Server Error and Usage Reporting utility
(accessible from the Configuration Tools submenu of Microsoft SQL Server 2005
menu), which gives you the ability to specify the type of data to be disclosed
(error information vs. usage reports) on a per instance and shared components
basis.

To further enhance available functionality, you can take advantage of
Microsoft SQL Server 2005 Express Edition Toolkit, which offers (in addition to
Management Studio Express) Business Intelligence Development Studio (providing
Reporting Services Report Designer, but lacking a number of features available
in other editions, such as the ability to create SQL Server 2005 Integration
Services or Analysis Services projects) and a Software Development Kit. This is
done via the same setup wizard that is employed in both the standard and
advanced product installations.

Among other management-related innovations introduced in the SQL Server 2005
product line, there is also a Dedicated Administrator Connection (DAC). This
provides a single-logon, restricted access to a SQL Server 2005 instance,
primarily for diagnostics and troubleshooting purposes, in situations when
standard connections to the database engine can not be established (typically
due to the server being locked in an abnormal state). In order to make DAC
available in the Express Edition (other SKUs do not impose this requirement),
the server must be operating with the trace flag 7806 turned on (which is by
default disabled, due its resource utilization overhead). One way to accomplish
this is to start the SQL Server service of the target instance with -T7806 value added to the Start
parameters entry in its Properties dialog box from within Services MMC snap-in,
however, with this approach the setting is not retained, which forces you to
repeat this procedure every time you want to be able to take advantage of DAC.
To make the change persistent across service restarts, use SQL Server
Configuration Manager instead. Expand its SQL Server 2005 Services node and
locate the item representing the database engine on the target instance. Select
Properties from its context sensitive menu and in the resulting dialog box,
switch to the Advanced tab. Find the Startup Parameters entry and add -T7806 to
the existing list, separating it with semicolon from other values. Click on the
OK command button to confirm your change and restart the service. To verify
that the task has been successfully completed, check the Windows application
log (for an event informing that the dedicated administrator connection support
was established and listing its dynamically allocated listening port), review
the output generated by DBCC TRACESTATUS, or simply attempt connecting via DAC
by running the following from the Command Prompt (assuming you are connecting
to the SQLEXPRESS instance on the local computer using Windows authentication):

SQLCMD -S .SQLEXPRESS -A

By default, DAC is permitted only from a SQL client software running on the
same computer. To allow remote connections, you need to turn on the ‘remote
admin connections’ server configuration option, which can be accomplished by executing
the following sequence of T-SQL commands on the target instance (interestingly,
while this setting is also configurable via Surface Area Configuration for
Features that is part of the Standard, Developer, or Enterprise products, it is
not included in the version of the same utility bundled with SQL Server 2005
Express Edition):

sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

Note that, as its name indicates, the ability to connect via DAC is
restricted to members of the Sysadmin fixed server role. In addition, in the
case of SQL Server 2005 Express Edition, connection must be established with the
SQLCMD.EXE utility (other editions support this functionality in combination
with Query Editor of SQL Server Management Studio). Only single Dedicated
Administrative Connection per instance is allowed at any given time. It is also
important to remember that DAC is intended for troubleshooting and diagnostic
purposes (which is reflected by limited resources allocated for it by the
database engine) hence you should avoid using it for other reasons (such as
running more elaborate queries).

In our next article, we will start exploring some of unique features of SQL
Server 2005 Express Edition that make it well suited for scenarios that involve
single-user databases and embedded applications.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles