So you’ve been running the Umbraco CMS for a while on MySQL, but you’ve been told you need to move it to Microsoft SQL Server. Does it sound intimidating? It doesn’t have to be. With the SQL script and instructions in this post, it’s not too bad. First, let’s start with an overview of the process.
Overview: 1. Use the Umbraco installer scripts to create blank Umbraco tables on your SQL server 2. Set up a linked database in SQL Server to your MySQL database 3. Migrate the data (using my script below) 4. Update your web.config to point to the new database
Step 1 - Create a new database on SQL Server First off, let’s hope you kept the original zip file you downloaded from the Umbraco site. Create a second IIS site and unzip the fresh Umbraco files there. Browse to the /install directory, and run through just enough steps to create a database on your SQL server. Once that database is in place, you can get rid of this new IIS site
Step 2 - Add MySQL database as linked There is much better information at CodeProject, but the basic idea involes installing the MySQL ODBC driver, add your MySQL server as a datasource, and add a ‘Linked Server’ in SQL Management Studio to your MySQL server as an ODBC connection.
Step 3 - Migrate Data You will want to customize the @dbName, @LinkedServerName, and @LinkedDbName variables appropriately, but the rest of it should work automatically. What’s happening here is that for each table we use default database collation for comparing strings, turn off IDENTITY_INSERT on tables with identity fields, and determine what fields uniquely identify each table. We then copy all data from the MySQL database to the SQL Server database, except for default records that already exist on tables in SQL Server.
DECLARE @collateOptions varchar(100) SET NOCOUNT ON
SET @dbName = ‘Umbraco’ SET @LinkedServerName = ‘Umbraco_old’ SET @LinkedDbName = ‘wesupport’
— FOREACHtable @tName in the @dbName database IF OBJECT_ID(‘tempdb..#umbTables’) ISNOTNULL DROPTABLE #umbTables SELECT TABLE_NAME INTO #umbTables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = @dbName ORDERBY TABLE_NAME WHILEEXISTS (SELECT TOP 1 TABLE_NAME FROM #umbTables) BEGIN SELECT TOP 1 @tName = TABLE_NAME from #umbTables
— Find outif the table has an identitycolumn SELECT @hasIdentity = OBJECTPROPERTY(OBJECT_ID(@tName), ‘TableHasIdentity’) — Allow us to mess with the identityfield explicitly SET @sql = ‘’ IF @hasIdentity = 1BEGIN SET @sql = ‘SET IDENTITY_INSERT ‘ + @tName + ‘ ON ‘ END
— Get a listoffieldnames — separated by commas SET @fieldNames = ‘’ IF OBJECT_ID(‘tempdb..#umbFields’) ISNOTNULL DROPTABLE #umbFields SELECT COLUMN_NAME INTO #umbFields FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = @dbName AND TABLE_NAME = @tName WHILEEXISTS (SELECT TOP 1 COLUMN_NAME FROM #umbFields) BEGIN SELECT TOP 1 @fName = COLUMN_NAME from #umbFields SET @fieldNames = @fieldNames + ‘ [‘ + @fName + ‘] ‘ DELETEFROM #umbFields WHERE COLUMN_NAME = @fName IFEXISTS (SELECT TOP 1 COLUMN_NAME from #umbFields) SET @fieldNames = @fieldNames + ‘ , ‘
— Figure outif this field — is an identityfield IF @hasIdentity = 1AND COLUMNPROPERTY(OBJECT_ID(@tName), @fName, ‘IsIdentity’) = 1 SET @IdentityFieldName = @fName END IF OBJECT_ID(‘tempdb..#umbFields’) ISNOTNULL DROPTABLE #umbFields
SET @cSQL = ‘SELECT @i = COUNT(*) FROM ‘ + @tName EXEC sp_executesql @cSQL, N’@iINTOUT’, @numBefore OUTPUT
— Get a listof primary keysintotemporarytable IF OBJECT_ID(‘tempdb..#umbPKeys’) ISNOTNULL DROPTABLE #umbPKeys CREATETABLE #umbPKeys (COLUMN_NAME sysname, DATA_TYPE varchar(50)) IFEXISTS (SELECT B.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME = @tName) INSERTINTO #umbPKeys SELECT B.COLUMN_NAME, C.DATA_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B INNERJOIN INFORMATION_SCHEMA.COLUMNSC ON B.TABLE_NAME = C.TABLE_NAME AND B.COLUMN_NAME = C.COLUMN_NAME AND B.TABLE_CATALOG = C.TABLE_CATALOG WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME = @tName ELSE — The table has no primary keys, so use — all fieldstomatchrecords INSERTINTO #umbPKeys SELECT B.COLUMN_NAME, B.DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS B WHERE TABLE_CATALOG = @dbName AND TABLE_NAME = @tName AND DATA_TYPE != ‘ntext’
— Foreach primary keyin this table WHILEEXISTS (SELECT TOP 1 COLUMN_NAME FROM #umbPKeys) BEGIN SELECT TOP 1 @pkFieldName = COLUMN_NAME, @pkFieldType = DATA_TYPE from #umbPKeys
SET @collateOptions = ‘’ IF @pkFieldType NOTIN (‘int’, ‘uniqueidentifier’, ‘smallint’, ‘tinyint’, ‘bigint’, ‘bit’, ‘datetime’) SET @collateOptions = ‘ COLLATE DATABASE_DEFAULT ‘
IF OBJECT_ID(‘tempdb..#umbPKeys’) ISNOTNULL DROPTABLE #umbPKeys
DELETEFROM #umbTables WHERE TABLE_NAME = @tName SET @IdentityFieldName = ‘’ END
— Cleanup IF OBJECT_ID(‘tempdb..#umbTables’) ISNOTNULLDROPTABLE #umbTables SET NOCOUNT OFF
4. Update web.config Go back to your original website and, after backing up your web.config file, open it up and find the setting under Configuration - AppSettings - umbracoDbDSN. Update it so it looks like this, but have it all on a single line (with your settings, of course):
Be sure to clear your web browser’s cache after doing all of this to make sure that it picks up a fresh copy of your site. And you should now have it connected to SQL Server. If you want, you can remove the linked server in SQL Management Studio to clean things up.