Migrating SQLEXPRESS 100MB to bigger server Umbraco 4.11
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
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.
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
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.).
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
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
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;
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.
Migrating SQLEXPRESS 100MB to bigger server Umbraco 4.11
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
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
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
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
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
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
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
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?
Hi Johan
Not that I know of - But I could be wrong here.
/Jan
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:
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
is working on a reply...