Getting to Know the SQL Server Management Studio

HOUR 3 Getting to Know the SQL Server Management Studio The Microsoft SQL Server Management Studio Express is the new interface that Microsoft has provided for management of your SQL … … Server database. It is the main tool that you will use when maintaining your databases and the objects they contain. In this hour you’ll learn:. The system databases that ship with SQL Server 2005 Express. What is available under the Security node of the Management Studio. The types of server objects that are available. What is available under …

Microsoft SQL Server Management StudioExpress 45 This not only provides you with standardization between databases, but provides you with a great jump start on creating the databases you need. If you modify Model, you do not affect any existing databases. All new databases will be affected by your changes. The MSDB Database The MSDB (Microsoft Database) databaseis used by SQL Server, SQL Server Management Studio, and SQL Server Agent. All three of them use it to store data, including scheduling information and backup and restore history information. For example, SQL Server maintains a complete backup and restore history in MSDB. There are several ways that you can add to or modify information stored in the MSDB database. They include . Scheduling tasks . Maintaining online backup and restore history . Replication The TempDB Database TempDB is asystem database that acts as a resource to all users working with a particular instance of SQL Server. TempDB holds the following objects: . Temporary user objects such as temporary tables, temporary stored procedures, temporary table variables, or cursors . Internal objects used by the database engine to perform tasks such as sorting . Row versions that are generated in data modification transactions The Security Node As its name implies, the Security Node enables you to manage SQL Server security. Using the Security Node, you can work with logins, add to and remove people from server roles, and create credentials. This chapter provides an introduction to security. For more information, see Hour 21, “SQL Server Authentication,” and Hour 22, “SQL Server Permissions Validation.” The Logins Node Logins representthe users and roles that have access to your system. Note in Figure 3.2 that two types of icons appear under the Logins node. One is granting a role access to the database, and the other is granting a user access to the database.
46 HOUR 3 Getting to Know the SQL Server Management Studio The Server Roles Node Server Roles arepredefined roles, supplied by SQL Server. Each Server Role possesses a pre-defined set of rights. Figure 3.3 shows the available Server Roles. You cannot add or remove Server Roles. FIGURE 3.2 Notice that Administrators is a role, and sa is a user. FIGURE 3.3 Each Server Role possesses a pre-defined set of rights.
Microsoft SQL Server Management StudioExpress 47 The Credentials Node Credentialsare new to SQL Server 2005. A credential is a record that contains the authentication information required for SQL Server to connect to an outside resource. Most credentials are made up of a Windows login and password. Server Objects Server Objects refer to a set of objects used at the server level (not at the database level). These objects include Backup Devices, Linked Servers, and Server Triggers. Backup Devices Backup devicesinclude the tapes and disks that you use to back up or restore your SQL Server. When creating a backup, you must designate the backup device that you want to use (see Figure 3.4). You select from a list of backup devices that you have created. FIGURE 3.4 When creating a backup, you must first designate the backup device you want to use. Linked Servers Linked serversenable you to work with other SQL Servers, as well as databases other than SQL Server databases, right from within Management Studio. This offers a few advantages: The Replication Node Data replication is the capability of a system to make copies of its data and application objects automatically in remote locations. You easily can propagateany changes to the original or data changes to the copies to all the other copies. Data replication enables users to make changes to data offline at remote locations. SQL Express synchronizes changes to either the original or the remote data with other instances of the database. The original database is referred to as the design master . You can make changes to definitions of tables or other application objects only at the design master. You use the design master to make special copies called replicas . Although there is only one design master, replicas can make other replicas. The process of the design master and replicas sharing changes is referred to as synchronization . To see an example of data replication at work, imagine that you have a team of salespeople who are out on the road all day. At the end of the day, each salesperson logs on to one of the company’s servers through Terminal Services. The replication process sends each salesperson’s transactions to the server. If necessary, the process sends any changes to the server data to the salesperson. Management The Management node contains tools that help you to manage your SQL Server. These tools include the capability to view both the SQL Server Logs and the Activity Monitor….. The Process Info page contains information about the connections. The Locks by Process page sorts the locks by the connection. The Locks by Object page sorts the locks by the object name. Creating a SQL Server Database Before you can build tables, views, stored procedures, triggers, functions, and other objects, you must create the database in which they will reside. A database is a collection of objects that relate to one another. An example would be all the tables and other objects necessary to build a sales order system. To create a SQL Server database, followthese steps: 1. Right-click the Databases node and select New Database. The New Database dialog appears (see Figure 3.5). 2. Enter a name for the database. 3. Enter a path for the database (see Figure 3.6). 4. Click to select the Options page and change any options as desired (see Figure3.7). 5. Click OK to close the New Database dialog and save the new database. The database now appears under the list of databases (see Figure 3.8) under the Databases node of SQL Server Management Studio. If the database does not appear, right-click the Databases node and select Refresh….