In-Depth

Tour de SQL Part II: Administrative Features

Every year, the Tour de France is won or lost in the mountains. In this stage of our tour of SQL Server 2005, we look at the critical administrative features that make or break the new database.

• By Eric Johnson
• 05/01/2006

You could make the case that the 2006 version of the Tour de France is a brand new race. Without Lance Armstrong, the field suddenly opens up, and anyone can win. Similarly, SQL 2005 is substantially different from previous versions, making it essentially a new product. Microsoft has reworked it from top to bottom, and the result is the most powerful database, business intelligence and data warehousing product to ever come out of Redmond. There are a lot of changes for development and business intelligence, but what’s in it for the database administrator? A lot.

Mirror, Mirror on the Database
Microsoft has introduced a new failover technology in SQL 2005, known as Database Mirroring. Database Mirroring has been included in the RTM release of SQL Server 2005 for evaluation purposes only, and Microsoft doesn’t support this feature for production use. It is scheduled to be released in a future service pack, however.

Database Mirroring is a software-based, high-availability solution that gives DBAs an alternative to hardware clustering or log shipping. Mirrors are implemented on a per-database basis, and can be configured for automatic failover in the event of a problem.

Two SQL Server instances are used for a mirror; the primary server is the principal, and the server with the copy is the mirror. SQL sends each transaction from the principal to the mirror and performs the insert, update or delete operation again on the mirror.

Speed is a factor to consider with mirroring. The amount of latency between principal and mirror depends on several factors. First is the transaction load on the principal. Because each transaction is actually run on both servers, the more transactions, the longer it takes for the mirror to catch up. Secondly, if the mirror is carrying a higher load than the principal or is a less powerful server, it can take longer to apply the transactions.

To set up automatic failover from the principal to the mirror, a third instance of SQL Server is required. This third instance, known as the witness, watches the other two servers and can initiate a failover if the principal becomes unavailable.

Unlike clustering, mirroring doesn’t use any shared resources or virtual machine names. Instead, when the client connects to the principal, it’s also given the name of the failover server. When the connection to the principal is lost, the client attempts to connect to the mirror. Alternatively, the name of both the principal and the mirror can be specified in the client’s connection string.

There are a number of benefits to database mirroring over clustering and log shipping:

• By utilizing database snapshots, you can allow Read access into a mirror. This allows the mirror to be used for other purposes, such as reporting, rather than just as a hot spare waiting for a failure to occur.
• The scope or protection is more granular than a hardware cluster, because mirrors can be used on a per-database basis.
• Mirroring provides a cost savings over clustering, since you don’t need more expensive hardware.
• The primary advantage of mirroring over log shipping is the ability to configure automatic failover.

Say "Cheese"
Much like famed Tour de France photographer Graham Watson, SQL 2005 can take pictures; in SQL’s case, they’re called database snapshots. When you create a snapshot, you provide a static, Read-only view of the database at the point the snapshot was taken. Initially, all the snapshot contains are pointers to the source database pages, which makes the process of creating a snapshot virtually instantaneous. When a user requests data from the snapshot, it’s retrieved from the source database. When a change is made to the source, the page to be changed is copied into the snapshot before modification. This preserves the data as it was at the time of the snapshot. Over time, pages will accumulate in the snapshot as source data is modified. Now, when a user requests data, it will return from the snapshot or the source, whichever one contains the original page. At any time, you can roll the source database back to the point in time when the snapshot was taken. In addition, you can maintain numerous snapshots of a single database.

[Click on image for larger view.]

Figure 1. Surface Area Configuration limits potential security risks by disabling features that can open SQL Server to attack.

Database snapshots can help in a number of situations:

• Just before making a large bulk data modification, you could make a snapshot. Then if something goes wrong with the modification, you can quickly roll back to the snapshot.
• Snapshots allow you to access a database mirror for reporting purposes, and not just short-term reports: You can create quarterly or yearly snapshots for historical reporting on financial data. For example, a snapshot could provide you with a frozen picture of exactly what your financial data looked like on Dec. 31, 2005.
• Testing or training groups. Testing groups need a baseline database to test applications against, and trainers need a consistent environment from class to class. You can take a snapshot, and when the training or testing is complete, revert to the snapshot. Just like that, you’re ready for the next run.

