Deployment

I ran across a forum post asking about moving multiple databases to Azure, so of course I Googled the asker’s question and found:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-migrate-your-sql-server-database

Since I might be doing a similar thing in the near future, I decided to see if I could push 4-5 small test databases to Azure SQLDB at the same time (SQL on Azure VM test may come later).  If this happens with my client it will be hundreds of databases, with the same schema but wildly varying sizes.

The “Plan”

• Create 5 sample databases
• Create .bacpacs on local drive
• Create Azure Logical Server if not already done (see step 4)
• Create a Notepad of SQLPackage.exe commands,  one for each .bacpac
• Run with “start” in CMD to fire off separate windows
• Wait.
• Enjoy.

I took the easy way out and created my 5 databases by creating 1 test database, backing it up and then restoring to Test2 – 5:

USE [master]
RESTORE DATABASE [AzureSQLDB_Test2] 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AzureSQLDB_Test1\AzureSQLDB_Test1.bak' 
WITH MOVE N'AzureSQLDB_Test1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AzureSQLDB_Test2.mdf',  
	 MOVE N'AzureSQLDB_Test1_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AzureSQLDB_Test2_log.ldf'
GO

USE [master]
RESTORE DATABASE [AzureSQLDB_Test3] 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AzureSQLDB_Test1\AzureSQLDB_Test1.bak' 
WITH MOVE N'AzureSQLDB_Test1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AzureSQLDB_Test3.mdf',  
	 MOVE N'AzureSQLDB_Test1_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AzureSQLDB_Test3_log.ldf'
GO

USE [master]
RESTORE DATABASE [AzureSQLDB_Test4] 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AzureSQLDB_Test1\AzureSQLDB_Test1.bak' 
WITH MOVE N'AzureSQLDB_Test1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AzureSQLDB_Test4.mdf',  
	 MOVE N'AzureSQLDB_Test1_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AzureSQLDB_Test4_log.ldf'
GO

USE [master]
RESTORE DATABASE [AzureSQLDB_Test5] 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AzureSQLDB_Test1\AzureSQLDB_Test1.bak' 
WITH MOVE N'AzureSQLDB_Test1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AzureSQLDB_Test5.mdf',  
	 MOVE N'AzureSQLDB_Test1_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AzureSQLDB_Test5_log.ldf'
GO

Create .bacpac files…basically just zip files of schema and data that Azure SQLDB can read.   You cannot do a traditional RESTORE DATABASE to an Azure logical server.   Right-click the database>>Tasks>>Export Data-Tier Application.  Then work through the GUI:

Browse for the location to place the .bacpac.   Remember this, as you will need it shortly.

Click Finish to create the file.

At this point, you need to make sure you have an Azure account and active Subscription.  You also need a Storage Account.   The Azure SQLDB lives on a Azure Logical server that has its own settings, firewall access rules, etc.   I am not going into those details as they are well documented in multiple places, and I know that your Google-Fu is strong!

SQLPackage.exe is a command line tool that is used to work with data-tier applications.   I’ve used it to extract schemas, do multiple schema compares at once, deploy changes to live databases, etc.   Read the first paragraph of the link…

I created a list of commands, one for each database, that I need to run to move my individual .bacpac files into Azure SQLDBs:

start SqlPackage.exe /a:import /tcs:"Data Source=myTestServer.database.windows.net;Initial Catalog=test1;User Id=YouCanCallMeAl;Password=LikeImmaLetYouSeeMyPassword_LOL" /sf:"C:\Users\Kevin3NF\Documents\SQL Server Management Studio\DAC Packages\AzureSQLDB_Test1.bacpac" /p:DatabaseEdition=Basic /p:DatabaseServiceObjective=Basic
start SqlPackage.exe /a:import /tcs:"Data Source=myTestServer.database.windows.net;Initial Catalog=test2;User Id=YouCanCallMeAl;Password=LikeImmaLetYouSeeMyPassword_LOL" /sf:"C:\Users\Kevin3NF\Documents\SQL Server Management Studio\DAC Packages\AzureSQLDB_Test2.bacpac" /p:DatabaseEdition=Basic /p:DatabaseServiceObjective=Basic
start SqlPackage.exe /a:import /tcs:"Data Source=myTestServer.database.windows.net;Initial Catalog=test3;User Id=YouCanCallMeAl;Password=LikeImmaLetYouSeeMyPassword_LOL" /sf:"C:\Users\Kevin3NF\Documents\SQL Server Management Studio\DAC Packages\AzureSQLDB_Test3.bacpac" /p:DatabaseEdition=Basic /p:DatabaseServiceObjective=Basic
start SqlPackage.exe /a:import /tcs:"Data Source=myTestServer.database.windows.net;Initial Catalog=test4;User Id=YouCanCallMeAl;Password=LikeImmaLetYouSeeMyPassword_LOL" /sf:"C:\Users\Kevin3NF\Documents\SQL Server Management Studio\DAC Packages\AzureSQLDB_Test4.bacpac" /p:DatabaseEdition=Basic /p:DatabaseServiceObjective=Basic
start SqlPackage.exe /a:import /tcs:"Data Source=myTestServer.database.windows.net;Initial Catalog=test5;User Id=YouCanCallMeAl;Password=LikeImmaLetYouSeeMyPassword_LOL" /sf:"C:\Users\Kevin3NF\Documents\SQL Server Management Studio\DAC Packages\AzureSQLDB_Test5.bacpac" /p:DatabaseEdition=Basic /p:DatabaseServiceObjective=Basic

Yes, that is a bunch of text for just 5 parameters.  Breaking it down:

• /a:import
• — import action to import the data and schema into Azure SQLDB
• /tcs:”Data Source=myTestServer.database.windows.net;Initial Catalog=test1;User Id=YouCanCallMeAl;Password=LikeImmaLetYouSeeMyPassword_LOL”
• — Server, Database, UserID and Password to connect to and create
• /sf:”C:\Users\Kevin3NF\Documents\SQL Server Management Studio\DAC Packages\AzureSQLDB_Test1.bacpac”
• — Source File that gets imported.  Use double quotes if your location has spaces in the path
• /p:DatabaseEdition=Basic
• — Basic, Standard, Premium (cost and performance from low to high)
• /p:DatabaseServiceObjective=Basic
• — Lots of options here if you chose Standard or Premium above.  Basic is just Basic.

Run these commands to import the .bacpacs:

Note that in my script I have ‘Start’ at the beginning.  What that does when I paste all 5 lines into a CMD window is fire off a new Window that then runs the SQLPacakage.exe command.   All 5 .bacpacs are then being executed simultaneously.   Without that Start, they will run one after the other.  Also, make sure you get the carriage return after the final command (line 6 in my code), or the 5th database will just sit there in the CMD window waiting for you to press enter.

My screen as they were running:

My SSMS after connecting to my Azure logical Server:

Note the Table_1 is there just to verify that the one database I actually changed came across properly.

That’s it.  They are there, ready for me to do whatever it is I plan to do with them

Some things to consider:

• Bandwidth:  Your internet connection will determine how fast this works (throughput and stability)
• Local or Azure storage: You can copy the .bacpacs to your storage account and run from there.   Adjust the /sf parameter accordingly
• Database size: There are limits to Azure SQLDB on size, performance, etc.   Do your research.
• Database performance tier: the more you want, the more you pay.  Again, do your research.
• Yes, I’m sure there are a number of other ways to do this, including Powershell options.   This is what I went to due to the GUI and simple nature of it.

That’s all I have for today…comments appreciated!

Thanks for reading.

Kevin3NF