SQL Server 2005 Management Studio Express Edition

Microsoft Word - Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition.doc Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition SQL Server Technical Article Writers: Eric Brown, Senior Consultant Quilogy Inc. Published: March 2006 Applies To: SQL Server 2005 Summary: Learn how to manage SQL Server 2005 Express Edition by using the free graphical management tool, SQL Server 2005 Management …

An Introduction to SQL Server Management Studio Express Edition SQL Server 2005 Management Studio Express Edition (SSMSE) provides the database developer and administrator with a robust set of tools for working with Microsoft® SQL Server™ Express Edition. Based on the same technology and functionality as that in SQL Server Management Studio, SSMSE uses Microsoft Visual Studio® .NET 2005 user interfaces and IDE layout, so that switching between SSMSE and Visual Studio .NET 2005 easy. This familiar interface allows .NET developers to be productive more quickly. The features in SSMSE are limited to those that are needed to manage a SQL Server 2005 Express Edition database. While you can use SSMSE to manage other editions of SQL Server 2005, you cannot manage components of the server that are not in SQL Server Express. For example, you can connect only to the relational database engine only by using SSMSE because other services are not installed with SQL Server Express. SSMSE and SQL Server Management Studio (SSMS) cannot be installed together. If multiple editions of SQL Server are installed on your computer, you can only use SSMS and not SSMSE. Getting started The next sections provide a tour of the features in SSMSE. Because of its graphical user interface, many SSMSE features can be accessed simply by right-clicking. You can accomplish many tasks by using both script and dialog boxes. The beginning developer can use dialog boxes to accomplish basic tasks. By design, SQL Server always creates a default state that is secure and functional. Changes to default settings are for the purpose of setting up the database for the unique needs of users and applications. Assuming that SQL Server 2005 Management Studio Express Edition is installed, let’s start by connecting to an instance. Connecting to servers This section covers how to connect to an instance of SQL Server Express. The Connect to Server dialog box allows users to provide both logon credentials and specific connection properties. You can use it to connect directly to SQL Server Express. For the authentication method, you can choose either SQL Server Authentication or Windows Authentication. By default, this is set to Windows Authentication. There are two tabs on the Connect to Server dialog box: the Login tab and the Connection Properties tab. To view the Connection Properties tab, click the Options button. Use the Connection Properties tab (shown in Figure 1) to input authentication credentials and server name….. 10 Microsoft Corporation ©2006 Figure 10: Specify Values for Template Parameters window Now, you have a basic understanding of how to create the basic objects that make up a database. In the next sections, we look at managing the database and server by using SSMSE. Basics of Database Administration Whether you are a beginning developer or a budding database administrator, you need to have some understanding of database administration. Following are the basic functions of database administration. • System management. Making sure the appropriate services and features are accessible and working. With SQL Server 2005, some features are turned off by default. Moreover, SQL Server Express is configured to not listen on the network via TCP/IP. • Security management. Managing access to the data. This starts at the operating system and ends with the database. This paper focuses on security topics related to users and logins. • Disaster preparedness. Recovering from user error and hardware and software failure. This paper covers backing up databases, understanding recovery models, and working with scripts to allow for the re-creation of database objects and data. In the following sections, we look at how to handle these database administration tasks by using SQL Server Browser, SQL Configuration Manager, and the SQL Surface Area Configuration tool. We review system management tasks, security settings, and backup and recovery models. System management When SQL Server Express is installed using the default settings, it is configured to be as secure as possible. Microsoft calls this configuration secure by default. After SQL Server Express is installed, you might need to reconfigure parts of the server based on your expected usage. For example, by default SQL Server Express allows only local connections. If you need to connect to SQL Server Express through your network, configure SQL Server Express to allow remote connections. A number of tools are available for accomplishing various configuration tasks. SQL Server Surface Area Configuration tool The Surface Area Configuration tool provides a scriptable interface for setting global security states for SQL Server features and services. Because SQL Server 2005 follows the security principle of secure by default, many features are turned off by default. Moreover, unless you indicate otherwise, the services you choose to install do not start automatically after installation is complete. Use the Surface Area Configuration tool to configure SQL Server and to manage SQL Server security. The Surface Area Configuration tool can be used to turn on and off services and features as well as to change configuration settings. It provides two basic sorting methods—View by Instance and View by Component. Use View by Component to quickly access and manage a certain grouping of features. View by Instance allows you to look over a specific instance of SQL Server and make changes