Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features

It goes without saying that you do not need to program a line of code to effectively administer SQL Server 2005; although you open yourself up to a lot more opportunity if you do. As one Microsoft SQL Server developer put it, “Ya gotta do what ya gotta do to keep your job.” My assessment of where SQL Server is going as a product tells me you don’t have much to worry about as long as you stay on top of things. Let’s now turn to the install and go to tools you can use to administer SQL Server 2005.

SQL Server’s GUI and command-line tools and utilities are listed as follows:

• SQL Server Management Studio   This is the principal administration suite used to administer SQL Server 2005. It allows you to carry out a number of administrative tasks such as adding servers and server groups; managing users and their rights and permissions; and managing databases, tables, indexes, and so on. Management Studio has query windows that can be used as a developer’s test bench for designing and testing T-SQL queries, batches, scripts, and so on (stuff you previously did in Query Analyzer). You can run the studio from the command line by executing SQLWB.EXE. For serious SQL Server development, you will use Visual Studio 2005 because you can step through execution of the stored procedure in it. In addition you also now have Profiler90.EXE, which launches the profiler from the command line (see Chapter 18) and DTA.EXE, the command-line access to the Database Tuning Advisor (see Chapter 18).

• Import/Export Wizard   This tool can be started from within Management Studio from the context menus of the Databases and Tables nodes. This tool is actually a wizard that will take you through the steps required to import and export data from OLE DB and ODBC databases.

• SQL Profiler   If you need to capture Microsoft SQL Server events from a server, this is the tool to accomplish that task. You can also use the profiler to audit SQL Server 2005. The profiler and monitoring is covered extensively in Chapter 18.

• SQL Server Configuration Manager   This tool is also used to start, stop, or pause the SQL Server services on the server. (I introduced this utility earlier in the chapter). It manages the Net-Libraries that connect to SQL Server clients and servers.

• Administrative wizards   A healthy dose of administrative wizards can be accessed from the Management Studio. These include wizards to create backup jobs, indexes, stored procedures, and so on. We will be talking about them in various chapters.

• Old command line utilities   A number of command-line utilities ship with SQL Server 2005. These are installed by default into the %drive%:\Program Files\ Microsoft SQL Server\MSSQL\Binn folder and the %drive%:\Program Files\ Microsoft SQL Server\90\Tools\Binn folder. Among these are utilities that are backward compatible with SQL Server 2000. These are the BCP, ISQL, and OSQL executables. BCP is a tool used for bulk copying of data, while ISQL and OSQL are used to send T-SQL statements to the server from the command line. ISQL works with the OLE DB provider, and OSQL works with the ODBC provider.

• SQLCMD   The SQLCMD utility replaces and combines in one command-line environment the OSQL and ISQL programs that were available in the earlier versions of SQL Server. This executable lets you connect to any instance of SQL Server via the OLE DB library. Once connected, you can run any T-SQL script. SQLCMD is especially useful for connecting to a “dead” server via the Dedicated Administrative Connection. You do this by specifying the -A switch at the command prompt.

Let’s now turn to an overview of the SQL Server Management Studio. The other graphical and command-line utilities are beyond the scope of this chapter, and I cover them in later chapters.