There are many reasons that a Windows administrator would want to create a new SQL server instance. Organization and segregation are two of the main reasons.
Latest posts by Andrew Jacops (see all)

As an analogy, think of your SQL server as a folder. Inside this folder we have many sub directories that allow us to organize and segregate our stored files. A lot of the time, the data in these sub folders will have the same or similar names. Or we just want to put pictures of cats under the “cats” folder and work files under the “work” folder.

The same is true with a SQL server. Creating a new SQL server instance is like creating a new subfolder. We can create a new instance that is just for testing applications and have another for production. We can also have two programs that have the same database name using the separate instances.

Creating separate SQL instances also cuts down on hardware and licensing costs. Administrators can use the same license for a single server. We don’t have to buy another server and/or another license.

Create the SQL Server Instance ^

It is important to note that we already have a single SQL server instance installed on this test server. This article explains how to install a separate SQL server instance side by side on the same server.

The first thing we need to do is acquire the installation media. When the auto run screen comes up, we want to select the Installation option to start the installation wizard.

Installation Center Main

The next screen asks us what type of installation we want to perform. Although it may not be the most obvious choice, select New SQL Server stand-alone installation or add features to an existing installation.

Installation Center Installation

The installation will now run the Setup Support Rules wizard to ensure that everything is good to go.

Installation Wizard RuleCheck

Upon completion it will report what issues, if any, were found and give a detailed report if you wish to view it. Click OK when you are finished looking the information over.

Installation Wizard Rule Passed

The installation will then search for the latest updates. You may choose to skip this scan and check for updates later. Click Next >.

Installation Wizard Product Updates

We are going to create a new SQL server instance, so select the Perform a new installation of SQL Server 2012 and click Next >.

Installation Wizard New Instance

Enter your license key. Unless your organization requires something different for licensing compliance, his should be the same key as the license key for the original instance of SQL server installed.

Installation Wizard License Key

The next box in the installation wizard is the EULA. Read and if you accept the license terms click Next >. Here you also have the opportunity to send Microsoft usage data for analysis.

Installation Wizard EULA

The next option depends on your environment and what you require from this SQL server installation. For the scope of this article, All Features With Defaults is selected. Click Next >.

Installation Wizard Features

The Feature Selection page lists all of the components that are going to be installed. Because we selected All Features With Defaults absolutely all of the options will be selected. Again, make sure that you select the options you require from creating this SQL server instance. Extra options will just slow the server down by requiring more resources. Click Next >.

Installation Wizard Features Confirm

The wizard runs through another rule check. This time it is for the Installation Rules. This screen, like the previous checks, will give information about the check and allow you to see more details if there is something that needs to be fixed. Click Next >.

Installation Wizard Rule Validation

Finally we are that the part of the installation wizard where we can name our new SQL server instance. This should be something informative and easy to recognize. Remember, you will be using this name to access the instance you create. For the purpose of this article, I will create the SQL server instance named New_Instance. Click Next >.

Installation Wizard Instance Name

Disk space requirements are then checked against what the installation requires. This is not the size of your database. You will need to allow sufficient space for your databases to grow comfortably. Click Next >.

Installation Wizard Disk Space

The Server Configuration dialogue box will allow you to configure what services of this instance you would like to automatically start. Change all of the services allowed to Automatic. Click Next >.

Installation Wizard Server Config

For the database engine configuration, I have chosen to use Windows Authentication Mode. You may choose to use mixed mode if you wish. Click the Add Current User button to add the user you are logged in as, or the Add… button to add a specific account for this purpose. Once complete, click Next >.

Installation Wizard Authentication

The next window is for the analysis services configuration. Again, click the Add Current User or the Add… button to select a specific account for this service and then click Next >.

Installation Wizard Analysis Service

As the reporting services configuration is out of the scope of this article, select the Install Only radio button and click Next >. This service can be configured at a later time.

Installation Wizard Analysis Reporting

For the distributed replay controller we will again click the Add Current User or the Add… button to select a specific account for this service and then click Next >.

Installation Wizard Distributed Replay

In the Controller Name textbox on the distributed replay client box, type something informative. In this case I chose to use New_Instance_DRC. Select an appropriate working directory and result directory by clicking the ellipses (…) button to the right of the fields. These should be directories that will easily be recalled. Click Next > when complete.

Installation Wizard Distributed Replay Client

On the Error Reporting dialogue box, select whether you would like to send Windows and SQL server error reports to Microsoft. I left the box unchecked as this is a test server. Click Next >.

Installation Wizard Error Reporting

Finally, we are at the last rule checking window. This part of the installation wizard will ensure all of the previous information entered will allow the installation for the new SQL server instance to complete successfully. Click Next > after reviewing any required information.

Installation Wizard Config Rules

Our Ready to Install dialogue box gives all of the information we used to create the SQL server instance. At this point, review the information ensuring that it is correct and click Install.

Installation Wizard Verify

The install will take some time depending on the resources available to the box. Although it is not required, an after-hours or low traffic period would be preferred to lessen the impact on the production environment.

Installation Wizard Installation

After some time, we are presented with the final window for the installation wizard. This box will give the status of every step in the process. Click the Close button after reviewing any pertinent information to end the SQL server instance installation wizard.

Installation Wizard Complete

Accessing the new SQL Server Instance ^

Accessing the newly created SQL server instance is as easy as adding the instance name after the server name. For example, our installation is localhost\New_Instance or <server_name>\<instance_name>.

Open the SQL Server Management Studio from the Windows Start menu.

SQLSMS Shortcut

When the Connect to Server dialogue box opens, type the server name and instance. Again, for this article we would use localhost\New_Instance and Windows Authentication.

SQLSMS Connect

In the left pane we can now see that we are connected to our new, running SQL server instance. From here we can manage it just as we would manage any other single SQL server.

SQLSMS Opened

Summary ^

Whether for licensing, organization, or segregation purposes, creating a SQL server instance is by far the cheapest and easiest way to implement a SQL server into your environment. Maintaining a clean landscape is much easier using separate instances. Thanks for reading!

29 Comments
  1. Jeff 5 years ago

    Thank you.  This is very clear and easy to understand.  I was up and running in just a few minutes.  I also see where there was 1 or 2 “gotchyas” that would have had me guessing for a bit.

  2. Dave 5 years ago

    Thanks Andrew.  You make the world a better place by helping people for the sake of helping people.

  3. Paisal 3 years ago

    Thank you. I was confuse because I thought local server name using computer name, it appear localhost

Leave a reply

Your email address will not be published.

*

© 4sysops 2006 - 2022

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account