We just moved a Sage CRM v7.1 system to have its database hosted on a Microsoft Azure SQL Database platform.

**This article is documentation of an exercise we did BUT Azure is NOT supported by Sage and we advise that you therefore do not implement this on a Live environment**

We thought it might be useful to document the lessons from this exercise.

***update 24 July 2017***

****************************************************************************************************************

See below for an update based on the experience of James Hutchison and this his team at Realisable Software Ltd who very kindly offered details on their experience moving to an Azure hosted environment.

****************************************************************************************************************

Please note :

• The Sage CRM system was version 7.1 so later versions may differ.
• If you use any of the code below, it is at your own risk.
• Always backup systems and databases before performing any changes.

Firstly in Microsoft Azure you need to create a SQL server. To do this we had to manually create a database there and this creates the Server. We then deleted the database as we did not need it. This process will setup a server (and user/password) and provide an address

EG

somename.database.windows.net

To prepare your Sage CRM database you need to do the following

1. Move the SelfService tables to the CRM database ( by default they are in the database CRMSelfService)

This is done by running the following SQL (from the CRMSelfService database context) – this copies the visitor tables to the core CRM db
SELECT * INTO crm.dbo.visitor FROM visitor
SELECT * INTO crm.dbo.visitorProfile FROM visitorProfile

2. Within the CRM database change the view [vRegisteredVisitor]

DROPVIEW [dbo].[vRegisteredVisitor]

go

CREATE VIEW [dbo].[vRegisteredVisitor] AS

select v.*,  Comp_Name
from Visitor v, vSummaryPerson
where Pers_PersonId=Visi_PersonId

GO

3. Change store procedure eware_get_identity_id

You need to remove or comment out the WITH LOG option for RAISERROR command.

The reason for this is that Microsoft Azure does not like this and throws an error

Only System Administrator can specify WITH LOG option for RAISERROR command.

EG

BEGIN
RAISERROR (‘crm_new_id: No Table Found %s’, 16, -1, @table_name)
WITH LOG;
RETURN 0;
END

change to

BEGIN
RAISERROR (‘crm_new_id: No Table Found %s’, 16, -1, @table_name)
RETURN 0;
END

4. Change your CRM registry settings to point the SelfService to the CRM database and restart IIS (or the CRM app pool).

You should verify that the changes have not affected the CRM system.

Depending on any customization you have done that should be it.

Next you need to open up MS SQL Management Studio and select your CRM database.

Right click and select Tasks->Deploy Database to Azure SQL Database

and follow the wizard.

If you hit other issues the system will tell you.  In some instances we got most of the way, then had a problem and had to delete the database on Microsoft Azure and start the deploy process over. These notes should help you be in good shape to start.

So what has the experience been like with Sage CRM since then?

• Startup is slower than it was but day to day use has been normal.
• We have had one query timeout where I would not expect one.
• We also ran a patch (not a full update) and we had some issues with the SQL, in this not being compatible with Microsoft Azure’s SQL. The issue looks to be related to the WITH LOGS issue. This is a concern for a major update and maybe this has been resolved since 7.1 (the patch we ran was 7.1m).

From a Sage re seller business point of view though this is a game changer. You no longer have to try get clients to commit to buying an expensive SQL server license and instead they could use Microsoft Azure SQL (the costs for this range from free to hundreds per month but in our example its around $12 USD per month)

If you have any questions or anything just let us know!

 

**********The notes from Realisable’s upgrade experience start here *******

A few things we needed to do:

a. Prior to migrating the database we needed to convert the collation sequence to same collation at the ‘master’ database on the Azure database instance.

This meant for us, converting from ‘Latin1_General_CI_AS’ to ‘SQL_Latin1_General_CP_CI_AS’.

The reason for this is the login was failing with ‘You need to recreate the views’ message. This was due to the following an error in the SQL logs:

Jul 14 2017 11:55:19.997   3212 3768 1    fqopen,time,sql,errormsg   47     SELECT st.name AS DBColType, sc.length AS DBColLength, sc.name AS DBColName, so.name AS DBTableName, sc.xprec AS DBPrecision, Bord_TableID as tableId, CASE WHEN ISNULLABLE = ‘1’ THEN ‘true’ ELSE ‘false’ END as AllowsNull FROM syscolumns sc, sysobjects so, systypes st, Custom_Tables WHERE sc.id = so.id AND sc.xusertype = st.xusertype AND so.name = Bord_Name AND Bord_DatabaseID IS NULL ORDER BY so.name, sc.name

Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation

Converting the database collation is no easy feat! We needed to (scripts found on principally on StackOverflow, not created):

