Migrate SharePoint Database from SQL Server 2012 backwards to SQL Server 2008 R2

Last week we were faced with the situation that we had to deploy a Database for SharePoint from another Service Provider.
No problem at all, just download the Backup-File, Restore it to the SQL Server Instance and mount it into SharePoint.

Simply: NO.

In our case we were provided with a SQL Server 2012 Database. It’s not possible to restore a Database from a SQL Server Instance running “SQL Server 2012” into a SQL Server Instance running “SQL Server 2008 R2”.

But we had to find a solution to this problem.

First thought was to choose
Database -> right mouse click -> Tasks -> Export data tier.

So we tested it with an existing database in a SQL Server Instance running SQL Server 2012. But we had no luck, the database contained views that referenced objects from system databases.

So we tried with another database. Still no luck as this one used extended properties, the ones that are used to describe what specific use this very column in that specific table has.

But there had to be a way to solve the problem.

That’s where the SQL Database Migration Wizard comes into play.
SQL Database Migration Wizard is a tool that you can download from Codeplex: https://sqlazuremw.codeplex.com/.
Fun fact: The name still references an older name of SQL Database. For the full history of names go to http://en.wikipedia.org/wiki/SQL_Azure.

Be careful to download the corresponding version to your SQL Server version:

  • v3x: SQL Server 2008 R2 SP1
  • v4x: SQL Server 2012
  • v5x: SQL Server 2014

Just download the right version for you, copy it to your Management Machine and start it from “SQLAzureMW.exe”.
The Wizard itself is really self-explanatory and there are a dozen blog posts out there how to use this wizard,
e.g. Grant Fritchey’s from May 2011: SQL Azure Migration Wizard to migrate OnPremise Databases to Microsoft Azure
or by Sascha Dittmann as part of a Backup strategy from Azure to OnPremise: Datensicherung unter SQL Azure
As you can see, all directions are possible, even
Azure -> Azure
and
OnPremise -> OnPremise.
That’s what makes the SQL Database Migration Wizard so powerful.

As mentioned in the Blog post title we had to deal with a SharePoint Database that we wanted to “restore” on another OnPremise SQL Server Instance.

This is where i want to point out the secrets that you have to be aware of in this very special case.

Disk where you start the SQL Database Migration Wizard from

Make sure that you place the SQL Database Migration Wizard on a drive that has at least double the amount of free space available of the database you want to migrate. This is because SQL Database Migration Wizard creates some temporary objects along with the scripted data. You do not want your SQL Server Instance to stop because you placed the SQL Database Migration Wizard on your C: drive and run out of space!

NotSupportedByAzureFile.Config

By default this file contains all that stuff that SQL Database is not capable of or simply does not support,
e.g. Fill Factor with Indexes, BULK INSERT and so on.

As you know that your OnPremise SQL Server Instance supports everything, you just have to comment all the exceptions (in theory). Under pressure i was not even able to build a valid XML file so i erased all exceptions and came up with a file like this:

<?xml version="1.0"?>
<TSQLNotSupportedByAzure xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <DefaultMessage>{0} is not supported in current version of SQL Azure</DefaultMessage>
    <Skip>
        <SupportedStatement Text="SET ANSI_NULLS ON" />
        <SupportedStatement Text="SET QUOTED_IDENTIFIER ON" />
    </Skip>
    <Table>
        <TableStatement>
            </TableStatement>
    </Table>
    <Index>
        <IndexOptions>
        </IndexOptions>
    </Index>
    <Schema>
        <SchemaChecks>
            </SchemaChecks>
    </Schema>
    <View>
        <ViewStatement>
        </ViewStatement>
    </View>
    <GeneralTSQL>
        </GeneralTSQL>
    <ActiveDirectorySP>
        </ActiveDirectorySP>
    <BackupandRestoreTable>
    </BackupandRestoreTable>
    <ChangeDataCapture>
    </ChangeDataCapture>
    <DatabaseEngineSP>
    </DatabaseEngineSP>
    <DatabaseMailSP>
    </DatabaseMailSP>
    <DatabaseMaintenancePlan>
    </DatabaseMaintenancePlan>
    <DataControl>
    </DataControl>
    <DistributedQueriesSP>
    </DistributedQueriesSP>
    <FullTextSearchSP>
    </FullTextSearchSP>
    <GeneralExtendedSPs>
    </GeneralExtendedSPs>
    <IntegrationServicesTable>
    </IntegrationServicesTable>
    <LogShipping>
    </LogShipping>
    <MetadataFunction>
    </MetadataFunction>
    <OLEAutomationSP>
    </OLEAutomationSP>
    <OLEDBTable>
    </OLEDBTable>
    <ProfilerSP>
    </ProfilerSP>
    <ReplicationSP>
    </ReplicationSP>
    <ReplicationTable>
    </ReplicationTable>
    <RowsetFunction>
    </RowsetFunction>
    <SecurityFunction>
    </SecurityFunction>
    <SecuritySP>
    </SecuritySP>
    <SQLMailSP>
    </SQLMailSP>
    <SQLServerAgentSP>
    </SQLServerAgentSP>
    <SQLServerAgentTable>
    </SQLServerAgentTable>
    <SystemCatalogView>
    </SystemCatalogView>
    <SystemFunction>
    </SystemFunction>
    <SystemStatisticalFunction>
    </SystemStatisticalFunction>
    <Unclassified>
    </Unclassified>
