/* The script creates the Citrix StoreFront database as per http://support.citrix.com/proddocs/topic/dws-storefront-12/dws-deploy-multi-database.html Update the variables below to suit your environment Jason Poyner, jason.poyner@deptive.co.nz 7 December 2012 v1.0 */ USE [master] declare @DATABASE_NAME nvarchar(50) declare @MDF_FILE nvarchar(200) declare @LOG_FILE nvarchar(200) declare @STOREFRONT_COMPUTER_GROUP nvarchar(200) declare @sql nvarchar(MAX) declare @paramdef nvarchar(MAX) /******************* UPDATE THESE VARIABLES **********************/ set @DATABASE_NAME = 'Citrix_StoreFront_Store' set @MDF_FILE = '"C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' + @DATABASE_NAME + '.mdf"' set @LOG_FILE = '"C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' + @DATABASE_NAME + '_log.ldf"' set @STOREFRONT_COMPUTER_GROUP = '\CitrixStoreFrontServers' /*****************************************************************/ set @sql ='CREATE DATABASE [' + @DATABASE_NAME + '] ON PRIMARY ( NAME = ' + @DATABASE_NAME + ', FILENAME = ' + @MDF_FILE + ' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% ) LOG ON ( NAME = ' + @DATABASE_NAME + '_log, FILENAME = ' + @LOG_FILE + ' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10% ) COLLATE latin1_general_CI_AS_KS' EXEC(@sql) IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin set @sql = '[' + @DATABASE_NAME + '].[dbo].[sp_fulltext_database] @action = enable' EXEC(@sql) end set @sql = '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' EXEC(@sql) set @sql = 'USE [' + @DATABASE_NAME + '] /****** 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)' EXEC(@sql) set @sql = 'USE [master]; CREATE LOGIN [' + @STOREFRONT_COMPUTER_GROUP + '] FROM WINDOWS;' EXEC(@sql) set @sql = 'USE [master]; ALTER LOGIN [' + @STOREFRONT_COMPUTER_GROUP + '] WITH DEFAULT_DATABASE = [' + @DATABASE_NAME +'];' EXEC(@sql) set @sql = 'USE [' + @DATABASE_NAME + ']; CREATE USER [CitrixSubscriptionDBUsers] FOR LOGIN [' + @STOREFRONT_COMPUTER_GROUP +'];' EXEC(@sql) set @sql = 'USE [' + @DATABASE_NAME + ']; EXEC sp_addrolemember @role, @user;' set @paramdef = '@role nvarchar(50), @user nvarchar(50)' EXEC sp_executesql @sql, @paramdef, @role='db_datawriter',@user='CitrixSubscriptionDBUsers' EXEC sp_executesql @sql, @paramdef, @role='db_datareader',@user='CitrixSubscriptionDBUsers'