In this chapter we’ll examine the basics of the relational database structure. We’ll define a relational database management system (RDBMS) and how one can be used to structure data relationally. While there are many other widely used powerful RDBMSs, in this book we’re going to focus on that of Microsoft’s latest incarnation – SQL Server 2008. We’ll examine the power of Microsoft’s implementation while keeping with the general ease of use seen with many other Microsoft products, how Wizards can be used to simplify almost all administration tasks, and finally we’ll examine the Command Line Interface (CLI) distributed with SQL Server 2008.

Note that in this chapter we’ll keep it fairly simply and spend much of the time focusing on the GUI management interface; however the remaining portion of the book will focus more on Transact-SQL statements and commands as opposed to the GUI wizards.

Database Systems and Applications

A very important concept which we must understand is what roles a Database Management System (DBMS) play in the full scope of an application. The database itself isn’t an application, nor does the database or Microsoft SQL Server communicate directly with the end-user. In fact, the end-user of an application should be utterly ignorant of which Database Management System (DBMS) is actually being used. While we’ve yet to examine a DBMS, and we’ve yet to examine anything relating to a relational database, let us first define the several layers of a data-driven application, these can be easily expressed in the figure below.

image 
Figure 1.1 – Database Place

The front-end application is a specific data-driven application, this can be any number of different applications, perhaps an ASP.NET e-commerce application, or a desktop employee database application, regardless, the application will not communicate directly with the DBMS via any other low-level languages, it will communicate with the DBMS using SQL, which we will soon discover is a query language used by our applications to update, insert, or retrieve specific data from the database.

The task of the Database Management System (DBMS) is to manage data stored in a database. The DBMS does this in a number of different ones, one of which is the way data is retrieved, no data shall be manipulated without using the DBMSs query language, this allows the DBMS to apply security and data integrity to all data in the database. The DBMS also views data differentially than an application, or a human may view the data. For example, consider that of an employee database, we think of this data as employee names, identification numbers, departments for which they work, etc. The DBMS on the other hand simply views this data as a set of binary data relating to one another via specific rules.

The DBMS offers different levels of data separation and protection, for example consider the following brief list.

  • Data Independence
  • Security
  • Integrity

Note: We will examine a complete list of rules and features of a DBMS in the following section when we discuss more specific technical details.

Data Independence

The front-end applications and users can act independently of physical and logical data. For example, consider the use of a flat-file database structure, which in other words is a simple binary file where data is stored in a specific way, no management system is in place, from within applications we simply append or alter data. Now consider the structure of the data has changed slightly, perhaps an additional data value is required, all applications making use of this flat-file database must now be changed to reflect the addition of the new data. While on the other hand, a database being managed by a DBMS delivers us a level of independence in that we needn’t alter any application code. To better illustrate this point, consider an employee database, the column birth_date is added to a table, the addition of this column won’t affect any applications making use of this table. While in our flat-file database example, the physical file has been altered and thus must be reflected in any code which makes use of this file. This level of data independence is considered logical as simply the logical structure of the database has been altered. Note that we also receive physical data independence, meaning that regardless of the alteration of the physical file or storage mechanism any applications relying on this database needn’t be altered.

Security

The DBMS also offers a level of customizable security, meaning that any unauthorized access or abuse will be denied. For example, consider that only specific users are allowed to retrieve an employee’s pay grade from the employee’s database, this can be a very simple task with a DBMS.

Integrity

A DBMS also offers a level of data integrity. For example an invalid date being specified within a column of DATE will be rejected, or even an invalid employee ID being specified. Perhaps a duplicate ID or the deletion of a data record where other data relates to that record, etc with be denied.

These concepts and more will be discussed in greater technical detail when we analyze the 12 rules for a DBMS defined by Dr. E. F. Codd in his work titled “A Relational Model of Data for Large Shared Data Banks” in the next section.

Relational Databases, an Understanding

We live in a data oriented world, where information and knowledge is power. In such a world where data is of such importance, we need a way of organizing and storing data of all kinds, from airline reservations, bank account history, to something as simple as the results of a baseball or football game. But how can we organize such data, you may be asking (or not) and the answer is a database, which can be summarized as a self-defined collection of organized data. That said; let us begin to look at the primary database model in use by many commercial Database Management Systems (DBMS) – the relational database model.

Overview

The most popular database model, sponsored by IBM in 1970 came to light when Dr. E. F. Codd published his work titled “A Relational Model of Data for Large Shared Data Banks” which proposed relational algebra as the basis for the database model and query language. In this model, data is to be represented as mathematical n-ary relations, an n-ary relation being a collection of distinct objects considered as a whole. Mathematically speaking, the core relational building blocks are the domain which can be defined as the data type, the tuple which is an unordered set of attribute values (note that in mathematics a tuple is defined as a set of objects in a specific order, but Dr. Codd introduced the concept of an unordered set of n-tuples), and attributes containing the attribute name and type name. In other words, the relational data consists of a heading and a body, the heading is defined as the domain attributes, which is further defined as an ordered pair of names and types, for example UserID is the name and int is the type and the body is a set of n-tuples defined as the attribute values (or data). That said a table is the visual representation of a relation (remember, n-ary relations defined above as a collection of distinct objects considered as a whole, hence the term database. The database consists of multiple relations or tables) and a tuple being a row, n-tuples defining n number of rows within the relation.

Note: Attribute values are defined as valid values based on the type definition (int, char, varchar, etc).

Note: A mathematical Set can be defined as a collection of distinct objects considered to be one. For example, a relation, while we speak of this as one object, it does however contain n-tuples and in fact is not a single object, another example can be the definition of a table, this table contains multiple rows and again is in fact not just a single item.

Further note that a tuple, while very similar to a Set, it however can contain duplicate objects.

Dr. Codd defined 12 rules for the relational model and the Data Management System (discussed below), these rules include:

  1. The Information Rule – All information in a relational database (including table and column names) should be represented as values in a table.

In a nut shell, the Database Management System (DBMS) consists of several system tables (or relations), which define the other relations and database objects being managed by the DBMS. MS SQL Server has the database titled master which consists of a number of different tables and objects; one table in particular is named sys.databases. This table consists of every database associated with the current MS SQL Server instance. There are other tables as well, ones which store all data for the tables within a specific database. This table is sys.tables and is a system table of every database object. These objects can be queried using a Data Manipulation Language command, SELECT.

  1. The Guaranteed Access Rule – All values in a relational database is guaranteed to be accessible using a combination of the table name, primary key value, and column name.
  1. Systematic Treatment of Null Values – The DBMS (Database Management System) provides systematic support for null values (unknown data), distinct from default values, and independent of any domain (or data type, discussed above).

What this rule states is that the NULL value is distinct from any other value and doesn’t belong to any domain (or datatype). The NULL value is simply used to represent unknown or missing data and doesn’t represent any other value.

  1. Dynamic Online Relational Catalog – The description of the database and its contents is represented at the logical level as tables and can therefore be queried using a query language (SQL for example).
  1. Comprehensive Data Sublanguage – The database must support at least one language and must have a well-defined syntax. It must also include functionality for data definition, data manipulation, data integrity, data authorization, and data transactions.

These sublanguages can be compared to that of the Data Manipulation Language, Data Definition Language, etc.

  1. View Updating Rule – Data should be presentable in various logical combinations, called views and all views should be theoretically updatable via the DBMS.

MS SQL supports views which can be thought of as either a virtual table or a stored query. The data presented in a view is not stored in a distinct object but is rather a combination of various other objects presenting a single display. What this rule states is that data should be updateable via the view, for example, consider the view has a column named user_id, the column, while perhaps not the column name of the real table, can be referenced in the update statement. Views are a great way of limiting and controlling different aspects of data, we will discuss this in great detail later.

  1. Set-Level Insertion, Update, and Deletion – The DBMS supports not only set-level retrievals but also set-level inserts, updates, and deletes (remember a set, in this case, is actually a relation or table).

In other words, table level update, insertion and deletion.

  1. Physical data independence – Application programs and ad hoc programs are logically unaffected when physical access methods or storage structures are altered.

Above we briefly discussed data independence. Again, physical data independence means that an application or program which relies on data managed by a DBMS should be unaffected when the physical database file is altered or the storage medium is changed. It’s the DBMS which will cope with any physical data changes, the applications which rely on the database needn’t be aware of any physical data changes.

  1. Logical data independence – Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table structures.

Again, recall that logical data independency refers to that of the logical data structure. When data is changed to a table structure any applications which rely on the database shouldn’t be affected.

  1. Integrity independence – The database language must be capable of defining integrity rules. They must be stored in the online catalog, and they cannot be bypassed.

Above we discussed briefly data integrity, recall that this comes in the way of, as the rule states, defined integrity rules which ensure that specific data falls within the scope of valid data. Again, perhaps a column has a rule defined indicating that an integer value can’t be larger than 100 and an attempt to write a value larger than 100 to this column is made, an error should be thrown rejecting this attempt. This rule also states that the integrity policies can’t be bypassed, this is the case as making use of the DBMSs query language should be the only way in which data can be manipulated.

  1. Distribution independence – Application programs and ad hoc requests are logically unaffected when data is first distributed or when it is redistributed.

The data manipulation sublanguage of a DBMS must enable application programs and queries to remain logically unchanged whether and whenever data is physically centralized or distributed/redistributed.

  1. Nonsubversion – It should not be possible to modify the database structure via a lower level language as to not avoid the integrity rules defined by the database language (SQL for example).

As we’ve noted several times when discussing data integrity. This rule is in place for exactly that reason, it makes it rather difficult to enforce specific integrity and security policies if requests are coming from various different lower level programming languages. However, forcing all data manipulation to be made through the DBMSs query language facilitates the enforcement of integrity and security.

The relational database has many advantages, and as I said above, it is the most popular database used. One advantage of a relational database is that you can modify the database structure without effecting any applications that were built on the original structure, as previously noted by the defined rules. For example, suppose you add one or more new attributes (or columns) to a relation (or table); there is no need to modify any applications dependent on this relation, unless of course you remove or modify an application dependent attribute.

Representing a relation graphically is very simple and obvious as most people are familiar with the concept of a table, for example, electronic spreadsheet programs, such as Microsoft Excel. However, to reaffirm our understanding, let us now graphically represent all registered users on our web site using a relation (or table). Figure 1.1 shows a relation defining exactly this.

image 
Figure 1.2 Sample Relation

As you can see, there are very little registered users, but more importantly there are three domains (or types) defined, let’s assume all of which are of type varchar (variable length character) and the relation contains two tuples or rows.

What is a Database Management System?

A Database Management System (DBMS) is a program defined to administer and manage databases, hence the 12 rules listed above, we need a management system to ensure that those rules are not broken. As Dr. Codd also pointed out in his editorial, users of such database systems need a level of indemnification, we need not understand how the actual data is structured and held on the hard disk, we need only to rely on the DBMS and the supported query language (in this case, SQL) as an interface to managing the database(s).

There are many different Database Management Systems (DBMSs) in circulation today, ranging from smaller systems running on personal computers to larger systems running on mainframes. Although Dr. Codd was working for IBM research at the time of his publication on relational database systems, ironically IBM was not the first to introduce a relational DBMS; the honor goes to the then small company who named their product Oracle. However, the particular DBMS we will examine and use throughout this book, as the title may suggest, is Microsoft’s latest incarnation – SQL 2008, which we will soon point out, while the fact that this DBMS runs on top of Windows, is quite powerful and in my opinion a top contender, hands down.

As stated above, there are many DBMS in circulation, but what sets one DBMS apart from another? The answer is not as simple as the question, it depends on the technology of the DBMS you wish to compare, for example, Microsoft SQL, MySQL and Oracle all use relational database designs (which is what we have been discussing), although it’s possible for DBMSs to use other designs, one of which is very simple – the flat file design, meaning that all data is stored in ASCII format in one or more files. Essentially flat file database models consist of strings that can be programmatically parsed and generally only a viable solution when dealing with small amounts of data. The relational database on the other hand is much more complex and logical; the database file is stored in binary format on the physical disk. The data in a relational database can act as individual objects, called relations (or tables), as we have already noted. Although, the logical aspect of such objects introduce themselves when we examine the term relation, which defines the fact that they can have relationships with other relations. Just as you can have relations with other people; let us examine this concept graphically in figure 1.3 below.

image
Figure 1.3 – Table Relations

