In my last article, I created a SharePoint 2016 image that can be reused multiple times to create a farm. The question was then can we do something similar with SQL Server? The very short answer is absolutely! It’s even built right into the GUI (which I just recently learned).
I’m going to start this article the same way I did in the article about creating a SharePoint Server 2016 image, with the following disclaimer:
If you don’t read another word, please read the following.
What I am doing here is not intended to be used in production. If you want to assume the liability and try it, go for it. Know that it’s on you to verify how this is supported by Microsoft in a production environment and that my intention is to show that it is how I am doing things in my DEVELOPMENT environment.
With that out of the way – let’s get down to business.
I am going to start by copying the 2012R2Base.vhdx file I created in my previous blog post to a folder named SQL2014Base and renaming it to SQL2014Base.vhdx as shown below.
NOTE: Yes, you could start the process from scratch with a new image but starting from an existing image lets me eliminate having to go through the OS update process before I install SQL and will save time in the long run.
Creating the base image for the SQL Server
To build my single SQL 2014 server I am going to open Hyper-V and go through the standard New Virtual Machine creation process until I get to the 5th step where the wizard asks me to create a virtual hard disk, use an existing hard disk or attach a virtual hard disk later.
NOTE: In this case I am allowing the VM to connect to the internet in order to download and install any SQL Server updates. Once updates have been installed and the server image created it will not be connected to the internet again.
With the network connection set I am going to select “Use an existing hard disk”, browse to my SQL2014Base.vhdx file and select it.
Now, I’ll complete the wizard and let the virtual machine creation process run. Once the VM has been created I am going to open the settings console and change the number of virtual processors to 4.
With the number of virtual processors set I am going to start the VM for the first time and then watch as it goes through the Windows setup process. Once I have skipped entering my PID, accepted the EULA, and set an administrator password I can log into my new virtual machine for the first time.
At this point I am going to change the server name and restart it so I can set Hyper-V remote desktop console to open in full screen mode.
Creating the SQL Server image
As the “Accidental DBA” I have no clue when this option was added to SQL Server but I wish I had known about it sooner. When you install SQL using the GUI there is actually an option under Advanced settings to prepare an image of a stand-alone instance of SQL Server.
What this is going to do is install the binaries and created a named instance of SQL Server that you will actually configure after you have created your server and joined it to a domain.
I’ll launch the wizard which will check for any available updates before proceeding. After I have accepted the license terms I select the features I want to install as a part of my SQL Server instance. In this case I am installing just the database engine and management tools.
The next step is to configure an instance name which will be required when you are completing the image creation process later.
At this point the wizard is ready to prepare the image which will install the binaries and those SQL features that I identified as a part of the wizard. When the wizard completes the server will be ready to have SYSPREP run against it to create the server image.
Creating a SQL server to support SharePoint Server 2016
With my base SQL image created I can move my attention to creating the SQL Server required to support my SharePoint Server 2016 instance. I’ll start by copying the SQL2014Base.vhdx file to a folder named SQL01 and then renaming it to SQL01.vhdx.
I’ll start the new virtual machine wizard, name it SQL01, and point it at the SQL01 folder on my SSD drive.
I’ll leave it as a Generation 1 virtual machine, assign it 8192 MB of memory and then make sure that the “Use Dynamic Memory” checkbox is unchecked.
On the Configure Networking page I will set the new virtual machine to use an internal network connection so there is no connection to the internet. With the internet connection set I’ll configure the virtual machine to use an existing virtual hard disk.
Clicking “Next” brings up the virtual machine summary and the Finish button. I’ll click Finish to complete the virtual machine creation process.
Once the New Virtual Machine wizard has completed the creation process I’ll set the number of virtual processors to 4 as I did before (SYSPREP does not persist hardware settings) and then start the server so it can run through the Settings, PID, EULA and setting the administrators password.
Before I can finish configuring the SQL server I have to join the server to a domain. To do that I’ll take the following steps:
1. Change the server name to SQL01 and restart the server.
2. Change the IP address so that the SQL server is on the same network as my domain controller (not 192.168.1.1).
3. Either turn off the firewall or open the appropriate ports to allow communication with the domain. Because this is an isolated development environment I am going to disable the firewalls.
4. Join the spmechanic.local domain
With my new virtual machine joined to the domain I’ll start the SQL Server Installation Center by going to the D drive and opening it from the installation media. Once I have the installation center open I’ll navigate to the Advanced Settings page and click the link for “Image completion of a prepared stand-alone instance of SQL Server.
NOTE: if you open the SQL Server Installation Center using the link on the Apps page the installation wizard will not detect the appropriate license for your image.
I’ll take the defaults on the next few screens; accept the EULA, and I’ll leave Use Microsoft Update unchecked. I should see the Select Prepared Features screen now which shows the named instance I created earlier or I can prepare the shared components of the SQL instance only. I’m going to configure my named instance obviously.
Clicking next here lets me review the features that were chosen as a part of the image set up but it does NOT let me make any changes to it. I’ll continue to the Instance Configuration page where I’ll enter my named instance name.
The next page of the wizard lets me configure my service accounts. I’ll set the service account of the database engine to a domain account specifically for that purpose and set the password.
With the service accounts configured I’ll move on to the database engine configuration. I’ll add the current user as an administrator and if I had a secondary drive I could specify where I want the various data directories to reside and finally I could enable FILESTREAM.
Clicking Next takes me to the Ready to Complete Image dialog where I can review and complete the image creation process.
Now that my SQL image has been created I can open it from SQL Server Management Studio (SSMS) and I should be able to access my SQL instance as shown below. It’s important to note thet when you access the SQL server using SSMS you will have to use the named instance in the server name filed so that it shows similar to the following screenshot.
Clicking the connect button will open the database for me where I can add my SharePoint Server 2016 installation account and get ready to configure SharePoint.
I hope you’ve found this helpful.