MVC ELMAH and SQL Azure

Question

back-story: We mainly use AWS for everything (hosting, database, notifications, etc.). Now, I'm looking at moving the database side to SQL Azure since we've been getting crazy bills on AWS RDS. So all I tried to do was create a DB in SQL Azure and update the connection string to point to the new DB. In the past, ELMAH (this specific implementation: https://github.com/alexanderbeletsky/elmah.mvc) worked flawlessly in the past.

current situation: I just created a new DB in SQL Azure and noticed key differences right away namely on not supporting:

ON [PRIMARY], NONCLUSTERED KEYS, etc.

I migrated my db fine (for now), but when I applied the updated scripts for ELMAH to the db and tried to go into the tool, I get errors!

I'm somehow convinced that it's a db problem, because if I remove: <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="DefaultConnection"/> which basically defaults ELMAH to store everything locally, I get to access ELMAH.

Anybody got ELMAH to work on SQL Azure? Could you give me the SQL script to generate the tables and stored procedures?

Answer 1

This is the db script I use for ELMAH DBs on SQl Azure:

--~Changing index [dbo].[ELMAH_Error].PK_ELMAH_Error to a clustered index.  You may    want to pick a different index to cluster on. SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].    [ELMAH_Error]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[ELMAH_Error]( [ErrorId] [uniqueidentifier] NOT NULL, [Application] [nvarchar](60) NOT NULL, [Host] [nvarchar](50) NOT NULL, [Type] [nvarchar](100) NOT NULL, [Source] [nvarchar](60) NOT NULL, [Message] [nvarchar](500) NOT NULL, [User] [nvarchar](50) NOT NULL, [StatusCode] [int] NOT NULL, [TimeUtc] [datetime] NOT NULL, [Sequence] [int] IDENTITY(1,1) NOT NULL, [AllXml] [nvarchar](max) NOT NULL, CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY CLUSTERED  ( [ErrorId] ASC )WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF) ) END  IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ELMAH_Error]') AND name = N'IX_ELMAH_Error_App_Time_Seq') CREATE NONCLUSTERED INDEX [IX_ELMAH_Error_App_Time_Seq] ON [dbo].[ELMAH_Error]  ( [Application] ASC, [TimeUtc] DESC, [Sequence] DESC )WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE  = OFF) GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =     OBJECT_ID(N'[DF_ELMAH_Error_ErrorId]') AND type = 'D') BEGIN ALTER TABLE [dbo].[ELMAH_Error] ADD  CONSTRAINT [DF_ELMAH_Error_ErrorId]  DEFAULT (newid()) FOR [ErrorId] END  GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ELMAH_GetErrorsXml]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[ELMAH_GetErrorsXml] ( @Application NVARCHAR(60), @PageIndex INT = 0, @PageSize INT = 15, @TotalCount INT OUTPUT ) AS   SET NOCOUNT ON  DECLARE @FirstTimeUTC DATETIME DECLARE @FirstSequence INT DECLARE @StartRow INT DECLARE @StartRowIndex INT  SELECT      @TotalCount = COUNT(1)  FROM      [ELMAH_Error] WHERE      [Application] = @Application  -- Get the ID of the first error for the requested page  SET @StartRowIndex = @PageIndex * @PageSize + 1  IF @StartRowIndex <= @TotalCount BEGIN      SET ROWCOUNT @StartRowIndex      SELECT           @FirstTimeUTC = [TimeUtc],         @FirstSequence = [Sequence]     FROM          [ELMAH_Error]     WHERE            [Application] = @Application     ORDER BY          [TimeUtc] DESC,          [Sequence] DESC  END ELSE BEGIN      SET @PageSize = 0  END  -- Now set the row count to the requested page size and get -- all records below it for the pertaining application.  SET ROWCOUNT @PageSize  SELECT      errorId     = [ErrorId],      application = [Application],     host        = [Host],      type        = [Type],     source      = [Source],     message     = [Message],     [user]      = [User],     statusCode  = [StatusCode],      time        = CONVERT(VARCHAR(50), [TimeUtc], 126) + ''Z'' FROM      [ELMAH_Error] error WHERE     [Application] = @Application AND     [TimeUtc] <= @FirstTimeUTC AND      [Sequence] <= @FirstSequence ORDER BY     [TimeUtc] DESC,      [Sequence] DESC FOR     XML AUTO  '  END GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ELMAH_GetErrorXml]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[ELMAH_GetErrorXml] ( @Application NVARCHAR(60), @ErrorId UNIQUEIDENTIFIER ) AS  SET NOCOUNT ON  SELECT      [AllXml] FROM      [ELMAH_Error] WHERE     [ErrorId] = @ErrorId AND     [Application] = @Application  '  END GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].    [ELMAH_LogError]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[ELMAH_LogError] ( @ErrorId UNIQUEIDENTIFIER, @Application NVARCHAR(60), @Host NVARCHAR(30), @Type NVARCHAR(100), @Source NVARCHAR(60), @Message NVARCHAR(500), @User NVARCHAR(50), @AllXml NVARCHAR(MAX), @StatusCode INT, @TimeUtc DATETIME ) AS  SET NOCOUNT ON  INSERT INTO     [ELMAH_Error]     (         [ErrorId],         [Application],         [Host],         [Type],         [Source],         [Message],         [User],         [AllXml],         [StatusCode],         [TimeUtc]     ) VALUES     (         @ErrorId,         @Application,         @Host,         @Type,         @Source,         @Message,         @User,         @AllXml,         @StatusCode,         @TimeUtc     )  '  END GO 

SQL Azure is a special version of SQL and there are some things that it wont support.

Did you use the SQL Azure Migrate Wizard to migrate your db?