As you can clearly see, relations Grades and Family are related to the Student relation. This has been done purposely due to design concerns, for example, if these relations were not related, we would be required to generate duplicate data so that Bob’s grades and family members could be distinguishable from Mary’s grades and family members. For example, without these relations being related we would have tables constructed as follows:

Student (StudentID, FirstName, LastName)
Grades (FirstName, LastName, Grade)
Family (FirstName, LastName, Father, Mother)

We duplicate the first and last name in every relation due to the fact that they are not related to each other, but now if we were to relate them, we could construct our tables as follows:

Student (StudentID, FirstName, LastName)
Grades (StudentID, Grade)
Family (StudentID, Father, Mother)

Obviously there are different patterns we could have chosen, but the advantages are clear, the goal of a well designed database should be to not duplicate any data, as duplication of data will become a management nightmare, if for example we were to update a student’s name in the above example, we would also be required to update that name in any relations where that name occurs, but, the second example only requires that we update the students name in a single relation. Another reason we should not duplicate data is unmanageable file sizes, in a large database, duplication of data can create unmanageable database sizes and hog systems resources, also doing query joins (which we will examine later, however is the technique of joining data from one or more relation based on a given key) on multiple large relations can be CPU intensive as well. This must all be taken into consideration during the design phase of your relational database.

Ok, where were we? Yes, back to the initial question, what sets the various DBMS apart from one another? If you compare relational database systems, then the answer can be as simple as coast, scalability, performance, hardware requirements, etc. It all dependent on your particular needs, if a freely available database is a requirement than the Express Edition of Microsoft SQL Server 2008 can be used; however a database size limit of 4GB is incurred.

And, without further ado, let’s introduce the star of our show, Microsoft SQL Server 2008!

Microsoft SQL Server 2008

Microsoft SQL Server 2008 contains quite a few new features over its predecessor, SQL Server 2005. Features such as support for FILESTREAM objects, which allow for the storage (or rather management, as filestream objects are stored within the file system and streamed by SQL Server 2008, as the name suggests when requested) of unstructured data within the database, improved XML support, the 8K limit on user defined data types no longer exist, a larger number of columns per table is also possible (100,000 columns and 3000 indexes), full text indexing enhancements, among many other improvements which will touch on in detail later. However in this section we’re going to focus on the development and construction of database objects using the Microsoft SQL Server 2008 Management Studio interface and the sqlcmd command line interface for those die hard CLI people.

First and foremost, before we begin tearing through the SQL Server Management Studio (SSMS) interface, let’s discuss a little bit about Microsoft SQL. While it’s true, many larger corporate environments still favor that of a UNIX platform the advances and technology found in MS SQL is nothing less than amazing, however, which may be seen as a downside to many, MS SQL can be run only on a Windows platform, that is, Windows Server 2003/2008, XP/Vista, etc, however what may seem as a disadvantage, in reality can become quite an advantage as the interface exposed via SSMS is really very flexible. As an administrator or developer we can take advantage of the GUI Wizards and other simplicities found within the Windows Operating System to define, build, customize and maintain our database infrastructures, while on a UNIX platform the interface is, generally much more complex. However, don’t confuse simplistic with a lack of features or power, MS SQL Server 2008 has just as much horsepower and features as any other enterprise level RDBMS, in fact, if you’re a .NET developer you’ll find the integration between C#, VB.NET, ASP.NET, etc quite attractive and powerful.

Microsoft SQL Server actually originated as a collaborative effort between Microsoft, Sybase, and Ashton-Tate. The first version named SQL Server 1.0 was released for OS/2 in about 1989, which was essentially the same as Sybase SQL Server 3.0 on UNIX. Eventually Windows NT was released and OS/2 support was dropped. SQL Server 6.0 was the first version of SQL Server designed specifically for Windows NT without any direction from Sybase.

Fast forwarding to today, we have a highly scalable enterprise level RDBMS with SQL Server 2008, supporting many different features benefiting the .NET developer with advanced user defined data types and other features noted above. Perhaps one of the most attractive features to the beginner is its ease of use and management via the SQL Server Management Studio GUI interface, which is exactly what we’re going to analyze now. Assuming you’ve already installed SQL Server 2008 (Express Edition is fine) and the SSMS has been installed, when first launched, you should be prompted with the connection dialog box requiring that you specify a server host address, instance name, and login credentials to the specified server. You may choose from either, Windows authentication or SQL Server authentication, however, note that unless otherwise specified during the setup phase of SQL Server, the only available SQL user account – sa is assigned a random password and is disabled, also note that Windows Authentication is the only available authentication mode enabled during a default setup.

Note: That SQL Server as of SQL Server 2000, has supported what are called named instances, as you might have guessed, allowing for multiple instances of SQL Server to run on a single machine. Only one instance of SQL Server on any machine can be installed under the default instance (referenced simply as HOSTANAME), thereafter any new instances must be named, and these named instances can be referenced as HOSTNAME\INSTANCE. Sounds good, but what’s the point of multiple instances? One practical use of multiple instances is application separation, for example, let’s suppose you have multiple applications relying on a database, you could separate each application into its own SQL instances, another use could be to run multiple versions of SQL simultaneously, maybe SQL 7.0, 2000, 2005 and 2008, however in this example, SQL 7.0 will use the default instance (as it has no other options) and 2000, 2005, and 2008 will become a named instance. Although, you must take into consideration that multiple SQL instances mean more system resources in use.

The SSMS connection dialog box should look similar to following figure.

Figure1.4 
Figure 1.4 – SSMS Connection Dialog Box

Note: While I am using a CTP (Customer Technology Preview) release of SQL Server 2008, note that the technology, screenshots and principals discussed herein are identical to that of the final release version.

Each available option is as follows.

  • Server type: Here you can select the service in which you wish to connect, such as the analysis service, reporting service, etc. The default option is Database Engine and indicates that we wish to connect to the RDBMS itself.
  • Server name: The hostname or IP address followed by the optional instance name. In the note box above I stated that default installations will make use of the default instance, meaning that we need only specify the hostname in this box, if however, SQL Server has been installed with an instance name, we should specify that instance as follows: HOSTNAME\INSTANCE.
  • Authentication: The current authentication method. You can choose from two different authentication modes – Windows authentication and SQL Server authentication. The latter is, by default disabled, as stated above, the default administration account is named sa and again by default has a random password set and is disabled. During the installation of SQL Server you may wish to enable mixed mode authentication and set the sa password, however in normal circumstances Windows authentication should suffice. Also note that during the setup you may specify the Windows accounts who have access to the SQL Server. Using the SSMS you may alter the valid user accounts and enable/disable mixed mode authentication as we will soon discover.
  • Username: If SQL Server authentication has been selected, this should be the SQL Server user account name.
  • Password: If SQL Server authentication has been selected, this should be the password for the previously specified SQL Server user account.

It’s also possible to specify addition options which affect the connection to the indicated SQL Server. Notice the Options button in the lower right of the connection dialog box. Once clicked, you will be presented with additional features shown in the screenshot below.

Figure1.5 
Figure 1.5 – SSMS Connection Properties

Connection to database: Here you can indicate the default database that SSMS should connect to once the connection to the SQL Server instance has been established.

Network protocol: Here you can indicate the network protocol that is to be used to establish a connection to the SQL Server instance. Microsoft SQL Server supports a few different network endpoint protocols, these endpoints include shared memory, TCP/IP and named pipe. Generally TCP/IP is used and port 1433 is the default port for a default instance. Named instances are configured with TCP dynamic ports, with a port number being assigned by the operating system. We’ll discuss the various network protocols later, for now, note that shared memory is a secure connection method for applications on the same machine and cannot be used across a network. TCP/IP is, as you may have guessed, used for connection across the network using standard IP address and port numbers. Named pipes on the other hand allow for connection across the network and the actual protocol used is dictated by the client by indicating the path. A path would look something similar to: \\<computer_name>\pipe\MSSQL$<instance_name>\sql\query.

  • Network packet size: The size of each network packet. Packets are the fixed-size chunks of data that transfer requests and results between clients and servers. For operations where large data results may be transmitted, a larger packet size may improve efficiency as fewer network reads and writes are required.
  • Connection time-out: The maximum number of seconds before the connection attempt is timed out.
  • Execution time-out: The number of seconds to wait for before a query execution is timed out.
  • Encrypt connection: As you may have guessed, this option can be used to encrypt the SQL Server connection.

After successfully establishing a connection to a SQL Server instance, you can begin navigating the SSMS interfaces to create new databases, modify existing databases, create relations (tables), modify relationships on tables, etc (this is the interface which communicates directly with the underlying DBMS). You can either accomplish your desired tasks using the GUI interface in SSMS or via the Query Editor, which is accessible via the New Query button located toward the top of the SSMS window. The Query Editor is basically a text editor with SQL query color highlighting and InteliSense support to help ease the script writing/editing phase. We will discuss the query language in detail later in the text, for now just note that everything we will do via the GUI interface can also be accomplished via a text query.

Note: The SQL Query Editor’s support for InteliSense is a new feature of SQL Server 2008 and is quite useful. For those of you who aren’t familiar with this, it’s been a feature of Visual Studio for quite some time and in short, simply suggests table names, column names, etc based on what you’ve started to type. The screenshot below demonstrates this.

Figure1.6 
Figure 1.6 – Query Editor InteliSense

Pretty neat, huh? Something that was very missing from SQL Server 2005, and more to the point, the current database from the above screenshot, does in fact have the table named Books! It sure makes you love writing SQL code! Oh, and I almost forgot, it also underlines anything that it sees as an error with a red line, much like Visual Studio or MS Word does when your document contains an error.

Let’s take a closer look at the SSMS GUI interface. We will then discuss some of the options available to you via this interface. Take a look at the screenshot below.

Figure1.7
Figure 1.7 – SSMS Interface

The SSMS interface may not look like it has much, however there are actually many different areas of this program we can explore to customize the SQL Server configuration, create, and delete database objects, tables, stored procedures, views, etc. The far left window pane is called the Object Explorer and contains all SQL Server instances with an established connection by SSMS. Notice that beneath each SQL Server instance is various objects associated with that particular instance of SQL Server – the databases, the security policies, management interfaces, etc. Before we analyze any of these objects let’s first analyze the different server configuration dialog boxes so that we can alter such settings as memory allocation, auditing and basic level of security such as the valid authentication modes, etc. Much of the settings altered via the dialog boxes simply invoke specific SQL statements, hence we’ll analyze both the GUI as well as the actual SQL statement executed.

Managing a Server using SSMS

Managing the currently connected SQL Server instance is actually quite simple, notice the parent object within the Object Explorer window pane, this is the representation of a SQL Server instance, the icon looks something like the typical database figure with a play button signifying that SSMS is currently connected to that instance (clip_image016). This same parent object also displays the hostname along with the version of SQL Server and the authenticated username (it’s slightly cut off in the screenshot above). It’s also possible for a single SSMS to manage multiple SQL Server instances, you may simply connect to another instance and it will be displayed within the Object Explorer window pane. Each SQL Server instance has its own set of database objects, security policies, etc and must be managed independently from one another. To initiate an additional SQL Server connection from within SSMS simply click the Connect button located directly below the Object Explorer title bar, select Database Engine and the SSMS connection dialog box, previously examined, will be presented, simply specify the correct details and that instance of SQL Server will then be visible from the Object Explorer window pane.

To manage a SQL Server instance and its configuration simply right click the desired instance from the Object Explorer window pane and select Properties. You will be presented with the Server Properties dialog box. This dialog box contains several different options, ranging from permission settings, connection properties such as maximum number of concurrent connections, to general security, and memory allocation among a few others. When the Server Properties dialog box is first opened, the option selected by default is titled General and simply displays general non-editable information pertaining to the SQL Server, such as system specifications, version, etc. Figure 1.8 below shows us just this.

Figure1.8
Figure 1.8 – Server Properties Dialog Box

Notice that the far left side of the dialog box contains three different sections, the first simply displays a list of different server configuration options you may select from, the second displays the current connection details, use this to ensure that you are indeed altering the desired SQL Server instance, and finally, the last is simply a visual indicator used to notify us of any actions currently underway.

Anyway, the General section of the Server Properties dialog box doesn’t need much explanation; let us move on.

Memory Configuration

The Memory section of the Server Properties dialog box contains options for optimizing the memory utilization and memory allocation for a SQL Server instance. Generally speaking, you probably don’t need to alter the default settings, never the less, we’re going to examine each option in detail. Refer to the screenshot below.

Figure1.9
Figure 1.9 – Memory page of the Server Properties Dialog Box

  • Use AWE to allocate memory: This option specifies that SQL Server will make use of Address Windowing Extensions (AWE) to allocate memory. This is valid only on 32-bit operating systems and enables the support for up to 64 gigabytes (GB) of physical memory. To use this option you must also configure Windows appropriately, which is beyond the scope of this book.
  • Minimum server memory: This option indicates the amount of memory SQL Server should allocate upon initialization. This amount of memory will not be released until SQL Server is shut down.
  • Maximum server memory: This option indicates the maximum allowable memory SQL Server may allocate when it starts and while it’s running.
  • Index creation memory: This option specifies the amount of memory to use during index creation sorts. Generally this option should be set to zero which indicates that dynamic memory allocation will be employed. On a side note, an index can be thought of as something much like that seen in a book. Consider that you wish to search for a specific key word within a printed manual. You can either manually examine each page of the book noting the page numbers where this specific word is found, or use the index of the book which simply lists each page number for that specific word. SQL Server makes use of indexes in a manner similar to this, without an index the DBMS must scan each row of a table until a specific criteria is met, however if an index has been created, it may simply look into the index and grab the corresponding data much quicker.
  • Minimum memory per query: The amount of memory that should be allocated for the execution of a SQL query. The default value is 1024 KB and can be set to a maximum of 2147483647 KB.
  • Configured values: This radio button simply indicates that the configured values for this page (memory) should be displayed.
  • Running values: This radio button indicates the running values for this page (memory). If you’ve reconfigured any of the values on this page and clicked the Apply button, select this radio button and the page will be changed to read-only allowing you to view the running values. If the values haven’t changed, the SQL Server instance must be restarted.

Processors Page

The Processors page of the Server Properties dialog box contains options for configuring the different CPU related options. For example, the maximum number of worker threads, processor affinity, I/O affinity, etc. Note that SQL Server 2008 Express Edition will make use of only a single processor regardless of the number in your machine. The screenshot below demonstrates what is shown on the Processors page.

Note: Processor affinity is a modification of the native central queue scheduling algorithm (the algorithm used to allocate processor time for specific threads or processes). By modifying this we may specify the physical or logical processor a thread or process should be executed on. For example, the operating system may move process threads among different processors, this, while generally efficient can reduce SQL Server performance as the processors cache must be repeatedly reloaded with data. Assigning processors to specific threads can improve performance under specific conditions by eliminating processor cache reloads and reducing thread migration across processors.

Further note that the I/O affinity is for the most part equal, however the I/O implying that the servers hard disk I/O is bound to a specific processor.

Figure1.10
Figure 1.10 - Process Page of the Server Properties dialog box

  • Automatically set processor affinity mask for all processors: This option allows SQL Server to automatically set the processor affinity mask.
  • Automatically set I/O affinity mask for all processors: This option, much like the previous one, allows SQL Server to automatically set the I/O affinity mask.
  • Maximum worker threads: The maximum number of SQL Server worker threads that are spawned. The setting of 0 indicates that SQL Server will automatically set the number of worker threads.
  • Boost SQL Server priority: Specifies whether SQL Server should run at a higher Windows scheduling priority than other processes on the same machine. If enabled, this option will allow Windows to spend more CPU time with SQL Server.
  • Use windows fibers (lightweight pooling): This option is available only on a Windows server operating system. Lightweight pooling is a method of reducing system overhead associated with process threads being moved between different processors (also called context switching).

Security Page

The Security page can be used to configure the SQL Server authentication methods, that is, Windows authentication or mixed mode authentication, enabling both Windows and SQL Server user accounts to be used. It’s also within this page that we may configure the auditing levels and enable advanced C2 audit tracing. The Security page is shown in the screenshot below.

Note: C2 Auditing is a very verbose level of auditing required to meet Class C2 security requirements. Class C2 was established by the U.S. National Computer Security Center and can be granted to products that pass the U.S. Department of Defense Trusted Computer System Evaluation Criteria tests. A C2 rating ensures the minimum allowable levels of confidence demanded for government agencies and other organizations that process classified or secure information. We will examine C2 Auditing in great detail when we examine security later in the book.

Figure1.11
Figure 1.11 – Security page of the Server Properties dialog box

  • Windows Authentication mode: This option indicates that only Windows accounts may be authenticated and used with SQL Server. This includes Active Directory user accounts.
  • SQL Server and Windows Authentication mode: This level of authentication allows both Windows user accounts to be authenticated as well as internal SQL Server user accounts associated only with this instance of SQL Server.
  • Login auditing: This section allows us to alter the auditing level for authentication only.
  • None: Indicates that no authentication level auditing should take place. This is not recommended.
  • Failed logins only: This method indicates that only failed login attempts should be audited. This is the default SQL Server selection.
  • Successful logins only: This method indicates that only successful logins should be audited. Again, this is not recommended as it makes it rather difficult to determine malicious authentication attempts.
  • Both failed and successful logins: Both failed and successful logins will be audited.
  • Server proxy account: This section can be used to enable the use of impersonation when system commands are executed. That is, it’s possible to invoke system level commands from within SQL Server’s Transact-SQL via the xp_cmdshell procedure. The user account specified within this section will be used to invoke the system commands. Ensure that the user account specified has the minimum required system-level permissions to avoid any system vulnerabilities.
  • Enable server proxy account: Check this box if you wish to enable the server proxy account.
  • Proxy account: The name of the proxy user account.
  • Password: The password used for the above indicated user.

The final section of this page, Options, allows us to alter advanced security properties.

  • Enable Common Criteria compliance: Enables Common Criteria compliance, which simply enables a set of security policies to be applied. We’ll discuss Common Criteria later in this book.
  • Enable C2 audit tracing: Enables C2 auditing logging and records them to a file in the \MSSQL\Data directory for default instances or the \MSSQL$instancename\Data directory for named instances of SQL Server.
  • Cross database ownership chaining: Enables cross ownership chaining. Employing the use of ownership chains may offer a slight performance boost as SQL Server alters the way in which permissions are evaluated on objects within a chain. A chain is built by multiple database objects accessing each other sequentially. To better illustrate ownership chains, consider that the user account Joe has permissions on every database object in a specific database, while the user Bob has been granted only permissions on a view. Once Bob performs a query against the view, SQL Server evaluates its permissions, the view then selects data from other tables, because the owner of the view and the other tables are of the same user – Joe, SQL Server does not evaluate Bob’s permissions any longer and it takes for granted that Joe has intended for Bob to select this data. Now, cross database ownership works in exactly the same fashion, consider that Joe is also an owner of a database object (table) of another database, if cross database ownership is enabled, and the view in which Bob has executed performs operations on this cross database table, permissions on this table will not be checked and the results returned.

    Cross database ownership chaining is discouraged as it opens the door for potential security threats and can be employed by highly privileged members of a database to access objects belonging to another database where they may not have such permissions.

Note: The Common Criteria for Information Technology Security Evaluation abbreviated Common Criteria (or CC) is an international standard (ISO/IEC 15408) and provides assurance that a product has been tested and in fact complies with a set of common security standards. The Enable Common Criteria compliance checkbox on the Security page of the Server Properties dialog box enables a set of elements which are required for CC. These elements will be discussed later in the book when we examine security.

We will examine security principals and best practices in more detail later in the book, including some of the potential security threats of cross database chaining. For now, I’d recommend that you not enable such things as cross database chaining.

Connections Page

The Connections page can be used to configuring the different properties relating to external SQL Server connections. We can limit the maximum number of allowable concurrent connections as well as the maximum allowable time before a connection is timed out. We may also configure SQL Server using this page to allow remote connections, that is, connections originating remotely. Take a look at the screenshot below.

Figure1.12
Figure 1.12 – Connections page of the Server Properties dialog box

  • Maximum number of concurrent connections: This option allows you to specify the maximum number of allowable concurrent connections. The default value, 0, indicates that an unlimited number of concurrent connections are allowed.
  • Use query governor to prevent long-running queries: Query governor can be used to limit the maximum amount of time a query can run. If a nonnegative value is specified, the query governor will disallow execution of any queries whose estimated cost exceeds that of the set value in seconds. If the checkbox is not checked, the query governor is turned off and all queries are allowed to run indefinitely.
  • Default connection options: Using any of the available options listed here, we can specify the default connection options. That is, the way in which the SQL Server connection will act by default. Refer to the table below for more details on each available option.

Connection Option

Description

implicit transaction

Controls whether a transaction is started implicitly when a statement is run. While we have yet to examine transactions, they are basically a means of tracking changes. For example, if a transaction is created, and data is updated across multiple tables, each SQL query is tracked and can either be committed or rolled back (all actions which have occurred within the transaction will be reverted to the original state).

cursor close on commit

Controls the behavior of the cursor after a transaction has been committed. A cursor, much like the cursor of a terminal screen is a position indicator. In SQL the position a cursor points to is a row in an active set, which is a query result obtained when a SELECT command is issued.

That said, with this option enabled, after a transaction has been committed, any open cursors will be closed automatically.

ansi warnings

Controls truncation and NULL in aggregate function warnings. For example, if a NULL value appears in an aggregate function (SUM, AVG, MAX, MIN, etc) a warning message will appear, if this option is turned off, no warning is issued.

Another case which is affected by this option is when divide-by-zero and arithmetic overflow errors occur, the statement(s) are rolled back and an error message is generated, however when off, NULL values will be returned and no error message generated.

ansi padding

Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

ansi NULLS

Controls NULL handling when using equality operators, that is, the SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a NULL value evaluates to FALSE. Thus when this option is on, and an equality operator is used within, for example, a SELECT statement using where column_name = null will return no rows as per the SQL-92 standard even if there are NULL values. However, if this option is disabled, the SQL-92 standard will not be used and will return only those rows with NULL values.

arithmetic abort

Terminates a query when an overflow or divide-by-zero error occurs during query execution.

arithmetic ignore

Similar to the previous option, however rather than aborting, a NULL value is returned when an overflow or divide-by-zero error occurs during a query.

quoted identifier

Differentiates between single and double quotation marks when evaluating an expression. This is on by default and defines that string literals must be delimited by single quotation marks and SQL identifiers can be delimited by double quotation marks.

no count

Turns off the message returned at the end of each statement that states how many rows were affected. By default, after a query has succeeded a message indicating the number of rows affected is displayed: (10 row(s) affected).

ANSI NULL Default On

New columns defined without explicit nullability are defined to allow NULL values.

ANSI NULL Default Off

New columns defined without explicit nullability are defined not to allow NULL values.

concat null yields null

Returns NULL when concatenating a NULL value with a string.

numeric round abort

An error is generated when a loss of precision occurs in an expression.

xact abort

Rolls back a transaction if a Transact-SQL statement raises a run-time error.


Table 1.1 – Default connection options

Note: Aggregate functions, referenced in the table above, perform calculations on a set of data and returns a single result, the set of data in which we speak is that of multiple rows within a table, for example suppose that we wish to determine the number of items sold on our ecommerce store, we may simply select all sold items in a table and use the COUNT aggregate function to count the number of rows returned from the invoice table, the returned value will be as you may have imagined – a single integer value indicating the number of rows matching our query. There are many other aggregate functions which we will examine later in the book.

The last section, remote server connections are pertinent only to remote connections and have the following available configuration options.

  • Allow remote connections to this server: If checked, this SQL Server instance will allow remote connections.
  • Remote query timeout: Specifies in sections the length of time before a remote query is timed out by the SQL Server. The default value is 600 seconds. A value of 0 indicates no timeout.
  • Require distributed transactions for server-to-server connections: This option protects the actions of a server-to-server procedure using a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. MS DTC is a service responsible for coordinating transactions across different servers. Put simply, we’ve roughly defined a transaction and we know that transactions can be used to ensure data integrity, for example, if an error occurs while writing data to any resource within the transaction we can simply rollback the transaction, thus restoring all resources to its original state. What MSDTC does is manage transactions across multiple servers, meaning that if we’re writing data across multiple different servers and an error occurs, we may rollback the transaction across all servers. Without MSDTC or another transaction manager this wouldn’t be possible.

Database Settings

The Database Settings page allows for a very general configuration of the basic database settings. Using this page you may configure such things as wait time for new backup tapes, whether or not compression should be used and the default data and log directories for database and log files respectively. Take a look at the screenshot below.

Figure1.13
Figure 1.13 – Database Settings page of the Server Properties dialog box

  • Default index fill factor: This option specifies how full Microsoft SQL Server should make each page when it creates a new index using existing data. To better understand this option we’ll have to discuss in greater detail indexes, however for now, the default value of 0 is fine.
  • Wait indefinitely: This option indicates that SQL Server should never timeout while waiting for a new backup tape during a backup operation.
  • Try once: This option indicates that SQL Server will timeout if a backup tape is not available when required.
  • Try for: This option allows us to manually specify the number of minutes SQL Server should wait for a backup tape before timing out.
  • Compress backup: As the name suggests, whether or not SQL Server should compress the backup.
  • Recovery interval: This option can be used to set the maximum time required for a database to be recovered. SQL Server will issue a checkpoint frequently which is run on a per-database basis and will ensure that all log information and modifications are flushed from memory to disk, thus reducing the recovery time needed when SQL Server starts. Each time SQL Server starts it recovers each database rolling back transactions that were not committed and rolling forward transactions that were committed but not yet flushed to disk when SQL Server was shutdown. SQL Server uses what are called transaction logs to record all operations; it’s these logs that are examined during a database recovery.
  • Data: This option can be used to specify the default location for database data files. This location can also be overridden when a database is created.
  • Log: This option can be used to specify the default location for database transaction log files. This location can also be overridden when a database is created.

Advanced

The Advanced page can be used to configure various different advanced options such as the new FILESTREAM storage settings, the default language, network packet size, etc. Take a look at the screenshot below.

Figure1.14
Figure 1.14 – Advanced page of the Server Properties dialog box

  • Filestream Configuration Level: Use this option to enable the new FILESTREAM storage feature of SQL Server 2008. There are three different ways in which the FILESTREAM storage feature can be configured and are defined and examined in the table below.

Configuration Level

Description

Transact-SQL Only

The FILESTREAM object will be accessible via Transact-SQL statements only. Remember, FILESTREAMs are stored as separate files using the operating systems file system, hence, with this method selected, the FILESTREAM object will be accessible only via SQL queries and not via direct file system access.

Transact-SQL and file system (local client access only)

With this option selected, the FILESTREAM will be accessible via local file system access and SQL queries.

Transact-SQL and file system (local and remote client access)

With this option selected, the FILESTREAM will be accessible via local and remote file systems. Remote file system accessible via a file share whose name is defined in the option to follow.


Table 1.2 – FILESTREAM Configuration Levels

Note: A computer restart may be required when you enable FILESTREAM storage support for the first time, while a simple SQL Server instance restart is required to disable FILESTREAM storage.

  • Filestream Share Name: Here you should specify the remote share name for the FILESTREAM storage network share.
  • Allow Triggers to Fire Others: This option indicates whether or not triggers are allowed to fire another trigger. Triggers can be nested up to 32 levels. While we have yet to examine triggers, they are simply a means of triggering an action based on specific conditions.
  • Cursor Threshold: Use this configuration option to specify the number of rows in the cursor set at which cursor keysets are generated asynchronously. That is, when a cursor generates a keyset (a set of values used to access specific rows or records in a database) for a result set, the query optimizer will estimate the number of rows that will be returned. If the estimated number of returned rows is larger than the value set in this threshold the cursor will be generated asynchronously, meaning that rows from this cursor may be fetched while the cursor continues to populate. If however, the estimated number of returned rows is less than or equal to this threshold the cursor will be generated synchronously and the query must wait until all rows have been fetched.

    A value of -1, the default value, indicates that all keysets are generated synchronously. However, a value of 0 indicates that all keysets should be generated asynchronously as all keysets will be larger than 0.

    Also note that this option is only relevant when cursors are used and defined as keyset cursors. Under normal circumstances, when data is required all data is returned at once.
  • Default Full-Text Language: Use this option to specify the default language for full-text indexed columns. By default this is the default language configured for the server.
  • Default Language: The default language of all new logins.
  • Max Text Replication Size: This option can be used to specify the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), and image data that can be added to a replicated column in a single SQL statement.
  • Scan for Startup Procs: This option specifies whether or not SQL Server will scan for and run all automatically run stored procedures configured on the server. The default value for this option is false, indicating that no scan should take place.
  • Two Digit Year Cutoff: This option indicates the maximum year which can be specified as a two digit year number.
  • Network Packet Size: The size of each network packet. Packets are the fixed-size chunks of data that transfer requests and results between clients and servers. For operations where large data results may be transmitted, a larger packet size may improve efficiency as fewer network reads and writes are required. This option can be used to specify the default packet size, however, if you recall, the packet size may be specified during connection to SQL Server as well using the advanced options found on the connection dialog box.
  • Remote Login Timeout: This option can be used to specify the time in seconds the server should wait before returning from a failed remote login attempt. The default value is 20 seconds.
  • Cost Threshold for Parallelism: This option specifies the estimated time in seconds which SQL Server will run parallel plans for queries.
  • Locks: The maximum number of locks available at any given time. Data locking is a mechanism used by SQL Server to synchronize access to data when multiple users attempt to accesses a piece of data simultaneously. In other words, locking is a way of ensuring data integrity. We’ll examine locking and the different lock levels later in the text. The default value of 0 indicates that an unlimited number of locks may be allocated.
  • Max Degree of Parallelism: This option can be used to limit the number of processors used in parallel plan execution. The default value of 0 indicates that all processors should be used.
  • Query Wait: Specifies the maximum amount of time in seconds that a query should wait for resources before timing out. The default value of -1 indicates 25 times the estimated query cost.

    Generally speaking, much of the options found herein on the Advanced page of the Server Properties dialog box probably needn’t be modified. The one in which I can see us modifying is the FILESTREAM storage option as it may become desirable to make use of the new FILESTREAM storage to manage and access unstructured data in a relational way, but more on this storage method later in the book.

Permissions

The Permissions page can be used to configure the SQL Server permissions, that is, the user accounts who have connect access, shutdown access, alter settings, etc. The page is broken into two parts, the top half lists all user accounts, both SQL Server and Windows accounts. You can select the user account whose security policy you wish to alter and the bottom half is used to grant and deny specific privileges. Take a look at the screenshot below.

Figure1.15
Figure 1.15 – Permissions page of the Server Properties dialog box

Starting, Stopping and Performing Other Actions with a Server

An instance of Microsoft SQL Server can be started, stopped and paused directly from within SSMS by simply right clicking on the desired instance from within the Object Explorer window pane and selecting your desired action. This is assuming of course that you have the appropriate permissions to perform such actions against an instance of SQL Server. To ensure that the desired user account(s) have the correct permissions you may use the Permissions page of the Server Properties dialog box. Refer to the screenshot below.

Figure1.16
Figure 1.16 – The dropdown below presented after right-clicking a server instance

First and foremost, notice that you may start, stop, pause and restart the selected SQL Server instance. Use the restart action only when you’ve made changes to the server that cannot become active until the instance has been restarted. Once you restart the SQL Server instance all currently established connections will be dropped from the server.

The next option in the dropdown menu I would like to point out is the Register option. If you refer to figure 1.7 above, which is a screenshot of the entire SSMS interface, the left hand window pane, which is by default the Object Explorer can be changed to Registered Servers pane which simply allows you to register a server with SSMS. Once an attempt to register a new server is made, the following dialog box will be displayed to collect general information regarding the SQL Server and its connection properties.

Figure1.17
Figure 1.17 – New Server Registration dialog box

The above details are pretty much identical to that seen in the connection dialog box previously examined, note that you can specify a friendly name for the server; this name will be displayed in the Registered Servers window pane. The Connection Properties tab simply contains all the information previously examined and allows you to specify the network packet size for the connection, the timeout, whether or not encryption should be used, etc.

After a server instance has been registered it will listed in the Registered Servers window pane and can be managed from there. You may create what are called Server Groups and organize the different registered servers within those groups and run Transact-SQL query and policies against the servers in that group. Lastly, a single registered server may be exported to an XML file and can be imported to the same or different machine. The same can be done for server groups and all servers beneath that group will be exported to an XML file.

Managing Resources using SSMS

The Object Explorer window pane can be used to manage all aspects of a database; we can create, delete, and modify tables, views, stored procedures, etc, we can also view activity and manage connections to the server instance, alter security policies by way of managing user accounts and their association with the different database resources. The Object Explorer is structured in a tree-like manner, much the way Windows Explorer is structured. The particular tree we’re going to examine now is the Databases tree.

Databases Tree

The Databases tree consists of several different folders, each folder has a specific purpose, one is used to store system-level database objects, the other is used to store database snapshot objects. User defined database objects will be placed directly beneath the Databases folder as a direct child. Consider the screenshot below which displays the Databases folder along with all child subfolders expended to display their content.

Note: A database snapshot is designed to present a point-in-time read-only copy of a database. A snapshot is consistent with the source database as of the moment the snapshot was created. Snapshot, again as stated, are read-only static pictures of the source database and can be used for reporting purposes or to revert the source database to the state of the snapshot. Multiple snapshots may exist and must be explicitly dropped (deleted/removed). Further note that database snapshots aren’t available on all versions of SQL Server, if you’re using the freely available Express edition, snapshots will not be available.

Figure1.18
Figure 1.18 – Databases folder expanded revealing child objects

As you can see, there has been one user defined database titled test, no database snapshots and several system databases, what are system databases and what good do they offer us? All very good questions, a system database is a database that SQL Server will use to store various system level configurations; there are six system databases in SQL Server 2008, all of which are defined below in table 1.3.

Database

Description

master

This database records all system level information for the SQL Server instance.

model

The template database used in the creation of all new database objects for this SQL instance.

msdb

The database used by the SQL Server Agent for scheduling alerts and jobs.

tempdb

A temporary workspace used for holding temporary or intermediate result sets. This database is recreated every time an instance of SQL Server is started. When the instance is shutdown, all results in this database are permanently deleted.

distribution

This database exists only if the server has been configured as a replication Distributor. This database stores metadata and history data for all types of replication, and transactions for transactional replication.

SQL Server supports database replication, much in the same fashion as Active Directory, let us for example suppose that we would like to spread the query load of our database system, we could setup replication to one or more different systems and load balance the network requests. There are many uses for database replication.

Resource

A read-only database that contains copies of all system objects that come packaged with Microsoft SQL Server.


Table 1.3 – System Databases

Lastly, if we were to use our mouse and right-click on the Databases folder we would be presented with a pop-up menu of various options, one of which titled New Database…, if we were to select this option, a new window would appear giving us access to modify the various database options (maximum database file size, file locations, database name, and various other options) for our new database. The new database window is only a shell hiding the underlying Transact-SQL command used to create a new database – CREATE DATABASE. We will examine this among many other Transact-SQL statements in full later in the book, for now, let’s focus on managing and creating objects using SSMS, thus bringing us to our next topic – creating a database.

Creating a Database

To create a new database, right click the Databases node beneath the desired SQL Server instance and click New Database. You will be presented with the New Database dialog box, here you must specify at a minimum the database name. Generally speaking, the default options are fine, never the less, take a look at the screenshot below, after which, we will examine the various new database options.

Figure1.19
Figure 1.19 – The New Database dialog box

General properties for a new database include the following:

  • Logical Name: The logical name of a database is that of the name referenced within SQL Server, this differs from the physical name which is the filename of the data and log file(s) stored on the file system. When a name is entered in the upmost textbox labeled Database name, the logical name and physical name will be a combination of the name supplied. For example, if we enter the text string mydata in the textbox, the logical names will be mydata and mydata_log for the data and log file respectively.
  • File Type: The type of file, data, log or filestream.
  • Filegroup: Its possible for a single database to constitute multiple physical files, a file group is a collection of database objects and files grouped into a specific group used for data allocation and administration purposes. While the concept of a filegroup is somewhat detailed and we’re not going to examine it now.
  • Initial Size: SQL Server supports dynamic file allocation which simply means that data and log files may automatically grow and shrink as needed. The rate at which the files grow may be configured as well as the maximum allowable file size. Simply edit the Autogrowth column of the file which you wish to configure and specify your desired properties. If auto growth is used, SQL Server will request from the operating system space as needed which will be in the specified chunks, for example, the screenshot above states that the file will make use of auto growth and will grow by 1 megabyte, that is, once the file has reached its maximum size, 1 megabyte will be requested from the operating system and the file will grow by that amount. The file can grow as needed; however, optionally a maximum file size may be set meaning that the file will not grow larger than the set size. If the file has reached its maximum allowable size and cannot grow anymore any attempt to commit data to the database will fail. Lastly, auto growth may be disabled and the initial size must be set to the maximum desired database size.
  • Path: You can optionally set the path where the files should be stored, generally, the default path, configurable via server-wise settings, discussed above is generally acceptable, unless of course you wish to make use of multiple files and spread the files across multiple physical hard disks or network locations, etc.

Finally, note the Add button located in the lower right of the New Database dialog box, if this button is clicked a new file will be added to the above list box, you may alter the filegroup and file type of this user-defined file as needed (a FILESTREAM object must be first added, which we will examine in a moment, you can then define a new file as a FILESTREAM object, data or log object). If multiple files exist within a single database the files will then be grown (assuming auto grow is used) in a round-robin fashion, that is once the primary is full the first secondary file will begin to fill, and continue in this manner until all files are at maximum capacity. We could then place each file on separate physical hard disks to spread the data and load. We’ll discuss filegroups and more when we analyze the DDL (Data Definition Language).

Options Page

The options page consists of several different database level options, many of which can also be configured as sever level default options. That is, the often used default settings should be configured at the server level and for the one-off case they may be overridden at the database level. The screenshot below shows the Options page, we’re not going to examine every available option, only the few that standout.

Figure1.20
Figure 1.20 – Options page of the New Database dialog box

  • Collation: Here you can specify the default collation for the new database. Generally speaking, the sever default is just fine.
  • Recovery model: The recovery model determines how much data loss is acceptable during a failure and what kind of backup and recovery options are available. There are three different options for the recovery model, full, simple, and bulk-logged. The full recovery model provides full data protection in the case of a failure, this model makes use of database backups and transaction log backups (remember a transaction log is simply a log which records all transactions that have taken place). With the full recovery model not only can you restore a full or differential database backup, but you can also recover the database to a specific point in time and to the point of failure.

    The simple recovery model allows you to recover data only to the most recent full database backup. Finally, the bulk-logged recovery model provides a high level of protection with however slightly better performance as not all bulk transactions are logged. I personally recommend that you stick with the full recovery model.
  • Compatibility level: This option allows you to specify the database compatibility level. Note that if a level other than the latest (SQL Server 2008) is specified not all features exposed by the latest database engine will be available to you in this database.
  • Auto Shrink: Use this option to automatically shrink the database to its smallest capable size. For example, if auto grow is in use and the database reaches, say, 200 megabytes and much of the data is eventually deleted and the consumed data is only 15 megabytes, the auto shrink feature will ensure than only the actual size of data will be consumed on the physical hard disk, not all 200 megabytes, thus the database will be shrunken in physical size. Without auto shrink, the database will continue to consume 200 megabytes of hard disk space until it has been manually shrunken. Generally speaking, I’d recommend that you not employ auto shrink and rather manually shrink when required.
Filegroups Page

Again, a file group is a collection of database objects and files grouped into a specific group used for allocation and administration purposes. That is, above when I stated that file growth on multiple files are performed in a round-robin fashion, this is only true for files stored within the same file group, hence if we were to create multiple file groups, and associate multiple data files within the various file groups (note that log files can not belong to a file group) then they may be grown concurrently. However, a secondary file assigned to a specific user defined file group will only be used to store data if database objects are assigned to that same file group, this is a method in which we can embrace to separate different objects within our database. Let us assume we’ve assigned a table to a user defined file group, and multiple files within this file group, then the data within this table will begin within the first file of that file group and will roll over to the other files within that file group if/when the first file of that group has reached its maximum allowable capacity.

There are two different file groups, the first being the primary file group which our primary data file belongs to as well as system tables and any other database object not explicitly designated for another file group and last we have the user defined file group. User defined file groups can be created either programmatically via Transact-SQL statements or within the New Database dialog box. For now, we’re going to simply use the New Database dialog box. After a file group has been created we must associate a physical database data file with that group. Consider the screenshot below.

Figure1.21
Figure 1.21 – Filegroups page of the New Database dialog box

After a new file group has been created you may indicate that this new file group should become the default file group for the database, that is, recall that I stated if no file group is specified during the creation of a database object (table for example) then that object will belong to the default file group. We’ll discuss file groups later when we examine their creation programmatically; however I think you got the idea!

What about FILESTREAM storage? Notice that within this page we may also create new FILESTREAM storage file groups. We know that FILESTREAM storage enables the storage of unstructured data while maintaining transactional consistency between the unstructured data and corresponding structured data. FILESTREAM storage can be used to store such things as large documents and multimedia based files on the file system and rich streaming APIs (Application Programming Interface) are available to us. While we’re going to examine FILESTREAM storage in great detail, for now, let us briefly discuss FILESTREAM storage.

One particular question you may have is, why use FILESTREAM storage if the file simply exists separately on the file system, why not just access the desired file(s) independently of the DBMS? One particular reason is file management, we noted in Codd’s rules that the DBMS should offer physical file independence, making use of the FILESTREAM storage fulfils this rule even for larger unstructured data without a large performance hit. For example, it is possible to store binary large object (BLOB) data using varbinary(max) columns (which will store the data directly in the table), however with large data we suffer an often large performance hit as SQL Server was not designed for such storage. The FILESTREAM storage however makes use of the NTFS file system for storage of data as the file system was designed for such reasons and still offers a relationship with the structured data of the database tables.

Note: For small objects, generally less than 1 megabyte, it may be beneficial to make use of varbinary(max) columns rather than FILESTREAM storage.

Moving on, FILESTREAM data must be stored within the FILESTREAM file groups, hence the screenshot above which allows for the construction of FILESTREAM file groups. A FILESTREAM file group is a special file group that contains file system directories rather than the files themselves. These file system directories are called data containers which are the interface between Database Engine storage and file system storage. To create a FILESTREAM file group, simply ensure that the FILESTREAM list box is active (by clicking on it) and then click the Add button found in the lower right corner of the dialog box, you may then specify a name for the newly created file group.

Note: A database data file must be created on the General page of the New Database dialog box and the filegroup assigned to this file should be that of the FILESTREAM file group you’ve just created. Note that the size limit for FILESTREAM storage is that of the NTFS volume it’s placed on. After the new file has been added and assigned to the FILESTREAM file group you must specify the path of the data container (remember, this is simply a directory on the file system). The path should point to a location on the file system, the path up to the last folder must exist, and the last folder must not exist. For example, C:\fsGroup\Books, the first half, C:\fsGroup must exist, but the Books subfolder should not.

Modifying an Existing Database

You’ve got your database created, its working nice, but wait, you’ve got to modify it now. Maybe add a new file group, additional files, or a FILESTREAM object, etc. Modifying an existing database is actually quite simple using SSMS. Simply locate your database beneath the Databases folder, right click the desired database and select Properties. You will then be presented with the Database Properties dialog box shown in the screenshot below.

Figure1.22
Figure 1.22 – Database Properties dialog box

Notice that you may manage the database data, FILESTREAM containers and log files from the Files page, the filegroups may be managed via the Filegroups page and options modified via the Options page. Much of what’s seen on these pages we’ve already examined; however what we have to examine is features such as change tracking, mirroring and transaction log shipping. We’re not going to spend much time here; however we will briefly examine these particular features.

The first feature I wish to discuss is change tracking, this is a new feature of SQL Server 2008. Essentially what this allows you to do is track changes to a particular table. Change tracking must be turned on for any table where you wish to track changes, and what this will do is record all changes that have occurred, well, actually changes that have been committed. We can then, at a later point in time determine the changes that have been committed to this table since a previous point in time which is configurable. Change tracking, as you may have guessed must be enabled at the database level. We must configure the retention period and retention units which define the number of days, hours or minutes that changes should be kept and whether or not auto cleanup should be enabled, which simply specifies whether or not the change data will be cleaned up after the set retention period.

What could we use change tracking for? Perhaps an application where there are several clients and data often changes server side; with change tracking we could sync the clients by simply retrieving a list of changes since the last sync occurred, this is done using version tracking, that is, every time a table whose change tracking has been enabled changes a tracking version will be incremented, from our clients we could then simply query all changes since the last sync version and perform the required actions to bring us up to date. It’s really quite useful.

The next feature we’ll discuss is mirroring. Mirroring is not new to SQL Server 2008 however it has been improved. Database mirroring is simply that, a mirror of a database with another SQL Server instance. SQL Server operates by recording all data changes in the transaction log files prior to recording the changes to the actual data pages. The changes are buffered in memory and then written to disk, with mirroring enabled; simultaneously this same memory block is also sent to the mirroring instance. There are different levels of database mirroring which we’re not going to discuss now, just note that mirroring can be enabled using this dialog box, or alternatively via a Transact-SQL ALTER DATABASE statement.

Lastly, the page Transaction Log Shipping can be used to automatically backup transaction log files and ship them to a secondary standby server in the case of a server failure. There are several ways in which log shipping can be configured, none of which we will discuss at this moment as the goal of this chapter is to examine the different tools available to us and general SQL principals. Also note that the database recovery mode must be set to FULL before log shipping can be employed.

Creating and Modifying Tables

We’ve created a database and we’ve configured it to meet our needs, however, without data our database is quite useless, thus we’ll now examine the SSMSs approach to creating and altering tables. A table can be created in one of two ways, either via Transact-SQL code or via SSMS’s Object Explorer. To create a table using SSMS we must first navigate Object Explorer to the Databases node, from there we should locate and expand the database node which we wish our new table to belong and right click the Tables folder of this database and select New Table.

The New Table tab will be opened and we must specify all column names of our new table along with their data type and nullability as shown in the screenshot below.

Figure1.23
Figure 1.23 – New Table tab

After you’ve entered a name in the Column Name field you must select a data type, while we’ve yet to examine the various data types supported by SQL Server 2008, we can however, list all available data types simply by expanding the dropdown in the Data Type column. Many data types require that we specify the precision, that is, the maximum valid length of data that may be entered in that column. For example, consider the screenshot shown in figure 1.22, notice that the ISBN column is of type VARCHAR (variable length character) and supports a maximum of 13 characters while the Title column is also of type VARCHAR, however supports the maximum allowable size by SQL Server, which is 2^31 – 1 bytes and 1 byte is a single, non-unicode character. That’s quite a few characters!

The Allow Nulls column can be used to explicitly indicate whether or not a specific column will allow NULL values to be specified in place of valid data matching that columns data type. If checked, the column will allow NULL values otherwise, when data is inserted into the table, all columns that don’t allow NULL values must be specified with valid data matching its defined data type. Alternatively, we may also define a column to have a default value, that is, during data insertion if no other value is specified for a column the default value will be used. The default value of a column can be defined by specifying a value in the Default Value or Binding option located in the Column Properties tab in the lower window pane.

Also notice the BookID column, this column if of type INT and has been defined as the primary key and identity column for the table. Identity columns are generally used on primary key columns to enforce unique values upon the addition of new rows. That is, the database engine will generate a unique incremental value to be assigned to the identity column (in this case, BookID) upon the addition of new rows to the table. There can be only one identity column per table. We’ll discuss identity columns and primary keys in further detail later; however a primary key ensures the uniqueness of a columns data hence allowing a row within the table to be uniquely identifiable and should be used for this purpose only. In our example above, our table will store information regarding books, while we could have defined the ISBN column as the primary key we’ve chose to assign the BookID column as the primary key. The BookID column should be unique and should be the means in which we refer to that book as this way we are guaranteed that of the book we desire. For this reason, the primary key does not support the insertion of NULL values, as it must guarantee uniqueness between rows. The primary key is also used by relating tables, for example, notice that the Author column is of type INT, this is the case as there may be another table titled Authors where all author information resides, we can then simply define the Author column as a foreign key to the Authors table primary key, thus assisting when we join tables together for the purpose of retrieving all relating data. Again, we’ll discuss these concepts and more in later chapters.

After you’ve defined all your columns, specified your primary key(s) (yes, there can be more than one primary key, which we’ll discuss later) and identity column you may close the New Table tab, you will then be presented with a dialog box asking you to specify a name for your new table. Ensure that you specify a unique name, after which the table will be created and you can begin making use of your new table.

To edit an existing table is also quite simple, we need only navigate to the Databases node of Object Explorer, expand the desired database along with the Tables folder, right click the table we wish to alter and select properties. After which you will be presented with the Table Properties dialog box. From this dialog box we may view the general options such as the data size, filegroup the table belongs to, etc, alter the table security, that is, define explicit permissions to the table itself which allows for a more granular security model and finally we may also enable change tracking. Recall that above we discussed change tracking and how to enable it at the database level, well, change tracking must be enabled at the database level prior to enabling it on a per table basis. Assuming change tracking has been enabled at the database level we may enable it on any table whose changes we wish to track using this dialog box, or alternatively via Transact-SQL code. The Table Properties dialog box is shown in the screenshot below.

