Moving schema with federation is still a manual process today, however with SQL Server 2012 tools you can move data between federation in separate servers. One easy way to move data is to use the Import and Export Data Wizard. IEDW does not natively understand federations natively but you can use it to export and import data with federation members or the root as the source or the destination. Given the tool only understand database scope, you need to repeat the set of steps below for each member and the root database to capture the full collection of data in federations. Lets walk through.
Here is how I do it for one member;
Step #1: Source - I use the SQL Server .Net client as the driver. ODBC would also work against SQL Azure. Note the source db name is system-GUID which is the native name of the member database.
The way to get that name of the members is simple;
Iterating over all members is also easy; To get to the next member,
Let get started with the IEDW. To get started with the Import and Export Wizard (IEDW) simply search for “Import and Export Wizard” under Programs. The first dialog that needs your input will be the “Choose Data Source” dialog.
You will need to provide the full connection information that you would like to export. Here we will use the db name from the source member in the initial catalog field. Then click next!
Step #2: Destination - The destination db can be a member or a regular database. In our case it is a regular database with the name “db1”. Destination could be a member too which would have a system-GUID name. The IEDW simply does not care about this since all we will do is move data with it.
Step #3: Mappings - There is one general gotcha in I/E wizard; it creates heap tables, imports data and then creates the indexes. SQL Azure does not like heap tables so you have 2 options;
- Have the schema ready on the destination (you can use generate script wizard to do that). USE IEDW just to move data.
- Edit the tsql in the mapping dialog so primary key constraints are in there in the initial create table.
Step #4: Run - Final step is just click finish and watch the rows fly.
That is it. This will move the data in a single db. You can repeat the process for the root db as well. simply provide the right db names and things will work.