StoreFront 2 – Creating & Configuring The SQL Database

StoreFront 2 – Creating & Configuring The SQL Database

Creating & Configuring The SQL Database

This section of the article provides the steps required to create and configure a SQL Database for Citrix StoreFront.

Citrix StoreFront requires SQL commands to be ran using the SQL Management Console.  To create the StoreFront Database perform the following steps :-

  • Logon to the Server using the SQL Management Console
  • Copy the commands below to a Notepad document or download the attachment StoreFront-Database-Creation-Script.txt file

USE [master]

CREATE DATABASE [%%DATABASE_NAME%%] ON  PRIMARY

( NAME = N’MyApps’, FILENAME = N’%%MDF_FILE%%’ , SIZE = 5096KB ,

  MAXSIZE = UNLIMITED, FILEGROWTH = 10% )

LOG ON

( NAME = N’MyApps_log’, FILENAME = N’%%LOG_FILE%%’ , SIZE = 560KB ,

  MAXSIZE = 2048GB , FILEGROWTH = 10% )

COLLATE latin1_general_CI_AS_KS

IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))

begin

EXEC [%%DATABASE_NAME%%].[dbo].[sp_fulltext_database] @action = ‘enable’

end

ALTER DATABASE [%%DATABASE_NAME%%] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET ANSI_NULLS OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET ANSI_PADDING OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET ANSI_WARNINGS OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET ARITHABORT OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET AUTO_CLOSE OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE [%%DATABASE_NAME%%] SET AUTO_SHRINK OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE [%%DATABASE_NAME%%] SET CURSOR_CLOSE_ON_COMMIT OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET CURSOR_DEFAULT  GLOBAL

ALTER DATABASE [%%DATABASE_NAME%%] SET CONCAT_NULL_YIELDS_NULL OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET NUMERIC_ROUNDABORT OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET QUOTED_IDENTIFIER OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET RECURSIVE_TRIGGERS OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET DISABLE_BROKER

ALTER DATABASE [%%DATABASE_NAME%%] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET DATE_CORRELATION_OPTIMIZATION OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET TRUSTWORTHY OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET ALLOW_SNAPSHOT_ISOLATION OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET PARAMETERIZATION SIMPLE

ALTER DATABASE [%%DATABASE_NAME%%] SET READ_COMMITTED_SNAPSHOT OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET HONOR_BROKER_PRIORITY OFF

ALTER DATABASE [%%DATABASE_NAME%%] SET READ_WRITE

ALTER DATABASE [%%DATABASE_NAME%%] SET RECOVERY FULL

ALTER DATABASE [%%DATABASE_NAME%%] SET MULTI_USER

ALTER DATABASE [%%DATABASE_NAME%%] SET PAGE_VERIFY NONE

ALTER DATABASE [%%DATABASE_NAME%%] SET DB_CHAINING OFF

  • Replace the references to %%DATABASE_NAME%% with the Name of the Database you want to create

E.G. CREATE DATABASE [StoreFront-Datastore] ON  PRIMARY

  • Replace the references to %%MDF_FILE%% with the exact path to the Database MDF file