Figure1.24
Figure 1.24 – Table Properties dialog box

Aside from editing the general properties of a table we may also edit the column structure of the table using the same interface which was used when the table was first created. Again, right click the table you wish to edit and select Design from the popup menu this time around, you will be presented with a new tab and can begin editing the table structure as if you were creating a new table. However note that if the table has data, some operations may not be possible.

To rename a table simply slowly double click the table in question from Object Explorer and the selection box will change to a textbox where you can specify the new name you’d like to assign to the table, alternatively right click the table and select Rename from the popup menu, just as you would do in Windows Explorer.

Last, but hardly least, we’ve yet to examine a means of defining relations using the SSMS interface. There are actually a few different ways in which relations may be defined, the first being via the Design mode of the table, if any column definition is right clicked, a popup menu will appear, we can then select Relationships and the Foreign Key Relationships dialog box will appear. We can use the Foreign Key Relationships dialog box to create and specify new foreign key relations as shown in the screenshot below.

Figure1.25
Figure 1.25 – Foreign Key Relationships dialog box

First and foremost, notice that I’ve already added a foreign key relationship; this was done simply by click the Add button in the lower left-most corner of the dialog box. Note the different properties that may be set for a foreign key relationship, the first property we must define is that of the columns and tables where the relationship exists, notice the property titled Tables and Columns Specifications, in figure 1.24, this property is selected, notice the browse button (depicted by three dots), click this button and the following dialog box will appear allowing you to specify the columns and tables.

clip_image002
Figure 1.26 – Table and Columns dialog box

Notice that we must first select the primary key table and the primary key column, secondly the foreign key table is that of the table that we’re currently modifying (or creating), thus we need only select the foreign key column. Recall that above I noted that the author details, such as name, age, etc will be defined in another table however related to the Books table via the Author column, thus the screenshot above depicts exactly this scenario.

Returning to the analysis of figure 1.24, we may also specify how the relationship is to behave, for example, if a primary key row referenced in a table related with a foreign key constraint is deleted we may choose to either reject the action as data relied on that primary key, or we can delete the foreign key reference as well.

Note: We’ll discuss foreign and primary keys in great detail in the chapters to come, for now just note that a foreign key constraint is used to establish and enforce a link between two tables (actually, two columns which can be of the same table or a different table), that is if an attempt to write a value to a foreign key column is made the value is first compared to that of the values in a primary key column of the relating table, if there are no equivalent values in the primary key column that match the specified value the attempt at data insertion will fail.

Database Diagrams

Another great feature of SSMS is database diagrams or better known as E-R (Entity-Relationship) Diagrams. We can use E-R diagrams to view, as the name suggests, entities and their relationships, however not only can we view entities and their relationships but we can also modify them using SQL Server’s Database Diagrams feature. The database we wish to construct a diagram of must be set to a minimum compatibility level of 90 which indicates SQL Server 2005. To create a database diagram simply right click the Database Diagrams folder beneath the desired database and select New Database Diagram from the popup menu. You’ll be presented with a dialog box requesting that you select the tables that should be part of this E-R diagram. The database I’ll use for this sample has two tables, Authors and Books, we’ll add both to the diagram giving us the following:

Figure1.26
Figure 1.27 – E-R Diagram depicting the Authors and Books table

You may have noted that there are currently no relationships between the two tables, however as specified above, this should not be the case. The tables do in fact relate to each other, again, as stated previously, the Authors table via the AuthorID column relates to the Books table via the Author column. We can define a relationship by simply dragging the AuthorID column from the Authors table to the Books table and releasing the mouse above the Author column of the Books table. The Tables and Columns dialog box, previously examined in figure 1.25 will appear with the desired values, that is a primary key of AuthorID and a foreign key of Author.

We’ll examine the creation of relationships in detail when we examine the Transact-SQL statements used to construct and define relationships. For now, simply note the ease of SSMS.

Note: We can also use the E-R diagram to rename columns and even change the data type of a column.

Security

There are two different levels of security, server-wide security and database security. To reflect this, Object Explorer exposes two different levels of Security folders (tress, nodes, etc). The first can be found at the root level beneath a server instance, while the second can be found beneath each database. From the server level Security folder you can create new SQL Server based user accounts, as well as link Active Directory or local system accounts to the SQL Server instance allowing for centralized management of user accounts. Once you have either created a new SQL account or linked an existing Active Directory account to this instance of SQL Server, you then need to assign server roles and/or database level roles to the user account. A role, in basic terms defines a user’s access to objects, in the case of a Server Role; the user account is assigned server-wide access to that SQL Server instance. A database role on the other hand assigns database level access, meaning that the users who have been assigned only a database role will be granted specific access to the specified database and has no server level authority to manage any server configurations or any other databases other than that of the ones they have been explicitly granted access to.

That said, let us examine the various server roles a user can be granted. Table 1.4 contains a list of predefined server roles and their descriptions.

Role

Description

bulkadmin

Can execute bulk INSERT statements.

dbcreator

Can create, alter (modify) and drop (remove) tables.

diskadmin

Can manage disk files.

processadmin

Can manage SQL Server processes.

securityadmin

Can manage SQL Server security, which includes managing CREATE DATABASE permissions, reviewing log files as well as resetting SQL account passwords.

setupadmin

Can manage linked servers and server startup procedures.

serveradmin

Can set server wide configuration options, as well as shutting down the instance of SQL server.

sysadmin

This is the ultimate in SQL Server accounts, all operations can be performed by users associated with this role.


Table 1.4 – Server Roles

You may also obtain a complete list of security permissions for the above server roles via the Transact-SQL stored procedure sp_srvrolepermission (using the Query Editor in SSMS type in this command and execute it by pressing F5 on your keyboard. We’ll analyze the Query Editor a little bit later in this chapter).

Let us now examine database specific roles, again, these roles are not server-wide, every database contains the same list of fixed database roles, when a role has been granted to a user, that user obtains that security clearance for only that database. Table 1.5 contains a list of fixed database roles and their corresponding descriptions.

Role

Description

db_denydatawriter

Cannot modify any data in any user table in the database.

db_denydatareader

Cannot select any data in any user tables in the database.

db_datawriter

Can modify all data in any user table in the database.

db_datareader

Can select all data from any user table in the database.

db_backupoperator

Can issue backup statements.

db_ddladmin

Can issue all DDL (Data Definition Language) statements but cannot issue GRANT, REVOKE, or DENY statements.

db_securityadmin

Can manage all aspects of database security.

db_accessadmin

Can add or remove users IDs associated with said database.

db_owner

Owner of the database. Has all database permissions granted to user.


Table 1.5 – Database roles

To obtain specific security permissions for the above fixed database roles you can execute the sp_dbfixedrolepermission stored produced, again using Query Editor enter this command and execute the script. The results will be that of the fixed database role permissions and their descriptions.

Note: There is however one database role we have yet to discuss, this is the public role, all users associated with a database belong to this role, thus if you wish to assign specific roles to all users of a database you can assign the permissions to the public role.

Creating a New Server Login

Excellent, so how can we create and manage SQL Server user accounts? We must first associate users to the instance of SQL Server, and then we can associate the newly added users to a specific database, or grant any combination of server-wide roles to that user. Using SSMS, if we expand the Security folder, we’ll discover a subfolder titled Logins, we can now right click this subfolder and a popup menu will appear allowing for the addition of new users. Select New Login and a new window will appear. This window, titled Login – New brings forth an interface for the addition of new user accounts, we can either specify a local Windows system account, an Active Directory based account or a new SQL Server internal account. The New Login window is shown in figure 1.27 below.

Note: Again, default installations of SQL Server have only Windows Authentication enabled. Mixed-mode authentication must be explicitly enabled as discussed above, again mixed-mode authentication can be enabled, allowing for both SQL Server based accounts and Windows/Active Directory accounts to be authenticated by SQL Server. To enable mixed-mode authentication, from the SSMS interface, under Object Explorer, right click the server name, select Properties, select the Security page and select the radio button titled SQL Server and Windows Authentication Mode.

Figure1.27
Figure 1.28 – New Login dialog box

First and foremost notice that there are actually quite a few different options available to us for new user accounts. Let us now take a moment to examine each page of the new users dialog box in detail. The first page, shown in figure 1.26 above is the General page and allows for basic options to be specified, each discussed in detail below.

  • Login name: If Windows authentication is used, this should be that of a valid Windows user account. Notice the Search button, you can use this to browse either the Directory or local system for user accounts. If this is a SQL Server login, simply specify a unique name you wish to use for this new user account.
  • Windows/SQL Server authentication: Use these radio buttons to specify which authentication mode should be used for the new user account – SQL Server or Windows authentication.
  • Password: This option is available only if SQL Server authentication is used and should be the password for the new user account.
  • Enforce password policy: Again, this is valid only for SQL Server authentication and will enforce the password policy defined by the server.
  • Enforce password expiration: This option will enforce the password expiration policy for SQL Server user accounts.
  • User must change password at next login: If enabled, the user will be required to change their password once logged in.
  • Mapped to certificate: This option should be used to map the new login to a certificate. That is, just as a login can be created from a Windows account, a login can also be created for a certificate. The certificate must already exist in the master database. However unlike Windows or SQL Server logins, certificate login accounts cannot be used to authenticate against, that is, you cannot login to an instance of SQL Server using a certificate login. What you can do, however, is use certificate logins to sign stored procedures. For example, you can give specific user accounts execute permission on these signed stored procedures, what this will do is grant additional permissions to these users, granted to the certificate login while the stored procedure is executing. In other words, this is useful when you require permissions on a stored procedure but you do not want to explicitly grant a user those rights. We’ll discuss this in considerably more detail later when we discuss security.
  • Mapped to asymmetric key: Just like that of the certificate, however rather than being mapped to a certificate the new login is mapped to an asymmetric key.
  • Map to Credentials: Specify the credentials you wish to map to this login account. Credentials define the user details you wish to use when accessing resources outside of SQL Server. The credential must already exist and can be created either via Transact-SQL statements or using SSMS by right clicking the Credentials folder, a child of the Security folder and clicking New Credential.
  • Default database: This option can be used to specify the login’s default database.
  • Default language: The default language for this login.
Server Roles

The Server Roles page can be used to grant server-wide security roles to a login. This page is quite simple and lists all of the server roles previously examined in table 1.4, simply select the desired roles. However, take care as to what roles you assigning the new login account, as, again, these are server-side security roles, the user will be granted access to all objects on the server.

User Mapping

The User Mapping page can be used to map new and existing logins to a database and assign database level permissions. A login can be mapped to a database in a number of different ways, via Transact-SQL, via the User Mapping page of the new login and edit login dialog box, or by navigating to the Users folder found beneath the Security folder of the database you wish a login to be associated with.

Note: There are different levels of securable scopes (a securable is a resources which the SQL Server database engine authorization system regulates access). The term login is at the server-level and refers to a SQL Server login account, that is, an account that can authenticate itself against the server. The term user is found at the database level and is simply a login account that has been granted permissions to one of more databases. For example, the login MELLIS may exist on the server, but doesn’t necessarily have permissions to access any database objects.

The User Mapping page is quite simple and can be seen in figure 1.28 below.

Figure1.28
Figure 1.29 – User Mapping page of the New Login dialog box

First and foremost, notice that the page is divided into two different sections; the top half lists all available databases while the bottom half allows you to grant roles to the mapped user account for the selected database. To begin, select the desired database objects by checking the checkbox found beneath the Map column. Notice that once the checkbox has been checked the User column will be populated with the login name specified on the General page. Finally, ensure that the desired database is active which can be identified by the entire row being highlighted with a light blue and grant the user account, for the selected database, one or more of the desired roles in the bottom half of the page.

Note: The Default Schema column defines the default schema in which database objects should be resident of when created by this user account, unless otherwise specified. The default schema is also used to resolve object names when the fully qualified name is not specified, if no default schema is specified the schema dbo is assumed.

Schemas are logical namespaces consisting of multiple database objects (tables, views and stored procedures). A namespace is defined as a set where all objects within have a unique name. For example, as it is with Object Oriented Programming, you can define a namespace and that namespace can contain objects with a name of that existing in another namespace because each object belonging to separate namespaces are logically separated and hence will not conflict with each other. We’ll examine schemas in great detail later in the book.