1. Create a script containing all the constraints – RecreateConstraints.sql
2. Drop all the indexes – DropIndexes.sql.
3. Drop all constraints – DropConstraints.sql
4. Convert all the columns in all the tables – ChangeCollation.sql (at the top of the script is the collation you need to change to).
5. Run the generated script from a. to regenerate the constraints.
6. I ran the ‘C:\Program Files (x86)\Sage\CRM\CRM\Setup\Scripts\RECREATE_INDEXES_MSSQL.sql’ to regenerate the indexes. (Not sure if we need to do this)
7. Change database collation using SQL Management studio, by going Properties and Options.

 

b.After migration we need to enable the SQL users to allow CRM to connect:

• Logon to converted database in Azure as the database administrator, ensuring that you are connected to the ‘master’ database.
• Enable the logon, as this may not be by default.ALTER LOGIN CRM ENABLE;
• We changed the password so that we were confident of the password being used.ALTER LOGIN CRM WITH PASSWORD = ‘a2b12dsfhrth12321345’
• Against the converted database in Azure we gave ‘db_owner’ rights to the CRM user.EXEC sp_addrolemember ‘db_owner’, ‘CRM’;

 

c. Alter the registry settings in the HKLM\Software\Wow6432Node\eWare\Config\<CRMInstance> per the coloured values below.

d. Restart IIS.

e. Login!

 

***James mentioned some SQL scripts above and below is the content (use at your own risk!!!!)

=====Change Collation=====

USE sagecrm;

begin tran

DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @max_length_str nvarchar(100);
DECLARE @is_nullable bit;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);

SET @collate = ‘SQL_Latin1_General_CP1_CI_AS’;

DECLARE local_table_cursor CURSOR FOR

SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N’IsUserTable’) = 1
ORDER BY [name]

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE local_change_cursor CURSOR FOR

SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, col.CHARACTER_MAXIMUM_LENGTH
, c.column_id
, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
JOIN INFORMATION_SCHEMA.COLUMNS col on col.COLUMN_NAME = c.name and c.object_id = OBJECT_ID(col.TABLE_NAME)
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@table) AND (t.Name LIKE ‘%char%’ OR t.Name LIKE ‘%text%’)
AND c.collation_name <> @collate
ORDER BY c.column_id

OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

WHILE @@FETCH_STATUS = 0
BEGIN

set @max_length_str = @max_length
IF (@max_length = -1) SET @max_length_str = ‘max’
IF (@max_length > 4000) SET @max_length_str = ‘4000’

BEGIN TRY
SET @sql =
CASE
WHEN @data_type like ‘%text%’
THEN ‘ALTER TABLE ‘ + @table + ‘ ALTER COLUMN [‘ + @column_name + ‘] ‘ + @data_type + ‘ COLLATE ‘ + @collate + ‘ ‘ + CASE WHEN @is_nullable = 0 THEN ‘NOT NULL’ ELSE ‘NULL’ END
ELSE ‘ALTER TABLE ‘ + @table + ‘ ALTER COLUMN [‘ + @column_name + ‘] ‘ + @data_type + ‘(‘ + @max_length_str + ‘) COLLATE ‘ + @collate + ‘ ‘ + CASE WHEN @is_nullable = 0 THEN ‘NOT NULL’ ELSE ‘NULL’ END
END
–PRINT @sql
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT ‘ERROR (‘ + @table + ‘): Some index or constraint rely on the column ‘ + @column_name + ‘. No conversion possible.’
–PRINT @sql
END CATCH

FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

END

CLOSE local_change_cursor
DEALLOCATE local_change_cursor

FETCH NEXT FROM local_table_cursor
INTO @table

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

commit tran

GO

=====Drop Constraints=====

Set NoCount ON

Declare @schemaName varchar(200)
set @schemaName=”
Declare @constraintName varchar(200)
set @constraintName=”
Declare @tableName varchar(200)
set @tableName=”

While exists
(
SELECT c.name
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN (‘D’,’C’,’F’,’PK’,’UQ’)
and t.[name] NOT IN (‘__RefactorLog’, ‘sysdiagrams’)
and c.name > @constraintName
)

Begin
— First get the Constraint
SELECT the
@constraintName=min(c.name)
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN (‘D’,’C’,’F’,’PK’,’UQ’)
and t.[name] NOT IN (‘__RefactorLog’, ‘sysdiagrams’)
and c.name > @constraintName

— Then select the Table and Schema associated to the current constraint
SELECT
@tableName = t.name,
@schemaName = s.name
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.name = @constraintName

— Then Print to the output and drop the constraint
Print ‘Dropping constraint ‘ + @constraintName + ‘…’
Exec(‘ALTER TABLE [‘ + @schemaName + N’].[‘ + @tableName + N’] DROP CONSTRAINT [‘ + @constraintName + ‘]’)
End

Set NoCount OFF

=====Drop Indexes=====

DECLARE @indexName VARCHAR(128)
DECLARE @tableName VARCHAR(128)

