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
Check 2: Does Management Studio Work?
Check Management Studio works by firing it up.
Click on NO when you see this dialog box: Check MSSQL Server installed correctly
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:
Enter the query shown below and hit F5 to run it:
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):
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)
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.
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:
Ensure that Enabled is set to Yes, and click on OK.
The following warning will appear:
Click on OK, and you will be returned to the Configuration Manager window, where TCP/IP will now be shown as 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.