Securables

The Securables page can be used to set permissions on another server-level securable object. For example, the new login that we’re creating, is, in itself a securable, however we can also, for example, grant specific permissions to our new login, these permissions can be to perform specific tasks on another login, end point, or even server (server-level securable). For example, we can grant our new login impersonate permissions on the sa securable (login), which as you know is the default SQL Server administrator account (assuming SQL Server authentication has been enabled). Consider figure 1.29 below.

Figure1.29
Figure 1.30 – Securables page

Notice that under the securables list box are several login accounts, these were added by simply clicking the Add button and selecting the option that all login securables should be added. Notice that the sa login has been selected, we can then assign our login alter, control, impersonate and view definition privileges for the sa account. That is, our newly created login account could possibly impersonate or even alter the sa account. This ability can be useful if we wish to delegate administrative privileges over specific securables to user accounts without granting excessive permissions. Again, we’ll discuss securables and the ways in which we may manage them later in the book, for now simply note that it’s quite easy to grant or explicitly deny privileges to user accounts over specific securables.

Status

The Status page can be used to define and alter the status of a new or existing login, we may enable/disable the login or even explicitly grant or deny database engine connect permissions.

Management Tree

There are many different child notes beneath the Management node of Object Explorer, however the first which we will discuss is SQL Server Logs. Beneath this node contains several error log files that can be viewed within this interface by simply double clicking the desired log file. However there are also several other audit log files which audit based on the SQL Server audit level, for example under the server properties dialog box, previously examined, we may enable user authentication auditing with various options – Login Failure, Login Success, both or no user authentication auditing at all. These audit entries are visible from the Windows Event Viewer Application log. There are also other levels of SQL Server auditing as well, we will discuss auditing in detail later in the book. For now, let us just note that there are very verbose auditing methods available to us.

The last item under the Management node we will discuss is titled Activity Monitor and can be used to monitor the currently running SQL Server processes as well as object locks. There are three different activity monitoring views available to us via the SSMS GUI interface, shown in figure 1.30. These views are defined in table 1.6.

View Name

Description

Process Info

This view gives us an abundance of details about the current processes running on our SQL Server instance, details include the various commands being executed at that point in time, the user account executing these commands, CPU usage, etc.

Locks by Process

This view lists all current data locks, including queued, granted and denied locks via the corresponding process.

Locks by Object

This view lists all current data locks, including queued, granted and denied locks via the corresponding object.


Table 1.6 – Activity Monitor views

Note: A data lock is a means of locking data to ensure that no other process attempts to alter the data while another is performing actions against that data.

Figure1.30
Figure 1.31 – SSMS Activity Monitor

The GUI interface is very straight forward and simple to navigate, from this interface we can also kill (or terminate) all process visible to us. There are also a few Transact-SQL statements we can use to view current server activity, these statements are in the form of Transact-SQL stored procedures – sp_who, sp_who2 and sp_lock. Each of which having their own list of acceptable arguments and specific uses. Although, sp_who and sp_who2 are very similar in the fact that they are both used to display active processes, sp_who2 offers a more verbose output than sp_who. These statements are very easily executed either via a command line tool (which will be discussed in a few moments) or via the Query Editor in SSMS. Listing 1.1 shows an example of executing sp_who and the corresponding results that may be returned.

Listing 1.1 – Executing sp_who

USE MASTER
EXEC sp_who
GO
Changed database context to 'master'.
spid  ecid   status      loginame          hostname     blk   dbname  cmd      request_id
----- ------ ---------   ----------------  ------------ ----- ------- -------- ----------
1     0      sleeping    sa                             0     master  TASK MAN 0
2     0      runnable    MOBILEHERC\Matt  MOBILEHERC    0     master  SELECT   0
3     0      sleeping    MOBILEHERC\Matt  MOBILEHERC    0     master  AWAITING 0

I know we have yet to discuss the query language we see above, but don’t concern yourself with the syntax or commands at this point as we will examine them in detail, for now just concern yourself with the idea that what we can do via the SSMS console can also be accomplished via Transact-SQL statements. Okay, back on track, in the above results we see the Server Process ID (SPID), the login name used to spawn that process, the hostname or the originating request which spawned the process, the database name, and the command being executed. If we desire, we can also filter the results from sp_who. This stored procedure can be executed with an argument specifying the user name you’d like displayed. The stored procedure then returns all current processes spawned from that user and ignores processes from any other user account. For example, sp_who 'sa', when executed, will only return processes spawned from the sa user account.

As stated above, sp_who2 on the other hand offers a more verbose output; we can obtain information such as CPU time, disk IO and more. You may execute this procedure in exactly the same fashion as seen above in listing 1.1.

Pretty straight forward so far, let’s now take a closer look at the sp_lock stored procedure. This procedure can be used to display information about the current system locks either granted, or queued. I know we have yet to discuss data locking, but bare with me, as we will discuss data locks later in the book, for now just note that a lock is a feature used by SQL Server to ensure data integrity. Refer to listing 1.2 for sample usage of this procedure.

Listing 1.2 – Executing sp_lock

use master
exec sp_lock
go
Changed database context to 'master'.
spid   dbid   ObjId       IndId  Type Resource      Mode     Status
------ ------ ----------- ------ ---- ------------- -------- ------
    52      1           0      0 MD   5(1:0:0)      Sch-S    GRANT
    52      1           0      0 MD   4(1:0:0)      Sch-S    GRANT
    52      1  1115151018      0 TAB                IS       GRANT
    52  32767         204      0 TAB                Sch-S    GRANT
    52  32767           0      0 MD   4(4:0:0)      Sch-S    GRANT
    52  32767           0      0 MD   5(4:0:0)      Sch-S    GRANT

This procedure may also be executed with up to two arguments; these arguments are used to filter the results returned. If we were to execute sp_lock with an argument of 52 and 53 the results returned would only contain lock information for SPID’s 52 and 53. Alternatively, we can execute this procedure with a single argument and results returned would contain locks only relevant for the specified SPID. Executing this procedure with either argument defined as NULL will return information on all system locks currently queued or granted.

Let us take a closer look at the various columns returned by sp_lock and their meaning. Refer to table 1.7 below.

Column

Description

spid

As defined above, this is the Server Process ID number for the process requesting a data lock.

dbid

The Database ID in which the lock is held. In listing 1.2 we see that three locks are held in database ID 1 and three locks are held in database ID 32767. To convert a database ID to its corresponding user friendly name you can execute the DB_NAME() function specifying a DBID as an argument. For example:

use master

select db_name(1)

go

-----------------

master

The database requesting this lock is the master database.

ObjId

The Object ID in which the lock is held. You can execute the OBJECT_NAME() function from within the database that the object belongs. For example:

use master

select object_name(1115151018)

go

------------------------------

spt_values

The lock belongs to the table spt_values, which in turn belongs to the master database.

IndId

The Index ID on which the lock is held. We have not yet discussed indices; however an index can be defined as a table of pointers where each row in the index table points to a row in a related table. This technique can be used to improve the performance of SQL queries on tables with many records (thousands).

Type

This field defines the lock type, whether it’s a database lock, table lock, metadata lock, etc. Some of the more common locks are discussed below.

RID = Lock on a single row in a table identified by a row identifier (RID).

TAB = Lock on an entire table, including all data and indexes.

DB = Lock on a database.

FIL = Lock on a database file.

MD = Locks on metadata, or catalog information.

Note metadata is the data which describes the database structure. Metadata includes descriptive data such as the table, columns, indices, constraints, and other items that are used to make up the database.

Resource

The value identifying the resource that is locked.

Mode

The lock mode requested. We will discuss the various lock modes in great detail later in the book.

Status

The status of the lock. This can either be, GRANT, WAIT or CNVRT. Respectively meaning the lock was granted, queued or will be converted from another mode.


Table 1.7 – sp_lock return values

Above, I mentioned that using the SSMS Activity Monitor we can kill or terminate any process visible to us, so let us now examine the execution of this task via Transact-SQL statements. The method used to achieve this task is the KILL statement. This statement accepts multiple arguments, one of which being the SPID of the process we wish to kill, the other is the WITH STATUSONLY option (discussed in a moment). Take caution not to kill processes performing important tasks, as this could have undesirable results, some processes we should not kill are ones executing the following commands:

AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SIGNAL HANDLER

Note, that while you can’t kill your own connection to SQL Server it may become important that you determine your current SPID. This can be done with the @@SPID Transact-SQL variable. Execute this command using the SELECT Transact-SQL statement. For example:

select @@SPID
go
------
52

Looks like our connection SPID is 52…

As stated above, the first argument we must supply to KILL is the SPID of the connection we wish to terminate, once a KILL command has been executed it may begin to roll back the effects of any pending transactions, for this reason, the KILL statement may not complete instantaneously. Hence, the WITH STATUSONLY option available to KILL, using this option you may retrieve the status of any pending KILL operations. Listing 1.3 demonstrates the execution of KILL and the corresponding output that may be generated.

Listing 1.3 – Using Transact-SQL to kill a process

kill 53
go

If no errors were returned, then the process was successfully killed, but what if process 53 is still visible via the invocation of sp_who or the SSMS Activity Monitor? Well then, perhaps the process is in the middle of a roll-back operation. Let us examine this possibility, if we execute KILL 53 WITH STATUSONLY, assuming there is a roll-back operation in progress then a message similar to the following should be returned to us:

SPID 53: Transaction rollback in progress. Estimated rollback completion: yy% Estimated time left: zz seconds.

If the process was previously killed and no new processes have been established with the same SPID, then the following message should be returned to us:

Process ID 53 is not an active process ID.

If for any other reason, the process was never killed or a new process has been created and assigned that SPID, executing KILL with the status only flag will result in the following message returned:

Status report cannot be obtained. Rollback operation for Process ID 53 is not in progress.

Okay, sounds good, but there is one more possible scenario we have yet to examine, this is the possibility of an orphan process, sp_who displays such a process with an SPID of -2. If we encounter such a scenario and we have the desire to kill this process, we must execute KILL with the UOW (Unit of Work ID) for any transactions associated with this process. The UOW can be obtained from querying the syslockinfo table and examining the req_transactionUOW column of the coresponding resource identification returned by sp_lock. Listing 1.4 examines this in detail.

Listing 1.4 – Retrieving the UOW via Transact-SQL

use master
exec sp_lock
go
Changed database context to 'master'.
spid   dbid   ObjId       IndId  Type Resource    Mode     Status
------ ------ ----------- ------ ---- ----------- -------- ------
    52      1           0      0 MD   5(1:0:0)    Sch-S    GRANT
    52      1           0      0 MD   4(1:0:0)    Sch-S    GRANT
    52      1  1115151018      0 TAB              IS       GRANT
    52  32767         204      0 TAB              Sch-S    GRANT
    52  32767           0      0 MD   4(4:0:0)    Sch-S    GRANT
    52  32767           0      0 MD   5(4:0:0)    Sch-S    GRANT

select rsc_text, req_transactionUOW from syslockinfo
go
rsc_text  req_transactionUOW
--------- ------------------------------
          00000000-0000-0000-0000-000000
4(4:0:0)  00000000-0000-0000-0000-000000
5(4:0:0)  00000000-0000-0000-0000-000000

Don’t concern yourself with the details of the above statements; what I want you to note however is that we can obtain the UOW for all processes, orphan or not with pending lock requests by executing the above statements. We can now execute KILL with the first argument as the UOW of the transaction we wish to kill.

Resource Governor

Beneath the Manage node of Object Explorer you may notice a number of different objects, one which I would like to take a few moments to discuss is Resource Governor, which is a new feature as of SQL Server 2008. Resource Governor allows you to manage SQL Server resources by placing limits on resource consumption, that is, consider a long running CPU intensive query, in some cases such a query can take up most, if not all server resources. With Resource Governor we can pool resources and set limits on CPU usage and memory utilization. Pooling resources in this manner can greatly reduce the probability of such a CPU intensive query consuming all available server resources.

We’ll examine the resource governor feature later in the book, for now just note that just like everything else we’ve examined, resource governors may be created via Transact-SQL statements or SSMS. As this chapter has mainly focused on performing tasks with SSMS we’ll examine the creation of resource governor objects with SSMS.

Resource Governor can consist of pools and groups. A resource pool, or simply pool, represents the physical resources of the server and can be thought of as a virtual SQL server instance inside an instance. A resource pool can be created by expanding the Resource Governor folder, right-click on the Resource pool folder and selecting New Resource Pool from the popup menu.

