Microsoft Systems Management Server 2003 : Maintaining the Database Through Microsoft SQL Server - SQL Server Components

9/15/2012 2:57:27 AM
In this section we’ll review some basic terminology and see how it relates to the SMS database. Every entity we call a database actually consists of two components: the database and its transaction log. The database is a collection of data records, object tables, and indexes organized in a specific structure designed to facilitate the displaying, sorting, updating, and analysis of the information it contains. The transaction log is used to record each action performed on the database, such as adding a new record or updating or deleting an existing record.

SQL Server 7.0 and SQL Server 2000 maintain their database and transaction logs in their own files. If SQL Server is installed on the same computer as SMS 2003, SMS can create the database and log files for you during its setup. If not, you’ll need to create the files in advance.

If you install SMS on the same computer as SQL Server, SMS will not only create the devices for you, but it will also tune SQL Server for use with SMS. This feature doesn’t, of course, relieve you of all responsibility in the maintenance of the server running SQL, but it does ease some of the setup concerns regarding SQL Server, especially if you’ve had little experience with it.

During the setup process for SMS 2.0, if SQL wasn’t already installed on the proposed site server, the SMS 2.0 installation process prompted you for the SQL Server source files and installed a dedicated SQL Server database for itself on that same server. The SMS 2003 installation process doesn’t do this. Therefore, you’ll need to have installed SQL Server before running SMS setup. If you follow the basic instructions that come with SQL Server and accept all the default values, choosing Windows Only Authentication as the security option, you’ll be just fine for SMS.

You can accomplish most of the actions you’ll need to perform on the server running SQL through the SQL Server Enterprise Manager. Through this console, you can create databases and transaction logs, set security, back up and restore the database, perform routine database maintenance tasks, and optimize SQL Server parameters for the SMS database. Let’s explore the process of creating devices in using SQL Server 2000.

Creating a Device in SQL Server 2000

The SQL Server Enterprise Manager, shown in Figure 1, is a Microsoft Management Console (MMC) snap-in. It groups its managed objects into five main categories:

  • Databases

  • Data transformation services

  • Management

  • Security

  • Support services

Figure 1. SQL Server Enterprise Manager.

SQL Server 2000 doesn’t require the creation of separate devices before the database can be generated. Instead, it requires the creation of database files that will contain both the actual database objects and the transaction log data. Follow these steps to create a SQL Server 2000 database file:

In SQL Server Enterprise Manager, navigate to the Databases folder, right-click it, and choose New Database from the context menu to display the Database Properties dialog box, shown in Figure 2.

Figure 2. The Database Properties dialog box.

In the General tab, enter a name for the database, such as SMS_xxx, where xxx represents the site code for your new site.

The database file name will appear as SMS_xxx_Data in the Database Files list in the Data Files tab, as shown in Figure 3. You can click the ellipsis (Browse) button (found in the Location column) to display the Locate Database File dialog box where you can modify the location of the file. In the Data Files tab you can also change the initial size through this entry.

Figure 3. The Database Properties Data Files tab.

In the File Properties section, the Automatically Grow File option is enabled by default. This option ensures that SQL Server monitors the size of your database and expands it as necessary according to the File Growth parameter you specify. You can also allow the growth to be unrestricted or set a maximum size.

Select the Transaction Log tab, shown in Figure 4, and configure the same parameters for the transaction log. The transaction log file will be named SMS_xxx_Log.

Figure 4. The Transaction Log tab.

Click OK to create the files.

SQL Server 2000 considers the database and transaction log files combined as representing the database and displays a single database entry.

  •  Microsoft Visual Basic 2008 : Processing and Storing Data in SQL Server 2005 - Optimizing the LINQSQL Class
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 6) - Disconnected Data Access
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 5) - Direct Data Access - Updating Data
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 4) - Direct Data Access - The DataReader
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 3) - Direct Data Access - Creating a Connection, The Select Command
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 2) - SQL Basics
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 1) - Understanding Databases, Configuring Your Database
  •  SQL Server 2008 : Failover clustering - Installing a clustered SQL Server instance
  •  SQL Server 2008 : Failover clustering - Clustering topologies and failover rules
  •  SQL Server 2008 : Failover clustering - Clustering overview
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us