Introduction

This blog is about how to configure/setup SQL Server 2012 and its services on local machine.

Blog Content Tested With:

• Windows 7 Ultimate Service Pack 1
• SQL Server 2012 Express Edition
• Database Management Tool:SQL Server Management Studio

Configuration Steps

1. SQL Server 2012 Installation: Installing from setup is not a rocket-science, its very similar to install any setup. You just have to click next/finish button.
For more please visit: http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2012/06/23/sql-server-2012-installation-guide/
These couple of points needs some attention when we installing SQL Server 2012 on our machine.

1. While installation please select the Default instance as Instance Name
2. On the mode selection page please select Mixed mode as we generally need windows Authentication Mode as well as SQL Server mode.

2. Allow remote connections in SQL Server 2012 :

1. Click Start, point to All Programs, click on Microsoft SQL Server 2012,under this folder you will find a new folder "Configuration Tools", and then click SQL Server Configuration Manager.
2. Click SQL Server Services from the left node, make confirm SQL Server (SQLEXPRESS) and SQL Server Browser running.
3. If SQL Server Browser is stopped, then select its properties(right click and select properties) and point to Service tab, change the Start Mode Disabled to Automatic, click the apply button, then click start option using right mouse click over SQL Server Browser.
4. Restart the SQL Server(SQLEXPRESS) using right mouse click over SQL Server(SQLEXPRESS).
5. Click SQL Server Network Configuration, point to Protocols for SQLEXPRESS, point to TCP/IP, make sure TCP/IP status is Enabled.
6. Open TCP/IP Properties form using right mouse click over TCP/IP, point to IP Address tab, point to TCP Port in Last section, change TCP Port to 1433, and click Apply button.

3. Create exceptions in Windows Firewall :

1. Click Start, then click to Control Panel, now click on Windows Firewall Settings
2. Click Change Settings link, point to Exceptions tab
3. Click Add port button, do the following:
   Name: 1433
   Port number: 1433
   Protocol: TCP

Click OK, and click apply

Enabling the Protocols for SQL Server Express

Under SQL native client 11.0 node you will find the branch named Client Protocols. Click on this branch. On the right-side pane you will be able to see three protocols

1. Shared Memory:Shared memory is the simplest protocol to use and has no configurable settings. Because clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer, it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.
2. TCP/IP:TCP/IP is a common protocol widely used over the Internet. It communicates across interconnected networks of computers that have diverse hardware architectures and various operating systems. TCP/IP includes standards for routing network traffic and offers advanced security features. It is the most popular protocol that is used in business today. Configuring your computer to use TCP/IP can be complex, but most networked computers are already correctly configured. To configure the TCP/IP settings that are not exposed in SQL Server Configuration Manager, see the Microsoft Windows documentation.
3. Named Pipes: Named Pipes is a protocol developed for local area networks. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first) or remote (on a networked computer).

To change the order, right click on protocol name from the right pane and then click on order. You will promped to a new window from where you can change the order of protocol which is to be used while communicating with SQL Server.