Manage Learn to apply best practices and optimize your operations.

Using Management Studio with the SQL Server Database Engine

Learn how SQL Server Management Studio works in this excerpt from the book SQL Server 2005: A Beginner's Guide.

Microsoft SQL Server 2005: The Complete Reference The following was excerpted from Chapter 2, 'SQL Server 2005 Management Studio,' of the book SQL Server 2005: A Beginner's Guide written by Dusan Petkovic and published by McGraw-Hill Osborne Media. Click here for the book excerpt series.

Bonus: TechTarget members take 45% off the price of this book at McGraw-Hill's online bookstore. Just enter promotion code AETT511 on the homepage.

Using Management Studio with the SQL Server Database Engine

SQL Server Management Studio has two main purposes:

  • Administration of the database servers
  • Management of database objects
  • The administration tasks, which can be started using SQL Server Management Studio, are, among others:

  • Registration of servers
  • Addition of new server groups
  • Management of several servers on one computer
  • Starting and stopping SQL Server
  • Database management
  • The first two administration tasks can be done using the Registered Servers component; for the other tasks, you use Object Explorer.

    The following tasks concerning database objects can be performed:

  • Creation and modification of database objects without using the Transact-SQL language
  • Management of database objects and their usage
  • Generation and execution of SQL statements
  • NOTE: With SQL Server Management Studio, you can manage objects of Database Engine, Analysis Services, Reporting Services, and Integration Services. In this chapter we will demonstrate the use of Management Studio concerning only Database Engine.

    Registering Servers

    SQL Server Management Studio separates the activities of registering servers and exploring server objects. Every server (local or remote) must be registered before use. A server can be registered during the first execution of SQL Server Management Studio or later. To register a database server in the Registered Servers window, right-click the Database Engine folder and select New and Server Registration. Figure 2-3 shows the registration of the server named NTB01109 in the New Server Registration window. You can export a registered server's registration information to an XML file and then import it into the same or another server by right-clicking the registered server and choosing Export. (The same is true for server groups.)

    SQL Server Management Studio also separates the registering of a server and connecting to a server. This means that registering a server does not automatically connect you to the server. To connect to a server from the Registered Servers window, right-click the server, choose Connect, and then New Query. You can also connect to a server from the Object Explorer window if you right-click the server name and choose Connect.

    Creating a New Server Group

    There are two ways to create server groups. In the Registered Servers window, right-click Database Engine, choose New, and select Server Registration. In the New Server Group dialog box, enter a (unique) name and select an existing location for the group.

    You can also create a server group after connecting to a server. In this case, right-click a server in the Registered Servers window, select New, and click Server Group.

    Figure 2-3 The New Server Registration window

    Managing Servers

    SQL Server Management Studio allows you to administer multiple database engines (called instances) on one computer using the Object Explorer component of SQL Server Management Studio. Each instance of SQL Server has its own set of database objects (system and user databases) that are not shared between different instances. The installation and management of SQL Server instances will be discussed in detail in Chapters 16 and 17.

    To manage a server and its configuration, right-click the server name in Object Explorer and choose Properties (see Figure 2-4). The Server Properties dialog box contains several different options, like Permissions and Security. The Permissions dialog box shows all logins and roles that can access the server. The lower part of the dialog box shows all permissions that can be granted to the logins and roles. The Security dialog box contains the information concerning the authentication mode of the server and the login auditing mode.

    You can replace the existing server name with a new name. Right-click the server in the Object Explorer window and choose Register. Now you can rename the server in the Register Server dialog box.

    Figure 2-4 The Server Properties dialog box

    Starting and Stopping SQL Server

    SQL Server can be started automatically each time the Windows operating system starts or by using SQL Server Management Studio. In the Object Explorer window, right-click the selected server and click Start in the pull-down menu. The pull-down menu also contains the corresponding Stop function that stops the activated server. The additional Pause function pauses the whole system, which means that new users are not allowed to log into the system.

    Managing Databases

    You can create a new database using SQL Server Management Studio and its component Object Explorer or Transact-SQL. (The next section discusses database creation and modification using Object Explorer. Database creation using the Transact-SQL language will be discussed in Chapter 4.)

    Click for the complete book excerpt series.

    Dig Deeper on SQL Server Database Modeling and Design