Many programmers like to build their websites and test their projects on local servers before publishing to production server. For ASP, .Net developers, to set up local SQL server environment on Windows computers, you can Microsoft SQL Server Express. In this guide, we will use this great free tool to setup SQL server on Windows OS, such as Windows XP, Vista, Windows 7, 8, etc. After that you can connect to local SQL server through Microsoft SQL Server Management Studio, create SQL databases on your computer and so on.
How to Setup Local SQL Server on Windows Computer?
We will be using Microsoft SQL Server 2014 Express in this demo. You can also download and use other versions, such as Microsoft SQL Server 2008, Microsoft SQL Server 2012, Microsoft SQL Server 2016, etc. Firstly go to download Microsoft SQL Server 2014 Express from following link: http://www.microsoft.com/en-us/download/details.aspx?id=42299.
There are different editions from above download page, make sure to choose and download Express with Tools. This package contains everything needed to install and configure SQL Server as a database server including the full version of SQL Server 2014 Management Studio. If you like more advanced features, such as Full Text Search and Reporting Services, you can also downoad Express with Advanced Services. Note that these SQL server installers come with both 32 bit and 64 bit editions, make sure to choose the right one according to your Windows OS.
How to Connect to Local SQL Server on Windows Computer?
Run SQL Server 2014 Management Studio on your PC, you will be prompted to connect to SQL server instantly.
Here are the steps to connect to local SQL server on Windows:
- Choose Database Engine as the Server type;
- Input your local server instance name in the Server name box;
- Choose Windows Authentication as the authentication type;
- Click Connect button at the bottom to connec to local SQL server.
How to Find Local Server Name in SQL Server Management Studio?
If you forgot your local SQL server instance name, there are different ways you can find it out. At the SQL Server Management Studio Connect to Server window, click the down arrow besides Server name, then click <Browse for more …>.
After that you will get a pop-up dialogue where you can browse for more local servers and network servers.
The Local Servers tab opens by default. You can switch to Network Servers if need. In the Local Servers tab, you can find your local database engine and local SQL servers. Click on a SQL server name and click OK to select and connect to it in SQL Server Management Studio.
Why there is No Local SQL Server in SQL Server Management Studio?
Sometimes you may not find any SQL server in SQL Server Management Studio following above steps. If you have this trouble, possibly you have only install SQL server management studio software without SQL server instance. You should make sure to have SQL database engine service installed on your Windows PC. When you download Microsoft SQL Server Express from Microsoft website and set up a local SQL server on your PC, if you choose SQL Server Management Studio Express, this does not contain the database. It contains only the tools to manage SQL Server instances. You can use this if you already have the database and only need the management tools. For instance, you may have created MSSQL database on remote SQL server and want only to manage SQL server remotely from computer. You can choose to install different SQL databases, including LocalDB, SQL Express, SQL Azure. For beginners, we recommend you to choose Express edition which includes the SQL Server database engine only.
When installing SQL Server 2014 Express edition, there will be a Feature Selection step, make sure to select and install at least the following two items: Database Engine Services and SQL Client Connectivity SDK.
How to Create MSSQL database on Local SQL Server?
Once you have connected to local SQL server using SQL Server Management Studio, you can easily create MSSQL databases on local SQL server from there. From the Object Explorer, expand the local server instance, right click on Database, and choose New Database …., then follow on-screen tips to set up local database server on your Windows computer.
