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.

Assumptions

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.

Preparation

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

sc_sql01

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

sc_sql02

Accept the license terms and click Next

sc_sql03

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

sc_sql04

Let the SQL Server setup files install

sc_sql05

For Feature Selection leave as default, click Next

sc_sql06

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

sc_sql07

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

Click Next

sc_sql08

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:

sc_sql09

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

sc_sql10

No changes are necessary on the User Instances tab

sc_sql11

No changes are necessary on the FILESTREAM tab. Click Next

sc_sql12

Click Next to install SQL Server

sc_sql13

Allow SQL Server to complete installation

sc_sql14

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 )

sc_sql15

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 dev.sitecore.net and for versions prior to 8.0 download from sdn.sitecore.net

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]
GO
CREATE DATABASE [DemoProject_CI_core] ON 
( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_core.MDF' ),
( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_core.LDF' )
 FOR ATTACH
GO

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

CREATE DATABASE [DemoProject_CI_Web] ON 
( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_web.MDF' ),
( FILENAME = N'D:\SQLData\DemoProject\DemoProject_CI_web.LDF' )
 FOR ATTACH
GO


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]
CREATE USER [sitecore] FOR LOGIN [sitecore] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [DemoProject_CI_Master]
CREATE USER [sitecore] FOR LOGIN [sitecore] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [DemoProject_CI_Web]
CREATE USER [sitecore] FOR LOGIN [sitecore] WITH DEFAULT_SCHEMA=[dbo]
GO

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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

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

Join 12 other followers

%d bloggers like this: