Touring the SQL Server System Databases


This section discusses how SQL Server manages itself and the databases installed thereon. All versions of SQL Server[2] implement the same (basic) core functionality and depend on the same four system databases. It turns out that SQL Server uses these internal databases behind the scenes to manage all other databases installed on the server. Using these databases, SQL Server keeps vital Data Description Language (DDL) information about each database, login, and every other database object in the system and user databases. This section takes a closer look at these databases, as well as how they are exposed to the network or your local application.

[2] Remember, "SQL Server Everywhere" is not really SQL Server at all.

SQL Server Instances

Several separate SQL Server "instances" can be installed on a single systemlimited only by your licenseeach of these separate installations is referenced by name using a standard syntax of "<Machine Name>\<Instance Name>" in addition to the default instance, which is referred to by just the machine name. Yes, each of these instances consumes space on your hard drive and (considerable) space in RAM, so, no, it's not a good idea to install too many instancestwo or three should be more than enough for most systems.

I took questions at a newsgroup where the members asked if they could get around the Express Edition limitations by installing another instance (or several instances). I said, yes, it's possible to have a single system handle more load using more than one instance, but it's a pretty silly way to get around the issue. It would make more sense to dedicate several systems to the Express edition instead of loading up a single processorbut even that's a bit silly. The Workgroup edition costs less than another system.


If you don't name a SQL Server instance but choose to configure the "default" instance, SQL Server's name is set when you build your computer systemit's the Windows machine name. While it's easy to rename your machine, remember that SQL Server won't know you've changed its name until you tell it you've done so. Figure 2.12 shows you how.

Figure 2.12. Telling SQL Server your system has been renamed.


Multiple Instances or Multiple Databases?

Each server instance can manage up to 32,767 databases, but generally, I don't ask a SQL Server instance to support more than a few databases, at most (and usually only 1 or 2) for performance reasonshardware is simply too cheap. Multiple instances are useful when it's necessary to create an application that depends on a dedicated SQL Server managing a unique database that can best be controlled by the application. Multiple databases on a single instance can be more efficient, as the server can more easily share resources (if they exist) between databases. While the engines can pass resources between "linked" server instances, it's pretty expensive to do sobut it can be an effective way to execute JOIN operations across diverse DBMS platforms. See "Linked Servers" in BOL for more details.

If you create an application that needs to store data on the local client workstation, you can include a quiet SQL Server (Express or other versions) setup in the application setup routines so the server instance is installed right on the client's system. Since there might already be an existing instance of SQL Server installed on the system, your application setup logic can choose to create a separate instance of SQL Server that does not affect the current instance(s). This new instance gets its own set of SQL Server support databases stored in their own files (see Table 2.1) and its own system administrator (SA) password. This also means you can back up, restore, or uninstall your application and SQL Server instance without disturbing the other instances.

Table 2.1. System and Application Databases Installed by SQL Server Setup

Database

Purpose

Default Size

master[3]

To manage the database descriptions, security login information, and other system-level information about the databases stored in this SQL Server instance. Also include here are stored procedures used to manage and protect all databases. Master is used to store all Login account and password information. It must always exist on any SQL Server instance.

11.0MB data, 1.25MB log

model

Used as a template for all new databases created on this SQL Server instance. The model database is re-created each time SQL Server starts. It must always exist on any SQL Server instance.

0.75MB data, 0.75MB log

tempdb

SQL Server's "scratchpad". It's used to hold temporary tables and stored procedures, as well as work with tables, cursors, and sorted rowsets on a connection-by-connection basis. It's also possible to create "global" temporary tables here that are visible to all connections. The tempdb database is re-created each time the SQL Server is started (from model), so nothing remains in tempdb between sessions. As connection sare dropped or "reset," the contents of tempdb are purged of anything created by the connection so applications should not see anything left over from a previous connectionunless you disable the "reset connection" feature. (I show how to do this in Chapter 9.)

Built from model

distribution

Used by the replication components of SQL Server, such as the Distribution Agent, to store such data as transactions, snapshot jobs, synchronization status, and replication history information. Any server configured to participate either as a remote distribution server or as a combined Publisher/Distributor has a distribution database.

Varies

Msdb

The msdb database is used by SQL Server, SQL Server Management Studio, and SQL Server Agent to store data, including scheduling information and backup and restore history information.

4.69MB data, 0.09MB log

ReportServer

Reporting Services catalog. Used to manage the reports and all aspects of Reporting Services.

At least 3.88MB data, 1.04 log


[3] Notice that these database names are all in lowercase. This is relatively unimportant unless you install your DBMS as "case-sensitive."

The recommended approach is to leverage the SQL Server Express (SQLEXPRESS) instance, if it exists. This reduces the complexity of the install and imposes less of a burden on the client system. Remember, each instance consumes memory, CPU time, and disk space.

Remember that if the User Instance feature is enabled and your application requests this option, separate and independent support databases are copied to the current user's Documents and Settings folder hierarchy. This approach permits applications to access the "user instance" of SQL Server as SA using the user's Windows credentials. I discuss the mechanics of this approach in vivid detail in Chapter 9.

User and System Databases

There are two types of databases managed by SQL Server"system" and "user". Both can be managed in varying degrees by SQL Server using the UI-driven tools such as Visual Studio, the SQL Management Studio, and your own code using .NET Framework classes and SQL Server Maintenance Objects (SMO). User databases contain your data as well as the support infrastructure used to protect, index, and store the data. System databases are created automatically when you install or start SQL Server, as described in Table 2.1.

Creating a User Database

It's not hard to create your own SQL Server user database using the tools that come with SQL Server. Check out SQL Server Management Studio (or the Express edition's SQL Server Management Express) and right-click on "Databases" in the object explorer. Choose "New Database..." to create a new user database, as shown in Figure 2.13.

Figure 2.13. Creating a new SQL Server database using SQL Server Management Studio Express.


All versions of Visual Studio also support the ability to create new SQL Server databases, as I describe in Chapter 5, "Managing Executables with the Server Explorer"some have more choices than others. In most cases, creating a new user database is as easy as creating a new Database project or Data Source and feeding answers to the wizard. While I rarely find the need to use the SQL Server dialect of SQL (Transact SQL [T-SQL]) to create databases, if you find a compelling need to do this and want to impress your friends, check out the T-SQL statement CREATE DATABASE in Books Online or Chapter 3 where I describe how to create your own databases and populate them with user objects like tables, views, and stored procedures.

When your server starts, it copies the model database to tempdb. That's fine, except when you have queries that rely heavily on tempdb. For example, when you use ORDER BY to sort the rowset, SQL Server uses tempdb as a scratchpad. If it has to stretch the size of tempdb as it builds the sorted rowset, initial performance can suffer. To resolve this issue, determine the size of tempdb after your system has run the procedures that use it. Next, set the size of the model database to make sure tempdb starts at this larger size.


Managing User Databases

If you use SQL Server Management Studio, you'll see that the System databases are broken out from User databases on each instance of SQL Server to which you connect. You might also see two ReportServer databases listed among the user databases. Depending on which sample databases you install (and this book's DVD includes many of these), the list of user databases might be quite long.

Once you attach or otherwise create your own user databases (as I'll show you in Chapter 3), these are logged into the master database. At this point, these user databases become "known" to SQL Server and visible to authorized login accounts and T-SQL queries. Each database stores its own DDL schema information, but the master database contains any "global" properties that apply to all databases and the security credentials required to access them. Whenever you add a new database or change the schema of an existing database, be sure to back up the master database soon thereafter. This will help when you need to rebuild your server after the flood, fire, or visiting in-laws. Before talking about creating user databases, I need to touch on how SQL Server protects itself and the data you ask it to store.




Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net