How to Check MSSQL Server installed correctly

After Successful installation of MSSQL Server we have to verify that installation is proper and working as per the requirement. First of this line said that installation is successful then what is the need for verification..? Yes its required to verify because we have to ensure it should work as expected. Let’s See how to check mssql server installed correctly

How to check  MSSQL Server installed correctly

Here are a short number of post-installation checks which are useful to perform after re-booting your new SQL Server. You don’t have to run these, and there are other ways to check, but they are very useful for non-DBAs to be sure that the installation is basically sound and a connection can be made to the new SQL Server before handing it over to someone else.

Check 1: Has the SQL Server Service Started?

Check SQL Server has started. Go to Run –> services.msc Check MSSQL Server installed correctly

Open Services and check Services are running state

Open Services and check Services are running state

Check 2: Does Management Studio Work?

Check Management Studio works by firing it up.

check management studio working ssms

check management studio working

mssql studio opening

mssql studio opening

Click on NO when you see this dialog box: Check MSSQL Server installed correctly

MSSQL Server Management Studio

MSSQL Server Management Studio

SQL Server login Screen

SQL Server login Screen

As you see in above screen first Select Server type : Database Engine Provide Server Name (Instance Name) and authentication type (Windows Authentication and SQL Server authentication)

Check 3: Can you run a basic query against the new SQL Server?

Check SQL Server works by running a simple query from Management Studio:

New Query to know Server version

New Query to know Server version

Enter the query shown below and hit F5 to run it:

Know Version of MSSQL

Know Version of MSSQL

Check 4: Is SQL Server Agent Running?

Check SQL Server Agent is running for scheduled jobs. There should be a green arrow next to the SQL Server Agent database symbol (it’s small, you might have to look hard):

SQL Server Agent

SQL Server Agent

Check 5: Can SQL Server be seen from the Network?

Check that the new SQL Server can be seen from another SQL Server on the same domain by running isql –L (or osql –L):
If you can’t see the new SQL Server in this list, check that the SQL Server Browser service is started on the machine where you have just installed SQL Server.

Check 6: Has the TCP/IP network protocol library been enabled on the server?

If the browser service is started but you still cannot connect to the server, click on Start ->Programs -> SQL Server 2008 -> SQL Server Configuration Manager (on the server where SQL Server’s just been installed)

SQL Server Configuration Manager

SQL Server Configuration Manager

The SQL Server Configuration Manager window opens.
Click on the SQL Server Network Configuration node and expand it.
In the example below, we have MSSQLSERVER (a base instance of SQL Server), and SQLEXPRESS showing as installed.
If in doubt, click on Protocols for MSSQLSERVER.

SQL SErver Configuration Manager Services

SQL SErver Configuration Manager Services

In the above screenshot, the TCP/IP network protocol library is disabled. We need to enable it in order that remote servers can talk to the newly installed SQL Server.

  • A word of explanation : In most installations, Named Pipes can be ignored, unless there is a requirement for it. In virtually all environments, VIA can also be ignored as this protocol requires a special network card. Shared memory is the “local” protocol that SQL Server uses when talking to a client application on the same server as itself, for example when SQL Server Management Studio connects to it. It is usually best to leave this enabled.

You will need the TCP/IP protocol enabled if you need to connect to your new SQL Server from a remote client or another server via TCP/IP, which is what most networks use.
If it shows as DISABLED (above), double click on the TCP/IP protocol line, and the following window will appear:

TCP IP properties MSSQL Server

TCP IP properties MSSQL Server

Ensure that Enabled is set to Yes, and click on OK.
The following warning will appear:

confirm changes

confirm changes

Click on OK, and you will be returned to the Configuration Manager window, where TCP/IP will now be shown as enabled

enabled check mssql serverr installed correctly

enabled

Go back to the Services applet, and re-start the MSSQLSERVER service so that the TCP/IP protocol can be used to connect to your new SQL Server.
Then try to connect to it again from a remote machine.
If you have experienced problems with the previous connectivity tests, you should now be able to repeat at least some of them successfully.

Thanks for the Read.

More Articles

MSSQL Server installation and configuration Step by Step Guide

Thanks for your wonderful Support and Encouragement

blank

Ravi Kumar Ankam

My Name is ARK. Expert in grasping any new technology, Interested in Sharing the knowledge. Learn more & Earn More

Leave a Reply

Your email address will not be published. Required fields are marked *