Keep in mind that snapshots are not backups; they require the source database in order to function. But they can be an effective means for recovering to a point-in-time faster than performing a complete database restore.

More Reliable Backup and Restore
Snapshots are quick and useful, but they’re no replacement for a full backup of your database. In SQL 2005, backup and restore operations have been retooled to provide more reliability and help prevent data loss. You can now configure up to four devices and back up your database to all of these devices at the same time. Thus, if one of the devices fails, you still have up to three other backups to use for a restore.

As with older versions of SQL Server, all devices used in a single backup or restore operation must be of the same type, so you can’t back up to disk with a tape mirror. Note that mirrored backups are meant to protect against failures of the backup media, and won’t prevent backup failures caused by errors in your database.

Once your databases are backed up several times over, restoring presents a different set of problems. Historically, if there was an error on the backup media, the backup file was pretty much useless. SQL 2005 introduces a new restore option, "Continue After Error," that allows the restore operation to continue despite any errors that may have occurred. The data that can be read from the backup device will be restored and you can then attempt to recover the database, or at least get back some of the data.

Another problem with restores is the time they take. In previous versions of SQL Server, users were unable to access the database during the restore operation. For large databases, that could translate into several hours of downtime. SQL 2005 offers a solution, with the ability to perform online restores, which can occur even as other parts of the database are being backed up.

Online restores only work if you back up using filegroups. After the first filegroup is restored, users can access any data in that filegroup. Subsequent filegroups will continue to be restored and more and more of the database will become available as the restore progresses. (You still cannot access data from a filegroup in the process of restoring.)

Dealing with Your Insecurities
SQL security has been a sore spot for admins. It’s still not perfect, but SQL 2005 has a new, much-improved security model. For starters, the Surface Area Configuration tool employs an "Off By Default" approach that limits the number of features, services and connections enabled in SQL 2005. Figure 1 on p. 30 illustrates how many sensitive features are disabled out of the box in order to prevent potential problems. Database mail, SQL Mail, CLR integration, Service Broker, and xp_CMDShell are just some of the features that are turned off by default.

Some of these features can create security holes if they’re enabled and not properly managed. By locking down SQL’s surface area, Microsoft helps limit the number of potential holes present in your installations. If you’re upgrading to SQL 2005, you’ll find that all features enabled in the previous version will be enabled after the upgrade. This opens up some holes, but prevents problems from occurring due to a feature’s unavailability. For instance, an app running on SQL 2000 using xp_cmdshell won’t break after your SQL 2005 upgrade.

The terminology of SQL Server has also changed. It’s not a big change, but understanding the terms will help you manage security. Everything is now referred to in terms of Principals, Securables and Permissions.

• Principals refer to anything that can request access to an object, such as SQL Logins, Windows Logins, Database Users or Database Roles.
• Securables are the objects to which Principals can have access. They include tables, views, databases, endpoints, schemas and so on.
• Permissions are the specific rights that a Principal has on a Securable.

[Click on image for larger view.]

Figure 2. SQL Server permissions are more granular, allowing for more control over your systems.

The latest version of SQL also introduces Schemas. These are collections of objects that share a namespace, allowing you to separate your database along functional boundaries.

When you create an object -- a table called "employee," for instance -- it’s placed into a schema, which you’ve named "HR." You access the table with the convention SchemaName.ObjectName, in this case HR.employee. This is similar to accessing objects with OwnerName.ObjectName in previous versions. You could have all the objects used by Human Resources in this HR schema, allowing you to grant permissions on the schema to HR users only. Schemas in SQL 2005 are created without tying them to user accounts, which builds a separation between security and schema management.

Available permissions have been greatly expanded. Most permissions take on a standard form:

• Alter: Grants users the right to alter objects
• Create: Allows users to create objects
• View: Allows users to view the properties of objects

Within each of these subsets you can grant a user rights to a specific object or all objects of a specific type. For example, you can grant a user the right to Alter the AdventureWorks database or you can grant the permissions to Alter all databases. Permissions work on a hierarchical basis. The general levels of the hierarchy, from highest to lowest, are server, database, schema and object. Having permissions at a higher level also grants those permissions at the lower levels, so a user with Alter Database rights will also be able to alter any object in that database.

