Sign in Register
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Johan 50 posts 174 karma points
    May 09, 2014 @ 12:36
    Johan
    0

    Migrating SQLEXPRESS 100MB to bigger server Umbraco 4.11

    Migrating from v5 to v4 (and beyond)

    Hi all 

     

    I am forced to migrate to a bigger db server because my website is working to well :-) Must be because I am running it on Umbraco. It is hosted at crystone in Sweden on a classic webhotell with 100MB MS SQL db space available. What are my options?

    can I or would you guys advice me to:

    1) Migrate to My SQL?

    2) Move to my own VPS and continue to run SQLEXPRESS but with bigger allowance

    3) Move straight to full MS SQL server because that is what you guys allways do out there when hitting the 100MB mark?

    4) Anything else?

     

    In my process of exporting the the db to install it on my VPS the export is in someway corrupt. Trying to fix this but crystone MS SQL version is 2005 one and it still uses exec sp_dboption instead of the Alter Database commands. This I have been able to sort out but I still have one problem left:

     

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsContentType'

    that match the referencing column list in the foreign key 'FK_cmsContentTypeAllowedContentType_cmsContentType'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsContent' that

     match the referencing column list in the foreign key 'FK_cmsContentVersion_cmsContent_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsContent' that

     match the referencing column list in the foreign key 'FK_cmsContentXml_cmsContent_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsDataType' tha

    t match the referencing column list in the foreign key 'FK_cmsDataTypePreValues_cmsDataType_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 5

    There are no primary or candidate keys in the referenced table 'cmsContent' that

     match the referencing column list in the foreign key 'FK_cmsDocument_cmsContent_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 5

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsContentType'

    that match the referencing column list in the foreign key 'FK_cmsDocumentType_cmsContentType_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsDictionary' t

    hat match the referencing column list in the foreign key 'FK_cmsLanguageText_cmsDictionary_id'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 5

    There are no primary or candidate keys in the referenced table 'cmsContent' that

     match the referencing column list in the foreign key 'FK_cmsMember_cmsContent_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 5

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsContentType'

    that match the referencing column list in the foreign key 'FK_cmsMemberType_cmsContentType_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsContent' that

     match the referencing column list in the foreign key 'FK_cmsPreviewXml_cmsContent_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsContentType'

    that match the referencing column list in the foreign key 'FK_cmsPropertyType_cmsContentType_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

    Msg 1776, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    There are no primary or candidate keys in the referenced table 'cmsContentType'

    that match the referencing column list in the foreign key 'FK_cmsPropertyTypeGroup_cmsContentType_nodeId'.

    Msg 1750, Level 16, State 1, Server MYWORKSTATION\SQLEXPRESS, Line 4

    Could not create constraint. See previous errors.

     

     

    Is there a Identity switch I shoudl switch off or on first before running the big .sql file of commands to import the databas?

     

    These are the first commands to be run in the .sql fil. Are there any faults there?

     

    ALTER DATABASE MyUmbracoDB set AUTO_CLOSE OFF

    GO

     

    ALTER DATABASE MyUmbracoDB SET RECOVERY BULK_LOGGED WITH NO_WAIT

    GO

     

    ALTER DATABASE MyUmbracoDB SET RECOVERY SIMPLE

    GO

     

    ALTER DATABASE MyUmbracoDB set torn_page_detection off

    GO

     

    ALTER DATABASE MyUmbracoDB SET READ_WRITE

    GO

     

    ALTER DATABASE MyUmbracoDB SET MULTI_USER

    GO

     

     

    ALTER DATABASE MyUmbracoDB set auto_shrink ON

    GO

     

    ALTER DATABASE MyUmbracoDB set ANSI_null_default off

    GO

     

    ALTER DATABASE MyUmbracoDB set recursive_triggers off

    GO

     

    ALTER DATABASE MyUmbracoDB set ANSI_nulls off

    GO

     

    ALTER DATABASE MyUmbracoDB set concat_null_yields_null off

    GO

     

    ALTER DATABASE MyUmbracoDB set cursor_close_on_commit off

    GO

     

    ALTER DATABASE MyUmbracoDB SET CURSOR_DEFAULT GLOBAL

    GO

     

    ALTER DATABASE MyUmbracoDB set quoted_identifier off

    GO

     

    ALTER DATABASE MyUmbracoDB set ANSI_warnings off

    GO

     

    ALTER DATABASE MyUmbracoDB set auto_create_statistics on

    GO

     

    ALTER DATABASE MyUmbracoDB set auto_update_statistics on

    GO

     

    if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )

    ALTER DATABASE MyUmbracoDB set db_chaining off

    GO

     

     

    /J

    Copy Link
  • Jan Skovgaard 11280 posts 23678 karma points MVP 10x admin c-trib
    May 09, 2014 @ 13:45
    Jan Skovgaard
    0

    Hi Johan

    Hmm, how big is your website? Perhaps you could save some MB if you don't need the revision versions of each page perhaps? If so you could try to see how many MB's could be saved using the unversion package.

    Otherwise I would say go for MSSQL. However there could be some issues with migrating depending on, which version of MSSQL express you're using and what MSSQL version you want to upgrade to.

    /Jan

    Copy Link
  • Johan 50 posts 174 karma points
    May 09, 2014 @ 13:51
    Johan
    0

    Thanks Jan for your quick replay. My website is a little less than 1000 webpages published and I will definately  go for the unversion package and see what that can do for me.

     

    Haw you stopped supporting the MySQL database? I am building websites that have a growing potential so I am always thinking about future space requirements

    Copy Link
  • Jan Skovgaard 11280 posts 23678 karma points MVP 10x admin c-trib
    May 09, 2014 @ 13:53
    Jan Skovgaard
    0

    Hi Johan

    You're welcome - Hope it helps.

    Ehm...I think Umbraco HQ has decided to focus less on MySQL. It can be done but not without any trouble. So unless you have the skills to figure out how to make it work I would go for MSSQL :)

    (I'm just a friendly guy hanging around the forum trying to help people in my spare time btw.).

    /Jan

    Copy Link
  • Johan 50 posts 174 karma points
    May 09, 2014 @ 13:57
    Johan
    0

    actually I have a datastructure node setup inside my Umbraco. Only 900 pages ar publishe but I am using more than 4000 nodes in my Umbraco so there might be alot of space to save by using the unVersion package

    Copy Link
  • Dennis Aaen 4499 posts 18254 karma points admin hq c-trib
    May 09, 2014 @ 14:04
    Dennis Aaen
    0

    Hi Johan,

    Another package, there are similar to unVersion is F.A.L.M. Housekeeping, maybe it's worth to take a look at this package too.

    http://our.umbraco.org/projects/backoffice-extensions/falm-housekeeping

    Just my addition to Jan´s post about the package unVersion

    /Dennis

    Copy Link
  • Johan 50 posts 174 karma points
    May 09, 2014 @ 15:14
    Johan
    0

    Thanks Dennis I'll try this package as well. The UnVersion package did work very well on a development machine. But on my host provider I think the trouble is the transaction files and the database limit of space makes it impossible to execute . If I delete row by row from MS SQL Server management studio tools then I can execute but If I take to many rows the server replys with transcation files are full

    Copy Link
  • Johan 50 posts 174 karma points
    May 09, 2014 @ 22:33
    Johan
    0

    when checking the version history of every object I see there is a number of nodes on 35. Is that the max number of versions stored in Umbraco?

    Copy Link
  • Jan Skovgaard 11280 posts 23678 karma points MVP 10x admin c-trib
    May 09, 2014 @ 22:56
    Jan Skovgaard
    0

    Hi Johan

    Not that I know of - But I could be wrong here.

    /Jan

    Copy Link
  • Johan 50 posts 174 karma points
    May 10, 2014 @ 20:35
    Johan
    1

    I couldnt use UnVersion or F.A.L.M Housekeeping raght away because the db blocked it through complaining there is not enough space because the transactionlogg was full. But I did use the code from UnVersion package to set up my own function to delete one node version history at a time. Then it worked. I paste my code here for future help:

     

     

        public string CheckVersionHistoryOfThisNode(int nodeID, bool deleteThisone)
        {
     
            string nodename = "";
            string VersionIDLast = "";
            string output = "";
            string Connstr = System.Configuration.ConfigurationManager.AppSettings["umbracoDbDSN"].ToString();
     
            System.Data.SqlClient.SqlConnection objSqlConnection = new System.Data.SqlClient.SqlConnection(Connstr);
     
            System.Data.SqlClient.SqlDataReader objSqlDataReader;
     
            System.Data.SqlClient.SqlCommand objSqlCommand = new System.Data.SqlClient.SqlCommand();
            objSqlCommand.Connection = objSqlConnection;
            objSqlCommand.CommandType = System.Data.CommandType.Text;
            string SQL = "SELECT cv.VersionId,cv.VersionDate, d.published, d.text, d.newest ";
            SQL=SQL+" FROM cmsContentVersion cv LEFT OUTER JOIN cmsDocument d ON d.versionId = cv.VersionId  ";
            SQL = SQL + " WHERE cv.ContentId =  " + nodeID.ToString();
            SQL=SQL+" ORDER BY  cv.VersionDate DESC ";
            objSqlCommand.CommandText = SQL;

      

            objSqlConnection.Open();
            objSqlDataReader = objSqlCommand.ExecuteReader();
     
            int count = 0;
            while (objSqlDataReader.Read())
            {
                if (count == 0)
                {
                    VersionIDLast = objSqlDataReader["VersionId"].ToString();
                    nodename = objSqlDataReader["text"].ToString();
                }
     
                count = count + 1;
            }
            if (count > 10)
            {
                string Deletestr = "";
                if(deleteThisone){
                    Deletestr = " [DELETED] ";
                    Deletestr = Deletestr+deleteversionhistoryOfThisNode(nodeID, VersionIDLast);
                }
                output = "

    [" + nodename + "] [" + nodeID.ToString() + "] [" + VersionIDLast.ToString() + "]
    " + Deletestr +"  "+ count.ToString() + "

    ";
            }
            objSqlConnection.Close();
            objSqlDataReader.Close();
     
            return output;
        }
        protected string deleteversionhistoryOfThisNode(int nodeid, string VersionID)
        {
            string temp = "";
     
     
            string SQLDeletecmsPreviewXml = " Delete from cmsPreviewXml where nodeid="+nodeid.ToString()+" and Versionid not in('"+VersionID+"')";
            temp = temp+ExecutedeleteCMDOfThisNode(SQLDeletecmsPreviewXml);
     
            string SQLDeletecmsPropertyData = "Delete from cmsPropertyData where contentNodeID=" + nodeid.ToString() + " and Versionid not in('" + VersionID + "')";
            temp = temp + ExecutedeleteCMDOfThisNode(SQLDeletecmsPropertyData);
     
            string SQLDeletecmsContentVersion = "Delete from cmsContentVersion where contentid=" + nodeid.ToString() + " and Versionid not in('" + VersionID + "')";
            temp = temp + ExecutedeleteCMDOfThisNode(SQLDeletecmsContentVersion);
     
            string SQLDeletecmsDocument = "Delete from cmsDocument where  nodeid=" + nodeid.ToString() + " and Versionid not in('" + VersionID + "')";
            temp = temp + ExecutedeleteCMDOfThisNode(SQLDeletecmsDocument);

                                

            return temp;
        }
        protected string ExecutedeleteCMDOfThisNode(string SQL)
        {
            string temp = "";
     
            string Connstr = System.Configuration.ConfigurationManager.AppSettings["umbracoDbDSN"].ToString();
     
            System.Data.SqlClient.SqlConnection objSqlConnection = new System.Data.SqlClient.SqlConnection(Connstr);
     
            System.Data.SqlClient.SqlDataReader objSqlDataReader;
     
            System.Data.SqlClient.SqlCommand objSqlCommand = new System.Data.SqlClient.SqlCommand();
            objSqlCommand.Connection = objSqlConnection;
            objSqlCommand.CommandType = System.Data.CommandType.Text;

           

            objSqlCommand.CommandText = SQL;
     
            objSqlConnection.Open();
            objSqlCommand.ExecuteNonQuery();
            temp = "done! ";
            objSqlConnection.Close();

     

            return temp;
        }
     
    /J

    Copy Link
  • Jan Skovgaard 11280 posts 23678 karma points MVP 10x admin c-trib
    May 11, 2014 @ 01:10
    Jan Skovgaard
    0

    Hi Johan

    Thanks for sharing the above code - It makes sense you could not install any packages since the DB table was full now that you mention it...

    I suppose you could also have a peek at the umbracoLog table and remove old entries from that as well...perhaps that could be a starting point if others should come across the same thing.

    /Jan

    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft