0

I want to migrate all of my databases into one Azure SQL database.

All of my databases have the same schema, and I want to generate some generic method or code to do it automatically.

For every database, it will create a new schema in the Azure SQL, and create all of the databases into that schema and copy all the data into it.

The databases are quite small so at first I was thinking about DMA (https://www.microsoft.com/en-us/download/details.aspx?id=53595), but it can not help me with multiple databases to one.

I was thinking trying to write a dynamic query to run on every database in the Instance and generate the relevant code and extracting it onto the Azure SQL via Linked server or OpenQuery/OpenRowSet.

Maybe I can use Powershell dbaTools?

I'm not very familiar with that but I've heard it can do magic( :-) )

What do you think?

Thanks Nathan

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nathan L
  • 31
  • 6
  • 1
    What is your end question here? You say you're thinking about using PoSh, does that mean you've written a script but it's not working, or you're stuck at a specific point? – Thom A Nov 03 '19 at 10:24
  • I'm trying to think on the best generic method to do it. – Nathan L Nov 03 '19 at 10:31
  • Asking what is "best" is off-topic for Stack Overflow; it's a place for asking specific programming questions, rather than abstract ones. If you've written something and it's failed we can certainly help you with that, but here isn't the right place to ask a "what can I use", or "what's best". – Thom A Nov 03 '19 at 10:34
  • I stand correct – Nathan L Nov 03 '19 at 10:38
  • How to migrate SQL Database to Azure: https://www.google.com.au/search?newwindow=1&ei=WjS_XfD0IoK89QOx27aACA&q=migrate+sql+server+to+azure&oq=migrate+sql+server+to+azure&gs_l=psy-ab.3..0l6j0i8i67.57994.60632..61378...0.2..0.241.1453.0j6j2......0....1..gws-wiz.......0i71j0i67.-n-3xn45V1M&ved=0ahUKEwjwiezk7M7lAhUCXn0KHbGtDYAQ4dUDCAo&uact=5 How to change schema of a database: https://stackoverflow.com/questions/17571233/how-to-change-schema-of-all-tables-views-and-stored-procedures-in-mssql Note: "Click and forget" solution for multiple databases will be quite complex. – Alex Nov 03 '19 at 20:15
  • thanks a lot Alex, you really gave me some new ideas – Nathan L Nov 05 '19 at 13:44

2 Answers2

3

According my experience and I searched a lot:

Since all of your databases have the same schema, you should first think about merge these data into one database, then deploy the database to Azure SQL Server. Azure SQL Server will help you create the database.

DMA, DMS or other tool do not support merge databases for now.

You can reference these blogs which is very helpful for you:

  1. How to merge two databases in SQL Server?
  2. Merge Multiple Databases into a Single Database
  3. Transfer data from one database to another database

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • thanks a lot, I'm sure am going to test this approach. I'm still trying to find some way to automate this, but this is a really good idea. thank you :-) – Nathan L Nov 05 '19 at 13:46
  • @NathanL You're welcome. I also hope you can find a good way to automate this. That's a requirement which many cases have put forward. Can you please accept(mark) it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.)?. This can be beneficial to other community members. Thank you. – Leon Yue Nov 06 '19 at 01:18
2

I've finally did it with the help of powershell script (and DBATools module) and the use of SQLCMD scripts.

I tried to do the database merge at first, but it didn't work on the schema I was working on. The trick was the each database must be unique for itself, so I have to transfer each and every table/view/procedure/function to a new schema and all of the object reference to relate to this new schema. There were no room for table merging nor data merge. each and every object must stay unique with its own data to transfer it to one Azure SQL Database.

So I had to set up some rules. One schema. the database schema shouldn't be changed. All the databases are with the same schema so I can use the same DDL script to create each and every object in the new database.
I used SQLCMD and used a parameter( $(NewSchema) ) in the script that I can create a new schema for each set of object and can control every object to relate to others in the same schema.

For the data transfer I wanted to use INSERT INTO [LinkedServer]...[Tablename] but, it didn't work too well because of Identity column in the tables. I could not manipulate it using "Set Identity On" because you have to be on the same session on the server in order to do this. Couldn't do it using Linked Server. Another issue I faced was a bunch of constraints on the database and foreign keys.

So I had to solve this issue in a different manner. PowerShell module DBATools come to the rescue. Running on every table in the database exporting out only the data as a script with the help of Export-DbaScript, storing it into a variable, replacing the schema name and running it on the remote server. The script already created with SET IDENTITY_INSERT On for each table. For the constraints, I used an SQLCMD script again to ALTER TABLE NOCHECK CONSTRAINT all constraints for the current schema and then used it to turn it back on (after all the data copied).

I rapped everything in a PowerShell script that gets parameters for all of the connections and databases and just do the job.

I'm pretty sure there are better ways of achieving this goal, but this one worked for me.

I'll love to here from you, what do you think of the solution I came out with?

I'll be happy to share scripts, if anyone asks me too.

Nathan

Alex
  • 4,885
  • 3
  • 19
  • 39
Nathan L
  • 31
  • 6