Basic Configuration After Installation

After the installation, you should verify the basic operation of SQL Server. SQL Server 7 is mostly self-configuring . Many options are available to fine-tune the configuration, but these are necessary only for specialized purposes. We'll discuss configuration in Chapter 15. Initially, you should leave the default configuration alone until you have a particular reason to change it.

Starting the SQL Server Service

After a successful installation, start SQL Server. The most common way to do this is by using the SQL Server Service Manager (from the Start menu). Choose Programs, Microsoft SQL Server 7.0, and then Service Manager. (The installation program also puts the SQL Server Service Manager in the startup group for the operating system, so after rebooting your machine, an icon for the SQL Server Service Manager appears on the taskbar, in the corner near the clock. You can also use the Services applet of the Windows NT Control Panel or SQL Server Enterprise Manager or issue a NET START MSSQLSERVER command from a Windows NT console (DOS prompt). You can also configure SQL Server to start automatically, either through the Services applet of the Windows NT Control Panel or by editing the properties of SQL Server in the Enterprise Manager.

After SQL Server is running (the icon on the taskbar will have a green arrow), use one of the most basic applications, SQL Server Query Analyzer, to ensure that you can connect. Initially, the only available SQL Server login name (sa) has a null password, so you can leave sa in the Login Name box and leave the password field blank. Or you can select the Use Windows NT Authentication option so that no Login Name or Password is required. Then change your database to the pubs sample database and run a couple of simple queries (for example, SELECT * FROM authors ORDER BY au_lname ) to be sure that SQL Server is running correctly.

Changing the System Administrator Password

After you verify that SQL Server is running and responding to queries, you should change the password for the System Administrator (sa) account. From the Query Analyzer, use sp_password to change the sa password. As mentioned earlier, initially the password is null (no password). Be sure to pick a password you'll remember, because by design there is no way to read a password (it is stored encrypted) it can only be changed to something else. Using Query Analyzer from the master database to change the password to Banks_14 , you'd issue the following command:

 sp_password NULL, 'Banks_14', sa 

Using SQL Server Enterprise Manager, you can change the password from the server's Security folder. Select Logins, and then double-click sa in the right pane.

Note that the actual password is stored in a table as SQL Server data. So if you've chosen a case-sensitive sort order, passwords will also be case sensitive and must be entered exactly as defined.

SQL Server Enterprise Manager vs. SQL Statements

You can do nearly any task with SQL Server. At the lowest level, you can issue an SQL statement or a stored procedure. However, it's usually simpler to use SQL Server Enterprise Manager, which provides a front end for these commands and frees you from needing to know exact steps and syntax. Administrators who are proficient with Microsoft Visual Basic for Applications (VBA) might choose to use simple VBA scripts that use the SQL Server database administration object model, known as SQL-DMO (SQL Distributed Management Objects).

Since this book is geared toward database developers and administrators, we'll typically show the SQL statements and stored procedures that are used to accomplish a task and simply reference the easier methods , assuming that this will provide the best explanation of what's going on. We'll also tell you what to do without providing step-by-step directions. You can consult the SQL Server documentation for exact syntax of all options and so on. This book is meant to complement, but not replace, the product documentation.

Configuring SQL Server's Error Log

A new error log is created each time the SQL Server service is started. (The online documentation says you have to stop and start the computer running SQL Server to start a new error log, but this is not true.) If you want to start a new error log without stopping and restarting SQL Server, you can use the sp_cycle_ errorlog system stored procedure. Keep in mind that the new error log created when you execute this procedure will not contain all the normal boot messages that are put in the error log when SQL Server starts up. You'll have to refer to an older copy of the error log to see these messages. However, if you execute sp_cycle_errorlog too many times, you might not have any copies that contain the boot messages. By default, SQL Server retains copies of the previous six error logs and gives the most recent copy (prior to the currently active error log) the extension .1, the second most recent copy the extension .2, and so on. The current error log has no extension. You can change the number of error logs maintained by editing the registry. Find the key HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\MSSQLServer\MSSQLServer, and edit that key by adding a new value. Define the value with the name NumErrorLogs and the type REG_DWORD. Supply any initial value desired, but keep in mind that the value you enter when editing the registry will be in hexadecimal.