SQL Server Installation Confirmation

When you have completed the installation of SQL Server I would recommend performing a SQL Server installation confirmation; check it has worked ok, by reviewing the installation results, start the services and connect to your new database server.

This article will provide some guidance on how best to do this.

When you have completed the installation several new items will appear on your server. For a complete installation confirmation, it is important to understand what has been installed:

• SQL Server Services; Your server will have several new services including SQL Server, SQL Server Agent, MS DTC and MS Search.
• Management Tools; tools that allow you to administer the SQL Server
• Databases; the system databases Master, Model, msdb, and TempDB
• Files and Folders; Default location is c:\\Program Files\\ Microsoft SQL Server

Stopping and Starting SQL Services

In order for SQL Server to work correctly the SQL Server Service must be started.

This can be done automatically or manually. If during the installation you chose for the SQL Server service to start automatically then it will start each time the operating system starts, for example after a server reboot etc. if you didn’t select this option during install and you would like configure your instance of SQL Server to do this after the install this can be done using Enterprise Manager (EM) SQL Server Services Manager and through the Computer Management Console under Services and Applications.

It is important to note that if you have not chosen for your SQL Server Service not to automatically start; that is you want to start it manually, you will have to start the service every time the operating system starts up again. This can be done using EM, SQL Server Service manager and Services in the Control panel.

When the SQL Service is "Started" it will allow users to connect to establish new connections.

"Pausing" the SQL Server Service prevents new connections but allows connections already made to carry on these users will be unaffected until they disconnect. This allows for a preparation of server maintenance without dramatically cutting the service.

"Stopping" disables logins and prevents users from connecting. The SQL Server will stop functioning.

SQL Server Agent Services

When this service is started you can automate activities such as jobs which can be used to automate system maintenance tasks among others. You can schedule for your databases to backed up during quiet off-peak periods without having to be physically there to kick off the job.

If the SQL Server Agent Service is stopped or paused these scheduled jobs will not be activated and hence will not occur.

Connecting to SQL Server

The final task in doing a SQL Server installation confirmation is connecting to the SQL Server. You can connect to SQL Server using one of the graphical administration tools such Query Analyser or Enterprise Manager (EM), alternatively you can use the OSQL and the command prompt utility.

To connect to a default instance you specify the server name. The following screen shots are taken from the login screen of Query Analyser:

You must connect to a named instance separately by specifying the instance as well as the server name in the following format:

(MyServer)\\(MyInstance)

Although you connect using both Enterprise Manager and Query analyser I would recommend using for testing your SQL Server connectivity with Query Analyser as the process is more straight forward.

Configuring SQL Server Enterprise Manager

To manage SQL Server using Enterprise Manager you must first register the server with Enterprise Manager.

Registering Your SQL Servers

When you register a SQL Server you must have permission to connect to that server. The local server will automatically be registered when SQL Server is installed. To register a server you must:

Right Click on SQL Server Group in EM

Select "New Server Registration"

The Server Name, and if applicable, the named instance:

The authentication Mode - Windows or SQL Server

A Server Group

Registration Information

The SQL Server registration information is stored in the operating system registry. EM uses this information each time that users connect to the SQL a registered server. This functionality is similar to the way in which Windows keeps a separate profile for each user.

Registering Multiple Named Instances

Registering multiple instances and servers allows you manage and administer these multiple instances and servers from one installation of EM on one computer. The New Server registration will guide you through this.

Administering a SQL Server

To be able to administer and manager an instance of SQL Server you must be a member of the sys admin role on that instance.

Creating Server Groups

Creating server groups allows you to manage your multiple instances of SQL Server by categorizing the servers into relevant groups with relevant titles.

Conclusion

This article has discussed how to perform a SQL Server installation confirmation, and that the process has completed correctly. We have looked at what services need to be started for connectivity to take place along with other services that when started allow for the automation of certain activities. We have looked at the client tools such as Query Analyser and Enterprise Manager that you can use to connect to the database server. I have also looked in detail at how to setup and configure your installation of Enterprise Manager.

Return to installing the SQL Server database