Given the new granularity of SQL permissions, you’ll need to create a careful security plan and implement it in a logical fashion, as shown in Figure 2. Failure to do so will result in a security model that is difficult to manage and that can lead to many more problems. The expanded security model is so detailed that, with some work, you can grant your DBAs access to manage all aspects of the SQL server without them having access to any of the data -- a great step forward in this Sarbanes-Oxley era.

Other new features advance the arena of security management. One of the most important is certificates, which SQL 2005 can use to enhance many aspects of security: They can be tied to logins, used for encryption, used to secure communications between servers, or tied to Integration Services packages to secure execution. By utilizing the Windows policy system, you can now set password complexity requirements and expiration policies.

Context switching has also been added to the engine. Jobs can be scheduled to run under a specific context other than the SQL Server Agent. Likewise, while running a query, a user can issue an EXECUTE AS statement and run queries in the context of a different user. Modules such as stored procedures, triggers or functions can also be configured to run as a specific user instead of the user calling the module. This can give the module elevated rights without the user having those permissions, again giving you tighter control over user security.

We’ve just scratched the surface of everything you can do with SQL 2005’s new security model. Your utilization and implementation will vary greatly based upon your environment, but there are few shortcomings to this new model, which is much more effective than previous versions.

Mailing It In
Not a big fan of SQL Mail? Join the club. SQL Mail has been a thorn in the side of DBAs for years. With its dependence on MAPI and the difficultly in using it for monitoring, SQL Mail has long needed a face-lift.

The solution is here, in the form of database mail. Database mail is an SMTP-based e-mail system with many advantages over SQL Mail, starting with no MAPI requirement. This means you don’t have to install an e-mail client to use it.

[Click on image for larger view.]

Figure 3. Database mail can be configured with multiple SMTP servers for failover.

Database mail is configured via profiles, providing several advantages. First, you can have multiple profiles with separate security on each. So, for example, all your end users can be granted access to one profile while administrators have access to another.

Each profile can be configured with multiple SMTP servers, so that mail can still be sent in the event of an SMTP server failure (see Figure 3). The database mail process is separate from the SQL process, so e-mails are sent to a queue and then delivered by database mail. If database mail goes offline, the e-mail simply continues to queue. When the process comes back online, the e-mail gets forwarded.

For users, profiles offer immediate response, eliminating the wait while e-mails are sent.

Logging and auditing lets you see a history of all e-mail activity. Additional features include the attachment size governor and prohibited file extensions, allowing you to limit the attachment size or type, respectively.

Other Cool Stuff
SQL 2005 introduces the Dedicated Administrator Connection (DAC). The DAC is a separate connection endpoint that allows administrators to access a SQL server even when the server is otherwise non-responsive. Access is gained via a command-line utility, sqlcmd or via the SQL Management Studio. The DAC connection must be initiated from the local server; no network-based connections are allowed. Using the DAC an administrator can connect and attempt to resolve the issue that has caused the server to become non-responsive, without having to re-boot the machine.

• Another favorite feature among DBAs will undoubtedly be Instant File Initialization. Previously, when you made a new database file, the file was created on disk and then filled with zeros to ensure that the data on the disk was deleted for security purposes. So restoring a 500GB database meant that you had to wait while 500GB worth of zeros were written to the file before SQL could even start restoring data.
  
  This problem is now a thing of the past. SQL 2005 claims the space on disk without filling it with zeros, which means even the largest data files are immediately available for use. If you require the added security provided by the initialization step, simply disable Instant File Initialization.
• A few data type changes have been tossed into the mix for SQL 2005. The XML data type has been added, allowing you to store XML documents. You can also link the XML data to XML schema definitions. SQL Server contains an entire set of built-in methods to modify and retrieve your XML data. SQL 2005 also introduces the MAX keyword for varchar, nvarchar and varbinary data types. Using MAX, you can store up to (2^31)-1 bytes of data in one column, just about 2GB. MAX is meant to replace the text, ntext and image data types, all of which are still included for backward compatibility. Also, unlike their predecessors, there are no special methods required to access the data stored in MAX columns.

Stage 3
Lance Armstrong was known as the most prepared rider in the Tour. He squeezed every drop out of his performance, which gave him the edge. On the next stage of our race, in June, we’ll discuss performance tuning. Like Lance, SQL must be properly maintained to work at peak form, and we’ll provide you the proper training regimen to get the most out of it.