E.G. ( NAME = N’MyApps’, FILENAME = N’E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\StoreFront-Datastore.MDF‘ , SIZE = 5096KB ,

  • Replace the references to %%LOG_FILE%% with the exact path to the Database MDF file

E.G. ( NAME = N’MyApps_log’, FILENAME = N’E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\StoreFront-Datastore_log.ldf‘ , SIZE = 560KB ,

  • Click on the New Query button and copy the modified commands from the Notepad document and execute them

Creating The Tables

The next step is to create the tables in the Database.  To create the tables in the Database perform the following steps :-

  • Copy the commands below to a Notepad document or download the attachment StoreFront-Database-Tables-Script.txt file

USE [%%DATABASE_PATH%%]

/****** Object:  Table [dbo].[User]  ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[User](

 [id] [int] IDENTITY(1,1) NOT NULL,

 [username] [nvarchar](100) COLLATE latin1_general_CS_AS_KS NOT NULL,

CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 

(

 [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 

 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) 

 ON [PRIMARY]

) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [username_idx] ON [dbo].[User] 

(

 [username] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 

 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 

 ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) 

 ON [PRIMARY]

/****** Object:  Table [dbo].[Subscription]  ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON 

CREATE TABLE [dbo].[Subscription](

 [id] [int] IDENTITY(1,1) NOT NULL,

 [subscription_ref] [varchar](32) COLLATE latin1_general_CS_AS_KS NOT NULL,

 [resource_id] [nvarchar](400) COLLATE latin1_general_CS_AS_KS NOT NULL,

 [user_id] [int] NOT NULL,

 [status] [int] NOT NULL,

 [metadata] [nvarchar](max) NULL,

 [secure_metadata] [nvarchar](max) NULL,

CONSTRAINT [PK_subscriptions] PRIMARY KEY CLUSTERED 

(

 [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 

 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) 

 ON [PRIMARY]

) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [subscription_ref_idx] ON 

 [dbo].[Subscription]

(

[subscription_ref] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 

 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 

 ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) 

 ON [PRIMARY]

CREATE NONCLUSTERED INDEX [user_resource_idx] ON [dbo].[Subscription] 

(

[user_id] ASC,

[resource_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 

 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 

 ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) 

 ON [PRIMARY]

/****** Object:  Default [DF_subscriptions_status] ******/

ALTER TABLE [dbo].[Subscription] 

 ADD  CONSTRAINT [DF_subscriptions_status]  

 DEFAULT ((0)) FOR [status]

/****** Object:  ForeignKey [FK_subscriptions_user_id] ******/

ALTER TABLE [dbo].[Subscription]  

 WITH CHECK ADD  CONSTRAINT [FK_subscriptions_user_id] 

 FOREIGN KEY([user_id])

REFERENCES [dbo].[User] ([id])

ALTER TABLE [dbo].[Subscription] 

 CHECK CONSTRAINT [FK_subscriptions_user_id]

CREATE TABLE [dbo].[SchemaDetails](

 [major_version] [int] NOT NULL,

 [minor_version] [int] NOT NULL,

 [details] [nvarchar](max) NULL

) ON [PRIMARY]

INSERT INTO [dbo].[SchemaDetails] ([major_version], [minor_version]) 

 VALUES (1, 0)

  • Replace the references to %%DATABASE_NAME%% with the Name of the Database you want to create

E.G. USE [StoreFront-Datastore]

  • Click on the New Query button and copy the modified commands from the Notepad document and execute them

Granting Access To The Database For The StoreFront Servers

The next step is to grant access to the database for the StoreFront servers as StoreFront uses Computer Accounts for access to SQL. Citrix recommends creating a Local Group on the SQL Server and then making the StoreFront Servers members of that Local Group.

Once the Local Group has been created perform the following steps to grant permissions to the Database for the Local Groups :-

  • Copy the commands below to a Notepad document or download the attachment StoreFront-DB-Local-Group-Permissions-Script.txt file

USE [master]

CREATE LOGIN [%%WINDOWS_USER%%] FROM WINDOWS;

ALTER LOGIN [%%WINDOWS_USER%%] 

 WITH DEFAULT_DATABASE = [%%DATABASE_NAME%%];

  • Replace the references to %%WINDOWS_USER%% with the Local Group created on the SQL Server

E.G. CREATE LOGIN [W2K8SQL01\StoreFront-Servers] FROM WINDOWS;

  • Replace the reference to %%DATABASE_NAME%% with the name of the StoreFront Database

E.G. WITH DEFAULT_DATABASE = [StoreFront-Datastore];

  • Click on the New Query button and copy the modified commands from the Notepad document and execute them

Creating A Database User

The last step is to create a database user mapped to the new login and granted permissions to the StoreFront Database.  To create a database user mapped to the new login and granted permissions to the StoreFront Database perform the following steps :-

  • Copy the commands below to a Notepad document or download the attachment StoreFront-DB-User-Script.txt file

USE [%%DATABASE_NAME%%]

CREATE USER [CitrixSubscriptionDBUsers] FOR LOGIN [%%WINDOWS_USER%%];

EXEC sp_addrolemember N’db_datawriter’, N’CitrixSubscriptionDBUsers’;

EXEC sp_addrolemember N’db_datareader’, N’CitrixSubscriptionDBUsers’;

  • Replace the references to %%WINDOWS_USER%% with the Local Group created on the SQL Server

E.G. USE [StoreFront-Datastore]

  • Replace the reference to %%DATABASE_NAME%% with the name of the StoreFront Database

E.G. CREATE USER [CitrixSubscriptionDBUsers] FOR LOGIN [W2K8SQL01\StoreFront-Servers];

  • Click on the New Query button and copy the modified commands from the Notepad document and execute them