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.
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.
The installation will now run the Setup Support Rules wizard to ensure that everything is good to go.
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.
The installation will then search for the latest updates. You may choose to skip this scan and check for updates later. Click Next >.
We are going to create a new SQL server instance, so select the Perform a new installation of SQL Server 2012 and click Next >.
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.
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.
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 >.
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 >.
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 >.
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 >.
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 >.
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 >.
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 >.
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 >.
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.
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 >.
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.
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 >.
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.
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.
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.
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.
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.
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.
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.
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!