SQL Server is all about databases, and EM is all about managing them. Using EM, it is incredibly easy to create and manage your databases. Creating a database is, if anything, too simple in EM. For example, administrators agonize for weeks over the creation of an Oracle database, carefully planning log and data file placement and setting recovery and performance options. Then they fire up EM, create a database accepting all the defaults, and complain that SQL Server is slower than Oracle. Microsoft has provided defaults ”such as the path to data and log files ”to simplify database creation, but in most cases, the defaults are not the best option. Before creating a database with EM, read Chapters 11 and 39, "Database Design and Performance," to get an insight into optimizing your database.
Creating and Modifying Databases
To create a database in EM, expand the server where you want the database created, right-click the Databases folder, and select New Database. This will bring up the Database Properties window, as illustrated in Figure 4.6.
Figure 4.6. Creating a database.
From the three tabs in the Database Properties window, you can configure the database name and collation, as well as data and log filenames, size, placement, and auto grow parameters.
After a database has been created, it can be modified by right-clicking the database and selecting Properties. Figure 4.7 shows the Options tab of the Database Properties window.
Figure 4.7. The Database Properties window.
In addition to changing the database options, you can alter or add log files, data files, and file groups, as well as manage permissions on the database.
Backup and Restore
Backup and restore options can be accessed on each database by right-clicking the database and selecting All Tasks. The resulting pop-up menu includes the options Backup Database and Restore Database. Selecting one of these options brings up the corresponding dialog box. For example, the Backup dialog box allows you to perform dynamic full, differential, log, and file or filegroup backups ; schedule a backup; create backup devices; and set backup options. Chapter 16, "Database Backup and Restore," covers this topic in detail.
Working with Database Diagrams
Enterprise Manager has the ability to create an Entity Relational Diagram, or ERD, which allows you to not only view, but also to modify your database layout. To create a new database diagram, right-click the database, go to New, and select Database Diagram. This will start the Database Diagram Wizard, allowing you to choose which tables to add to the diagram. Figure 4.8 shows the Table Selection window of the Database Diagram Wizard.
Figure 4.8. The Table Selection window of the Database Diagram Wizard.
After the tables have been selected, click Next and Finish to create the diagram. Figure 4.9 shows the completed diagram.
Figure 4.9. The database diagram.
Note that in the preceding diagram, the jobs table is displayed differently from the rest. By selecting one or more tables, a right-click brings up the various options available in the diagram window. In this case, I have selected to display the jobs table in Standard view, which displays the column properties, and I have added a column named Job_Title to the table. When changes are made to a database diagram, they are stored in memory until you select Save. Even though these changes are in memory, it is shared memory, so they are visible to anyone else working in a database diagram. When you save the changes to the diagram, they are also made permanent in the database. If you want to defer this to a later time, choose Save Change Script instead of Save. This saves the changes as a script, which can be run later and which also serves as a record of revisions. After the script is saved, exiting the database diagram window and answering no when requested to save your changes will prevent the changes from becoming effective until you run the script.