SQL Server 2005 Express Edition - Part 7 - Post Installation Configuration Tasks (Management)
September 24, 2007
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 Environment\General 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
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;
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.