A resource pool supports the specification for both minimum and maximum memory and CPU utilization. The minimum resource utilization represents the minimum guaranteed resource availability of the pool and does not overlap with any other pool, while the maximum resource consumption specifies the maximum possible resource consumption however does in fact overlap with the other resource pools. That is, if a minimum guaranteed resource value is specified, Resource Governor will guarantee that the pool may utilize the specified minimum level of resources, however the maximum available resources are not guaranteed and simply state, well, the maximum resource consumption capable by the pool.

Note: The minimum resource values across all pools cannot exceed 100% of the server resources. The maximum resource values can be in the range of minimum-100%.

Before we discuss any more theory or discuss workload groups, lets checkout the New Resource pool dialog box.

Figure1.31
Figure 1.32 – Resource Governor Properties

Notice the two resource pools that already exist, internal and default. We can define up to a maximum of 20 different resource pools, as there are two default pools upon installation, we’re left with the possibility of 18 user defined resource pools. If you scroll down in the top most window pane, you can define a user defined resource pool simply by clicking in the Name column of an empty row and typing the name for your new resource pool, click the OK button for the changes to be committed.

Using the Resource Governor Properties dialog box you can specify the minimum and maximum utilization for both the CPU and memory for a resource pool, hence any workload groups assigned to a pool will be limited to that specified here. A workload group serves as a container for sessions that are similar according to the classification rules that are applied to each request. That is, Resource Governor allows you to define classification rules that are applied to new sessions, when a rule is met, the session will then be associated with an existing workload group, which in itself is associated with a resource pool and thus is limited to the resources defined within that pool. Again, we’ll discuss this in great detail later, for now, note that using the Resource Governor Properties dialog box we can also define workload groups for the currently selected resource pool. That is, again simply by clicking in the Name column of an empty row and typing the name for your new workload group. Also notice that a workload group may further customize the resources, we can limit CPU time, importance, etc.

Note: If a resource pool has a nonzero minimum value defined the effective maximum value of other pools is readjusted as the minimum of the configured maximum value of a pool and the sum of the minimum values of other pools subtracted from 100%. We’ll analyze the algorithm used to adjust the minimum and maximum resource consumption across pools later in the book when we delve deeper into Resource Governor.

Authoring Queries

Using SSMS you can author queries by way of the Query Editor. To open the Query Editor select the New Query button from the SSMS toolbar, if you’re not currently connected to a server you’ll be prompted with the Connection dialog box previously examined. You may cancel the dialog box and use the Query Editor in a disconnected state if you so desire. When you’re ready to execute a portion or all of the Transact-SQL statements in your new query you will be required to connect to an instance of SQL Server, it’s also possible to change a queries connection at any time, that is, associate the query window with that of another SQL Server instance.

The Query Editor consists of a text editor, a results window and several buttons in the toolbar, refer to figure 1.32 below. The Query Editor can be used to, not only write and generate Transact-SQL statements, but also to construct script files, we can save the content of the query to an external file if we desire. Finally, making use of the Query Editor has its advantages, such as InteliSense, which if you recall is used to suggest Transact-SQL stored procedures, tables, etc based on the first few characters of what you’ve typed. The Query Editor can also be used to analyze the execution path of a Transact-SQL query, that is, the cost incurred by each step of the query. Such information can be used to optimize stored procedures and other large complicated queries. Lastly, the Query Editor includes a query designer which allows you to graphically select tables and columns which should be included in the result set.

Figure1.32
Figure 1.33 – Query Editor with results

Notice that the bottom window pane has two different tabs, the first being the results tab, that is, the desired results generated based on the specified query, and the second tab, messages. This tab is used to output messages from the server to the client; such messages include the number of affected rows and any warnings or errors that may have occurred during the execution of the query.

Notice the status bar of the Query Editor window, addition information can be found here, information such as the status of the query, the currently connected server and version, the authenticated user name, currently active database and the number of returned rows within the result set.

A query can be executed either by pushing the Execute button in the toolbar or by pressing F5 on your keyboard, additionally, a query can be halted by either clicking the stop button in the toolbar or pressing Alt+Break on your keyboard.

Finally, we’ve noted that the Query Editor offers benefits to editing script files, such benefits, as previously mentioned, include InteliSense and error notification, however, another benefit is syntax highlighting, which works by displaying reserved words in blue, variables in black, comments in green and string literals in red. The color scheme can of course be changed as well.

Solutions

When dealing with a large application project it is often required that multiple script files exist, for this reason, SSMS, similar to Visual Studio, supports solutions via the Solution Explorer window pane. By default, the Solution Explorer window pane is not visible, however can be accessed by selecting Solution Explorer from the View menu item (optionally, Ctrl+Alt+L).

A solution can consist of multiple query files and connection objects. For example, let us create a new solution, this is done by clicking on the File menu, selecting New and then clicking New Project. You will then be presented with the New Project dialog box as shown in figure 1.33.

Figure1.33
Figure 1.34 – New Project dialog box

Enter a name for your new project, choose the location on your hard disk and specify a name for your new solution. Optionally, you can choose to add the new project to an existing solution; however in this case, we’ll choose to create a new solution. Once the project and consequentially the solution are created, you can begin to manage objects within the project and solution. You may add connection information, and script queries to a project.

Note: A project is simply a means of managing and organizing files.

SSMS supports version control via the integration of Visual Source Safe. That is, directly from within Solution Explorer it’s possible to check in and out script files from the version control server. Such integration allows you to continue working within SSMS and gain the script editing benefits of Query Editor while also gaining versioning control.

Command Line Interface

Let us now examine the command line equivalent to SSMS’ Query EditorSQLCMD. This tool offers a similar connection to the DBMS as SSMS Query Editor does, however this interface is purely text based and has no GUI interface for point and click operations with the mouse. SQLCMD can be initiated from the Windows command prompt. Establishing a connection to the SQL Server instance of choice using SQLCMD is very simple, but before we begin, let’s first examine some of the various command line arguments available to us with this utility.

The base syntax for this utility is as follows:

sqlcmd 
[
{ { -U login_id [ -P password ] } | –E }
]
[-S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]
[ -l time_out ] [ -t time_out ] [ -h headers ]
[ -s col_separator ] [ -w column_width ] [ -a packet_size ]
[ -e ] [ -I ]
[ -c cmd_end ] [ -L [ c ] ] [ -q "query" ] [ -Q "query" ]
[ -m error_level ] [ -V ] [ -W ] [ -u ] [ -r [ 0 | 1 ] ]
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] 
[ -k [ 1 | 2 ] ]
[ -y display_width ] [-Y display_width ]
[ -p [ 1 ] ] [ -R ] [ -b ] [ -v ] [ -A ] [ -X [ 1 ] ] [ -x ]
[ -? ]
]

We will not examine every possible command line argument available to us; however I do want to examine some of the obviously important arguments. Let’s begin with the most important argument – the SQL Server we wish to establish a connection with. The SQL Server address is supplied to SQLCMD via the command line switch –S. The connection string must be in the form of: server_name [\instance]. If the instance is omitted then a connection to the default SQL Server instance will be attempted. Let us now briefly examine the authentication credentials we will use to access the SQL Server instance (specified by the -S command line switch). The authentication credentials are supplied to SQLCMD via the command line switches -U and -P, it is however possible to omit the password command line argument. For example, if the -P switch is omitted SQLCMD will prompt for a password upon first execution and your password will not be displayed on screen as you enter it. There is also another authentication method we may choose from, this being the trusted connection, meaning that your current Windows authentication is used to authenticate you to the remote SQL Server. The switch to specify this authentication mode is -E, note that this is the default, if no authentication switches are specified your current Windows authentication is used. Let us examine this.

If our current Windows account also has permissions on the SQL Server we are attempting a connection with, then we can use SQLCMD’s default behavior. For example:

C:\>sqlcmd -S localhost\sqlserver
1>

Again, we are not required to specify any authentication because our currently authenticated Windows account has been granted access to this instance of SQL Server. You can also connect to an instance of SQL Server by specifying a local SQL user account and corresponding password, for example:

C:\>sqlcmd -S localhost\sqlserver -U sqlaccount
Password:
1>

As you can see, -P was omitted from the command line and we were prompted for the password, after the password has been successfully entered, you are then granted access to SQL Server.

Once connected to SQL Server using any of the above methods (note that connections established via the above methods, enter us into interactive mode, meaning that we can execute commands in real-time), you are now free to execute Transact-SQL statements by entering them at the SQLCMD prompt, which is signified by the 1>. The 1 as part of the SQLCMD prompt signifies that this is the first line of a SQL statement and will continue to increment until you execute the GO command which sends the entered statements to the SQL Server. Prior to the execution of the GO command, all input entered is buffered locally. If you wish to clear the local command buffer you may do so by executing the :reset command, no information will be sent to the SQL Server instance and the buffer will be flushed. We have seen examples of interactive mode above in the various listings, we enter a SQL command, followed by GO to submit our command buffer to SQL Server and output is returned directly to our console. Although, there may be times when you wish to execute SQLCMD in non-interactive mode, for example, to execute pre-written SQL scripts, or execute a specific command and have the results returned to a text file. This is called non-interactive mode because SQLCMD connects to a SQL Server instance, executes a predefined set of tasks and terminates, no user interaction is required. For example, let’s examine the following command:

sqlcmd -S localhost\sqlserver -i MyScript.sql -o Results.txt

The above command is used to execute a pre-written Transact-SQL script, have the results returned to a text file and when finished, terminate the connection to the server. The -i argument instructs SQLCMD to use the specified file as an input file, in this case MyScript.sql, and the -o argument is used to specify the output file. You may also execute a single statement and have the results returned to the console or text file. The command line arguments used to accomplish this are -q and -Q. Both of which instruct SQLCMD to behave differently, for example –q (lower case) is used to execute the statement and upon completion return the user to interactive mode, -Q (upper case) on the other hand executes the statement and terminates the connection with the server. Let us examine this behavior:

C:\>sqlcmd -S localhost\sqlexpress -q "use master"

Changed database context to 'master'.

1>

Simple enough, the statement was executed, the results returned to the console and you are then placed into interactive mode. The -Q argument behaves in exactly the same manner as above other than the fact that the SQL Server connection is terminated after execution of the specified statement.

Note: The Transact-SQL statement that you wish to execute must be enclosed in quotation marks.

It’s also possible to set scripting variables either implicitly via the -v command line argument or explicitly using the setvar command. The use of scripting variables have their obvious practical uses, if for example you write a SQL script to perform general actions on various tables in a database and wish to run this script against multiple different databases you need only change the variable definitions and the script is compatible with the other databases. Another use is ease of upgradeability, let us imagine that we have written a very complex and large Transact-SQL script, we may divide various important pieces of data into script variables so that only those variables need updating and we need not modify the contents of the script to perform any updates. Let’s examine the use of script variables, for example we may write script such as:

Listing 1.5 – MyScrip.sql Transact-SQL script

:setvar DB "OurDB"
:setvar Table "products"
:setvar NameColumn "name"
:setvar UsrName "%Monitor%"
use $(DB)
select * from $(Table) where $(NameColumn) like '$(UsrName)'

Executing the script is quite simple:

sqlcmd –S localhost\sqlexpress –i MyScript.sql

Not a very practical use, but the advantages are obvious none the less; we need only to modify the variables and our entire select statement has changed according to our new requirements. Now imagine that we have a very large script and perhaps this script is intended to be used by someone else, they can now modify the script by way of variables without searching the entire script and modifying Transact-SQL statements.

SQLCMD is very powerful and has its advantages. I do recommend that you utilize this tool to its fullest, examine the above syntax and execute the command with the -? argument so you can become better acquainted with this tool.

Conclusion

This chapter covers different management utilities bundled with SQL Server 2008. We’ve examined the various different aspects of managing and creating database objects using the GUI tools available to us, we’ve also analyzed the different dialog boxes we must interact with to configure our SQL Server instance. Lastly we took a peak at the Command Line Interface (CLI) utility called SQLCMD and noted some benefits of such this utility. SQL Server Management Studio is quite a flexible and powerful tool for both developers and administrators and should be used to its fullest whenever possible.

Finally, this chapter should serve you as a reference to the different available tools bundled with SQL Server, the chapters to come however will discuss, for the most part, managing and developing database objects and SQL Server instances via Transact-SQL statements and will not discuss the various management utilities bundled with SQL Server 2008.