</TSQLNotSupportedByAzure>

SQLAzureMW.exe.config

This configuration file does contain not only the L10N strings for the GUI, but also some Application Settings, starting with line 207 πŸ˜‰

DBCollation

The most important key that you have to specify is

<add key="DBCollation" value="Latin1_General_CI_AS_KS_WS"/>

because SharePoint needs this collation for all of it’s databases.
If you do not specify the DBCollation, the SQL Database Migration Wizard will simply not set this property for the Database and therefore the Default Collation of the SQL Server Instance will be set for the Database that you migrate. Trust me, i have done this once and will never forget about it, as a 30 GB Database will take some time to migrate :-/

BCPFileDir

Another important key is

<add key="BCPFileDir" value="E:\BCPData"/>

because this is where your scripted data will reside and surely you remember that you do not want to fill up your system drive πŸ˜‰

ScriptTableAndOrData

I recognized in version v4.15.5 that you can choose to just script the SCHEMA or both SCHEMA and DATA or DATA only.
So set this key according to the action you want to perform.
In our case it’s ScriptOptionsTableSchemaData of course πŸ˜‰

<add key="ScriptTableAndOrData" value="ScriptOptionsTableSchemaData"/>

Let’s do the migration

Once you payed attention to these important points, you can start the SQL Database Migration Wizard by executing the exe file.

  • On the first screen hit the radio button “Analyze / Migrate: Database” and click “Next >”.
  • Enter the Source-SQL Server Instance information along with your Authentication credentials, specify the Source Database by it’s name and click “Connect”.
  • Select the Database and click “Next >”
  • Hit the radio button “Script all database objects” and click “Next >”
  • The “Script Wizard Summary” just shows what it’s named for. Click “Next >”
  • You are prompted with the Question: “Ready to generate SQL script?” Click “Yes” and help yourself to some coffee πŸ˜‰
  • After a few seconds or some hours, the blue progress bar on top will hit the right border of the wizard and below the progress bar the simple hint “Done!” will appear. You now have the possibility to save the logged information to your hard disk.
  • Click “Next >”
  • Enter the Destination-SQL Server Instance information along with your Authentication credentials, leave the radio button next to “Master DB (List all databases) and click “Connect”.
  • You might now select an existing Database that you want to migrate your Source Database into. Otherwise click “Create Database”
  • Enter the Database Name and be lucky, that you have specified the “SharePoint Collation” in the file “SQLAzureMW.exe.config” because you do not really want to scroll down the whole list of available Collations and pick the right one by accident ;-). Click “Create Database”.
  • Click “Next >”.
  • You are prompted with the question: “Execute the script against destination server”? Of course you want to: click “Yes”.
  • Once again the blue progress bar flushes the screen and you read the four letters “Done!”.
  • Again you have the chance to save the logged information to your hard disk.
  • As you have reached the final screen of the Wizard, klick “Exit”.

Now you have migrated a database running on a SQL Server 2012 Instance into a SQL Server 2008 Instance.

Be careful!

Technically i bet you can “downgrade” a Database from SQL Server 2014 to SQL Server 2005. But keep in mind that from one version to the next some things might have changed within SQL Server.
SQL Server is for good reason not backward compatible.
Some names of DMVs might have changed, the call of RAISERROR has changed.
I even think that it’s not possible to migrate assemblies that you have written in C# and deployed into SQL Server using the CLR. This is not proven, just a thought.
So please be careful and test all of the functionality that the Application that uses the Database provides is still working.

I hope you enjoyed this blog post,
Thanks for reading, Christoph

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s