Setting Up Sitefinity 7 and Amazon RDS Instance ( SQL Server )

Recently I had the need to setup a new Sitefinity 7.0 Instance, but I decided to use the Amazon RDS, just so it could be more cost effective to the client. I must say I'm very happy with the results so far.  I'm quite sure there is a certain degree of caching at Amazon Level, because the results I'm having in delivering pages from Sitefinity are quite superb.

What is Amazon Relational Database Service

Amazon Relational Database Service or Amazon RDS is a distributed relational database service by Amazon.com. It is a web service running "in the cloud" and provides a relational database for use in applications. It is aimed at simplifying the set up, operation, and scaling a relational database. Complex administration processes like patching the database software, backing up databases and enabling point-in-time recovery are managed automatically. Scaling storage and compute resources can be performed by a single API call. Amazon RDS was first released on 22 October 2009 supporting MySQL databases. This was followed by Oracle Database in June 2011, Microsoft SQL Server in May 2012 and PostgreSQL in November 2013.

Assumptions

1. I'm going to assume you have a WebServer EC2 already setup.
2. I'm also going to assume that you have create and RDS Instance, in our case we are using the SQL Server engine.

The Setup I'm using its EC2 with RDS

• So we have to create New RDS Instance
• We have to Setup the relevant Firewall Settings, including opening the port 1433.

Now you will need to try the connection, to do this you connect to your database Instance EndPoint as you would normally connect to your Local DB but with the appropriate credentials, using the SQL Server management studio ( if you cannot connect make sure you have opened the port in the security group ).

• Migrate The Data, With the SQLAzure Tool

So here is where things start to be different.  You cannot just do a restore of SQL Server database at this moment in time ( a bit of a shame ), however there is a tool called SQLAzureMW which works wonderfully.

So just follow the wizard to move data from Server A to the New RDS, the tool will move most or all the Data and Tables across nicely, if you you don't encounter any problem.  In our case we encountered a problem which I'm going to explain.

• SF_CHUNKS PROBLEM

In my scenario I moved an existing website,  in our case the images were being stored in the DB, so this created a problem for me, as the sf_Chunks table failed to copy across, so it felt as if  there was a bit of data corruption.

So I devised a way to migrate the chunks From Server A to the New RDS, 

• THE SOLUTION

The solution is devised into 2 parts.  Because we are working with 2 different SQL Enviroments you need to Setup a Linked Server, below is the code I used to create this

• 1) SETUP LINKED SERVERS

SQL: Create Linked Servers

EXEC sp_addlinkedserver
@server = 'TempDB',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=myinstance.us-east-1.rds.amazonaws.com;UID=myuser;PWD=mypassword;'

• 2) CHUNKS_MIGRATION_SCRIPT

Now that we have created a Linked Server, this means from your local database you can query your remote database instance, it is at this point that you will be running this script below.

The way I managed to solve my problem was to move 1000 records at the time, so all you got to do is change the following line BETWEEN 1 AND 1000, so if you have 10000, I moved 1000 records at the time.

SQL: Chunk Migration
---------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE
    
        @RANDOM_GEN_NO        AS VARCHAR(50),
        @TEMP                                                AS VARCHAR(50),
        @sze    int ,
        @ordinal    int,
        @file_id    uniqueidentifier ,
        @dta    varbinary(max) ,
       @voa_version    smallint



DECLARE EMP_CURSOR CURSOR FOR

WITH sf_chunks AS
(
        SELECT sze,ordinal,file_id,dta,voa_version,
        ROW_NUMBER() OVER (ORDER BY sze) AS 'RowNumber'
        FROM HANCOCK_LIVE.dbo.sf_chunks
)


SELECT * FROM sf_chunks WHERE RowNumber BETWEEN 10001 AND 10197;

OPEN EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR
    INTO @sze,@ordinal,@file_id,@dta,@voa_version,@TEMP
WHILE (@@FETCH_STATUS = 0)
BEGIN
     
INSERT INTO [TempDB].[LIVEDB].[dbo].[sf_chunks]
                     ([sze]
                     ,[ordinal]
                     ,[file_id]
                     ,[dta]
                     ,[voa_version])
         VALUES
                     (
        
                 @sze
                 ,@ordinal
                 ,@file_id
                 ,@dta
                 ,@voa_version

             )

        FETCH NEXT FROM EMP_CURSOR
        INTO @sze,@ordinal,@file_id,@dta,@voa_version,@TEMP
END

CLOSE EMP_CURSOR
DEALLOCATE EMP_CURSOR

SET NOCOUNT OFF
---------------------------------------------------------------------------------------------------------
       
So far I'm very pleased with the end result, its fast and easy to backup, using the RDS tools, and comes across as a cost effective solution.