Questions, Answers, and Tips: MCDBA Certification

This article previously appeared in Windows NT Magazine.

How difficult is Microsoft's SQL Server database administrator (DBA) certification process?

Before SQL Server 7.0, specific SQL Server DBA certification wasn't available. Microsoft offered the Administering Microsoft SQL Server and Designing and Implementing Databases with Microsoft SQL Server exams only as electives in Microsoft's other certification programs. Now, Microsoft has extended its Certified Professional Program to include Microsoft Certified Database Administrator (MCDBA) certification. The new certification requires you to pass four core exams and one elective exam. The core exams are

The elective exams are

Because of increased interest in data warehousing, customer relationship management, and data mining, we think an MCDBA will be as important as an MCSE. For more information about MCDBA certification, check out http://www.microsoft.com/train_cert.

In the syntax for creating a linked server using sp_addlinkedserver, what does N refer to?

SQL Server 7.0 includes full Unicode support, which simplifies international localization. N is the Transact SQL (T-SQL) shorthand that tells SQL Server you're referencing a Unicode string. SQL Server 7.0 Books Online (BOL) gives the following example for using sp_addlinkedserver to create a linked server named SEATTLESales:

USE master     GO     EXEC sp_addlinkedserver     'SEATTLESales',     N'SQL Server'     GO

The N prefix tells SQL Server to use the Unicode character set to translate the 'SQL Server' string. The N prefix isn't necessary if you use the same code page on all your clients and servers and don't deal with language localization problems. However, Unicode is wonderful if you're not sure you're using the same code page on all your clients and servers.

A downside to Unicode is that Unicode characters use more storage space than characters in a regular SQL Server character set use. A regular SQL Server character set is a single-byte set that supports up to 256 characters. Unicode is a double-byte set that supports as many as 65,536 distinct values. Each stored character in the extra range of supported characters consumes space.

We've barely scratched the surface of this important topic. For more information about Unicode, check out "Using Unicode Data" and "The Difference Between Unicode and Character Sets" in BOL.

How can I define error messages in a variety of languages?

SQL Server ships with support for 24 languages. You can locate SQL Server's default language setting in syslanguages. This default setting controls the date and time format and the language in which the system returns messages. You can purchase localized versions of SQL Server in French, German, Spanish, and Japanese. These versions contain two sets of system messages, one in US English and one in the local language. To display messages in the local language, replace the default language setting with the language ID of the local language. To find a language ID, type

SELECT @@LANGID

To determine the language currently in use, type

SELECT @@LANGUAGE

This command returns the name of the language in use (in US English; us_english's language ID is 0).

You can use the keyword language to specify default session languages in Open Database Connectivity (ODBC) and Object Linking and Embedding Database (OLE DB). To specify a default session language in DB-Library, you can run the DBSETNATLANG macro before calling dbopen to establish a connection. Another option is to use the Transact SQL (T-SQL) SET LANGUAGE command.

You can also define messages in multiple languages. SQL Server stores the messages in sysmessages. English is always present, but you can use the sp_addmessage command to add custom messages to sysmessages:

sp_addmessage [@msgnum =] msg_id,     [@severity =] severity,     [@msgtext =] 'msg'     [, [@lang =] 'language']     [, [@with_log =] 'with_log']     [, [@replace =] 'replace']

In this command, the [@msgnum =] msg_id parameter is the ID (an inte-ger) of the message, with a default setting of NULL. Acceptable values for user-defined error messages start with 50001. The combination of msg_id and the language argument (in the fourth displayed line of this command) must be unique; SQL Server returns an error if the ID already exists for the specified language.

In the line that begins [@severity =], severity is the severity level of the error; severity is type smallint, with a default setting of NULL. Valid levels are 1 through 25; however, only systems administrators can add a message with a severity level of 19 through 25.

Next, [@msgtext =] 'msg' is the text of the error message. Msg is nvarchar (255), with a default setting of NULL.

In the next line, [@lang =] 'language' specifies the language in which SQL Server writes messages. You can install multiple languages on the same server, so the language argument specifies the language in which SQL Server writes each message. When you omit language, SQL Server uses the default language for the session.

The [@with_log =] 'with_log' parameter specifies whether SQL Server writes the message to the Windows NT Application Log; with_log is varchar(5), with a default setting of FALSE. If you set this value to TRUE, SQL Server always writes the error to the NT Application Log. If you set this value to FALSE, SQL Server doesn't always write the error to the NT Application Log. Depending on how the system raises the error, SQL Server can write the error to the log even if you set this value to FALSE. Only systems administrators can use this option. Finally, the optional [@replace=] 'replace' parameter indicates that the system will replace the existing msgid parameter with the values in this stored procedure call.

When I email ISQL/w query results or paste them into a Microsoft Word document, how can I tell whether SQL Server correctly formatted the results?

By default, SQL Server generates column-aligned result sets from an ISQL/w connection. This formatting looks great in an ISQL/w window that has fixed-width fonts; however, most fonts aren't fixed-width. One solution is to specify tab-delimited output: In the Query Options dialog box, select Tab Delimited from the Result Output Format text menu, as Screen 1 shows. This change lets you easily convert the query results into a table after you paste the output into a Word document.

When I export Microsoft Access data to SQL Server 6.5, SQL Server changes my Access AutoNumber fields to Long Integer fields. Do you have a solution?

When you export an Access table to SQL Server 6.5, any AutoNumber fields in the Access table become Long Integer fields in the SQL Server table. To fix this problem, create a SQL pass-through query that sets the Identity_Insert option to ON for the SQL Server table. This setting lets you update the Identity column with data from your Microsoft Access table. (For more information about implementing this workaround, see the Microsoft article "ACC: Exporting Tables to SQL Server Changes AutoNumber to Long".

Is there a fast way to install only the Open Database Connectivity (ODBC) drivers from the SQL Server 7.0 CD-ROM onto a client PC?

In the x86 directory, run the 6MB+ mdac_typ.exe program on your Win9x system. This executable installs ODBC and Object Linking and Embedding Database (OLE DB) drivers.

I know that when I use dump and load commands to move databases from one SQL Server 6.5 server to another server, I have to create database fragments in the same order. However, if I use SQL Transfer Manager, does the creation order of the database fragments matter, or can I assume the database move will work as long as I create the target database of equal or greater size?

Using dump and load commands to move databases between servers can be problematic. Using SQL Transfer Manager to move your databases should be successful, unless you use segments (SQL Server 6.5 lets you define up to 32 segments) to manually partition I/O and the segment sizes don't match between servers. SQL Server 7.0 uses filegroups instead of segments—Microsoft seems to be sending the message that multidisk RAID is a better approach for multiserver database moves. If you've invested a lot of time setting up segments and replication under SQL Server 6.5, plan for work as you migrate to SQL Server 7.0. (For more information about multiserver dump and load operations and database fragments, see SQL Server Source, August 1997 and April 1998.)

If SQL Server removes the inactive portion of the transaction log when you issue a DUMP TRAN command with no parameters, why would you use the TRUNCATE_ONLY option? Do you use the TRUNCATE_ONLY option only when your transaction logs are not important?

This question applies to SQL Server 6.x, because Microsoft replaced the DUMP command with the BACKUP command in SQL Server 7.0. (SQL Server 7.0 supports the DUMP command only for backward compatibility.) In this answer, we talk about backing up only the transaction log, not the main database. You use the DUMP DATABASE command to back up the main database.

DUMP TRAN MyDB to MyDumpDevice is the standard command for dumping your transaction log. This command clears the inactive portion of the log, which lets SQL Server shrink the log and store all the old transactions on the database dump device specified in the DUMP command.

DUMP TRAN MyDB with TRUNCATE_ONLY removes the inactive portion of the transaction log without creating a backup copy of it. This command frees space in the log to prevent you from running out of space and halting database transaction processing; however, you can't recover the database using transaction log dump files. This command doesn't take a target dump device name because you're not backing up the transaction log. Most production database maintenance plans don't use this option, because this option reduces your ability to recover the database to a precise time. However, you occasionally might want to run a DUMP TRAN with TRUNCATE_ONLY. For example, you might want to run this command immediately before running a full DATABASE DUMP command, because the TRUNCATE_ONLY will clear out the log and decrease the time required to back up the database. Microsoft Books Online (BOL) strongly recommends you do a full database dump after running DUMP TRAN using the TRUNCATE_ONLY option.

You should use the DUMP TRAN MyDB with NO_LOG command only in emergency situations when the transaction log is full. (When the log is full, you can't perform a normal DUMP TRAN or DUMP TRAN with TRUNCATE_ ONLY.) This option differs from the TRUNCATE_ONLY option, because NO_LOG doesn't log the truncate operation as it removes the inactive portion of the log. Although strange, the TRUNCATE_ONLY option writes information to the transaction log so that SQL Server knows you have truncated the log. This information is necessary to ensure full recovery of the database after an unplanned system outage. The NO_LOG option bypasses this safety check, which means the database might be unrecoverable if the system experiences a cold shutdown. You should always issue a full DATABASE DUMP command after running DUMP TRAN using the NO_LOG option.

DUMP TRAN MyDB with NO_TRUNCATE is handy in disaster recovery situations when the data portion of the user database is damaged but the master database and log portion of the user database are on undamaged database devices. For a successful recovery, you must store the database and log portions of a user database on separate database devices. In fact, always ensure that you create your database log and data portions on separate devices. Rather than deal with the concept of devices, SQL Server 7.0 enforces the rule that your data and log must exist on separate files.

Is the new Index Tuning Wizard as good as a smart DBA?

The Index Tuning Wizard is very good. To begin using this wizard, you need to use SQL Server Profiler to gather data for the Index Tuning Wizard to analyze (the wizard uses statistical sampling techniques). Next, you run the Index Tuning Wizard, which uses its internal optimization engine to pick the best set of indexes for the queries and tables it's analyzing. You can also run the Index Tuning Wizard against one query with the SQL Server Query Analyzer (only members of the sysadmin fixed server role can use the SQL Server Query Analyzer to perform index analysis).

Here's anecdotal evidence of the Index Tuning Wizard's proficiency: During prerelease builds of SQL Server 7.0, Microsoft used the Index Tuning Wizard on the SAP benchmarks Microsoft was running. Early index models in the benchmark application caused noticeable deadlocking problems when concurrent users reached the 5000 user mark. The Index Tuning Wizard selected an optimal set of indexes for the SAP workload, which put the human tuning experts to shame.

Obviously, this wizard doesn't do everything. It doesn't recommend indexes on system tables or tables that cross-database queries not in the currently selected database reference.

Where can I find information about the basics of relational databases?

You have several options. If you're interested in classic database papers, we recommend Readings in Database Systems (Morgan Kaufman Publishers, May 1998). This resource is a collection of articles Michael Stonebraker and Joseph L. Hellerstein recommend. Another option is to read the latest issues of SIGMOD Record from the Association for Computing Machinery (ACM), which include a series of interesting short articles, "Reminiscences on Influential Papers" (http://www.acm.org/sigmod/record). You can also find a lot of the SQL Server architects' primary research in SIGMOD Record.

Another approach is to find a virtual database class. For example, Stanford University's Dr. Jeffrey Ullman has posted his database class notes from the past few semesters (http://www-db.stanford.edu/~ullman/cs345-notes.html). Professor Jennifer Widom has posted her notes about database system implementation (http://www.stanford.edu/class/cs346/handouts.html).

You can also check out The Data Administration Newsletter . This resource isn't as academic as the other approaches we've suggested, but this newsletter discusses up-to-date enterprise-shop thinking about current data administration topics.