Building a Continuous Integration Environment for Sitecore Part 3 – SQL Server

This is part 3 of the series of post that I am doing about building a Continuous Integration Environment using Sitecore, TeamCity, Octopus Deploy, and Unicorn.

Part 1 – Setting Up TeamCity
Part 2 – Setting up OctopusDeploy
Part 3 – This post
Part 4 – IIS
Part 5 – Octopus Environment and Deployment Configuration
Part 6 – TeamCity Project
Part 7 – OctopusDeploy Project
Part 8 – Sitecore Item Synchronization using Unicorn
Part 9 – Displaying Build number on Sitecore Home Page & Tagging Git
Part 10 – config transformations using Octopus Deploy
Part 11 – Deploying to a Sitecore Multi-Instance Configuration

In this post I will be installing SQL Server. This server is going to be used by Sitecore. All Sitecore instances will be running on a separate IIS Server that is still to be built. This short post will show installing SQL Server.


Just a quick reminder

All the servers are running Windows Server 2012 R2.All the servers are stand alone, and are not part of a domain. All servers have C and D drives.

Everything that is documented here and on subsequent posts are based on freshly installed Windows Servers.


Create the following drives as they will be needed later

  • D:\SQLBackup
  • D:\SQLData

Download the following software. It will be used during this setup

SQL Server

For this I am using SQL Server Express, but any version of SQL Server should suffice. By keeping SQL Server on its own server, if I want to upgrade versions, it will be easier for me if I do decide in the future to swap in a new server, or servers. It would be a little bit more difficult if I installed SQL Server on the IIS server where Sitecore will be located.

From the software downloaded, run SQLEXPRWT_x64_ENU.exe


Select New SQL Server stand-alone installation or add features to an existing installation


Accept the license terms and click Next


Accept default to include SQL Server product updates, and click Next


Let the SQL Server setup files install


For Feature Selection leave as default, click Next


You can leave instance configuration with default options and click Next, or I create an instance name to show what version of SQL Server it is


Change the SQL Server Browser to Automatic Startup Type.
Leave the collation as default.

Click Next


I prefer mixed mode instead of only Windows authentication mode. Generate a secure random password to be assigned to the SA account.

Switch to Data Directories tab:


Set Data root dictionary, User database directory, User database log directory, Temp DB directory, and Temp DB Log directory to D:\SQLData

Set Backup Directory to D:\SQLBackup


No changes are necessary on the User Instances tab


No changes are necessary on the FILESTREAM tab. Click Next


Click Next to install SQL Server


Allow SQL Server to complete installation


Click Close

Run SQL Server Configuration Manager

Select SQL Server Network Configuration | Protocols for SQL2012 (Note that this is based on the name I gave the instance )


Ensure TCP/IP is enabled

Sitecore Setup

Now that SQL Server is running its time to attach the databases from the version of Sitecore being used.

For Sitecore 8.0 and above, download from and for versions prior to 8.0 download from

Just remember only a Certified Sitecore Developer will be able to access these sites and have the ability to download files.

Download the zip file of the relevant version of Sitecore, and extract the zip file to a relevant location on disk.

For the project I am working on that is based on currently using 7.2 rev 140526

My convention, or preference, is to create a folder under the SQLData folder which is named after the project being worked on. For this Demo I will just create a folder called DemoProject

Copy the *.mdf and *.log file from the \Databases folder, and copy them to D:\SQLData\DemoProject. Do not copy the \oracle folder as it is not required.

The next think I like to do is to rename the files to match the project name and the environment that they will be used in. Therefore rename the files as follows:

Original File Name Modified File Name
Sitecore.Core.MDF DemoProject_CI_Core.mdf
Sitecore.Core.ldf DemoProject_CI_Core.ldf
Sitecore.Master.MDF DemoProject_CI_Master.mdf
Sitecore.Master.ldf DemoProject_CI_Master.ldf
Sitecore.Web.MDF DemoProject_CI_Web.mdf
Sitecore.Web.ldf DemoProject_CI_Web.ldf

Run SQL Server management studio, and execute the following SQL statement to attach the databases.

USE [master]
CREATE DATABASE [DemoProject_CI_core] ON 
( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_core.MDF' ),
( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_core.LDF' )

CREATE DATABASE [DemoProject_CI_Master] ON 
( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_master.MDF' ),
( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_master.LDF' )

( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_web.MDF' ),
( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_web.LDF' )

You may receive multiple messages about running the upgrade step but that is nothing to worry about

As this server is not part of a domain I am going to create a SQL Server user account that can access the databases, so that when I configure the connection strings from Sitecore, it will connect with no problems

For simplicity I am going to just create a user named sitecore, and having a default password of sitecore. Please note that this is for demo purposes only. You should either

  • Have the servers as part of a domain, and have a domain user granted access to SQL Server. This domain user will need to the account that the AppPool that the site on IIS will run under
  • Create a very strong password

CREATE LOGIN [sitecore] WITH PASSWORD=N'€sitecore'

Finally you want to give this SQL user permission to access the databases just created. For simplicity I will be giving them the dbo schema, but you may want to rethink that on a production environment

USE [DemoProject_CI_Core]

USE [DemoProject_CI_Master]

USE [DemoProject_CI_Web]

At this point, I am not having a custom database, so I will not be installing an Octopus Tentacle on this server to do any database deployments. Any changes to Sitecore will be done using Packages or Unicorn

Next Steps

At this point SQL Server is ready for use. In the next step I will setup and configure the IIS Server.


My musing about anything and everything

Tagged with: , , ,
Posted in CI, Continuous Integration, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other subscribers
%d bloggers like this: