Deploying EPiServer Alloy Database to SQL Azure

DeployingToSQLAzureThis article is related to article EPiServer in Azure Web Sites and SQL Azure. Deploy EPiServer 7.5 Alloy template database to the SQL Azure you will probably get at least 3 errors:
  1. Error SQL70015: Error validating element [dbo].[aspnet_Membership_GetNumberOfUsersOnline]: Deprecated feature 'Table hint without WITH' is not supported on SQL Azure.
  2. Error SQL71564: Table Table: [dbo].[CompletedScope] does not have a clustered index.
  3. Error SQL72014: .Net SqlClient Data Provider: Msg 40512, Level 16, State 1, Procedure InsertCompletedScope, Line 8 Deprecated feature 'Multiple table hints without comma' is not supported in this version of SQL Server.
Luckily all the errors are fixable.

1. aspnet_Membership_GetNumberOfUsersOnline Table hint without WITH

SQL Script for fix:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[aspnet_Membership_GetNumberOfUsersOnline]
    @ApplicationName            nvarchar(256),
    @MinutesSinceLastInActive   int,
    @CurrentTimeUtc             datetime
AS
BEGIN
    DECLARE @DateActive datetime
    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)

    DECLARE @NumOnline int
    SELECT  @NumOnline = COUNT(*)
    FROM    dbo.aspnet_Users u WITH(NOLOCK),
            dbo.aspnet_Applications a WITH(NOLOCK),
            dbo.aspnet_Membership m WITH(NOLOCK)
    WHERE   u.ApplicationId = a.ApplicationId                  AND
            LastActivityDate > @DateActive                     AND
            a.LoweredApplicationName = LOWER(@ApplicationName) AND
            u.UserId = m.UserId
    RETURN(@NumOnline)
END
GO
 

2. CompletedScope does not have a clustered index

SQL Script for fix:

GO
IF EXISTS (SELECT Name FROM sysindexes WHERE Name = 'IX_CompletedScope_CompletedScopeID') 
	DROP INDEX [IX_CompletedScope_CompletedScopeID] ON [dbo].[CompletedScope] WITH ( ONLINE = OFF )
GO
CREATE CLUSTERED INDEX [IX_CompletedScope_CompletedScopeID] ON [dbo].[CompletedScope]
(
	[completedScopeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

3. InsertCompletedScope Multiple table hints without comma is depricated

SQL Script for fix:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertCompletedScope]
@instanceID uniqueidentifier,
@completedScopeID uniqueidentifier,
@state image
As
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT ON
		UPDATE [dbo].[CompletedScope] WITH(ROWLOCK, UPDLOCK) 
		    SET state = @state,
		    modified = GETUTCDATE()
		    WHERE [email protected] 
		IF ( @@ROWCOUNT = 0 )
		BEGIN
			--Insert Operation
			INSERT INTO [dbo].[CompletedScope] WITH(ROWLOCK)
			VALUES(@instanceID, @completedScopeID, @state, GETUTCDATE()) 
		END
		RETURN
RETURN
GO
 
This article is related to article EPiServer in Azure Web Sites and SQL Azure. Deploy EPiServer 7…