If you have a bunch of databases that you want to migrate into a single Azure SQL DB, and the table names are all different then that’s quite easy. If you don’t mind renaming things, then there’s a solution for same-named tables too…
Add/migrate one database
Head over to the Azure SQL section https://portal.azure.com/#create/Microsoft.AzureSQL
Create a new DB of whatever spec you require here’s some instructions.
Note, if you choose public endpoint, remember to add the IP address of the machine you’ll be using to do the migration – there is a checkbox.
Remember to save your new username and password!
Next, grab your connection strings when your new DB is ready in the new resource:
Download and install Data Migration Assistant https://www.microsoft.com/en-us/download/details.aspx?id=53595
It’s best to do an assessment first.
You will need to put in connection/credentials to your source database. Let this run to identify any problems before you try your migration.
Now do a new migration project. Enter your source and new target database in azure (you have the connection details already).
Select all the tables in your db you want to migrate, generate the script, deploy the schema, then if there are no errors, you can migrate the data.
After this you can see your new tables and data in SSMS (download here) with the same connection string as earlier.
If you have errors at the schema stage did you run the assessment first? The problems are usually thrown up there and will need resolving first.
Add more databases
As mentioned, if your other databases you want to combine have different table names then this isn’t too hard, you can just start a new migration and change the source, but keep the same destination. You’ll run into problems if there are existing filenames, primary keys, defaults, dependencies.
One solution – rename them
If you’re, for example, using .NET and Entity then you can rename your tables, then simply add the new table name in the entity definition, for example, if we add a prefix of newprefix_ to all the tables, then we can just add/edit this line in the context’s cs file, example:
The format is:
entity.ToTable("newprefix_TableName");
Don’t forget constraints!
Do your migration but when the script is generated, pause here. We need to rename all the constraints. Copy the script into an editor and do some search and replace:
PK_ for PK_newprefix_
DF_ for DF_newprefix_
FK_ for FK_newprefix_
Then you can deploy and you won’t have problems with clashes.
After your Migration (schema and data!) is complete for each database, in SSMS do the following before importing the next one, for each table in your import:
EXEC sp_rename 'TableName', 'newprefix_TableName';
Once everything has its new name, run your next import, rinse and repeat. It’s a bit time-consuming if you have a lot of tables mind.
Of course this assumes you have a pretty simple database, as far as stored procedures, views, etc, there may be problems, but if you get schema errors, sp_rename is your friend!
Why do this?
We had a bunch of small, low activity databases that we wanted to consolidate into the cheaper hosting solution of a single Azure SQL DB, compared to an entire EC2 on AWS dedicated to them, and I couldn’t figure out a good way of doing this, as they all had the same table names in each db! With this solution the code using everything only needed minimal work and the only hard part was the tedious work in all the renaming!
Comments