DECLARE [indexes] CURSOR FOR

SELECT          [sysindexes].[name] AS [Index],
[sysobjects].[name] AS [Table]

FROM            [sysindexes]

INNER JOIN      [sysobjects]
ON              [sysindexes].[id] = [sysobjects].[id]

WHERE           [sysindexes].[name] IS NOT NULL
AND             [sysobjects].[type] = ‘U’
–AND               [sysindexes].[indid] > 1

OPEN [indexes]

FETCH NEXT FROM [indexes] INTO @indexName, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
–PRINT ‘DROP INDEX [‘ + @indexName + ‘] ON [‘ + @tableName + ‘]’
Exec (‘DROP INDEX [‘ + @indexName + ‘] ON [‘ + @tableName + ‘]’)

FETCH NEXT FROM [indexes] INTO @indexName, @tableName
END

CLOSE           [indexes]
DEALLOCATE      [indexes]

GO

=====Recreate Constraints=====

— SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS.
declare @SchemaName varchar(100)
declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique_constraint varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)
declare @is_primary_key varchar(100)

declare CursorIndex cursor for
select schema_name(t.schema_id) [schema_name], t.name, ix.name,
case when ix.is_unique_constraint = 1 then ‘ UNIQUE ‘ else ” END
,case when ix.is_primary_key = 1 then ‘ PRIMARY KEY ‘ else ” END
, ix.type_desc,
case when ix.is_padded=1 then ‘PAD_INDEX = ON, ‘ else ‘PAD_INDEX = OFF, ‘ end
+ case when ix.allow_page_locks=1 then ‘ALLOW_PAGE_LOCKS = ON, ‘ else ‘ALLOW_PAGE_LOCKS = OFF, ‘ end
+ case when ix.allow_row_locks=1 then  ‘ALLOW_ROW_LOCKS = ON, ‘ else ‘ALLOW_ROW_LOCKS = OFF, ‘ end
+ case when INDEXPROPERTY(t.object_id, ix.name, ‘IsStatistics’) = 1 then ‘STATISTICS_NORECOMPUTE = ON, ‘ else ‘STATISTICS_NORECOMPUTE = OFF, ‘ end
+ case when ix.ignore_dup_key=1 then ‘IGNORE_DUP_KEY = ON, ‘ else ‘IGNORE_DUP_KEY = OFF, ‘ end
+ ‘SORT_IN_TEMPDB = OFF, FILLFACTOR =’ + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions
, FILEGROUP_NAME(ix.data_space_id) FileGroupName
from sys.tables t
inner join sys.indexes ix on t.object_id=ix.object_id
where ix.type>0 and  (ix.is_primary_key=1 or ix.is_unique_constraint=1) –and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
and t.is_ms_shipped=0 and t.name<>’sysdiagrams’
order by schema_name(t.schema_id), t.name, ix.name
open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName
while (@@fetch_status=0)
begin
declare @IndexColumns varchar(max)
declare @IncludedColumns varchar(max)
set @IndexColumns=”
set @IncludedColumns=”
declare CursorIndexColumn cursor for
select col.name, ixc.is_descending_key, ixc.is_included_column
from sys.tables tb
inner join sys.indexes ix on tb.object_id=ix.object_id
inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
inner join sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
where ix.type>0 and (ix.is_primary_key=1 or ix.is_unique_constraint=1)
and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
order by ixc.index_column_id
open CursorIndexColumn
fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn
while (@@fetch_status=0)
begin
if @IsIncludedColumn=0
set @IndexColumns=@IndexColumns + @ColumnName  + case when @IsDescendingKey=1  then ‘ DESC, ‘ else  ‘ ASC, ‘ end
else
set @IncludedColumns=@IncludedColumns  + @ColumnName  +’, ‘

fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
end
close CursorIndexColumn
deallocate CursorIndexColumn
set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else ” end
—  print @IndexColumns
—  print @IncludedColumns

set @TSQLScripCreationIndex =”
set @TSQLScripDisableIndex =”
set  @TSQLScripCreationIndex=’ALTER TABLE ‘+  QUOTENAME(@SchemaName) +’.’+ QUOTENAME(@TableName)+ ‘ ADD CONSTRAINT ‘ +  QUOTENAME(@IndexName) + @is_unique_constraint + @is_primary_key + +@IndexTypeDesc +  ‘(‘+@IndexColumns+’) ‘+
case when len(@IncludedColumns)>0 then CHAR(13) +’INCLUDE (‘ + @IncludedColumns+ ‘)’ else ” end + CHAR(13)+’WITH (‘ + @IndexOptions+ ‘) ON ‘ + QUOTENAME(@FileGroupName) + ‘;’

print @TSQLScripCreationIndex
print @TSQLScripDisableIndex

fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName

end
close CursorIndex
deallocate CursorIndex