How to Configure the SQL Server Express GUI Administration Tool

Graphical toolset makes SQL Server Express administration very easy.

Microsoft SQL Server Express is a free and concise version of the enterprise editions of SQL Server. The GUI tools that come for free with SQL Server Express (2005 and up) include SQL Server Management Studio Express (SSMSE), Surface Area Configuration Tool and SQL Server Configuration Manager. You can download all of these tools from the Microsoft Download Center.

Things You'll Need

• .NET Framework 2.0
• MSXML 6.0 Parser

Instructions

Management Studio Express

1. Click on the Windows "Start" menu, select "All Programs" and browse to your installed SQL Server Express item. Select "SQL Server Management Studio Express."

2. Click on the "Objects Explorer" toolbar. This is a hierarchical arrangement of all database objects in a server. Click on "Connect" and select "Database Engine" from the list. In the "Connect to Server" dialog box, set server instance as ".\sqlexpress", "(local)\sqlexpress" or "<your Windows server name>\sqlexpress". The default server instance name at installation is sqlexpress. You can replace "sqlexpress" with your chosen name for this SQL Server Express instance. Click "Connect" to connect to your instance.

3. Click on "Options" to navigate through and explore the SQL Server settings provided. Change settings only if you are sure of the results. Click the "OK" button to save changes.

4. Click on the "Object Explorer Details" page from the "View" menu. It will show details of each database object. Switch to the list view if you want to multi-select many objects for update or deletion.

5. Click on the "General" tab in the "Environment" window. Select the "At Startup" list to review the startup options. The default setting will be "Open Object Explorer." You can change that to "Open new query window" to simulate SQL Server 2000, "Open Object Explorer and new query," "Open empty environment" or "Hide system objects in Object Explorer." Click "OK" to save your selection, which will determine the behavior of Management Studio at startup the next time.

Configuration Manager

1. Load SQL Server Configuration Manager from the Windows "Start" menu. Three major information categories will be displayed, namely, SQL Server Services, SQL Server Network Configuration and SQL Native Client Configuration.

2. Select "SQL Server Services" node and click on the service you want to manage from the list. Right-click on an item of your choice from the list in the pane on the right. You can select your SQL Server instance from possibly more than one registered server. Select "Properties" from the pop-up menu. The Properties window will show advanced configuration options for your SQL Server Express instance.

3. Change the desired properties and click the "OK" button to save the changes. Only four properties can be changed in the Express edition, namely, "Dump Directory" (path for log files), "Error Reporting" (Yes/No), "SQM Reporting" (yes/no to indicate whether service quality reports highlighting most used features are to be sent to Microsoft) and "Startup Parameters" (such as -d<SQL Server path name>).

4. Select "Protocols for <your SQL Server instance name>" under the "SQL Server Network Configuration" node. You will see four default protocols listed in the pane on the right. These are "Shared Memory," "Named Pipes," TCP/IP and VIA (Virtual Interface Protocol). Out of these, the first three protocols and enabled by default and VIA is disabled as it is not available in the express edition. To change default settings, right-click on the protocol and select "Enabled" or Disabled."

5. Navigate to the "SQL Native Client Configuration" node under Configuration Manager and click on "Client Procotols." Client protocols are the same as the network configuration protocols mentioned above. Right-click on "Client Protocols" and select "Properties." The client protocols properties box is divided into two columns, "Disabled Protocols" and "Enabled Protocols." Change the status and/or priority order for each protocol using the left-right and up-down arrow buttons. Click "OK" to save the changes. If you enabled the "Shared Memory" protocol, check the "Enable Shared Memory Protocol" checkbox from properties for the "SQL Server Services" node.

6. Right-click on "Aliases" under "SQL Native Client Configuration." Select "New Aliases" from the drop-down menu. A screen showing four parameters will come up. Enter a name against "Alias," which will be used for connection. Similarly, enter the appropriate values for the "Port No." (server listens on it) and "Server" (name or IP address of your SQL Server Express instance). Select a protocol from the drop-down list by right-clicking "Protocol" (defaults to TCP/IP). If you selected "Named Pipe" as the protocol, enter the pipe name against the port number parameter. Click the "OK" button to save changes.

Tips & Warnings

• You will not find help pages installed with the GUI tools. Press "F1" to bring up MSDN Online Help.

• Install SQL Server Books Online for your version.

• Enable the "SQL Server Express" filter to hide enterprise-only features.

References

• Microsoft TechNet Library: Tutorial: SQL Server Management Studio
• Code Beach: SQL Server 2005 Express Tools

Resources

• Microsoft Download Center: SQL Server 2005 Management Studio Express
• Microsoft Download Center: SQL Server 2008 Management Studio Express